Hello. I have created MYSQL server on Ubuntu and been using it on my Raspberry PI for a while. I have noticed some strange things:
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 7920 - 7930 seconds of inactivity time. (I have run some tests to calculate it).
3. Once the program hangs on
cursor = 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 ~7920 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 7920 seconds to drop the existing connection.
Please can someone give me any clues how to fix this error. I highly appreciate any ideas