WL#5370: Keep forward-compatibility when changing 'CREATE TABLE IF NOT EXISTS ... SELECT' behaviour
Affects: Server-5.5 — Status: Complete
Change behavior of 'CREATE TABLE IF NOT EXISTS
SELECT ...' statement on MySQL 5.5 when the destination table exists. The original behavior: If the table exists, CREATE TABLE IF NOT EXISTS ... SELECT is converted to INSERT ... SELECT, i.e. the result of 'SELECT ...' is inserted into the existing table. Behavior in 5.5+: If the table exists, do nothing. Example ======== mysql> create table t1 (a varchar(10)); Query OK, 0 rows affected (0.01 sec) -- Current behaviour -- --------------- mysql> create table if not exists t1 select "hello" as a; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql> show warnings; +-------+------+---------------------------+ | Level | Code | Message | +-------+------+---------------------------+ | Note | 1050 | Table 't1' already exists | +-------+------+---------------------------+ 1 row in set (0.00 sec) mysql> select * from t1; +-------+ | a | +-------+ | hello | +-------+ 1 row in set (0.00 sec) -- New behaviour -- ----------- mysql> create table if not exists t1 select "hello" as a; Query OK, 0 row affected, 1 warning (0.00 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> show warnings; +-------+------+---------------------------+ | Level | Code | Message | +-------+------+---------------------------+ | Note | 1050 | Table 't1' already exists | +-------+------+---------------------------+ 1 row in set (0.00 sec) mysql> select * from t1; Empty set (0.00 sec) This change in implementation of CREATE TABLE ... SELECT introduces a potential failure of statement based replication when a 5.5 instance is set as a slave for a pre-5.5 master. Indeed, rows inserted on the master will not appear in the table on the slave. To address the potential statement based replication failure, replication of CREATE TABLE ... SELECT in 5.1 will be changed to be forward-compatible.
What is changed =============== In 5.1: * CREATE TABLE IF NOT EXISTS ... SELECT will insert nothing and not be binlogged if the underlying table exists and is a view. * Replication of CREATE TABLE .. IF NOT EXISTS ... SELECT is changed to write two binary log events: CREATE TABLE IF NOT EXISTS and INSERT ... SELECT. * The manual is updated to reflect the changes upcoming in 5.5. In 5.5: * Execution of CREATE TABLE IF NOT EXISTS ... SELECT is changed from: to: IF NOT (table exists) IF NOT (table exists) create table create table ELSE copy rows warning ELSE END IF warning copy rows END IF For CREATE TABLE statement, "table exists" check returns true when a base table or view with the same is present in the schema. For CREATE TEMPORARY TABLE, "table exists" check returns true when a temporary table with the same name is present in the session. This definition of "table exists" was introduces by the fix for BUG#47418 and is kept. The optional IGNORE keyword clause continues to apply to "copy rows" part of the statement. The optional REPLACE keyword only has effect on records, produced by SELECT (i.e. on potential duplicate key errors). Rationale for the change ======================== The current semantics turned out to be too difficult to support, as reflected int the 'Affected bugs' section. It is found by many counter-intuitive: * In case an underlying table exists, CREATE TABLE statement produces an error, CREATE TABLE IF NOT EXISTS, a warning. In either case nothing is changed. However, CREATE TABLE ... SELECT, produces an error, and does nothing, whereas CREATE TABLE IF NOT EXISTS SELECT, produces a warning, but inserts records. Seen from that angle, lack of symmetry can be observed. * The result of CREATE TABLE IF NOT EXISTS ... SELECT can be unpredictable if SELECT column list differs from the definition of the destination table. * The reason to use IF NOT EXISTS clause, in many cases, is to make execution idempotent. Currently each execution of the statements modifies the content of the table. * Old results can always be achieved by splitting the original statement in two: CREATE IF NOT EXISTS and INSERT ... SELECT. Making cross-version replication work ===================================== This solution consists of two parts, one in 5.1, another in 5.5. In 5.5 and onward: - If the table (or a view with the same name) exists, the statement is not binlogged, neither in statement-based nor in row-based mode. This ensures that both 5.1 and 5.5 slaves are in sync with the master operating in new mode, (when metadata on master and slave were in sync). This also fixes BUG#47442. In 5.1: - If the table exists, binlog two events: CREATE TABLE IF NOT EXISTS (generated based on show_create_info output) and INSERT ... SELECT (based on manual query rewrite, stripping the first part of CREATE TABLE, and replacing it with INSERT/REPLACE statement. - If the underlying table is a view, the statement will not insert anything and not be binlogged. The transformation from CREATE to INSERT should be as follows: Let us have CREATE TABLE IF EXISTS
( ) SELECT It is converted into: INSERT INTO ( ) SELECT where the list := The existing table's rightmost N columns. N is the column number of the SELECT clause. - Change to row mode if --binlog-format is MIXED, as master and slave always keep consistency in row mode. Statement-based replication might go out of sync on the slave if it just applies the two statements separately. This limitation of 5.1 to 5.5 replication needs to be documented in the upgrade manual. Note: the problem is not present when all servers are 5.5+. Affected bugs ============= BUG#47132 CREATE TABLE.. SELECT.. data not inserted if table is view over multiple tables Solution in 5.5: If the existing object is a view, nothing is done (except a warning). BUG#47442 RBR breaks on CREATE TABLE IF EXISTS AS SELECT Solution in 5.5: If the underlying table is a view, nothing is written into the binary log, including CREATE TABLE ... IF EXISTS statement itself. Note, that if the underlying table is a base table, we currently (5.1) write CREATE TABLE IF NOT EXISTS statement into the binary log, to make sure that the table is created on the slave if it does not exist there. This changed in 5.5 to never binlog the statement if the underlying object exists. BUG#49494 CREATE TABLE IF NOT EXISTS does wrong insert when view exists with the name If the view with the same name exists, no insertion is done. BUG#48814 CREATE TABLE IF NOT EXISTS SELECT does not fail The statement continues to produce no error. It, however, does not attempt an insert. BUG#47899 CREATE TABLE...SELECT binlogged wrongly if binlog_format=row QQ: Mats adds a solution. BUG#39804 Failing CREATE ... SELECT doesn't appear in binary log. This no longer leads to slave going out of sync, and thus does not need fixing. Not a bug: BUG#23992 "CREATE TABLE .. IF NOT EXISTS" confusion
Two trees are created to contain all changes required by this worklog: mysql-5.1-wl5370 mysql-trunk-wl5370 Fixes for all affected bugs will be pushed into these trees one by one, and the trees will be handed off to System QA when coding is complete. After QA sign-off, the changes from 5.1-wl5370 will be null-merged into trunk-wl5370 and the trees will be pushed into the main trees. In other words, forward-compatible replication code will only be present in 5.1 release series.
Copyright (c) 2000, 2022, Oracle Corporation and/or its affiliates. All rights reserved.