Terminating idle mysql connections

by

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…

More Related Contents:

  1. Getting “Lock wait timeout exceeded; try restarting transaction” even though I’m not using a transaction
  2. How to Prevent Connection Timeouts for Large MySQL Imports
  3. How can I stop a MySQL query if it takes too long?
  4. mysql_fetch_array() expects parameter 1 to be resource, boolean given in [closed]
  5. How to group by two rows in a column
  6. How to perform grouped ranking in MySQL
  7. How to abort INSERT operation in MySql trigger?
  8. Mysql or/and precedence?
  9. MySQL Trigger after update only if row has changed
  10. MySQL “Group By” and “Order By”
  11. MySQL Query to select data from last week?
  12. ERROR 2003 (HY000): Can’t connect to MySQL server on ‘127.0.0.1’ (111)
  13. Migrate from Oracle to MySQL
  14. Which is more efficient: Multiple MySQL tables or one large table?
  15. #1139 – Got error ‘repetition-operator operand invalid’ from regexp
  16. Are there disadvantages to using a generic varchar(255) for all text-based fields?
  17. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  18. MySQL InnoDB foreign key between different databases
  19. MySQL JSON: How to find object from key value
  20. Advanced MySql Query: Update table with info from another table
  21. Commit data in a mysql container
  22. MySQL sum elements of a column
  23. Difference between SET autocommit=1 and START TRANSACTION in mysql (Have I missed something?)
  24. Why MySQL is giving error “Not allowed to return a result set from a function”?
  25. MySQL cumulative sum grouped by date
  26. Where I can find a list of “mysqldump” exit codes?
  27. Equivalent of MSSQL IDENTITY Column in MySQL
  28. Update multiple rows with multiple ‘where’ clauses for each individual row
  29. WHERE datetime older than some time (eg. 15 minutes)
  30. Is there a way to use ON DUPLICATE KEY to Update all that I wanted to insert?

Leave a Comment