mysql-connector-python

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

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

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    |
+-----------------------------------+----------+

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)

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)

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

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

(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&q=connect+timeout

(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)

(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

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