3

I seem to have the following error:

Lost connection to MySQL server during query

However, I know for absolute fact that this is not about the wait_timeout nor the max_allowed_packet, as I've set both to their maximums. I've also changed (not sure if it's relevant) the net_read_timeout and net_write_timeout to ten times their normal values (300 and 600 rather than 30 and 60 respectively). This is happening specifically during a DROP TABLE query (not a table for anything innodb-specific, just an ordinary table). However, the error logs aren't even showing any particular information when it fails.

How do I find out what to do from here?

Thanks to all who can help.

Editing as per comments: I'm using MySql 5.7.15 on Ubuntu.

As for UPTIME, I'm getting an increased value, yes. (My last run had 60700 before and 63892 afterwards.)

For the specific error message, I'm getting OperationalError: (_mysql_exceptions.OperationalError) (2013, 'Lost connection to MySQL server during query') [SQL: 'drop table nameofthetable'] {This was being accessed via a Python script.}

However, I'm seeing nothing in it from the error.log; all I see is:

2017-04-02T18:57:31.972169Z 9 [ERROR] InnoDB: Table mysql.innodb_table_stats not found. 2017-04-02T18:57:31.973008Z 9 [ERROR] InnoDB: Fetch of persistent statistics requested for table thedatabase.asimilartable but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

So it doesn't even seem to be recording the error involving the hang/crash/failure of the query in the logs.

Since the above, I'm seeing the same problems even with other queries that aren't drops (e.g. CREATE TABLE).

I do not know if the innodb stuff is in fact related to this, but I've tried attempting to create the innodb tables a la InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6 , but even the attempt to drop or create the tables fails ("Table 'mysql.innodb_index_stats' doesn't exist" if I try to create them, similar if I try to drop the existing ones). I'm not sure if mysql_install_db or mysql_upgrade will even be applicable here as I don't have internet access on that machine.

Continued: It seems that it's not just the drop query that it's failing on. Even a simple CREATE TABLE (i.e., creating an empty table, with no data) is also failing.

The mysql docs indicate that this sometimes happens due to connectivity issues, especially if it's in mid-query. However, the same machine works at other times and connection to the database for others is fine. How would I best debug this?

  • How big is/was the table? What Operating System? (DROP TABLE involves OS actions.) – Rick James Apr 2 '17 at 2:21
  • Hi Rick, the OS is Ubuntu. The table is 2378 MB. – Dread Pirate Roberts Apr 2 '17 at 2:28
  • Incidentally, it seems that there's also another unrelated error that's been going on (innodb_table_stats is supposedly missing), but that hasn't caused this to be a problem before; normally the run has dropped the table perfectly fine. Frustratingly, the error logs show nothing about the specific problem regarding the loss of connection. – Dread Pirate Roberts Apr 2 '17 at 2:55
  • Hmmm... A 2GB table will take some time to delete, but I would not expect to exceed 300/600. There have been a number of fixes related to innodb_table_stats -- What version are you running? Did you run mysql_upgrade if you upgraded recently? – Rick James Apr 2 '17 at 3:34
  • 1
    Losing the connection in a case like this should mean the server has crashed. There's no good reason to lose a connection during DROP TABLE. Please show the exact error along with the codes before it. And check with SHOW STATUS LIKE 'UPTIME'; to verify that it continues incrementing after the error, with larger values seen after the error compared to the values before the error. – Michael - sqlbot Apr 2 '17 at 11:45
0

Please run mysql_upgrade command to verify and fix any incompatibilities if you upgraded MySQL. More details on this link

After this is done, try dropping the table again.

0

I believe you are having hardware problems (RAM), I experienced this using my old server running out of ram with months of uptime. just hard reset to free up some memory.

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.