Bug #55478 | Row events wrongly apply on the temporary table of the same name | ||
---|---|---|---|
Submitted: | 22 Jul 2010 11:56 | Modified: | 27 Jan 2011 20:08 |
Reporter: | Libing Song | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
Version: | 5.5, 5.1 | OS: | Any |
Assigned to: | Libing Song | CPU Architecture: | Any |
[22 Jul 2010 11:56]
Libing Song
[22 Jul 2010 11:59]
Libing Song
see Bug#47899.
[22 Jul 2010 12:42]
Sveta Smirnova
Thank you for the report. Verified as described. Looks like version 5.1 is not affected.
[22 Jul 2010 14:15]
Luis Soares
Hi Sveta, Li-Bing, I get a failure in 5.1 as well: "Table definition on master and slave does not match: Column 0 type mismatch - received type 254, test.t1 has type 3" if I use MIXED, create the table and then issue a statement that switches to ROW. Sample test case: source include/master-slave.inc; SET BINLOG_FORMAT='MIXED'; CREATE TEMPORARY TABLE t1(c1 INT); CREATE TABLE t1(c2 char(10)); INSERT INTO t1 SELECT RAND(); CREATE TABLE IF NOT EXISTS t1 SELECT 1; CREATE TABLE IF NOT EXISTS t1 SELECT 1; SELECT * FROM t1; DROP TEMPORARY TABLE t1; SELECT * FROM t1; sync_slave_with_master; Sveta, can you please re-verify this against 5.1?
[23 Jul 2010 5:28]
Sveta Smirnova
Luís, thank you for the test. It fails with 5.1 too.
[5 Aug 2010 4:01]
Zhenxing He
See also bug#55709
[15 Oct 2010 8:21]
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/120810 3324 Li-Bing.Song@sun.com 2010-10-15 Bug#55478 Row events wrongly apply on the temporary table of the same name Rows events were applied wrongly on the temporary table with the same name. But rows events are generated only for base tables. As temporary table's data never be binlogged on row mode. Normally, base table of the same name cannot be updated if a temporary table has the same name. But there are some cases which can generate rows events on the base table of same name. After this patch, slave opens only base table when applying a rows event.
[18 Oct 2010 6:46]
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/120927 3324 Li-Bing.Song@sun.com 2010-10-18 Bug#55478 Row events wrongly apply on the temporary table of the same name Rows events were applied wrongly on the temporary table with the same name. But rows events are generated only for base tables. As temporary table's data never be binlogged on row mode. Normally, base table of the same name cannot be updated if a temporary table has the same name. But there are two cases which can generate rows events on the base table of same name. Case1: 'CREATE TABLE ... SELECT' statement. In mixed format, it will generate rows events if it is unsafe. Case2: Drop a transactional temporary table in a transaction. BEGIN; DROP TEMPORARY TABLE t1; # t1 is a InnoDB table INSERT INTO t1 VALUES(rand()); # t1 is a MyISAM table COMMIT; 'DROP TEMPORARY TABLE' will be put in the transaction cache and binlogged after the rows events generated by the 'INSERT' statement. After this patch, slave opens only base table when applying a rows event.
[19 Oct 2010 17:33]
Omer Barnir
triage: changing tag from CHECKED to SR51MRU based on low risk and feedback from development
[21 Oct 2010 5: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/121432 3544 Li-Bing.Song@sun.com 2010-10-21 Bug#55478 Row events wrongly apply on the temporary table of the same name Rows events were applied wrongly on the temporary table with the same name. But rows events are generated only for base tables. As temporary table's data never be binlogged on row mode. Normally, base table of the same name cannot be updated if a temporary table has the same name. But there are two cases which can generate rows events on the base table of same name. Case1: 'CREATE TABLE ... SELECT' statement. In mixed format, it will generate rows events if it is unsafe. Case2: Drop a transactional temporary table in a transaction (happens only on 5.5+). BEGIN; DROP TEMPORARY TABLE t1; # t1 is a InnoDB table INSERT INTO t1 VALUES(rand()); # t1 is a MyISAM table COMMIT; 'DROP TEMPORARY TABLE' will be put in the transaction cache and binlogged after the rows events generated by the 'INSERT' statement. After this patch, slave opens only base table when applying a rows event.
[21 Oct 2010 6:02]
Libing Song
Pushed into mysql-5.1-bugteam and merged into mysql-5.5-bugteam and mysql-trunk-merge.
[1 Nov 2010 12:28]
Jon Stephens
Documented bugfix as follows in the 5.1.53 changelog: If there exist both a temporary table and a non-temporary table having the same, updates normally apply only to the temporary table, with the exception of a CREATE TABLE ... SELECT statement that creates a non-temporary table having the same name as an existing temporary table. When such a statement was replicated using the MIXED logging format, and the statement was unsafe, updates were misapplied to the temporary table. Changelog entry for 5.5.8 includes above plus the following: Updates were also applied wrongly in the case where a temporary table that used a transactional storage engine was dropped inside a transaction, followed by updates within the same transaction to a non-temporary table having the same name. Set Need Merge status, waiting for merge to -trunk.
[4 Nov 2010 17:29]
Jon Stephens
Already documented in the 5.5.8 changelog; still waiting for merge to trunk.
[13 Nov 2010 16:19]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:38]
Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)
[15 Nov 2010 13:26]
Jon Stephens
Does not appear in a 5.6 release; no additional changelog entries required. Closed.
[18 Nov 2010 15:53]
Bugs System
Pushed into mysql-5.1 5.1.54 (revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (version source revid:build@mysql.com-20101118153531-693taxtxyxpt037i) (merge vers: 5.1.54) (pib:21)
[16 Dec 2010 22:32]
Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)
[13 Jan 2011 3:22]
James Day
Please add the missing word "name" before the comma in the release notes for this: If there exist both a temporary table and a non-temporary table having the same, updates normally apply only to the temporary
[27 Jan 2011 20:08]
Jon Stephens
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.