[mysql] Terminating idle mysql connections

Manual cleanup:

You can KILL the processid.

mysql> show full processlist;
+---------+------------+-------------------+------+---------+-------+-------+-----------------------+
| Id      | User       | Host              | db   | Command | Time  | State | Info                  |
+---------+------------+-------------------+------+---------+-------+-------+-----------------------+
| 1193777 | TestUser12 | 192.168.1.11:3775 | www  | Sleep   | 25946 |       | NULL                  |
+---------+------------+-------------------+------+---------+-------+-------+-----------------------+

mysql> kill 1193777;

But:

  • the php application might report errors (or the webserver, check the error logs)
  • don't fix what is not broken - if you're not short on connections, just leave them be.

Automatic cleaner service ;)

Or you configure your mysql-server by setting a shorter timeout on wait_timeout and interactive_timeout

mysql> show variables like "%timeout%";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| connect_timeout          | 5     |
| delayed_insert_timeout   | 300   |
| innodb_lock_wait_timeout | 50    |
| interactive_timeout      | 28800 |
| net_read_timeout         | 30    |
| net_write_timeout        | 60    |
| slave_net_timeout        | 3600  |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 28800 |
+--------------------------+-------+
9 rows in set (0.00 sec)

Set with:

set global wait_timeout=3;
set global interactive_timeout=3;

(and also set in your configuration file, for when your server restarts)

But you're treating the symptoms instead of the underlying cause - why are the connections open? If the PHP script finished, shouldn't they close? Make sure your webserver is not using connection pooling...

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to timeout

Waiting for Target Device to Come Online Spring Boot Java Config Set Session Timeout How to dispatch a Redux action with a timeout? Spring Boot REST API - request timeout? 5.7.57 SMTP - Client was not authenticated to send anonymous mail during MAIL FROM error How to set timeout in Retrofit library? How to set connection timeout with OkHttp How to modify the nodejs request default timeout time? How to handle ETIMEDOUT error? Timeout for python requests.get entire response

Examples related to kill

How to kill a nodejs process in Linux? What does 'killed' mean when a processing of a huge CSV with Python, which suddenly stops? how to kill hadoop jobs Why number 9 in kill -9 command in unix? How to kill a process on a port on ubuntu How to kill a child process by the parent process? How to kill all processes matching a name? How do I kill an Activity when the Back button is pressed? Terminating idle mysql connections How can I stop a running MySQL query?

Examples related to wait

How to make the script wait/sleep in a simple way in unity How do I make a delay in Java? Wait some seconds without blocking UI execution Protractor : How to wait for page complete after click a button? How to wait until an element is present in Selenium? Javascript sleep/delay/wait function How to wait till the response comes from the $http request, in angularjs? How to add a "sleep" or "wait" to my Lua Script? Concept behind putting wait(),notify() methods in Object class How can I wait for 10 second without locking application UI in android

Examples related to python-idle

How to turn on line numbers in IDLE? How to run a python script from IDLE interactive shell? How to repeat last command in python interpreter shell? Terminating idle mysql connections Any way to clear python's IDLE window? How to remove tab indent from several lines in IDLE? How to start IDLE (Python editor) without using the shortcut on Windows Vista?