Lost connection on DELETE FROM, not on SELECT

Question

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.

Answer 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.

Answer 2

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:

• Recovering from a corrupted InnoDB index
• Fixing Innodb table corruption