Python MYSQL connection does not work after 1h idle

[zazas321]

Hello. I have created MYSQL db on UBUNTU server. I have my Python script running which is connecting to that MYSQL db.

1	mysql-connector-python

I connect to my db using the following command:

1	myConnection = mysql.connector.connect(host=hostname, user=username, passwd=password, db=database, autocommit=true, auth_plugin = "mysql_native_password")

I have started noticing very strange issues with my python program. I have been testing it for a while and can confirm the following:

After 1 hour of IDLE connection ( not performing any querries ), the connection will still be UP (I can still see the connection in the mysql PROCESSLIST) but it wont allow me to perform any queries. Performing any querry will cause the cursor method to hang (I call this cursor method before executing any query).

1	myConnection .cursor() # will hang if called after 1 hour of idle

I have checked the timeout variable:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

mysql> show variables like "%timeout%"; +-----------------------------------+----------+ | connect_timeout                   | 10       | | delayed_insert_timeout            | 300      | | have_statement_timeout            | YES      | | innodb_flush_log_at_timeout       | 1        | | innodb_lock_wait_timeout          | 50       | | innodb_rollback_on_timeout        | OFF      | | interactive_timeout               | 28800    | | lock_wait_timeout                 | 31536000 | | mysqlx_connect_timeout            | 30       | | mysqlx_idle_worker_thread_timeout | 60       | | mysqlx_interactive_timeout        | 28800    | | mysqlx_port_open_timeout          | 0        | | mysqlx_read_timeout               | 30       | | mysqlx_wait_timeout               | 28800    | | mysqlx_write_timeout              | 60       | | net_read_timeout                  | 30       | | net_write_timeout                 | 60       | | replica_net_timeout               | 60       | | rpl_stop_replica_timeout          | 31536000 | | rpl_stop_slave_timeout            | 31536000 | | slave_net_timeout                 | 60       | | wait_timeout                      | 28800    | +-----------------------------------+----------+

I believe these are the default parameters as I havent changed anything. My mysql database is running on UBUNTU server.


So as you can see from above, wait_timeout and interactive_timeout is set to 28800 so it should only timeout after 8 hours. I dont seem to understand why it does not allow me to execute any queries after 1 hour of idle.


***UPDATE***

I still havent found the solution but I have ran some tests and got more information.

1. As I have mentioned above, after about 3600 seconds, the connection will still be active and I can see it on the list of processes:

1 2 3 4 5 6 7 8 9 10 11

mysql> SHOW PROCESSLIST; +------+-----------------+---------------------+------+---------+---------+------------------------+------------------+ | Id   | User            | Host                | db   | Command | Time    | State                  | Info             | +------+-----------------+---------------------+------+---------+---------+------------------------+------------------+ |    5 | event_scheduler | localhost           | NULL | Daemon  | 1382754 | Waiting on empty queue | NULL             | | 2262 | PTL             | 192.168.3.251:51752 | test | Sleep   |    7729 |                        | NULL             | | 2263 | PTL             | localhost           | NULL | Query   |       0 | init                   | SHOW PROCESSLIST | | 2264 | PTL             | 192.168.3.251:51908 | test | Sleep   |    2884 |                        | NULL             | | 2387 | PTL             | 192.168.3.251:45676 | test | Sleep   |     183 |                        | NULL             | +------+-----------------+---------------------+------+---------+---------+------------------------+------------------+ 5 rows in set (0.00 sec)

Notice the connection with ID 2262. It has been idling for 7729 seconds. If I wait for another few minutes, at about 8000 the connection will suddenly drop.

So what I know so far:
1. After 3600 seconds, the connection remains but no it will not allow me to execute any queries
2. After about 8000 seconds, the connection will dissapear all of a sudden. I have verified it with the SHOW PROCESSLIST command:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

mysql> SHOW PROCESSLIST; +------+-----------------+---------------------+------+---------+---------+------------------------+------------------+ | Id   | User            | Host                | db   | Command | Time    | State                  | Info             | +------+-----------------+---------------------+------+---------+---------+------------------------+------------------+ |    5 | event_scheduler | localhost           | NULL | Daemon  | 1383008 | Waiting on empty queue | NULL             | | 2262 | PTL             | 192.168.3.251:51752 | test | Sleep   |    7983 |                        | NULL             | | 2263 | PTL             | localhost           | NULL | Query   |       0 | init                   | SHOW PROCESSLIST | | 2264 | PTL             | 192.168.3.251:51908 | test | Sleep   |    3138 |                        | NULL             | | 2387 | PTL             | 192.168.3.251:45676 | test | Sleep   |     437 |                        | NULL             | +------+-----------------+---------------------+------+---------+---------+------------------------+------------------+ 5 rows in set (0.00 sec)   mysql> SHOW PROCESSLIST; +------+-----------------+---------------------+------+---------+---------+------------------------+------------------+ | Id   | User            | Host                | db   | Command | Time    | State                  | Info             | +------+-----------------+---------------------+------+---------+---------+------------------------+------------------+ |    5 | event_scheduler | localhost           | NULL | Daemon  | 1383018 | Waiting on empty queue | NULL             | | 2263 | PTL             | localhost           | NULL | Query   |       0 | init                   | SHOW PROCESSLIST | | 2264 | PTL             | 192.168.3.251:51908 | test | Sleep   |    3148 |                        | NULL             | | 2387 | PTL             | 192.168.3.251:45676 | test | Sleep   |     447 |                        | NULL             | +------+-----------------+---------------------+------+---------+---------+------------------------+------------------+ 4 rows in set (0.00 sec)

You can see from the above, I have called the SHOW PROCESSLIST and the time was 7983. Then after about 10 seconds, I have called it again, and the connection was no longer there.

I cannot get my head around where those timeouts are coming from. From the timeout variable , the wait_timeout and interactive_timeout are set to 28800 by default, so I expect the idle connections to be timed out after 8 hours.



Another strange thing - this only seems to be happening when I connect to mysql DB from python script. If I connect to the mysql db from my ubuntu virtual machine, I have tried waiting for 1.5 hours without executing any queries and then executing some query - it worked fine. So it seems to only be a problem with python. Can someone help me debug this issue please.

[zazas321]

Still need help regarding this issue

[Larz60+]

It's more likely that the problem is with MySQL and not with python.
If it were python, we would have heard it from more folks but as far as I know, have not.

MySQL has several timeouts, you should check them for proper value (especially since the time period seems constant)
I don't use MySQL, so don't know about them, but here's a page where you can start:
https://dev.mysql.com/doc/search/?d=&p=1...ct+timeout

[tomtom]

I used to have similar problem with mysql database. when I used with a windows server, I search and search but didn't find a solution on it, so what I did was to create a function that connect to the mysql db and call it any time I want to make a query to the dp. That is how I solved my own

these is the example:

1 2 3 4 5

def connect_to_db(): # function connect to database     return mysql.connector.connect(host="localhost",                                    user="root",                                    passwd="",                                    database="lookup_bot")

Then I do this whenever I want to make a query:

1 2	mydp = connect_to_db() my_cursor = mydp.cursor(buffered=True)

[zazas321]

It's more likely that the problem is with MySQL and not with python.
If it were python, we would have heard it from more folks but as far as I know, have not.

MySQL has several timeouts, you should check them for proper value (especially since the time period seems constant)
I don't use MySQL, so don't know about them, but here's a page where you can start:
https://dev.mysql.com/doc/search/?d=&p=1...ct+timeout

Hey thanks for the reply first of all. The issue only appears when I connect to mysql through python. I can connect to mysql through other machines from shell and I can idle for up to 8 hours and still execute the command without any issues.

[zazas321]

I used to have similar problem with mysql database. when I used with a windows server, I search and search but didn't find a solution on it, so what I did was to create a function that connect to the mysql db and call it any time I want to make a query to the dp. That is how I solved my own

these is the example:

1 2 3 4 5

def connect_to_db(): # function connect to database     return mysql.connector.connect(host="localhost",                                    user="root",                                    passwd="",                                    database="lookup_bot")

Then I do this whenever I want to make a query:

1 2	mydp = connect_to_db() my_cursor = mydp.cursor(buffered=True)

Thanks for the reply. Can you explain what the code does? Do you make a new connection everytime you want to perform a query? If so, that does not seem like a good solution?
The problem for me is that after 1 hour of idling, the connection is still active and I can see the connection on the processlist as I have shown before. Even though the connection is still active, it wont let me do any queries.

As a temporary solution, I have some function that is being executed every 10 minutes which performs some query to my database to ensure that the connection does not idle for too long. That seems to work however that is not an optimal solution

[tomtom]

I used to have similar problem with mysql database. when I used with a windows server, I search and search but didn't find a solution on it, so what I did was to create a function that connect to the mysql db and call it any time I want to make a query to the dp. That is how I solved my own

these is the example:

1 2 3 4 5

def connect_to_db(): # function connect to database     return mysql.connector.connect(host="localhost",                                    user="root",                                    passwd="",                                    database="lookup_bot")

Yes, that is what I did but have not find any bug on it

Then I do this whenever I want to make a query:

1 2	mydp = connect_to_db() my_cursor = mydp.cursor(buffered=True)

Thanks for the reply. Can you explain what the code does? Do you make a new connection everytime you want to perform a query? If so, that does not seem like a good solution?
The problem for me is that after 1 hour of idling, the connection is still active and I can see the connection on the processlist as I have shown before. Even though the connection is still active, it wont let me do any queries.

As a temporary solution, I have some function that is being executed every 10 minutes which performs some query to my database to ensure that the connection does not idle for too long. That seems to work however that is not an optimal solution

[jefsummers]

I have a MySQL project that I am currently working on (in Java, sorry), and whenever I want to do anything I make a call to a function I call assureCon where I check to see if the connection is valid, and if not then remake the connection. I haven't tried connection to MySQL in Python but I am sure there must be a similar function as my call to isValid() in Java.

1 2 3 4 5 6 7 8

private void assureCon() {     try {         if (!con.isValid(2)) {             con = DriveManager.getConnection(url, user, password);             st = con.createStatement();     } catch (SQLException ex) {         showSQLErr(ex);     }

[jefsummers]

Got a few minutes to look it up. To see if a connection is still available/working, use ping - see MySQL Ping

[ndc85430]

Why aren't people using connection pooling in their applications (e.g. Hikari if you're on the JVM; I'm not sure what exists for Python)?