0

I am attempting to the record of a specific Member if it no longer has child records in another table. When I commit the DELETE FROM, I am receiving a Lost Connection or MySQL server has gone away. If I SELECT the same record from the database, I get a valid return. I have included the interaction below:

SELECT m.* FROM Member m LEFT OUTER JOIN MemberAssociation ma ON m.memberID = ma.memberID WHERE m.scanCode = 12345 AND ma.associationCode IS NULL;
/* The expected Member records, which appear to be valid.  2 Rows */

DELETE m.* FROM Member m LEFT OUTER JOIN MemberAssociation ma ON m.memberID = ma.memberID WHERE m.scanCode = 12345 AND ma.associationCode IS NULL;
ERROR 2013 (HY000): Lost connection to MySQL server during query

DELETE m.* FROM Member m LEFT OUTER JOIN MemberAssociation ma ON m.memberID = ma.memberID WHERE m.scanCode = 12345 AND ma.associationCode IS NULL;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: my_database

ERROR 2013 (HY000): Lost connection to MySQL server during query

Is it possible that there is something I can do to fix this problem, or is it more likely that I stumbled across a software bug?

I am running innodb_version: 5.6.19-67.0 and MariaDB version: 10.0.13-MariaDB. The Member table is an InnoDB table.

D. Bunnell
  • 463
  • 6
  • 12

2 Answers2

1

I have resolved the issue myself and have some recommendations about anyone experiencing something like this in the future.

In my case, MySQL was failing because of a table corruption. I found the error by using the CHECK TABLE tool.

CHECK TABLE Member;
+-------------------------+-------+----------+----------+
| Table                   | Op    | Msg_type | Msg_text |
+-------------------------+-------+----------+----------+
| my_database.Member | check | status   | OK       |
+-------------------------+-------+----------+----------+

CHECK TABLE MemberAssociation;
ERROR 2013 (HY000): Lost connection to MySQL server during query

I then tried to copy the data out, but could not due to a PRIMARY KEY issue:

CREATE TABLE MemberAssociation_recover LIKE MemberAssociation

INSERT INTO MemberAssociation_recover SELECT * FROM MemberAssociation;
ERROR 1062 (23000): Duplicate entry 'XXXXX-YYYYY-ZZZZ' for key 'PRIMARY'

Something is wrong obviously.

At this point I did a mysqldump and discovered that there was a duplicate for the majority of rows in the MemberAssociation table. Because this was a development database, I restored the data via the following procedure:

CREATE TEMPORARY TABLE MemberAssociationIgnore LIKE MemberAssociation;
INSERT IGNORE INTO MemberAssociationIgnore SELECT * FROM MemberAssociation;
DELETE FROM MemberAssociation;
INSERT INTO MemberAssociation SELECT * FROM MemberAssociationIgnore;

Note: On a production system, I would not follow the above procedure. I would recommend copying the data out of the corrupted table into a new table that has no keys and manually cleaning out any duplicates before applying the removed keys and then copying the data back into the original table.

D. Bunnell
  • 463
  • 6
  • 12
0

Please refer to the MySQL:MariaDB for a background on when this happens.

A related SO query also discusses on the same issue.

Basically, it appears that your DELETE query may be taking too long and the MySQL server-client connection times out.

EDIT Based on response from OP

As per this link a corrupted Innodb table can also cause this problem. Refer to the below 2 links for the diagnostic and troubleshooting process:

Community
  • 1
  • 1
Prahalad Deshpande
  • 4,709
  • 1
  • 20
  • 22
  • I have referenced the SO post you linked, but do not feel that it is applicable here. I have a net_read_timeout of 30, wait_timeout of 28800 and interactive_timeout of 28800. Furthermore, the query reports the Lost Connection immediately after I try to run it. These timeouts are said to happen in seconds, the error returns in less than a second. – D. Bunnell Jan 25 '16 at 18:33