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

[23 Mar 2005 13:48] Guilhem Bichot
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".
[15 Jul 2005 19:14] Bugs 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
[15 Jul 2005 21:01] Konstantin Osipov
Fixed in 4.1 tree, tagged 4.1.14, merged into 5.0 (tagged 5.0.10)
[18 Jul 2005 19:33] Paul Dubois
Noted in 4.1.14, 5.0.10 changelogs.