0

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
0

1 Answer 1

-1

Leonard,

Terminology for MySQL 8 primary key may have been restricted to - Following are the syntax of the ALTER TABLE statement to create a primary key in MySQL: ALTER TABLE table_name ADD PRIMARY KEY(column_list); – Wilson Hauck 9 mins ago

This url has terminology change for more recent version as well. mysqltutorial.org/mysql-index/mysql-create-index – good luck.

2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.