Mysql connector python - presents different behavior locally and in production

Question

I have a simple container running a test code that connects to a MySQL server db using MySQL connector/python (2.1.4), runs a simple query, counts the result and prints it every 1 second. If any connection error occurs, it is simply printed, ignored and the next loop iteration starts.

import time
import os
import mysql.connector
import datetime

while True:
  time.sleep(1)

  try:
    conn = mysql.connector.connect(host=os.environ['DB_SERVER_NAME'], 
                                port=int(os.environ['DB_PORT']),
                                user=os.environ['DB_USER'], 
                                password=os.environ['DB_PASSWORD'],
                                database=os.environ['DB_NAME'])
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM accounts")
    print('{} - no-peewee-mysql-connector counting accounts: {}'.format(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), len(cursor.fetchall())))
    cursor.close()
  except mysql.connector.Error as err:
    if err.errno == mysql.connector.errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == mysql.connector.errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        # print("connection error: {}".format(err.sqlstate))
        print("Error code:", err.errno)        # error number
        print("SQLSTATE value:", err.sqlstate) # SQLSTATE value
        print("Error message:", err.msg)       # error message
        print("Error:", err)                   # errno, sqlstate, msg values
        s = str(err)
        print("Error:", err)                   # errno, sqlstate, msg values
        print(type(err))
  finally: 
    try:
        conn.close()
    except Exception as err:
        print("closing connection error: {}".format(err))

it runs perfectly in my local machine, here is a sample log:

2017-08-11 15:20:47 - no-peewee-mysql-connector counting accounts: 0

2017-08-11 15:20:48 - no-peewee-mysql-connector counting accounts: 0

2017-08-11 15:20:49 - no-peewee-mysql-connector counting accounts: 0

2017-08-11 15:20:50 - no-peewee-mysql-connector counting accounts: 0

2017-08-11 15:20:51 - no-peewee-mysql-connector counting accounts: 0

Error code: 2013
SQLSTATE value: None
Error message: Lost connection to MySQL server during query
Error: 2013: Lost connection to MySQL server during query
Error: 2013: Lost connection to MySQL server during query
<class 'mysql.connector.errors.InterfaceError'>

Error code: 2013
SQLSTATE value: None
Error message: Lost connection to MySQL server during query
Error: 2013: Lost connection to MySQL server during query
Error: 2013: Lost connection to MySQL server during query
<class 'mysql.connector.errors.InterfaceError'>

Error code: 2003
SQLSTATE value: None
Error message: Can't connect to MySQL server on '172.17.0.1:4002' (111 Connection refused)
Error: 2003: Can't connect to MySQL server on '172.17.0.1:4002' (111 Connection refused)
Error: 2003: Can't connect to MySQL server on '172.17.0.1:4002' (111 Connection refused)
<class 'mysql.connector.errors.InterfaceError'>

Error code: 2003
SQLSTATE value: None
Error message: Can't connect to MySQL server on '172.17.0.1:4002' (111 Connection refused)
Error: 2003: Can't connect to MySQL server on '172.17.0.1:4002' (111 Connection refused)
Error: 2003: Can't connect to MySQL server on '172.17.0.1:4002' (111 Connection refused)
<class 'mysql.connector.errors.InterfaceError'>

Error code: 2003
SQLSTATE value: None
Error message: Can't connect to MySQL server on '172.17.0.1:4002' (111 Connection refused)
Error: 2003: Can't connect to MySQL server on '172.17.0.1:4002' (111 Connection refused)
Error: 2003: Can't connect to MySQL server on '172.17.0.1:4002' (111 Connection refused)
<class 'mysql.connector.errors.InterfaceError'>

Error code: 2003
SQLSTATE value: None
Error message: Can't connect to MySQL server on '172.17.0.1:4002' (111 Connection refused)
Error: 2003: Can't connect to MySQL server on '172.17.0.1:4002' (111 Connection refused)
Error: 2003: Can't connect to MySQL server on '172.17.0.1:4002' (111 Connection refused)
<class 'mysql.connector.errors.InterfaceError'>

2017-08-11 15:21:00 - no-peewee-mysql-connector counting accounts: 0

2017-08-11 15:21:01 - no-peewee-mysql-connector counting accounts: 0

In this case the connections errors where caused by me, starting and stopping the mysql server.

On Amazon ECS the behavior over night was weird. It worked normally from 11PM untill 3PM when some peaks of CPU were evident going from 0.3% to 1%, not alarming but weird. Then at 10AM the CPU consumption went to a mean of 0.75% and a lot of connection errors started to happen, they are more frequent, but not constant.

The error message is as follows:

2003: Can’t connect to MySQL server on ‘host:port’ (-3 Try again)

Note that it is different from the (111 connection refused) subcode that I receive locally.

I have other three experiments running the same task, but with different configurations of ORM and mysql client lib, and all the other three behave well.

This behavior looks suspicious, but I can't find a good explanation. As it took all the night to be evident I can't debug it easily on my local environment.

Any guidance will be of good value.

Connect to your DB directly and check open connections and see if for some reason all of that is getting filled up — Tarun Lalwani, Aug 11 '17 at 15:49
@TarunLalwani RDS reports a steady (low variation and far from the limit) number of open connections, It's running on a QA environment that has every prod setting but the dynamic load of clients — Felipe Jun, Aug 11 '17 at 16:33
See if the approach discussed in https://stackoverflow.com/questions/207981/how-to-enable-mysql-client-auto-re-connect-with-mysqldb this helps. Re-use your connection, don't create it again and again in your loop. Not a good thing to do — Tarun Lalwani, Aug 11 '17 at 17:21

Mysql connector python - presents different behavior locally and in production

0 Answers0