MySQL Bugs: #9359: Prepared statements take snapshot of system vars at PREPARE time

Bug #9359	Prepared statements take snapshot of system vars at PREPARE time
Submitted:	23 Mar 2005 13:48	Modified:	18 Jul 2005 19:33
Reporter:	Guilhem Bichot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1 and 5.0	OS:	Linux (linux)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
This has been discussed with Dmitri and Konstantin. See how-to-repeat.
A consequence of this bug is that if you enable rpl_timezone with ps-protocol (remove --disable_ps_protocol), the test will fail in replication of CONVERT_TZ(,,@@time_zone); because it processes @@time_zone only at prepare time, thus thd->time_zone_used is set to 1 only at prepare time, and reset to 0 when execution starts, and not set back to 1, so the content of @@time_zone is not written to binlog (as thd->time_zone_used==0 at execution, binlogging time), thus slave fails. So when you close this bug, please re-enable rpl_timezone in ps-protocol. Thanks.

How to repeat:
Create mysql-test/t/ps_sys_var.test with this in it:
prepare stmt from "select @@time_zone";
set @@time_zone:='Japan';
execute stmt;
deallocate prepare stmt;
and do
touch mysql-test/r/ps_sys_var.result. Run 
./mysql-test-run --skip-innodb t/ps_sys_var.test and observe the result:
+ prepare stmt from "select @@time_zone";
+ set @@time_zone:='Japan';
+ execute stmt;
+ @@time_zone
+ SYSTEM
+ deallocate prepare stmt;

We should see "Japan" instead of "SYSTEM".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/27189

Fixed in 4.1 tree, tagged 4.1.14, merged into 5.0 (tagged 5.0.10)

Noted in 4.1.14, 5.0.10 changelogs.