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 Email Updates:
Status: Closed Impact on me:
None 
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, jani@a88-112-41-254.elisa-laajakaista.fi +2 -0
  Added more tests of field conversions.

ChangeSet@1.2326, 2006-09-26 13:19:25+03:00, jani@a88-112-41-254.elisa-laajakaista.fi +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.