MySQL Bugs: #48001: mysql-console-output wrong (variables differ to mysqladmin and PhpMyAdmin)

Bug #48001	mysql-console-output wrong (variables differ to mysqladmin and PhpMyAdmin)
Submitted:	12 Oct 2009 13:15	Modified:	12 Oct 2009 15:36
Reporter:	Ute Diestel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.0.45-log	OS:	Linux (RedHat 5.3 (tikanga))
Assigned to:		CPU Architecture:	Any
Tags:	console, MySQL, output wrong, variable

Description:
The output from show variables in mysql-client differ to phpMyAdmin and mysqladmin. (all time I used the same user@localhost)

----
The mysql-client (console) is from redhat-paket: 
#rpm -qi mysql
Version: 5.0.45,   Release: 7.el5 
Source: mysql-5.0.45-7.el5.src.rpm
Signature: 01.04.2008

mysaldmin --version:  Ver 8.41  Distr. 5.0.45 for redhat-linux-gnu on x86_64

phpMyAdmin: localhost 2.11.9.2  (2.x because PHP version)

------------------- what me matter -----------------------------
:-((  It seems mysql-client (console) output is wrong. This matter because I often use only console to administrate.

How to repeat:
--------------- what I do ---------------------------

### start (basic position)
=====================================================
mysql> show variables;
...
wait_timeout ... 600

### change variable
=====================================================
I change variables in mysql client (console) with:

mysql> set global wait_timeout = 255;
Query OK ...

or with:

mysql> set @@global.wait_timeout= 255;
Query OK ...

### control in console (mysql-client)
=====================================================
mysql> show variables;
...
wait_timeout ... 600
==> NOT changed  (also when I repeate,repeate and wait a little time) !!!

### control in diffent ways (phpMyAdmin, mysqladmin)
====================================================

#mysqladmin -u xxx -p variables
...
wait_timeout ... 255

# control in phpMyAdmin

==> wait_timeout ... 255

Suggested fix:
??

This is not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.0/en/set-option.html, about GLOBAL:

"f you change a global system variable, the value is remembered and used for new connections until the server restarts. (To make a global system variable setting permanent, you should set it in an option file.) The change is visible to any client that accesses that global variable. However, the change affects the corresponding session variable only for clients that connect after the change. The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL  statement)."

appendix:
=============

But.... also after new-connect with mysql-client (console: exit, and then mysql -u xxx -p) the variable is not change.

# after re-connect (new-connection):
mysql> show variables;
...
wait_timeout ... 600

... and thread-ID Connection changed.

This is also explained in the manual, http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout:

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

mysql command line client is interactive client, so it sets CLIENT_INTERACTIVE option.

huiii ... thats it !!! 
what a trap (time-warp :-)

...I have a flashback ...
=========== all time use ==========
mysql> show global variables;
===================================

Thank you !

(sorry I don't know the correlation between interactiv_ and wait_timeout in mysql-console) 

---------------------------------------------------------------

I need a little time to understand your answer, 
but for documentation now what I understand:

mysql> show variables;
...
interactive_timeout = 600
wait_timeout        = 600

mysql> set global interactive_timeout = 255;
#exit + re-connect in mysql-client (console)
mysql> show variables;
...
interactive_timeout ... 255
wait_timeout        ... 255     #but global wait_timeout still is 600 !!

mysql> show global variables;
...
interactive_timeout ... 255
wait_timeout        ... 600

---- solution ----
mysql-client(console): 
On connect use the interactive_timeout-value to set wait_timeout for connection
(overwrite the global wait_timeout for this connection).

mysqladmin + phpMyAdmin:
do what (I) expected, 
show's all time the global server-variables "wait_timeout" + "interactive_timeout" (current global server setting)
phpMyAdmin: no diffent if you use "SQL" "show variables" or "show global variables" or link to "system variables" (for "timeout" variables)