Hello. I would like to get some clarifications from someone who has experience on MYSQL databases. I have some issues regarding clients timing out when they should not be timing out.
I have a python script running on Raspberry PI which connects to mysql database and performs various tasks. MYSQL database is created on UBUNTU server.
1. After about 1 hour of inactivity on the same mysql connection, my Python script no longer worked ( Not able to execute any mysql queries as the program just hangs whenever I call function:cursor = myConnection.cursor()
2. If I connect to my mysql database on my ubuntu machine and execute command:
mysql> SHOW PROCESSLIST;
+------+-----------------+---------------------+------+---------+---------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+---------------------+------+---------+---------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 1379500 | Waiting on empty queue | NULL |
| 2262 | PTL | 192.168.3.251:51752 | test | Sleep | 4475 | | NULL |
| 2263 | PTL | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
+------+-----------------+---------------------+------+---------+---------+------------------------+------------------+
3 rows in set (0.00 sec)
As you can see from the above, There is still a connect with the ID 2262 and the time since last query is 4475 seconds). On this mysql connection, I will not be able to perform any mysql queries as I have mentioned above. The connection will drop after about 8000 seconds of inactivity time. (I have run some tests to calculate it).
3. Once the program hangs oncursor = myConnection.cursor()
. The code will remain stuck until about 15 minutes has passed. Then my program will return "MYSQL connection not available".
All these times are so strange to me. So to sum up:
1. I cannot execute any mysql queries if the connection remains IDLE(performs no queries) for more than 1 hour ( 3600 seconds ) . If I perform query after about 3500 seconds of inactivity. Everything will work fine.
2. After about ~8000 seconds if inactivity, the connection will drop and I will no longer see it in the PROCESSLIST
I have checked the timeout settings in my mysql database:
mysql> show variables like "%timeout%";
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| 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 |
+-----------------------------------+----------+
22 rows in set (0.00 sec)
And I cannot link any of those timeouts to what I am seeing in practise. There is nothing related to 3600 seconds if inactivity to stop accepting queries from that connection and nothing related to 8000 seconds to drop the existing connection.
Please can someone give me any clues how to fix this error. I highly appreciate any ideas
I have a python script running on Raspberry PI which connects to mysql database and performs various tasks. MYSQL database is created on UBUNTU server.
1. After about 1 hour of inactivity on the same mysql connection, my Python script no longer worked ( Not able to execute any mysql queries as the program just hangs whenever I call function:cursor = myConnection.cursor()
2. If I connect to my mysql database on my ubuntu machine and execute command:
mysql> SHOW PROCESSLIST;
+------+-----------------+---------------------+------+---------+---------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+---------------------+------+---------+---------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 1379500 | Waiting on empty queue | NULL |
| 2262 | PTL | 192.168.3.251:51752 | test | Sleep | 4475 | | NULL |
| 2263 | PTL | localhost | NULL | Query | 0 | init | SHOW PROCESSLIST |
+------+-----------------+---------------------+------+---------+---------+------------------------+------------------+
3 rows in set (0.00 sec)
As you can see from the above, There is still a connect with the ID 2262 and the time since last query is 4475 seconds). On this mysql connection, I will not be able to perform any mysql queries as I have mentioned above. The connection will drop after about 8000 seconds of inactivity time. (I have run some tests to calculate it).
3. Once the program hangs oncursor = myConnection.cursor()
. The code will remain stuck until about 15 minutes has passed. Then my program will return "MYSQL connection not available".
All these times are so strange to me. So to sum up:
1. I cannot execute any mysql queries if the connection remains IDLE(performs no queries) for more than 1 hour ( 3600 seconds ) . If I perform query after about 3500 seconds of inactivity. Everything will work fine.
2. After about ~8000 seconds if inactivity, the connection will drop and I will no longer see it in the PROCESSLIST
I have checked the timeout settings in my mysql database:
mysql> show variables like "%timeout%";
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| 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 |
+-----------------------------------+----------+
22 rows in set (0.00 sec)
And I cannot link any of those timeouts to what I am seeing in practise. There is nothing related to 3600 seconds if inactivity to stop accepting queries from that connection and nothing related to 8000 seconds to drop the existing connection.
Please can someone give me any clues how to fix this error. I highly appreciate any ideas
