Bug #22762 create talbe if not exists like a-temp-table binlogged w/o "if not exists"

• Submitted: 27 Sep 2006 19:53
• Modified: 21 Nov 2006 20:05
• Reporter: Andrei Elkin
• Status: Closed
• Category: MySQL Server: Row Based Replication ( RBR )
• Severity: S3 (Non-critical)
• Version: 5.1.12
• OS: Linux (linux)
• Assigned to: Andrei Elkin
• CPU Architecture: Any

[27 Sep 2006 19:53] Andrei Elkin

Description:
CREATE TABLE IF NOT EXISTS LIKE a-temporary-table  truncates the "IF NOT EXISTS".

How to repeat:
set @@global.binlog_format=row;
CREATE TEMPORARY TABLE tt1 (a int);
CREATE TABLE t1 like tt1;
SHOW BINLOG EVENTS;

=>
...
 Query       |         2 |         780 | use `test`; CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) 

Suggested fix:
same as bug#22027

[27 Sep 2006 20:06] Andrei Elkin

How to repeat correction:
create table if not exists t1 like tt1;

[28 Sep 2006 0:05] MySQL Verification Team

Hi Andrei,

I was unable to repeat on Suse 10.1.: Any thing I missed? Thanks in advance.

miguel@light:~/dbs/5.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.12-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set @@global.binlog_format=row;
Query OK, 0 rows affected (0.17 sec)

mysql> CREATE TEMPORARY TABLE tt1 (a int);
Query OK, 0 rows affected (0.00 sec)

mysql> create table if not exists t1 like tt1;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW BINLOG EVENTS\G
*************************** 1. row ***************************
   Log_name: light-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 102
       Info: Server ver: 5.1.12-beta-debug-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: light-bin.000001
        Pos: 102
 Event_type: Query
  Server_id: 1
End_log_pos: 199
       Info: use `test`; CREATE TEMPORARY TABLE tt1 (a int)
*************************** 3. row ***************************
   Log_name: light-bin.000001
        Pos: 199
 Event_type: Query
  Server_id: 1
End_log_pos: 300
       Info: use `test`; create table if not exists t1 like tt1
3 rows in set (0.19 sec)

mysql> 

miguel@light:~/dbs/mysql-5.1> bk changes | head
ChangeSet@1.2327, 2006-09-26 17:20:14+03:00, [e-mail] +2 -0
  Added more tests of field conversions.

ChangeSet@1.2326, 2006-09-26 13:19:25+03:00, [e-mail] +2 -0
  Fix for bug#20208
  A better fix for bug#10025.

  Fixed test case plus added new tests.

  After fixing Bug#20208 "Blobs greater than 8K are being truncated to 8K"

[23 Oct 2006 5:36] Andrei Elkin

Miguel, there was an unexactness in description, i am sorry.
    set @@SESSION.binlog_format=row;
is meant. 
Thanks for showing your log from which is clear your slave's sql thread
must have got its session value as 'statement' or 'mixed' (default).

The patch for the parent bug#22027 is approved.

[20 Nov 2006 15:14] Lars Thalmann

Pushed into 5.1.14.

[21 Nov 2006 20:05] Paul DuBois

Noted in 5.1.14 changelog.

With row-based binary logging, for CREATE TABLE IF NOT EXISTS LIKE
temporary_table statements, the IF NOT EXISTS clause was not logged.