I am working on a MySQL DB transfer (InnoDB tables) from a production server to a local development machine. The dump file is generated via phpMyAdmin and is about 1.6GB in size. The import process seems to work fine up until the very end when it fails with "ERROR 2013 (HY000): Lost connection to the MySQL server during query." I have run down the likely timeout culprits to no avail. At this point, I have isolated the problem to 3 tables of various sizes (# of fields and row count). If I create a fresh dump file without those 3 tables, everything imports 100% fine. I have also tested importing each of the 3 suspect table separately and each one fails.
Digging a bit more, I have been able to determine that the INSERT portion of the dump file runs fine. The problem lies with the ALTER TABLE statement that sets up the indexes. If I remove the two ALTER statements at the end of the dump file the import completes without issue. I also ran the ALTER TABLE statement manually and noticed that the error is thrown immediately. There is no delay to indicate a timeout issue. As soon as the query is executed the error occurs. The statement in question is
ALTER TABLE `dbQZLTxvXB0_gf_entry_meta`
ADD PRIMARY KEY (`id`),
ADD KEY `meta_key` (`meta_key`(191)),
ADD KEY `entry_id` (`entry_id`),
ADD KEY `meta_value` (`meta_value`(191));
I broke the query down and ran each portion separately with no error. The error only triggers when query is run as-is from the dump file. I have tried with a MySQL 8 server and a MySQL 5.7 server, and I have used three different clients. It happens in every combination that I have tried.
I have no idea what could be happening here, and this is the first time that I have encountered this kind of issue with MySQL. Any thoughts or suggestions on where to go next?
EDIT:
Results of SHOW VARIABLES LIKE "%timeout":
| Variable_name | Value |
|---|---|
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| mysqlx_connect_timeout | 30 |
| mysqlx_idle_worker_thread_timeout | 60 |
| mysqlx_interactive_timeout | 28800 |
| mysqlx_port_open_timeout | 0 |
| mysqlx_read_timeout | 30 |
| mysqlx_wait_timeout | 28800 |
| mysqlx_write_timeout | 60 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| replica_net_timeout | 60 |
| rpl_stop_replica_timeout | 31536000 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 28800 |
