Flask + PyMySQL giving error no attribute 'settimeout'

Question

Since some days I am having this error from time to time in my Flask Website

[Sat Sep 14 00:04:31.016107 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]   File "/var/www/xxxx/__init__.py", line 1612, in showArticleTitlePage
[Sat Sep 14 00:04:31.016119 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]     language = session['locale'])
[Sat Sep 14 00:04:31.016130 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]   File "/var/www/cardsrealm/SQL.py", line 1376, in get_articles_before
[Sat Sep 14 00:04:31.016146 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]     cursor.execute(query, t)
[Sat Sep 14 00:04:31.016176 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]   File "/var/www/xxxx/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
[Sat Sep 14 00:04:31.016188 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]     result = self._query(query)
[Sat Sep 14 00:04:31.016198 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]   File "/var/www/xxxx/venv/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
[Sat Sep 14 00:04:31.016209 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]     conn.query(q)
[Sat Sep 14 00:04:31.016219 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]   File "/var/www/xxxx/venv/lib/python3.6/site-packages/pymysql/connections.py", line 517, in query
[Sat Sep 14 00:04:31.016230 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
[Sat Sep 14 00:04:31.016241 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]   File "/var/www/xxxx/venv/lib/python3.6/site-packages/pymysql/connections.py", line 732, in _read_query_result
[Sat Sep 14 00:04:31.016251 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]     result.read()
[Sat Sep 14 00:04:31.016262 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]   File "/var/www/xxxx/venv/lib/python3.6/site-packages/pymysql/connections.py", line 1075, in read
[Sat Sep 14 00:04:31.016273 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]     first_packet = self.connection._read_packet()
[Sat Sep 14 00:04:31.016297 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]   File "/var/www/xxxx/venv/lib/python3.6/site-packages/pymysql/connections.py", line 674, in _read_packet
[Sat Sep 14 00:04:31.016310 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]     recv_data = self._read_bytes(bytes_to_read)
[Sat Sep 14 00:04:31.016321 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]   File "/var/www/xxxx/venv/lib/python3.6/site-packages/pymysql/connections.py", line 688, in _read_bytes
[Sat Sep 14 00:04:31.016332 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013]     self._sock.settimeout(self._read_timeout)
[Sat Sep 14 00:04:31.016342 2019] [wsgi:error] [pid 4693:tid 140670017648384] [remote 66.249.66.44:60013] AttributeError: 'NoneType' object has no attribute 'settimeout'

This settimeout is happening from time to time. I noticed people saying I should connect and close connection to make it single thread safy.

self.conn = self.create_connection()
cursor = self.conn.cursor()
query = ''' SELECT MY QUERY '''.format(self.xxxColumn, self.xxxColumn)
cursor.execute(query, t)
result = cursor.fetchall() 
cursor.close()
self.close_connection()

Above shows how I am handling it. I added the following to my SQL:

connect_timeout = 200
read_timeout = 200
write_timeout = 200
max_allowed_packet = 1073741824

But I still keep having this error!

Cumquat

error shows that self._sock is None so maybe your connection is not open. – Ungulate

I create connection with self.create_connection() Inside create connection I even check with a ping if the connection is in fact open... Only if the connection was closed in the middle of the task – Cumquat

Answer 1

I meet the same error with you.

I use mysqlpool in my code

DB_MIN_CACHED = 10
DB_MAX_CACHED = 10
DB_MAX_SHARED = 20
DB_MAX_CONNECYIONS = 150
DB_BLOCKING = True
DB_MAX_USAGE = 0
DB_SET_SESSION = None
DB_CREATOR = pymysql


import dbutils
from dbutils.pooled_db import PooledDB

import pymysql, os, configparser
from pymysql.cursors import DictCursor


class BasePymysqlPool(object):
    def __init__(self, envconfig):
        self.DB_HOST = envconfig.DB_HOST
        self.DB_PORT = envconfig.DB_PORT
        self.DB_DATABASE = envconfig.DB_DATABASE
        self.DB_USER = envconfig.DB_USER
        self.DB_PASSWORD = envconfig.DB_PASSWORD
        self.CHARSET = envconfig.CHARSET

        self.DB_MIN_CACHED = envconfig.DB_MIN_CACHED
        self.DB_MAX_CACHED = envconfig.DB_MAX_CACHED
        self.DB_MAX_SHARED = envconfig.DB_MAX_SHARED
        self.DB_MAX_CONNECYIONS = envconfig.DB_MAX_CONNECYIONS
        self.DB_BLOCKING = envconfig.DB_BLOCKING
        self.DB_MAX_USAGE = envconfig.DB_MAX_USAGE
        self.DB_SET_SESSION = envconfig.DB_SET_SESSION
        self.DB_CREATOR = envconfig.DB_CREATOR

class MyPymysqlPool(BasePymysqlPool):

    __pool = None

    def __init__(self, conf_env=None):
        self.conf = conf_env
        super(MyPymysqlPool, self).__init__(self.conf)
        self._conn = self.__getConn()
        self._cursor = self._conn.cursor()

    def __getConn(self):
        if MyPymysqlPool.__pool is None:
            __pool = PooledDB(creator=self.DB_CREATOR,
                          mincached=self.DB_MIN_CACHED,
                          maxcached=self.DB_MAX_CACHED,
                          host=self.DB_HOST,
                          port=self.DB_PORT,
                          user=self.DB_USER,
                          passwd=self.DB_PASSWORD,
                          db=self.DB_DATABASE,
                          use_unicode=False,
                          charset=self.CHARSET,
                          cursorclass=DictCursor)
        return __pool.connection()

    def getconn(self):
        self._conn = self.__getConn()
        self._cursor = self._conn.cursor()
    
    def getOne(self, sql, param=None):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchone()
        else:
            result = False
        return result
    
    def __query(self, sql, param=None):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        return count
    
    def update(self, sql, param=None):
        return self.__query(sql, param)

    def dispose(self, isEnd=1):
        if isEnd == 1:
            self.end('commit')
        else:
            self.end('rollback')
        self._cursor.close()
        self._conn.close()

    def end(self, option='commit'):
        if option == 'commit':
            self._conn.commit()
        else:
            self._conn.rollback()


mysqlhelper= MyPymysqlPool(config_db)
sql = '''select md5_id from queryresult
 where businessid = %s and md5_id = %s;
 '''
mysqlhelper.getconn()
result_db_num = mysqlhelper.update(sql, [businessid, md5_id])
mysqlhelper.end()
mysqlhelper.dispose()

I try to set DB_MAX_CONNECYIONS=100 to DB_MAX_CONNECYIONS=150, but it seems like not work out.

I noticed that the less ask the less the error. So, I use lock to solve this proble.

import threading

lock = threading.Lock()

lock.acquire()
curos.excure(command,data)
lock.release()

in my code:

lock.acquire()
mysqlhelper.getconn()
result_db_num = mysqlhelper.update(sql, [businessid, md5_id])
mysqlhelper.end()
mysqlhelper.dispose()
lock.release()

Then, done!

Meuse