Forums

Maxing MySQL Connections

I have gone through every thread on this site regarding this topic but none of the suggestions have worked for me. I continue to receive the User 'username' has exceeded the 'max_user_connections' resource (current value: x) error. Can somebody please help me? Thank you!

Here is my code:

try:
    engine = create_engine(SQLALCHEMY_DATABASE_URI)
    connection = engine.connect()
    result = connection.execute("SELECT * FROM ex_click_track;")
finally:
    connection.close()

And my error:

---------------------------------------------------------------------------
MySQLInterfaceError                       Traceback (most recent call last)
/usr/lib/python3.5/site-packages/mysql/connector/connection_cext.py in _open_connection(self)
    175         try:
--> 176             self._cmysql.connect(**cnx_kwargs)
    177         except MySQLInterfaceError as exc:

MySQLInterfaceError: User 'divdasani' has exceeded the 'max_user_connections' resource (current value: 6)

During handling of the above exception, another exception occurred:

ProgrammingError                          Traceback (most recent call last)
/usr/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   2157         try:
-> 2158             return fn()
   2159         except dialect.dbapi.Error as e:

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in unique_connection(self)
    344         """
--> 345         return _ConnectionFairy._checkout(self)
    346

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in _checkout(cls, pool, threadconns, fairy)
    790         if not fairy:
--> 791             fairy = _ConnectionRecord.checkout(pool)
    792

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in checkout(cls, pool)
    531     def checkout(cls, pool):
--> 532         rec = pool._do_get()
    533         try:

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in _do_get(self)
   1195                 with util.safe_reraise():
-> 1196                     self._dec_overflow()
   1197         else:

/usr/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
     65             if not self.warn_only:
---> 66                 compat.reraise(exc_type, exc_value, exc_tb)
     67         else:

/usr/lib/python3.5/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    248             raise value.with_traceback(tb)
--> 249         raise value
    250

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in _do_get(self)
   1192             try:
-> 1193                 return self._create_connection()
   1194             except:

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in _create_connection(self)
    349 
--> 350         return _ConnectionRecord(self)
    351

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in __init__(self, pool, connect)
    476         if connect:
--> 477             self.__connect(first_connect_check=True)
    478         self.finalize_callback = deque()

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in __connect(self, first_connect_check)
    673             self.starttime = time.time()
--> 674             connection = pool._invoke_creator(self)
    675             pool.logger.debug("Created new connection %r", connection)

/usr/lib/python3.5/site-packages/sqlalchemy/engine/strategies.py in connect(connection_record)
    105                             return connection
--> 106                 return dialect.connect(*cargs, **cparams)
    107

/usr/lib/python3.5/site-packages/sqlalchemy/engine/default.py in connect(self, *cargs, **cparams)
    411     def connect(self, *cargs, **cparams):
--> 412         return self.dbapi.connect(*cargs, **cparams)
    413

/usr/lib/python3.5/site-packages/mysql/connector/__init__.py in connect(*args, **kwargs)
    181     if HAVE_CEXT and not use_pure:
--> 182         return CMySQLConnection(*args, **kwargs)
    183     return MySQLConnection(*args, **kwargs)

/usr/lib/python3.5/site-packages/mysql/connector/connection_cext.py in __init__(self, **kwargs)
     77         if kwargs:
---> 78             self.connect(**kwargs)
     79

/usr/lib/python3.5/site-packages/mysql/connector/abstracts.py in connect(self, **kwargs)
    735         self.disconnect()
--> 736         self._open_connection()
    737         self._post_connection()

/usr/lib/python3.5/site-packages/mysql/connector/connection_cext.py in _open_connection(self)
    178             raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,
--> 179                                              sqlstate=exc.sqlstate)
    180         self._do_handshake()

ProgrammingError: 1226 (42000): User 'divdasani' has exceeded the 'max_user_connections' resource (current value: 6)

The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)
<ipython-input-152-4b26d71e3558> in <module>()
      1 try:
      2     engine = create_engine(SQLALCHEMY_DATABASE_URI)
----> 3     connection = engine.connect()
      4     result = connection.execute("SELECT * FROM ex_click_track;")
      5 finally:

/usr/lib/python3.5/site-packages/sqlalchemy/engine/base.py in connect(self, **kwargs)
   2100         """
   2101 
-> 2102         return self._connection_cls(self, **kwargs)
   2103 
   2104     def contextual_connect(self, close_with_result=False, **kwargs):

/usr/lib/python3.5/site-packages/sqlalchemy/engine/base.py in __init__(self, engine, connection, close_with_result, _branch_from, _execution_options, _dispatch, _has_events)
     88         else:
     89             self.__connection = connection \
---> 90                 if connection is not None else engine.raw_connection()
     91             self.__transaction = None
     92             self.__savepoint_seq = 0

/usr/lib/python3.5/site-packages/sqlalchemy/engine/base.py in raw_connection(self, _connection)
   2186         """
   2187         return self._wrap_pool_connect(
-> 2188             self.pool.unique_connection, _connection)
   2189 
   2190

/usr/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   2160             if connection is None:
   2161                 Connection._handle_dbapi_exception_noconnection(
-> 2162                     e, dialect, self)
   2163             else:
   2164                 util.reraise(*sys.exc_info())

/usr/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception_noconnection(cls, e, dialect, engine)
   1474             util.raise_from_cause(
   1475                 sqlalchemy_exception,
-> 1476                 exc_info
   1477             )
   1478         else:

/usr/lib/python3.5/site-packages/sqlalchemy/util/compat.py in raise_from_cause(exception, exc_info)
    263     exc_type, exc_value, exc_tb = exc_info
    264     cause = exc_value if exc_value is not exception else None
--> 265     reraise(type(exception), exception, tb=exc_tb, cause=cause)
    266 
    267 if py3k:

/usr/lib/python3.5/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    246             value.__cause__ = cause
    247         if value.__traceback__ is not tb:
--> 248             raise value.with_traceback(tb)
    249         raise value
    250

/usr/lib/python3.5/site-packages/sqlalchemy/engine/base.py in _wrap_pool_connect(self, fn, connection)
   2156         dialect = self.dialect
   2157         try:
-> 2158             return fn()
   2159         except dialect.dbapi.Error as e:
   2160             if connection is None:

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in unique_connection(self)
    343 
    344         """
--> 345         return _ConnectionFairy._checkout(self)
    346 
    347     def _create_connection(self):

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in _checkout(cls, pool, threadconns, fairy)
    789     def _checkout(cls, pool, threadconns=None, fairy=None):
    790         if not fairy:
--> 791             fairy = _ConnectionRecord.checkout(pool)
    792 
    793             fairy._pool = pool

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in checkout(cls, pool)
    530     @classmethod
    531     def checkout(cls, pool):
--> 532         rec = pool._do_get()
    533         try:
    534             dbapi_connection = rec.get_connection()

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in _do_get(self)
   1194             except:
   1195                 with util.safe_reraise():
-> 1196                     self._dec_overflow()
   1197         else:
   1198             return self._do_get()

/usr/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback)
     64             self._exc_info = None   # remove potential circular references
     65             if not self.warn_only:
---> 66                 compat.reraise(exc_type, exc_value, exc_tb)
     67         else:
     68             if not compat.py3k and self._exc_info and self._exc_info[1]:

/usr/lib/python3.5/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause)
    247         if value.__traceback__ is not tb:
    248             raise value.with_traceback(tb)
--> 249         raise value
    250 
    251 else:

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in _do_get(self)
   1191         if self._inc_overflow():
   1192             try:
-> 1193                 return self._create_connection()
   1194             except:
   1195                 with util.safe_reraise():

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in _create_connection(self)
    348         """Called by subclasses to create a new ConnectionRecord."""
    349 
--> 350         return _ConnectionRecord(self)
    351 
    352     def _invalidate(self, connection, exception=None, _checkin=True):

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in __init__(self, pool, connect)
    475         self.__pool = pool
    476         if connect:
--> 477             self.__connect(first_connect_check=True)
    478         self.finalize_callback = deque()
    479

/usr/lib/python3.5/site-packages/sqlalchemy/pool.py in __connect(self, first_connect_check)
    672         try:
    673             self.starttime = time.time()
--> 674             connection = pool._invoke_creator(self)
    675             pool.logger.debug("Created new connection %r", connection)
    676             self.connection = connection

/usr/lib/python3.5/site-packages/sqlalchemy/engine/strategies.py in connect(connection_record)
    104                         if connection is not None:
    105                             return connection
--> 106                 return dialect.connect(*cargs, **cparams)
    107 
    108             creator = pop_kwarg('creator', connect)

/usr/lib/python3.5/site-packages/sqlalchemy/engine/default.py in connect(self, *cargs, **cparams)
    410 
    411     def connect(self, *cargs, **cparams):
--> 412         return self.dbapi.connect(*cargs, **cparams)
    413 
    414     def create_connect_args(self, url):

/usr/lib/python3.5/site-packages/mysql/connector/__init__.py in connect(*args, **kwargs)
    180 
    181     if HAVE_CEXT and not use_pure:
--> 182         return CMySQLConnection(*args, **kwargs)
    183     return MySQLConnection(*args, **kwargs)
    184 Connect = connect  # pylint: disable=C0103

/usr/lib/python3.5/site-packages/mysql/connector/connection_cext.py in __init__(self, **kwargs)
     76 
     77         if kwargs:
---> 78             self.connect(**kwargs)
     79 
     80     def _do_handshake(self):

/usr/lib/python3.5/site-packages/mysql/connector/abstracts.py in connect(self, **kwargs)
    734 
    735         self.disconnect()
--> 736         self._open_connection()
    737         self._post_connection()
    738

/usr/lib/python3.5/site-packages/mysql/connector/connection_cext.py in _open_connection(self)
    177         except MySQLInterfaceError as exc:
    178             raise errors.get_mysql_exception(msg=exc.msg, errno=exc.errno,
--> 179                                              sqlstate=exc.sqlstate)
    180         self._do_handshake()
    181

ProgrammingError: (mysql.connector.errors.ProgrammingError) 1226 (42000): User 'divdasani' has exceeded the 'max_user_connections' resource (current value: 6) (Background on this error at: http://sqlalche.me/e/f405)

[edit by admin: formatting]

divdasani | 5 posts | Jan. 5, 2019, 11:54 p.m. | permalink

It looks like you have a number of MySQL consoles open, and a few programs running. Each of the MySQL consoles will have a connection open, and if the programs are also accessing the database, they may be using one or more too. So if you try closing the MySQL consoles, that should fix the problem.

Staff giles | 12074 posts | PythonAnywhere staff | Jan. 6, 2019, 2:30 p.m. | permalink