MySQL for Azure connection reset by peer - unpredictable errors in Python code

Question

I have a python application connecting to our Azure MySQL database. It parses through a large CSV of items, hits an external API for data, and then posts that data to the db.

In the loop, I do a couple of SELECT queries that are parameter driven, an INSERT to add a new item to my db, and I do not close the connection cursor and db until after the loop has completed.

What's weird is that sometimes it will run for 1000's of items with no issues. Other times, it may run for 40 and then throw an Error 54 - connection reset by peer. Others, it will run for 200-300 and then fail. It seems to follow no pattern - the data being queried or inserted seems to make no difference. The failure is at different points in the loop, but always when I try to execute a query.

The parsing is quick - no more than 10-30 seconds between queries.

I run the python app locally on my Mac with Catalina 10.15.7 and Python 3.8.2

Answer 1

Hi, @ScottFergusson - Please leverage the resources listed below along with the additional resources that are also helpful in this situation.

• Chapter 5 Connector/Python Coding Examples (link)
• Python on Azure (link)
• Quickstart: Use Python to connect and query data in Azure Database for MySQL (link)
• Azure Database for MySQL (link) - 3rd Party Blog

The one thing that needs to be pointed out is the need for retry methods, as this is a common practice to implement across all data services. Please see: Retry guidance for Azure services (link)

Additional Resources:

Connecting efficiently to Azure Database for MySQL with ProxySQL (link)
MySQL Database on Azure FAQ - Connection Issues (link)