3

Sometimes I get the error from MYSQL server:

Lost connection to MySQL server at 'reading initial communication packet', system error: 0 

I cannot find the cause, since most of the time it works, but every week for some hours I get this error. I googled, but there seem to be only users which have this error permanently. But in this case, it only occurs sometimes. I checked hosts.allow and hosts.deny, but the host is allowed and not denied.

Also sometimes I get the error:

File './database/table.MYD' not found (Errcode: 24) 

It occurs very rarely. But it occurs for some hours once a week, sometimes on multiple days, but suddenly the problem disappears again. I have checked the open files limit. It's 2048 and should be absolutely enough. I also tried to increase the number of open files nevertheless, but no effect. I thought, perhaps the process does not close some tables. But this is impossible, because after a while everythings o.k. again and the process opens maximum 100 tables at once. I also checked the MySQL-runtime-environment, and there were 930 opened files. I cannot explain that. After a while it's 129.

I am running a MySQL-Server on a SUSE-Linux machine. I connect to the MySQL-Server from another host by the command line tool "mysql" and by MySQL-C-connector. The MySQL-Server is version 5.0.67.

  • Have you checked your system and mysql logs for any software/hardware errors? Have you setup any monitoring of resources cpu/mem and IO? Do you notice any pattern with the load and the errors? – Chida Aug 17 '12 at 16:31
  • What is the output of dmesg and /var/log/messages when the problem happens? – user130370 Aug 18 '12 at 22:11
  • Thank you for your answers! It seems to be a problem with the backup tool that we use. I have to check this together with our administrator. I'll give you feedback. I have installed a monitor that observes the "open files"-runtime-variable of MYSQL. It logs every minute the amount of open files in MYSQL. But the problem is the bottleneck only exists during some seconds. So I have to write another monitor that logs every second. I'll give you feedback. – user1606545 Sep 17 '12 at 10:30
  • The /var/log/messages do not show any errors during the specified time as our administrator says. But the problem seems to be related to the mysqlhotcopy program. It starts after midnight and as well, the problems always occur after midnight. – user1606545 Sep 18 '12 at 9:34
2

The problem is solved now.

It turned out that our backup program "mysqlhotcopy" was the problem.

The cause is that mysqlhotcopy locks, as I can see it, in one statement all tables of all databases when called without database parameter, respectively all tables of one database when called with database parameter. As our databases can contain some thousands of tables, this turned out to be impossible with our current system configuration.

So I wrote a new mysqlhotcopy-script and the problem has disappeared up to now.

  • I was getting this sporadically while using the parallel_tests gem and I was able to reduce/fix it by increasing the values of both max_connections and max_connect_errors in my my.cnf file. Too early to say it's resolved but we'll see... – Joshua Pinter Aug 25 '18 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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