The error "(pymysql.err.OperationalError) (2006, 'MySQL server has gone away (ConnectionResetError(104, 'Connection reset by peer'))')" frequently occurs during the execution of scheduled batch jobs in Azure Database for MariaDB

Question

Occurrence Time: 1:30 AM JST

Occurred Query: (The query is one executed by the App Service at scheduled times)

TRUNCATE TABLE table_name

Reproduction:

By manually restarting the DB instance and executing the App Service function, the error is reproduced.

Inexplicable Issue:

The error does not occur every day, but rather every other day.

Answer 1

Hi Mingqi Cai,

You're encountering the error (2006, 'MySQL server has gone away (Connection reset by peer)') during scheduled batch jobs in Azure Database for MariaDB, particularly when executing TRUNCATE TABLE queries. This issue occurs intermittently—roughly every other day—around 1:30 AM JST, and can be reproduced by restarting the database and executing the App Service function manually.

The most likely causes include stale or idle database connections being reused by your application, Azure maintenance or auto-scaling events that temporarily disrupt connectivity, and the nature of TRUNCATE TABLE, which requires an exclusive lock and can be sensitive to contention or replication delays. Network fluctuations or restarts of the App Service could also contribute to the issue.

To mitigate the problem, implement connection retry logic that attempts to reconnect if error 2006 occurs. If you're using SQLAlchemy, enable pool_pre_ping=True to ensure that pooled connections are alive before use. Consider replacing TRUNCATE TABLE with DELETE FROM table_name if performance permits, as it's less sensitive to locking issues. It's also important to monitor Azure for scheduled maintenance or resource scaling events that might affect database availability. Finally, if you're on a lower-tier SKU, consider upgrading to improve stability and reduce the chance of connection drops.You're encountering the error (2006, 'MySQL server has gone away (Connection reset by peer)') during scheduled batch jobs in Azure Database for MariaDB, particularly when executing TRUNCATE TABLE queries. This issue occurs intermittently—roughly every other day—around 1:30 AM JST, and can be reproduced by restarting the database and executing the App Service function manually.

The most likely causes include stale or idle database connections being reused by your application, Azure maintenance or auto-scaling events that temporarily disrupt connectivity, and the nature of TRUNCATE TABLE, which requires an exclusive lock and can be sensitive to contention or replication delays. Network fluctuations or restarts of the App Service could also contribute to the issue.

To mitigate the problem, implement connection retry logic that attempts to reconnect if error 2006 occurs. If you're using SQLAlchemy, enable pool_pre_ping=True to ensure that pooled connections are alive before use. Consider replacing TRUNCATE TABLE with DELETE FROM table_name if performance permits, as it's less sensitive to locking issues. It's also important to monitor Azure for scheduled maintenance or resource scaling events that might affect database availability. Finally, if you're on a lower-tier SKU, consider upgrading to improve stability and reduce the chance of connection drops.