MySQL Bugs: #45689: SESSION 'interactive_timeout' variable is useless

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:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1, 5.0, 5.1 bzr	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout

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)

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

2)
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.

just a clarification:

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

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

2)
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!

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."

Thank you for the report.

Verified as described.