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

A plain SELECT printed ERROR 2013 (HY000): Lost connection to MySQL server during query, then No connection. Trying to reconnect..., and then showed a result as if nothing had happened. Something had happened: the open transaction was gone, and my session was a new one.

What I Was Doing

I was in the middle of a transaction in the mysql client, on MySQL 9.7.4 in Docker. I'd changed a row, and stepped away for a moment:

mysql

START TRANSACTION;
UPDATE products SET QuantityOnHand = 49 WHERE Id = 3;

Then I came back and checked it:

mysql

SELECT Id, QuantityOnHand FROM products WHERE Id = 3;

What I Expected

49, from my own transaction.

What I Got

Output

ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    186
Current database: inventory

+----+----------------+
| Id | QuantityOnHand |
+----+----------------+
|  3 |             50 |
+----+----------------+
1 row in set (0.007 sec)

What I Checked

The value. 50, not my 49, so the change was gone.

The connection.

mysql

SELECT CONNECTION_ID();

Output

+-----------------+
| CONNECTION_ID() |
+-----------------+
|             186 |
+-----------------+
1 row in set (0.001 sec)

mysql>

Before all this, it was:

Output (earlier in the same terminal)

+-----------------+
| CONNECTION_ID() |
+-----------------+
|             184 |
+-----------------+
1 row in set (0.001 sec)

A different number, which makes this a new session.

The reason. Another session had ended mine, to free the row it was holding:

mysql: root session

KILL 184;

The docs' page on this error lists this as a cause: "You (or the db administrator) has killed the running thread with a KILL statement or a mysqladmin kill command."

Why It Happens

The server closed the connection, and the client only found out when it next tried to use it. MySQL rolls back the open transaction of a connection that ends. The client then reconnected by itself, and ran the statement again in the new session, outside any transaction.

That automatic reconnect is the dangerous part. Anything you type next runs with autocommit on, so it saves immediately, even if you thought you were still in a transaction.

The Fix

1. Assume your transaction is gone. Check the data, as above.
2. Find out why the connection ended. Common causes are a KILL, a server restart, a network failure and an idle connection reaching wait_timeout.
3. Start the work again in a new transaction, from START TRANSACTION.

To stop the client from reconnecting by itself, so that you get an error instead, start it with --skip-reconnect.

After the Fix

Doing the work again, in the new session, commits it for real:

Output

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.000 sec)

mysql> UPDATE products SET QuantityOnHand = 49 WHERE Id = 3;
Query OK, 1 row affected (0.002 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.005 sec)

Common Mistakes

Mistake	What happens	Do this instead
Carrying on after the reconnect	Your next statements run outside the transaction, and save at once	Start the transaction again
Looking only at the result, and not the messages above it	You miss that it's a new session	Read the whole output
Blaming the query	The query is rarely the cause	Check the server's log and any KILL

Troubleshooting

Symptom	Likely cause	Fix
It happens after a long idle time	wait_timeout closed the connection	Reconnect, or raise the limit
It happens during a restart	The server went down	Check docker compose logs mysql
It happens as you sign in, with --ssl-cert and --ssl-key	The server doesn't trust that client certificate's CA, and it ended the TLS handshake	Use a certificate signed by the CA the server trusts
In .NET, the error is Fatal error encountered during command execution	The driver's version of the same event	The fix

Conclusion

ERROR 2013 means the connection ended, and with it your open transaction. The client's automatic reconnect hides that, so check the data, find out why it ended, and start your transaction again.

Additional Resources

• MySQL server has gone away — every usual cause.
• KILL Statement — what it ends.

Related

• How to Stop a Running Query or Connection in MySQL with KILL
• How to Use a Transaction in MySQL: START TRANSACTION, COMMIT and ROLLBACK
• Fixed: "Fatal error encountered during command execution" and "Reading from the stream has failed" with MySql.Data