| Bug #41719 | delayed INSERT into timestamp col needs set time_zone for concurrent binlogging | ||
|---|---|---|---|
| Submitted: | 23 Dec 2008 19:08 | Modified: | 15 Apr 2009 10:02 |
| Reporter: | Andrei Elkin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.0, 5.1 bzr | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[23 Dec 2008 20:07]
Andrei Elkin
Please ignore 5.0 remark in the Description, Enter hit too early. The issue applies to 5.1 and 6.0 as well as either version binlog the delayed insert the same way.
[24 Dec 2008 7:27]
Sveta Smirnova
Thank you for the report. Verified as described with versions 5.0 and 5.1. Can not repeat with version 6.0.
[24 Dec 2008 7:28]
Sveta Smirnova
In the "how to repeat"
insert into t values ('2008-12-23 19:39:39', 1);
should be
insert delayed into t values ('2008-12-23 19:39:39', 1);
Regular inserts are not affected.
[24 Dec 2008 12:55]
Andrei Elkin
Sveta, thanks for confirming and correcting! Actually at opening I meant the 2nd of the connection two insert to be DELAYED. Your version reperesents the same bug, because the delayed insert's time_zone is not in the binlog and the default of the server is used.
[12 Feb 2009 11:46]
Andrei Elkin
Li Zhou, passing this bug to you after being asked to share load by Lars.
[7 Mar 2009 5:18]
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/commits/68575 2767 Leonard Zhou 2009-03-05 BUG#41719 delayed INSERT into timestamp col needs set time_zone for concurrent binlogging When do 'insert delayed' operation, the time_zone info doesn't follow with the row info. So when we do insert sometime later, time_zone didn't write into binlog. This will cause wrong result for timestamp column in slave. Our solution is that adding time_zone info with the delayed-row and restoring time_zone from row-info when execute that row in the furture by another thread. So we can write correct time_zone info into binlog and got correct result in slave.
[12 Mar 2009 9:11]
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/commits/69001 2767 Leonard Zhou 2009-03-09 BUG#41719 delayed INSERT into timestamp col needs set time_zone for concurrent binlogging When do 'insert delayed' operation, the time_zone info doesn't be keeped in the row info. So when we do insert sometime later, time_zone didn't write into binlog. This will cause wrong result for timestamp column in slave. Our solution is that adding time_zone info with the delayed-row and restoring time_zone from row-info when execute that row in the furture by another thread. So we can write correct time_zone info into binlog and got correct result in slave.
[23 Mar 2009 3:44]
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/commits/70002 2767 Leonard Zhou 2009-03-23 BUG#41719 delayed INSERT into timestamp col needs set time_zone for concurrent binlogging When do 'insert delayed' operation, the time_zone info doesn't be keeped in the row info. So when we do insert sometime later, time_zone didn't write into binlog. This will cause wrong result for timestamp column in slave. Our solution is that adding time_zone info with the delayed-row and restoring time_zone from row-info when execute that row in the furture by another thread. So we can write correct time_zone info into binlog and got correct result in slave.
[24 Mar 2009 1:42]
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/commits/70130 2767 Leonard Zhou 2009-03-24 BUG#41719 delayed INSERT into timestamp col needs set time_zone for concurrent binlogging When do 'insert delayed' operation, the time_zone info doesn't be keeped in the row info. So when we do insert sometime later, time_zone didn't write into binlog. This will cause wrong result for timestamp column in slave. Our solution is that adding time_zone info with the delayed-row and restoring time_zone from row-info when execute that row in the furture by another thread. So we can write correct time_zone info into binlog and got correct result in slave.
[24 Mar 2009 11:51]
li zhou
Pushed int 5.0-bugteam, 5.1-bugteam, 6.0-bugteam
[27 Mar 2009 14:32]
Bugs System
Pushed into 5.0.80 (revid:joro@sun.com-20090327142516-55gumdxj39z6eijj) (version source revid:leonard@mysql.com-20090324004505-9kmb3dvtzti9v6rb) (merge vers: 5.0.79) (pib:6)
[27 Mar 2009 14:57]
Bugs System
Pushed into 5.1.34 (revid:joro@sun.com-20090327143448-wuuuycetc562ty6o) (version source revid:leonard@mysql.com-20090324062427-6l1ekgtoepm2i09c) (merge vers: 5.1.34) (pib:6)
[30 Mar 2009 12:04]
Jon Stephens
Documented bugfix in the 5.0.80 and 5.1.34 changelogs as follows:
An INSERT DELAYED into a TIMESTAMP column issued concurrently
with a an insert not using DELAYED on the same column, but
applied after the other insert, was logged using the same
timestamp as generated by the other (non-DELAYED) insert.
Set NDI status pending merge of fix to 6.0 tree.
[13 Apr 2009 9:22]
Bugs System
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090413084402-snnrocwzktcl88ny) (version source revid:horst@mysql.com-20090324104521-8wg34o459kh4j431) (merge vers: 6.0.11-alpha) (pib:6)
[15 Apr 2009 10:02]
Jon Stephens
Fix also now documented in 6.0.11; closed.
[9 May 2009 16:40]
Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (merge vers: 5.1.34-ndb-6.2.18) (pib:6)
[9 May 2009 17:37]
Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (merge vers: 5.1.34-ndb-6.3.25) (pib:6)
[9 May 2009 18:35]
Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (merge vers: 5.1.34-ndb-7.0.6) (pib:6)
[25 Dec 2010 10:07]
Sveta Smirnova
Bug #23966 was marked as duplicate of this one.
Description: However it does not have set @@session.time_zone associated as can be seen with running mysqlbinlog on a binlog file composed with the How-to-repeat script. Therefore, in concurrent execution with a plain insert, if the delayed insert is recorded after the plain it will re-use the plain insert's time_zone - not its own - at execution of binlog (e.g upon replicating to the slave sid..e). It seems, the issue relates to the 5.0 version solely. How to repeat: connection one; CREATE TABLE `t` ( `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `a` int(11) default NULL ); SET @@session.time_zone='+01:00'; insert into t values ('2008-12-23 19:39:39', 1); connection two; SET @@session.time_zone='+02:00'; insert into t values ('2008-12-23 19:39:39', 1); select * from t /* the correct results on master in connection two's timezone */; +---------------------+------+ | date | a | +---------------------+------+ | 2008-12-23 20:39:39 | 1 | | 2008-12-23 19:39:39 | 2 | +---------------------+------+ delete from t; # apply the binlog ../client/mysqlbinlog var/log/master-bin.000001 | mysql -h127.0.0.1 -P11110 -uroot test connection two; mysql> select * from t; +---------------------+------+ | date | a | +---------------------+------+ | 2008-12-23 20:39:39 | 1 | | 2008-12-23 20:39:39 | 2 | +---------------------+------+ # The 2nd row has changed to incorrect. Suggested fix: Binlog insert delayed with its parent session's time_zone.