Bug #55474 | Replication diff and 'no warning' for Views when WL#5370 included in 5.1 | ||
---|---|---|---|
Submitted: | 22 Jul 2010 9:24 | Modified: | 15 Oct 2010 13:45 |
Reporter: | Nidhi Shrotriya | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S3 (Non-critical) |
Version: | 5.1-wl5370 | OS: | Any |
Assigned to: | Libing Song | CPU Architecture: | Any |
[22 Jul 2010 9:24]
Nidhi Shrotriya
[22 Jul 2010 10:45]
Nidhi Shrotriya
Another scenario where Bug#49494 fix causes the replication diff. in 5.1 pre -> 5.1 post fix wl5370. Steps: -------- DROP DATABASE IF EXISTS testdb_wl5370; CREATE DATABASE testdb_wl5370; USE testdb_wl5370; CREATE TABLE testdb_wl5370.t1_3 (a INT, b INT); CREATE VIEW testdb_wl5370.v1_3 as SELECT b, a FROM testdb_wl5370.t1_3; INSERT INTO testdb_wl5370.t1_3 values (1,2); INSERT INTO testdb_wl5370.v1_3 values (3,4); CREATE TABLE testdb_wl5370.t3_3 (a INT, b INT); INSERT INTO testdb_wl5370.t3_3 values (5,6); INSERT INTO testdb_wl5370.v1_3 select * from testdb_wl5370.t3_3; CREATE TABLE IF NOT EXISTS testdb_wl5370.v1_3 SELECT * FROM testdb_wl5370.t3_3; SELECT * from testdb_wl5370.t1_3; < INSERT INTO `t1_3` VALUES (1,2),(4,3),(6,5),(5,6); --- > INSERT INTO `t1_3` VALUES (1,2),(4,3),(6,5); 5.1-pre: -------------- mysql> CREATE TABLE IF NOT EXISTS testdb_wl5370.v1_3 SELECT * FROM testdb_wl5370.t3_3; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> show warnings; +-------+------+-----------------------------+ | Level | Code | Message | +-------+------+-----------------------------+ | Note | 1050 | Table 'v1_3' already exists | +-------+------+-----------------------------+ 1 row in set (0.00 sec) mysql> SELECT * from testdb_wl5370.t1_3; +------+------+ | a | b | +------+------+ | 1 | 2 | | 4 | 3 | | 6 | 5 | | 5 | 6 | +------+------+ 4 rows in set (0.00 sec) 5.1-post: ----------- mysql> SELECT * from testdb_wl5370.t1_3; +------+------+ | a | b | +------+------+ | 1 | 2 | | 4 | 3 | | 6 | 5 | +------+------+ 3 rows in set (0.00 sec)
[2 Aug 2010 10:19]
Libing Song
Here is the patch. http://lists.mysql.com/commits/114318 It was pushed into mysql-5.1-wl5370 tree as a postfix.
[17 Aug 2010 13:01]
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/115957 3479 Li-Bing.Song@sun.com 2010-08-17 WL#5370 Keep forward-compatibility when changing 'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour BUG#55474, BUG#55499, BUG#55598, BUG#55616 and BUG#55777 are fixed in this patch too. This is the 5.1 part. It implements: - if the table exists, binlog two events: CREATE TABLE IF NOT EXISTS and INSERT ... SELECT - Insert nothing and binlog nothing on master if the existing object is a view. It only generates a warning that table already exists. @ sql/sql_class.h Declare virtual function write_to_binlog() for select_insert. It's used to binlog 'create select' @ sql/sql_insert.cc Implement write_to_binlog(); Use write_to_binlog() instead of binlog_query() to binlog the statement. if the table exists, binlog two events: CREATE TABLE IF NOT EXISTS and INSERT ... SELECT @ sql/sql_lex.h Declare create_select_start_with_brace and create_select_pos. They are helpful for binlogging 'create select' @ sql/sql_parse.cc Do nothing on master if the existing object is a view. @ sql/sql_yacc.yy Record the relative postion of 'SELECT' in the 'CREATE ...SELECT' statement. Record whether there is a '(' before the 'SELECT' clause.
[18 Aug 2010 4:58]
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/116025 3484 Li-Bing.Song@sun.com 2010-08-18 WL#5370 Keep forward-compatibility when changing 'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour BUG#55474, BUG#55499, BUG#55598, BUG#55616 and BUG#55777 are fixed in this patch too. This is the 5.1 part. It implements: - if the table exists, binlog two events: CREATE TABLE IF NOT EXISTS and INSERT ... SELECT - Insert nothing and binlog nothing on master if the existing object is a view. It only generates a warning that table already exists. @ mysql-test/r/trigger.result Ather this patch, 'CREATE TABLE IF NOT EXISTS ... SELECT' will not insert anything if the creating table already exists and is a view. @ sql/sql_class.h Declare virtual function write_to_binlog() for select_insert. It's used to binlog 'create select' @ sql/sql_insert.cc Implement write_to_binlog(); Use write_to_binlog() instead of binlog_query() to binlog the statement. if the table exists, binlog two events: CREATE TABLE IF NOT EXISTS and INSERT ... SELECT @ sql/sql_lex.h Declare create_select_start_with_brace and create_select_pos. They are helpful for binlogging 'create select' @ sql/sql_parse.cc Do nothing on master if the existing object is a view. @ sql/sql_yacc.yy Record the relative postion of 'SELECT' in the 'CREATE ...SELECT' statement. Record whether there is a '(' before the 'SELECT' clause.
[19 Aug 2010 7:43]
Libing Song
It was fixed in the final patch for WL#5370
[25 Aug 2010 10:23]
Bugs System
Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825102234-a3q8x0l7voa13ts3) (version source revid:alik@ibmvm-20100825102234-a3q8x0l7voa13ts3) (merge vers: 5.5.6-m3) (pib:20)
[1 Sep 2010 13:13]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100901130501-4g2k86dub29auj8y) (version source revid:alik@sun.com-20100901130012-9bmmvzcnnw6n5rw6) (merge vers: 5.6.1-m4) (pib:21)
[1 Sep 2010 13:14]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100901130614-pgop3m80rmutewxn) (version source revid:alik@sun.com-20100901130033-8k19cjn6n2blm3py) (pib:21)
[8 Sep 2010 1:45]
Paul DuBois
Bug does not occur in any released version.
[8 Sep 2010 1:46]
Paul DuBois
No changelog entry needed.
[28 Sep 2010 8:45]
Bugs System
Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)
[14 Oct 2010 8:36]
Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:51]
Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 9:07]
Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[15 Oct 2010 13:45]
Jon Stephens
Reverting to Closed state. See above.