Python Forum
Python MYSQL connection does not work after 1h idle
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Python MYSQL connection does not work after 1h idle
#1
Hello. I have created MYSQL db on UBUNTU server. I have my Python script running which is connecting to that MYSQL db.
mysql-connector-python
I connect to my db using the following command:
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).
myConnection .cursor() # will hang if called after 1 hour of idle
I have checked the timeout variable:

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:

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:
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.
Reply
#2
Still need help regarding this issue
Reply
#3
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
Reply
#4
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:
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:
mydp = connect_to_db()
my_cursor = mydp.cursor(buffered=True)
Reply
#5
(Oct-05-2021, 11:04 AM)Larz60+ Wrote: 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.
Reply
#6
(Oct-05-2021, 03:11 PM)tomtom Wrote: 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:
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:
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
Reply
#7
(Oct-06-2021, 05:21 AM)zazas321 Wrote:
(Oct-05-2021, 03:11 PM)tomtom Wrote: 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:
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:
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
Reply
#8
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.
private void assureCon() {
    try {
        if (!con.isValid(2)) {
            con = DriveManager.getConnection(url, user, password);
            st = con.createStatement();
    } catch (SQLException ex) {
        showSQLErr(ex);
    }
Reply
#9
Got a few minutes to look it up. To see if a connection is still available/working, use ping - see MySQL Ping
Reply
#10
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)?
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  MAC Python IDLE issue shadow12 4 784 Oct-29-2021, 12:22 AM
Last Post: shadow12
  Serial connection connection issue Joni_Engr 15 1,931 Aug-30-2021, 04:46 PM
Last Post: deanhystad
  Python and MySql ogautier 8 1,237 May-20-2021, 11:10 PM
Last Post: Pedroski55
  Customize Python Keywords for IDLE alanvers 0 922 Apr-03-2021, 10:56 AM
Last Post: alanvers
  Python and MySQL Pedroski55 5 1,373 Mar-23-2021, 06:40 AM
Last Post: ndc85430
  Python MySQL ogautier 0 1,170 Sep-03-2020, 03:54 PM
Last Post: ogautier
  Insert into mysql through python LaKhWaN 0 935 Aug-26-2020, 04:54 AM
Last Post: LaKhWaN
  How can I make a plot representing connection relationship with python matplotlib? Berlintofind 1 911 May-08-2020, 09:27 PM
Last Post: jefsummers
  Python mysql query help please tduckman 4 2,355 Mar-13-2020, 03:42 PM
Last Post: Marbelous
  Connection DATABASE to Python MenThoLLt 3 1,147 Jan-17-2020, 10:35 PM
Last Post: DT2000

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020