Python – Error handling & Retry in MySQL on Ubuntu No.101

How to implement the error handling and retry with some interval in MySQL on Ubuntu by Python is shown in this blog.

Contents

▼1. Why do we need an error handling and an retry logic?
▼2. Prerequisites
▼3. Creating the Python code to implement the error handling & retry logic
- 3-1. Developing a python code for an error handling and a retry logic
- 3-2. Results for this code
▼4. Others: Confirming the listening port of MySQL
▼5. Reference

▼1. Why do we need an error handling and an retry logic?

We often use a cloud service and a remote service through the network. if the network intermittently cause something problems, the client application cannot access these services and will stop. to avoid this case, we need to add an error handling and an retry logic in an client application.

▼2. Prerequisites

An error handling and a retry logic will be added into the code that was shown in the following blog.

Python – Connect to MySQL on Ubuntu 20.04 by pyodbc No.98

▼3. Creating the Python code to implement the error handling & retry logic

3-1. Developing a python code for an error handling and a retry logic

This python code implements 3 retries with 15 seconds interval using while after a connection failure. To cause the connection failure, the listening port 2206 is changed to incorrect port 2205 in the connection string.

# runmysqlquery_retry.py

import pyodbc
import time
import datetime

driver = '{MYSQL ODBC 8.0 Driver}'
server = 'localhost'
dbname = 'testdb'
user = 'hiveuser'
pw = 'hivepassword'
query = "select * from testtbl"
success = False
retryc = 0
max_retries = 3

while not success and retryc < max_retries:
    try:
        conn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=3305;DATABASE='+dbname+';UID='+user+';PWD='+pw+';')
        cursor = conn.cursor()
        cursor.execute(query)
        row = cursor.fetchone()
        while row:
            print(str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()

        success = True
    except pyodbc.Error as ex:
        retryc += 1
        print("Current Timestamps:" + str(datetime.datetime.now()) + "retry count: " + str(retryc))
        print("==========================")
        print("Error: " + str(ex))
        print("==========================")
        time.sleep(15)
# if success is success, close the cursor and connection
if success:
    cursor.close()
    conn.close()

3-2. Results for this code

Since the interval is 15 seconds, retry is implemented after 15 seconds from the connection failure.

python3 ./runmysqlquery_retry.py 
Current Timestamps:2022-12-16 20:16:58.126307retry count: 1
==========================
Error: ('HY000', "[HY000] [MySQL][ODBC 8.0(w) Driver]Can't connect to MySQL server on 'localhost:3305' (111) (2003) (SQLDriverConnect)")
==========================
Current Timestamps:2022-12-16 20:17:13.140861retry count: 2
==========================
Error: ('HY000', "[HY000] [MySQL][ODBC 8.0(w) Driver]Can't connect to MySQL server on 'localhost:3305' (111) (2003) (SQLDriverConnect)")
==========================
Current Timestamps:2022-12-16 20:17:28.155940retry count: 3
==========================
Error: ('HY000', "[HY000] [MySQL][ODBC 8.0(w) Driver]Can't connect to MySQL server on 'localhost:3305' (111) (2003) (SQLDriverConnect)")
==========================

▼4. Others: Confirming the listening port of MySQL

The following query can confirm the listening port of MySQL.

mysql> show global variables like 'PORT'
    -> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

▼5. Reference

That’s all. Have a nice day ahead !!!