1

I'm using mysql version 5.0.5 and trying to dump my data, in order to prepare for version upgrading, and get an error.

I run:

./mysqldump -u root -p --add-drop-table --all-databases > /data/all_db_dump.mysql

And get:

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `abc` at row: 408640

I went over some similar questions and found some suggestions:

  • run with --skip-extended-insert - Did not help
  • run with --hex-blob - Did not help
  • configured max_allowed_packet to 1024M - Did not help (I have 4 GB RAM)
  • configured net_read_timeout to 7200 - Did not help
  • configured net_write_timeout to 7200 - Did not help

For all of the above and get the same error.

I monitored the server's memory while dumping the data and didn't see any issue. What could be the issue?

Omri
  • 353
  • 1
  • 5
  • 17

1 Answers1

2

Considering that you've already set the timeout, packet and other variables at GLOBAL level and not session level, there are few other options that I can think of.

Option1 : verify table corruption

Analyze and repair table 'abc' to verify the corruption in case of myisam engine. (Also consider migrating tables to innodb if possible) Another approach to identify table corruption is to select data (row:408640) from those table and you should get an error or MySQL may shutdown as well.

Suggestion to what you're trying to achieve (in case you couldn't get mysqldump working):

You can upgrade MySQL by in-place upgrade method as well. Steps are as follows :

  • stop MySQL server (with innodb_fast_shutdown=0) and copy full datadir folder as backup (alternative of mysqldump, in case of any issues)

  • upgrade(replace) MySQL binaries (/basedir/bin/) with the binaries of version you're upgrading to

  • start MySQL server as usual and execute mysql_upgrade

Note :

  1. In-place upgrade is much faster than logical dump in case your data size is too large

  2. do not skip major release version while upgrading I.e. 5.0 to 5.6 In stead you'll have to do 5.0 -> 5.5 -> 5.6

  3. look out for deprecated variables before upgrading to any version
  4. make sure to analyze/repair tables after any method of upgradation.

One more thing, when you dump using mysqldump, you need to generate .sql file not .mysql file (all_db_dump.sql)

sql-no
  • 56
  • 5
  • Thanks. The table engine is InooDB. I exported the table and didn't see any issue. However, when i trying dump the DB i get the same error on other tables as well, so i guess it's not a table issue, but a my.cnf configuration. Isn't it? As for the upgrade, i'm planning to upgrade directly from 5.0 to 5.7. I've an article which explain it possible. http://mysqlserverteam.com/upgrading-directly-from-mysql-5-0-to-5-7-using-an-in-place-upgrade/ What do you think about that? – Omri May 17 '17 at 09:17
  • Well...It's seems like a corruption issue. While dumping the data, I get a lot of errors like Error: page 2293761 log sequence number 1587 553576633 is in the future! Current system log sequence number 1574 4154952816. Your database may be corrupt or you may have copied the InnoDB tablespace but not the InnoDB log files. – Omri May 17 '17 at 11:33
  • URL seems perfect for In-Place upgrade, just make sure to verify deprecated variables before upgrade. I would suggest you to perform check on your DB then record master position, stop mysql and copy data folder (datadir) and setup slave on fresh machine (same mysql version as current). Start mysqls and configure slave for your DB using master position. Add --skip-slave-start and upgrade your slave as described in URL. Run mysqlcheck on slave again after upgrade and start slave. Stop web services (to stop DB changes) and compare your DB on master-slave. Swap IP between master-slave. – sql-no May 18 '17 at 04:40