MySQL ERROR 2013 (HY000): Lost connection to MySQL server during query

Question

The problem: Importing a 2GB dump file into MySQL 5.6 always crashes with ERROR 2013. There are several answers on StackOverflow about how to deal with this, but I have implemented all the suggested fixes and the error persists. I'm looking for further suggestions.

The dump file is generated using:

/opt/local/lib/mysql56/bin/mysqldump --login-path=local --opt --events --all-databases >/Volumes/Documents/dbBackup/mysql.dump

It is read back using

mysql -u root -p
Enter password:
source /Volumes/Documents/dbBackup/mysql.dump

It fails after about 50,000 queries with

ERROR 2013 (HY000): Lost connection to MySQL server during query
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/opt/local/var/run/mysql56/mysqld.sock' (61)
ERROR: 
Can't connect to the server

I am using the default my.cnf, modified as follows (these parameters are those recommended by various posts. Note that the restore is though localhost - only one server is involved - and that the timeouts are set to 1 day (!) and the buffer sizes to 1GB (the server is an Apple Mac Mini with 16 GB of RAM))

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
query_cache_type=1
query_cache_size=64MB
net_write_timeout=6000
net_read_timeout=6000
net_buffer_length=64M
max_allowed_packet=1G
connect_timeout=600
innodb_file_per_table=off

What should I try next?

[Later]

Here are the last lines from the error log:

2017-11-06 10:14:04 67090 [Note] /opt/local/lib/mysql56/bin/mysqld: ready for connections.
Version: '5.6.34-log'  socket: '/opt/local/var/run/mysql56/mysqld.sock'  port: 0  Source distribution
2017-11-06 10:17:30 17224b000  InnoDB: Assertion failure in thread 6209974272 in file fsp0fsp.cc line 3281
InnoDB: Failing assertion: not_full_n_used >= descr_n_used
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
10:17:30 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68101 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fcca400a800
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 17224ae68 thread_stack 0x40000
0   mysqld                              0x000000010e033fd9 my_print_stacktrace + 61
1   mysqld                              0x000000010de84fc2 handle_fatal_signal + 696
2   libsystem_platform.dylib            0x00007fff90621f1a _sigtramp + 26
3   ???                                 0x00000001722450a0 0x0 + 6209949856
4   libsystem_c.dylib                   0x00007fff914ff9a3 abort + 129
5   mysqld                              0x000000010e0c5e85 _ZL34fseg_find_last_used_frag_page_slotPhP5mtr_t + 0
6   mysqld                              0x000000010e0c56db _Z14fseg_free_stepPhP5mtr_t + 864
7   mysqld                              0x000000010e05b124 _Z21btr_free_but_not_rootmmm + 242
8   mysqld                              0x000000010e09142c _Z20dict_drop_index_treePhP5mtr_t + 176
9   mysqld                              0x000000010e189ee1 _Z12row_upd_stepP9que_thr_t + 988
10  mysqld                              0x000000010e14f796 _Z15que_run_threadsP9que_thr_t + 522
11  mysqld                              0x000000010e14ffc4 _Z12que_eval_sqlP11pars_info_tPKcmP5trx_t + 341
12  mysqld                              0x000000010e16dc27 _Z24row_drop_table_for_mysqlPKcP5trx_tbb + 1831
13  mysqld                              0x000000010e0f3730 _ZN11ha_innobase12delete_tableEPKc + 466
14  mysqld                              0x000000010ddc8209 _Z15ha_delete_tableP3THDP10handlertonPKcS4_S4_b + 181
15  mysqld                              0x000000010df65cc0 _Z23mysql_rm_table_no_locksP3THDP10TABLE_LISTbbbb + 1784
16  mysqld                              0x000000010df65586 _Z14mysql_rm_tableP3THDP10TABLE_LISTcc + 621
17  mysqld                              0x000000010df122d5 _Z21mysql_execute_commandP3THD + 4913
18  mysqld                              0x000000010df105b3 _Z11mysql_parseP3THDPcjP12Parser_state + 733
19  mysqld                              0x000000010df0df68 _Z16dispatch_command19enum_server_commandP3THDPcj + 1129
20  mysqld                              0x000000010df1004e _Z10do_commandP3THD + 217
21  mysqld                              0x000000010ded25bf _Z24do_handle_one_connectionP3THD + 350
22  mysqld                              0x000000010ded2454 handle_one_connection + 59
23  mysqld                              0x000000010e1f633f pfs_spawn_thread + 311
24  libsystem_pthread.dylib             0x00007fff9035205a _pthread_body + 131
25  libsystem_pthread.dylib             0x00007fff90351fd7 _pthread_body + 0
26  libsystem_pthread.dylib             0x00007fff9034f3ed thread_start + 13

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7fcca30d6210): is an invalid pointer
Connection ID (thread ID): 1
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

Answer 1

This is more serious than the usual 2013 error: here, the server is crashing. If it was the buffer, you'd see a connection lost followed by a successful reconnect.

To try to avoid the issue, you might try and split the restore in several files. As long as the server is not in use by anyone else, that ought to work.

To investigate what is happening, you want to look at the error log, and you might want to run mysqld under strace by checking mysqld's PID with ps and capturing operations with sudo strace -p PID -f. You'll get oodles of output and will only be interested in the last 100 lines or so, so "strace... | tail -n 1000 > output.log" ought to do it.

In your case MySQL attempts to suggest something:

InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

So, try checking the suggested link.

This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.

Verify that the package is updated and the system is working properly. Otherwise, I'm afraid you need to strace the process and hope the error becomes clearer.

Answer 2

Problem solved, although without knowing exactly what caused it. I uninstalled the MySQL server completely, deleted all the tables, reinstalled the server, re-initialised it, and restored the database dump. This time, the restore was successful. I assume, as @LSemi suggested, that there was corruption in the InnoDB tablespace.