Mysqldump Error 2013 Lost Connection To Mysql Server
Contents |
to: mysqldump: Error 2013: Lost connection to MySQL mysqldump: got error: 2013: lost connection to mysql server during query when using lock tables server during query when dumping table ....If so there
Mysqldump Net_write_timeout
are several options you can try to resolve: 1) it is possible that the mysqldump connection timeout backup is hitting the MySQL timeout limits. The particular variables in question here are net_read_timeout and net_write_timeout which are set to 30 net_read_timeout mysql and 60 seconds respectivley. You can change these in the my.cnf config file by setting them as you desire. Here is an example. After making these changes to my.cnf you must restart MySQL net_read_timeout = 120 net_write_timeout = 900 If you prefer to change these settings
Mysql Lost Connection
without needing to reboot MySQL you can do that with the following SQL statements: set global net_read_timeout = 120; set global net_write_timeout = 900; Again, use the value for these settings that you feel is appropriate for your setup. 2) Increase max_allowed_packet setting You might try increasing the max_allowed_packet configuration setting. You could try setting it to 256M or even up to 1G 3) You could have a corrupt table Sometimes "Lost connection" errors are caused by a corrupt table. You could try setting innodb_force_recovery = 1 and restart mysql to see if that helps. If it does, doing the dump might fix the page cache in which you can often turn this back to 0 or remove. Was this article helpful? Yes / No, I want to contact support Powered by Groove
Sep 2009 12:08 Reporter: John Miller Email Updates: Status: Verified Impact on me: None Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request) Version:5.1.34-community via TCP/IP mysqldump skip table OS:Microsoft Windows (2k3 SP2) Assigned to: Triage: Needs Triage: D2 (Serious) mysqldump quick View Add Comment Files Developer Edit Submission View Progress Log Contributions [9 Jul 2009 22:47] John Miller Description:
Error 2013 (hy000): Lost Connection To Mysql Server During Query
This command gets run as part of our backup: D:\util\mantis_backup_test>mysqldump -q -e --max_allowed_packet=500m --net_buffer_length=16m -u XXXX -pXXXXXXXXX rpcbugtracker 1>test.backup mysqldump: Error 2013: Lost connection to MySQL server during query when https://ottomatik.groovehq.com/knowledge_base/topics/solving-error-2013-lost-connection-to-mysql-server-during-query-when-dumping-table dumping table `mantis_bug_file_table` at row: 743 Row 743 does not seem "special": mysql> select id, length(content), bug_id,filename from mantis_bug_file_table where id=743; +-----+-----------------+--------+-------------+ | id | length(content) | bug_id | filename | +-----+-----------------+--------+-------------+ | 743 | 76288 | 2313 | P0130rs.doc | +-----+-----------------+--------+-------------+ 1 row in set (0.00 sec) If I go into my application (mantis) and remove that attachment - https://bugs.mysql.com/bug.php?id=46103 removing that row from the table: mysql> select id, length(content), bug_id,filename from mantis_bug_file_table where id=743; Empty set (0.00 sec) I just get the same error with a different row: mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `mantis_bug_file_table` at row: 429 The table itself is big: D:\Program Files\MySQL\MySQL Server 5.0\data\rpcbugtracker>dir /os Volume in drive D is Applications Volume Serial Number is 1099-7FDC Directory of D:\Program Files\MySQL\MySQL Server 5.0\data\rpcbugtracker 01/21/2009 05:03 AM
2014 20:41 Reporter: - - Email Updates: Status: No Feedback Impact https://bugs.mysql.com/bug.php?id=72519 on me: None Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical) Version:5.5.37 OS:Linux (Ubuntu 14.04) Assigned to: View Add Comment Files Developer Edit Submission http://stackoverflow.com/questions/8251727/mysql-archive-data-what-to-do-when-its-too-big View Progress Log Contributions [3 May 2014 3:09] - - Description: I tried to backup my database with the mysqldump command. But it failed lost connection with an error message, "mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `creation_log` at row: 59655". The log of mysqldump was: ======================================== nodchip@nighthawk ~ % mysqldump -uroot -p[password] -A -v --flush-privileges --rou tines --result-file mysql_backup_2014-05-03.sql (skipped) -- Retrieving table structure for table lost connection to creation_log... -- Sending SELECT query... -- Retrieving rows... mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `creation_log` at row: 59655 ======================================== I added "innodb_force_recovery = 6" to [mysqld] section in my.cnf, restart the mysqld and tried mysqldump again. But the result was not changed. The definition of "creation_log" table is: ======================================== mysql> desc creation_log; +------------+------------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+-------------------+-----------------------------+ | PROBLEM_ID | int(10) unsigned | NO | MUL | NULL | | | USER_CODE | int(10) unsigned | NO | | NULL | | | DATE | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | MACHINE_IP | text | NO | MUL | NULL | | | SUMMARY | text | YES | | NULL | | +------------+------------------+------+-----+-------------------+-----------------------------+ ===============================
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up MySQL archive data…what to do when it's too big up vote 5 down vote favorite I use an INSERT INTO & DELETE FROM combination in a PHP script to take data out of an operational MySQL table and put into into an archive table. The archive table has gotten too big. Even though no day-to-day operations are performed on it, mysqldump chokes when we back up (error 2013): Error 2013: Lost connection to MySQL server during query when dumping table 'some_table' at row: 1915554 What can I do? Should my PHP script move it to another DB (how?)? Is it okay to keep the large table in the operational db?--in that case, how do I get around the mysqldump issue? Thanks! php mysql mysqldump share|improve this question asked Nov 24 '11 at 2:56 Kyle 7,636145790 do you use the mysql or mysqli driver? –Book Of Zeus Nov 24 '11 at 3:01 @Book of Zeus - not sure, but this also happens at the shell –Kyle Nov 24 '11 at 3:16 you mean shell using the /usr/bin/mysql? (I had the same problem and i fixed it using mysqli) –Book Of Zeus Nov 24 '11 at 3:21 add a comment| 5 Answers 5 active oldest votes up vote 3 down vote accepted Are you by chance dumping using memory buffering and running out of swap and physical RAM? If so, you can try dumping row by row instead. Try adding --quick to your mysqldump statement. According to the documentation, you should combine --single-transaction with --quick. Source: http://dev.mysql.com/doc/refman/5.5/en/mysqldump.html share|improve this answer answered Nov 24 '11 at 3:05 Will Bickford 4,0901944 tried it with mysqldump --opt --single-transaction --quick -u someuser -p and got the same error –Kyle Nov 24 '11 at 3:13 How much data is in the table and how many rows does it have? Also are you sure you aren't running out of disk space? –Will Bickford Nov 24 '11 at 3:15 This MySQL bug suggests checking your timeout settings: bugs.mysql.com/bug.php?id=30809 –Will Bickford Nov 24 '11 at 3:17 I'm sure I have enough disk space. I get a ERROR 2013 (HY000) when I run SELECT COUNT(*) so I'm not sure about rows. The error says it crash