MySQL Bugs: #47442: RBR breaks on CREATE TABLE IF EXISTS <existing VIEW> AS SELECT

Bug #47442	RBR breaks on CREATE TABLE IF EXISTS <existing VIEW> AS SELECT
Submitted:	18 Sep 2009 14:06	Modified:	2 Sep 2010 14:12
Reporter:	Matthias Leich	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Row Based Replication ( RBR )	Severity:	S3 (Non-critical)
Version:	5.1,5.4	OS:	Any
Assigned to:	Libing Song	CPU Architecture:	Any
Tags:	RBR

Description:
My script:
----------
--source include/master-slave.inc
CREATE VIEW t2  AS SELECT 1 f1;
CREATE TABLE IF NOT EXISTS t2 AS SELECT f1 FROM t2;
sync_slave_with_master;

Please note the following more or less "problematic"
properties of the last CREATE TABLE:
----------------------------------------------------
1. Per manual:
   http://dev.mysql.com/doc/refman/5.4/en/create-table.html
   ...
   IF NOT EXISTS is given and the table already exists ...
   - The table definition given in the CREATE TABLE part
      is ignored. No error occurs, even if the definition
      does not match that of the existing table. 
   - My understanding of the following stuff:
      An INSERT into the table of the result set of the SELECT
      is at least tried.
2. There is already a table with the name t2, but this is a VIEW
   And this VIEW cannot be insertable !!
   Example:
      CREATE VIEW v1 AS SELECT 1;
      INSERT INTO v1 VALUES(1);
      ERROR HY000: The target table v1 of the INSERT
                   is not insertable-into
3. The INSERT goes into t2 but the SELECT runs also on t2       

Result on mysql-next-bugfixing 2009-09-16 12:23:16:
---------------------------------------------------
./mysql-test-run --mysqld=--binlog-format=row \
   --no-check-testcases <script>

At line 4: sync_slave_with_master failed: 'select master_pos_wait('master-bin.000001', 641, 300)' returned NULL indicating slave SQL thread failure

=== mysql-test.log ===
...
CREATE VIEW t2  AS SELECT 1 f1;
CREATE TABLE IF NOT EXISTS test.t2 AS SELECT f1 FROM t2;
Warnings:
Note 1050  Table '/work2/6.0/mysql-next-bugfixing-old/mysql-test/var/tmp/mysqld.1/#sql_3721_0' already exists

    Well, I would expect here also to see an error message
    that the VIEW t2 is not insertable into. IMHO the
    warning is less important.

=== SHOW SLAVE STATUS ===
...
Last_Errno  1103
Last_Error  Error 'Incorrect table name '/work2/6.0/mysql-next-bugfixing-old/mysql-test/var/tmp/mysqld.1/#sql_325b_0'' on query. Default database: 'test'. Query: 'CREATE TEMPORARY TABLE IF NOT EXISTS ``.`/work2/6.0/mysql-next-bugfixing-old/mysql-test/var/tmp/mysqld.1/#sql_32
5b_0` (
  `f1` int(1) NOT NULL DEFAULT '0'
) MAX_ROWS=209715'
!! What is the empty string at the beginning of "``.`/work2/...."?
   Does the system assume the database name is an empty string ? !!

SHOW BINLOG EVENTS IN 'master-bin.000001' (a bit edited)
--------------------------------------------------------
End_log_pos Info
107   Server ver: 5.4.4-alpha-debug-log, Binlog ver: 4
269   use `test`; CREATE ALGORITHM=UNDEFINED
      DEFINER=`root`@`localhost` SQL SECURITY DEFINER
      VIEW `t2` AS SELECT 1 f1
337   BEGIN
572   use `test`; CREATE TEMPORARY TABLE IF NOT EXISTS
       ``.`/work2/..../var/tmp/mysqld.1/#sql_381d_0` (
      `f1` int(1) NOT NULL DEFAULT '0' ) MAX_ROWS=209715
641   COMMIT

SHOW BINLOG EVENTS IN 'slave-bin.000001';
End_log_pos Info
107   Server ver: 5.4.4-alpha-debug-log, Binlog ver: 4
269   use `test`; CREATE ALGORITHM=UNDEFINED
      DEFINER=`root`@`localhost` SQL SECURITY DEFINER
      VIEW `t2` AS SELECT 1 f1

The result with replication format "statement" and "mixed"
is fine and nothing stops.

My environment:
---------------
- mysql-next-bugfixing 2009-09-16 12:23:16
- mysql-5.1-bugteam 2009-09-16 09:01:28 
- Linux OpenSuSE 11.0 (64 Bit)
- Intel Core2Duo

How to repeat:
See above

Duplicate of Bug#47418

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/89414

3649 Li-Bing.Song@sun.com	2009-11-05
      BUG#47442 BR breaks on CREATE TABLE IF EXISTS <existing VIEW> AS SELECT
      
      'CREATE TABLE IF NOT EXIST ... SELECT' statement is binlogged as 
      'CREATE TEMPORARY TABLE ...' with a wrong table name when a view exists 
      with the same name. 
      
      Base table is in the same name space with view. when excuting 
      open_and_lock_tables_derived, create_table is initialized as the
      existing view. and create_table->table is now a temporary table for
      the view. This cause the bug to happen.
      
      In this patch, create_table->table will be masqueraded as a base table 
      with the correct table name and db name if it is exists as a view.

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/95937

3798 Li-Bing.Song@sun.com	2010-01-05
      BUG#47442 BR breaks on CREATE TABLE IF EXISTS <existing VIEW> AS SELECT
      
      'CREATE TABLE IF NOT EXIST ... SELECT' statement is binlogged as 
      'CREATE TEMPORARY TABLE ...' with a wrong table name when a view exists 
      with the same name. 
      
      Base table is in the same name space with view. when excuting 
      open_and_lock_tables_derived, create_table is initialized as the
      existing view. and create_table->table is now a temporary table or base
      table which the view is derived. This cause a wrong binlog query which 
      leads slave crash.
      
      After a long time of discussion, we ultimately decided to do the following
      in this patch.
      about 'CREATE TABLE IF EXISTS <existing VIEW>(COLUMNS) AS SELECT ...' and
            'CREATE TABLE IF EXISTS <<existing VIEW> LIKE ...',
      the {COLUMNS and SELECT | LIKE} part are binlogged when a view exists with
      the same name.
      In another words, the binlog query is same as the binlog query
      which was binlogged when the table does not exist and it is
      created succefully.
      
      To implement the resolution, source table is used to binlog the query of 
      'CREATE TABLE IF EXISTS <<existing VIEW> LIKE ...'. A temporary table which is
      created by using the same query of 'CREATE TABLE IF EXISTS <existing VIEW>
      (COLUMNS) AS SELECT ...' is used to binlog 'CREATE TABLE IF EXISTS <existing VIEW>
      (COLUMNS) AS SELECT ...' statement.

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/97735

3798 Li-Bing.Song@sun.com	2010-01-21
      BUG#47442 BR breaks on CREATE TABLE IF EXISTS <existing VIEW> AS SELECT
      
      'CREATE TABLE IF NOT EXIST ... SELECT' statement is binlogged as 
      'CREATE TEMPORARY TABLE ...' with a wrong table name when a view exists 
      with the same name. 
      
      Base table is in the same name space with view. when excuting 
      open_and_lock_tables_derived, create_table is initialized as the
      existing view. and create_table->table is now a temporary table or base
      table which the view is derived. This cause a wrong binlog query which 
      leads slave crash.
      
      After a long time of discussion, we ultimately decided to do the following
      in this patch.
      about 'CREATE TABLE IF EXISTS <existing VIEW>(COLUMNS) AS SELECT ...' and
            'CREATE TABLE IF EXISTS <<existing VIEW> LIKE ...',
      the {COLUMNS and SELECT | LIKE} part are binlogged when a view exists with
      the same name.
      In another words, the binlog query is same as the binlog query
      which was binlogged when the table does not exist and it is
      created succefully.
      
      To implement the resolution, source table is used to binlog the query of 
      'CREATE TABLE IF EXISTS <<existing VIEW> LIKE ...'. A temporary table which is
      created by using the same query of 'CREATE TABLE IF EXISTS <existing VIEW>
      (COLUMNS) AS SELECT ...' is used to binlog 'CREATE TABLE IF EXISTS <existing VIEW>
      (COLUMNS) AS SELECT ...' statement.

It will be fixed by bug#47132.
After the patch for bug#47132, 'CREATE TABLE IF NOT EXISTS ... SELECT' will do nothing and binlog nothing if the table exists.

Please only close after a test case is in.

It will be fixed by WL#5370.
After WL#5370, 'CREATE TABLE IF NOT EXISTS ... SELECT' will insert nothing with a warning that 'table already exits.' if a view of the same name already exists.

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/116060

3184 Li-Bing.Song@sun.com	2010-08-18
      WL#5370 Keep forward-compatibility when changing 
              'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour
      BUG#47132, BUG#47442, BUG49494, BUG#23992 and BUG#48814 will disappear
      automatically after the this patch.
      BUG#55617 is fixed by this patch too.
            
      This is the 5.5 part.
      It implements:
      - 'CREATE TABLE IF NOT EXISTS ... SELECT' statement will not insert
        anything and binlog anything if the table already exists.
        It only generate a warning that table already exists.
      - A couple of test cases for the behavior changing.

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/116062

3186 Li-Bing.Song@sun.com	2010-08-18
      WL#5370 Keep forward-compatibility when changing 
              'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour
      BUG#47132, BUG#47442, BUG49494, BUG#23992 and BUG#48814 will disappear
      automatically after the this patch.
      BUG#55617 is fixed by this patch too.
                  
      This is the 5.5 part.
      It implements:
      - 'CREATE TABLE IF NOT EXISTS ... SELECT' statement will not insert
        anything and binlog anything if the table already exists.
        It only generate a warning that table already exists.
      - A couple of test cases for the behavior changing.

It disappears after the patch for WL#5370. As behavior of CREATE TABLE IF NOT EXISTS SELECT has changed. It will never insert anything and not be binlogged
after the worklog.

Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documentation of the fix for the current issue is being handled with documentation of WL#5370, so closed bug report without further action.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)

Setting back to Closed state per my comment of 26 August.

Setting back to Closed. See previous comments.

Noted in 5.1.51, 5.5.6, 5.6.1 changelogs.

As of MySQL 5.5.6, handling of CREATE TABLE IF NOT EXISTS ... SELECT
statements has been changed for the case that the destination table
already exists:

* Previously, for CREATE TABLE IF NOT EXISTS ... SELECT, MySQL produced
  a warning that the table exists, but inserted the rows and wrote the
  statement to the binary log anyway. By contrast, CREATE TABLE ...
  SELECT (without IF NOT EXISTS) failed with an error, but MySQL
  inserted no rows and did not write the statement to the binary log.

* MySQL now handles both statements the same way when the destination
  table exists, in that neither statement inserts rows or is written to
  the binary log. The difference between them is that MySQL produces a
  warning when IF NOT EXISTS is present and an error when it is not.

This change in handling of IF NOT EXISTS results in an
incompatibility for statement-based replication from a MySQL 5.1
master with the original behavior and a MySQL 5.5 slave with the new
behavior. Suppose that CREATE TABLE IF NOT EXISTS ... SELECT is
executed on the master and the destination table exists. The result
is that rows are inserted on the master but not on the slave.
(Row-based replication does not have this problem.)

To address this issue, statement-based binary logging for CREATE
TABLE IF NOT EXISTS ... SELECT is changed in MySQL 5.1 as of 5.1.51:

* If the destination table does not exist, there is no change: The
  statement is logged as is.

* If the destination table does exist, the statement is logged as the
  equivalent pair of CREATE TABLE IF NOT EXISTS and INSERT ... SELECT
  statements. (If the SELECT in the original statement is preceded by
  IGNORE or REPLACE, the INSERT becomes INSERT IGNORE or REPLACE,
  respectively.)

This change provides forward compatibility for statement-based
replication from MySQL 5.1 to 5.5 because when the destination table
exists, the rows will be inserted on both the master and slave. To
take advantage of this compatibility measure, the 5.1 server must be
at least 5.1.51 and the 5.5 server must be at least 5.5.6.

To upgrade an existing 5.1-to-5.5 replication scenario, upgrade the
master first to 5.1.51 or higher. Note that this differs from the
usual replication upgrade advice of upgrading the slave first.

A workaround for applications that wish to achieve the original
effect (rows inserted regardless of whether the destination table
exists) is to use CREATE TABLE IF NOT EXISTS and INSERT ... SELECT
statements rather than CREATE TABLE IF NOT EXISTS ... SELECT
statements. 

Along with the change just described, the following related change
was made: Previously, if an existing updatable view was named as the
destination table for CREATE TABLE IF NOT EXISTS ... SELECT, rows
were inserted into the underlying base table and the statement was
written to the binary log. As of MySQL 5.1.51 and 5.5.6, nothing is
inserted or logged.