mysql进程保持活力 - 为什么？

Question

I have a problem of mysql processes staying alive.

SHOW PROCESSLIST; returns a list of processes. There are processes existing for hours - according to process ID, host (port).

It seems like the process performs a query (SELECT) than sleeps, performs the query again - and it happens all over again. The "time" shown in PROCESSLIST keeps resetting after each sleep.

How to make Mysql automatically kill such processes? I suppose that such a process stays alive after a PHP script timeout (it also performs a CURL task).

[EDIT - to answer comments]

The query: SELECT COUNT(*) FROM keywords WHERE updated < '2018-05-03 00:00:00' AND status = '0' AND active = '1;

columns: updated (DATETIME), status (INT) and active (BOOL) are INDEXED.

There is ~120K records in the table "keywords".

My PHP script is connecting to MySQL via PDO.

I have also set the following variables:

interactive_timeout = 180 wait_timeout = 180

Answer 1

The behavior you have described is possible only with persistent connections. Persistent connections allow PHP to re-use a previous database connection when the credentials match. While there are downsides, persistent connections are not necessarily a bad thing and it is not necessary to manually kill them off.

If you want to disable persistent connections, then you must find the place in your code where the database connection is initialized. It should look something like this:

<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(
    PDO::ATTR_PERSISTENT => true
));
?>

Once you've located it, either remove the PDO::ATTR_PERSISTENT option or set it to false:

<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass, array(
    PDO::ATTR_PERSISTENT => false
));
?>

After you've disabled persistent connections, it will be impossible for the database session to persist after the exit of the PHP script. As stated by the docs:

The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.