0

MySQL is stopping when I am exporting data from a table with large data.

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table sales_entry_details at row: 132371

MySQL is stopping for another reason which I could not resolve. So I decided to export it then the above error is showing.

This table contains 372882 rows.

What might cause MySQL to stop??

Are there any configs to change?

I got these from error logs: 

2019-11-19T11:42:19.074634Z 0 [Note] Executing 'SELECT * FROM 
INFORMATION_SCHEMA.TABLES;' to get a list of tables using the deprecated partition engine. You may use the startup option '--disable-partition-engine-check' to skip this check. 
2019-11-19T11:42:19.076626Z 0 [Note] Beginning of list of non-natively partitioned tables
2019-11-19T11:42:19.144444Z 0 [Note] End of list of non-natively partitioned tables
2019-11-19T11:43:15.348723Z 0 [ERROR] InnoDB: Operating system error number 23 in a file operation.
2019-11-19T11:43:15.351602Z 0 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
2019-11-19T11:43:15.355585Z 0 [ERROR] InnoDB: File .\retech\sales_entry_details.ibd: 'Windows aio' returned OS error 123. Cannot continue operation
2019-11-19T11:43:15.359611Z 0 [ERROR] InnoDB: Cannot continue operation.
2019-11-19T11:43:18.994689Z 0 [Note] InnoDB: FTS optimize thread exiting.
2019-11-19T11:44:56.106058Z 0 [Warning] InnoDB: 13 threads created by InnoDB had not exited at shutdown!
Hello World
  • 2,673
  • 7
  • 28
  • 60
  • 1
    A similar question has been answered - [here](https://stackoverflow.com/questions/53091734/getting-lost-connection-to-mysql-when-using-mysqldump-even-with-max-allowed-pack). See if this helps? – Arun Selin Nov 19 '19 at 11:23
  • 1
    MySql didn't stop. You lost your connection, maybe because you have a timeout set – nacho Nov 19 '19 at 11:24
  • @nacho I checked in service and it appears MySQL has stopped!!!! – Hello World Nov 19 '19 at 11:26
  • 1
    `SHOW VARIABLES LIKE '%timeout%';`` And how long did it run before timing out? – Rick James Nov 20 '19 at 23:00

1 Answers1

0

In my case, when mysql goes down, I usually find issues in either:

tail /var/log/mysql/error.log

or:

OOM issue (something like Out of memory in UB : OOM killed process xxx (mysqld) from dmesg).

In case of OOM issue, recalculate memory and tune innodb_buffer_pool_size in my.cnf.

dropyourcoffee
  • 318
  • 1
  • 5
  • 13
  • I have change `innodb_buffer_pool_size` but still same error, I have added some log reports – Hello World Nov 19 '19 at 11:48
  • 1
    Error 23 is "file table overflow", meaning "mysql cannot open more files". check ```lsof ``` and tune ```open_files_limit``` variable in ```my.cnf```. (or maybe it exceeded maximum number of files that can be created in the mysql bin directory). – dropyourcoffee Nov 19 '19 at 12:02
  • The value of `open_files_limit` was 4161. Now I increased it but still, the same error is showing, what is the fix? – Hello World Nov 19 '19 at 12:11
  • 1
    what does ```lsof | grep mysql | wc -l``` say ?? – dropyourcoffee Nov 19 '19 at 12:18
  • I windows OS, is there any alternative if `lsof` of windows?? – Hello World Nov 19 '19 at 13:54
  • 1
    I dont know very much about windows but i have just done a little research on it and there is an external tool called sysinternal. Checkout https://learn.microsoft.com/en-us/sysinternals/downloads/handle – dropyourcoffee Nov 19 '19 at 14:14