Bug #45689 SESSION 'interactive_timeout' variable is useless
Submitted: 23 Jun 2009 19:37 Modified: 25 Jun 2009 7:16
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1, 5.0, 5.1 bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: qc
Triage: Triaged: D3 (Medium)

[23 Jun 2009 19:37] Peter Laursen

1) "interactive_timeout	Yes	Yes	Yes	Both	Yes"
('Both' means that 'interactive_timeout' can be both a GLOBAL and a SESSION variable)

2)"On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()). See also interactive_timeout."

Both doc starements are correct - server behaves like that - but behaviour is inconsistent and confusing.

How to repeat:
I have 'interactive_timeout' = 10' in configuration. 'wait_timeout' is not explicitly defined (and will be default = 28800)

SET INTERACTIVE_TIMEOUT = somevalidnumber 
.. is consistent. SHOW returns what was SET. All in accorance with 1) above

I connect with a non-interactive client and an interactive client and execute 'SHOW VARIABLES LIKE 'wait_timeout'. Non-interactive client returns '28800', interactive client returns '10'  All in accordance with 2) above

But the problem is that "SET INTERACTIVE_TIMEOUT = somevalidnumber" has no effect at all - except that the value of the SESSION variable will change.  But except for this it changes nothing. SESSION SET INTERACTIVE_TIMEOUT is never used for any purpose.

(the clients I used were 1) the 'standard' SQLyog (non-interactive) and 2) a specially compiled SQLyog build compiled as interactive (by setting interactive flag true in mysql_real_connect()).  For two reasons: 1) this is the client I know best 2) it logs when reconnects take place and thus it is easy to verify timeouts followed by reconnects)

Suggested fix:
This particular bug is a particular well-documented bug (ie: the docs are just as buggy as the server! :-) ).  But it is never the less a a bug as SESSION interactive_timeout variable is useless and only confusing.

It looks very much like somebody implementing SESSION interactive_timeout variable did not understand what 'interactive_timeout' is for and how it is used by the server!

SESSION interactive_timeout variable should be removed - simply. 

And actually with the option to SET SESSION 'wait_timeout' since early 5.0 I would consider to depreciate 'interactive_timeout'.  But as it involves the protocol and/or the client API it is probably not so simple. So backwards compability may be more important.

Please don't tell that his is *not a bug* just because behaviour is documented.  It is inconsistent and 'opaque' (thanks for this word, Sveta!) for no reason.
[23 Jun 2009 21:37] Peter Laursen
just a clarification:

Once connection has been established (with either interactive or non-interactive client) ..

I can "SET wait_timeout .." for the session.  This works as expected.  The server will time-out the session as 'wait_timeout' was SET

I can also "SET interactive_timeout .." for the session. It changes the value of the 'interactive_timeout' SESSION variable *only*.  It has no effect on how and when the server will time-out the session. That is why SESSSION 'interactive_timeout' variable is useless and confusing and should be removed!
[24 Jun 2009 11:08] Peter Laursen
Whether this will come out to be classified as a *bug*, *feature request* or *not a bug*, I do not expect a priority fix (it is not very important either if only the behaviour is clear).

But I do request the docs to be updated with a statement for the 'interactive_timeout' variable like "As 'interactive_timeout' is a SESSION variable it may be SET from a client and the value of the SESSION variable will change accordingly.  But the 'interactive_timeout' SESSION varaible is not used by the server to handle timeout for that session - only 'wait_timeout' is. In other words: the SESSION 'interactive_timeout' variable is currently not used."
[25 Jun 2009 7:16] Sveta Smirnova
Thank you for the report.

Verified as described.