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:
None 
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
Description:
While testing replication for WL#5370 with
mysql-5.1-wl5370 tree(dated 20jul)
in the scenario: Old Master (5.1.48)-> New Slave (mysql-5.1-wl5370)

There are 2 issues -
1) Replication diff. This is due to rows not getting copied after wl5370 in 5.1, when view with same name exist.
< INSERT INTO `t1_2` VALUES (1),(2),(3),(7),(8),(9); 
--- 
> INSERT INTO `t1_2` VALUES (1),(2),(3); 

5.1-pre ->5.1-post
------------------------
5.1-pre
mysql> SELECT * FROM testdb_wl5370.v2_2; 
+------+ 
| a    | 
+------+ 
|    1 | 
|    2 | 
|    3 | 
|    7 | 
|    8 | 
|    9 | 
+------+ 
6 rows in set (0.00 sec) 

5.1-post
mysql> SELECT * FROM testdb_wl5370.v2_2; 
+------+ 
| a    | 
+------+ 
|    1 | 
|    2 | 
|    3 | 
+------+ 
3 rows in set (0.00 sec) 

The above difference in 2 5.1 versions makes master and slave out of sync.

2) No warning is thrown as 'Table v2_2 already exists', when view with same name exists. This is also different. Warning should be thrown in any case.

- Warning should be added.
- Behavior should be consistent in 5.1 for tables and views.

How to repeat:
Steps to reproduce:
--------------------
DROP DATABASE IF EXISTS testdb_wl5370;
CREATE DATABASE testdb_wl5370;
USE testdb_wl5370;
CREATE TABLE testdb_wl5370.t1_2(a INT);
CREATE TABLE testdb_wl5370.t3_2(c INT);
INSERT INTO testdb_wl5370.t1_2 VALUES (1),(2),(3);
INSERT INTO testdb_wl5370.t3_2 VALUES (7),(8),(9);
CREATE VIEW testdb_wl5370.v2_2 AS SELECT * FROM testdb_wl5370.t1_2;
SELECT * FROM testdb_wl5370.t1_2;
CREATE TABLE IF NOT EXISTS testdb_wl5370.v2_2(a INT) SELECT * FROM  testdb_wl5370.t3_2;
SELECT * FROM testdb_wl5370.v2_2;
[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.