How to connect PHP to MySQL as an interactive connection is it will use interactive_timeout instead of wait_timeout?

Question

So, MySQL has wait_timeout and interactive_timeout. By default, MySQL connections we make use wait_timeout. I want to use interactive_timeout for connection with users so the timeout won't disturb user interaction. How can I create an interactive connection?

My current code is this:

$db_conn = mysqli_connect(HOSTNAME, DBUSERNAME, DBPASSWORD,DATABASE);

How exactly do you intend to have a PHP script be interactive? — Niet the Dark Absol, Jun 14 '20 at 12:49
Why don't you increase the `wait_timeout` for the non interactive session? — M. Dekker, Jun 14 '20 at 12:49
Are you going to use it with a socket server such as ratchet or with a regular web-server? — Your Common Sense, Jun 14 '20 at 14:09

score 1 · Accepted Answer · answered Jun 14 '20 at 12:50

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_interactive_timeout

"The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that uses the CLIENT_INTERACTIVE option to mysql_real_connect(). See also wait_timeout."

and, from PHP:

https://www.php.net/manual/en/mysqli.real-connect.php

score 1 · Answer 2 · answered Jun 14 '20 at 13:43

Even if you had read the documentation, it is not really clear what interactive_timeout means.

It means if a connection of an interactive client is idle for too long, the connection is automatically closed. This won't disturb any user interaction, because if they are running queries, by definition the connection is not idle.

The default value of both wait_timeout and interactive_timeout is 28800 seconds (8 hours). So the client would have to be doing nothing but holding open the connection for 8 hours before it times out due to inactivity. This is very unlikely to affect any PHP request, which close as soon as the request is done.

It is also not mentioned in the documentation that the only existing client that uses interactive_timeout is the mysql command-line client.

All connectors for languages like PHP, Java, ODBC, Python, Perl, etc. use the regular wait_timeout. I have not seen any connector that has an option to use interactive_timeout. Programmatic connectors are not considered interactive, even though the application they are connecting from accepts user input.

Even the new MySQL Shell client does not enable interactive_timeout. It relies on the traditional wait_timeout that other clients use.

How to connect PHP to MySQL as an interactive connection is it will use interactive_timeout instead of wait_timeout?

2 Answers2