Bug #34707 Row based replication: slave creates table within wrong database
Submitted: 20 Feb 2008 21:21 Modified: 17 Oct 2008 17:20
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S2 (Serious)
Version:5.1,6.0 OS:Any
Assigned to: Mats Kindahl CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R1 (None/Negligible) / E2 (Low)

[20 Feb 2008 21:21] Matthias Leich
Description:
Bug found during work on
WL#3761 Extend the tests of transactional behaviour
        within replication

Testscript stored within mysql-test/suite/rpl/t :
source include/master-slave.inc;
=================================================
connection master;
--disable_warnings
DROP DATABASE IF EXISTS mysqltest1;
--enable_warnings
CREATE DATABASE mysqltest1;

CREATE TABLE mysqltest1.without_select (f1 BIGINT);
CREATE TABLE mysqltest1.with_select AS SELECT 1 AS f1;
sync_slave_with_master;

Protocols:
==========
TEST                           RESULT         TIME (ms)
-------------------------------------------------------
rpl.<testcase>        'row' [ fail ]

=== SHOW MASTER STATUS ===
---- 1. ----
File    slave-bin.000001
Position        655
Binlog_Do_DB
Binlog_Ignore_DB
==========================

=== SHOW SLAVE STATUS ===
---- 1. ----
Slave_IO_State  Waiting for master to send event
...
Last_Error      Error 'Table 'mysqltest1.with_select'
                doesn't exist' on opening table 
                `mysqltest1`.`with_select
...

mysqltest: At line 12: could not sync with master
  ('select master_pos_wait('master-bin.000001', 772)'
   returned NULL)
....

The content of <testcase>.log does not give additional
information. It simply contains the SQL commands.

A "find var -follow | grep with"
shows that
- the slave created the table "with_select"
  within the wrong directory/database
- Last_Error :  Error 'Table 'mysqltest1.with_select'
                doesn't exist'  ...
  is correct
====================================================
var/slave-data/mysqltest1/without_select.MYD
var/slave-data/mysqltest1/without_select.MYI
var/slave-data/mysqltest1/without_select.frm
var/slave-data/test/with_select.MYD   <--- !!!!
var/slave-data/test/with_select.MYI   <--- !!!!
var/slave-data/test/with_select.frm   <--- !!!!
var/master-data/mysqltest1/with_select.MYD
var/master-data/mysqltest1/with_select.MYI
var/master-data/mysqltest1/with_select.frm
var/master-data/mysqltest1/without_select.MYD
var/master-data/mysqltest1/without_select.MYI
var/master-data/mysqltest1/without_select.frm

The same problems happens in case of
   CREATE TABLE mysqltest.with_select1 AS 
   SELECT * FROM mysql.t1;
(assuming that mysql.t1 exists)

My environment:
- mysql-5.1 ChangeSet@1.2554, 2008-02-15
- mysql-6.0 ChangeSet@1.2497.7.21, 2008-02-15
- compile-pentium-debug-max
- Linux (OpenSuSE 10.3) 64 Bit
- Intel Core2Duo (64 Bit)

How to repeat:
See above
[21 Feb 2008 12:17] Matthias Leich
The bug itself seems similar to
Bug#33386 Row based replication misses cross database
          create statements
which is in state closed though I was unable to find
any Changeset which contains the bug number.
So I fear it is not fixed.

The main difference of the current bug to Bug#33386
is that the SELECT within
CREATE TABLE mysqltest1.with_select AS SELECT 1 AS f1;
has no reference to a table at all.

A simple and from the user point of view most probably
acceptable workaround would be to separate table creation
from the filling with data. Something like
CREATE TABLE mysqltest1.with_select (f1 INT);
INSERT INTO mysqltest1.with_select VALUES(1);
[22 Feb 2008 21:09] Omer Barnir
workaround: change to the target database and do the create of a 'local' table
referencing the source table from the other database
[3 Aug 2008 11:17] Sven Sandberg
When a CREATE...SELECT statement is logged in row mode, the statement generates a Query_log_event representing a CREATE TABLE statement, followed by row events (Table_map and Write_rows events). The CREATE TABLE statement is created by calling store_create_info in sql_show.cc, i.e., the same function that is called to execute SHOW CREATE TABLE statements. Since SHOW CREATE TABLE does not print the database name, the database name is not included in the binlog either.

The solution is to make store_create_info capable of printing the database name.

We need to decide whether SHOW CREATE TABLE should print the database name. I think that would be a nice feature, but it is possible that it would break third-party code that tries to parse the result of SHOW CREATE TABLE.
[11 Aug 2008 7:51] Lars Thalmann
The goal should be to *stop* using SQL statements directly from
replication to find out information about tables etc.

It is not robust enough and if someone changes the SQL output, then
replication will break.  We should try to localize code, so that one
change (e.g. in SHOW CREATE TABLE) does not break some totally
different part of code (e.g. replication).

The long-term solution is to have service interface to the server to
provide the correct information.  This should include information
about database.  Then these interfaces can be tested separately to
ensure that they are robust.

I think the best solution for this bug might be to create such an
extended interface function (that provides also database name), while
keeping the user visible SHOW CREATE TABLE the same.
[13 Aug 2008 13:06] 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/51527

2658 Mats Kindahl	2008-08-13
      Bug #34707: Row based replication: slave creates table within wrong database
      
      The failure was caused by executing a CREATE-SELECT statement that creates a
      table in another database than the current one. In row-based logging, the
      CREATE statement was written to the binary log without the database, hence
      creating the table in the wrong database, causing the following inserts to
      fail since the table didn't exist in the given database.
      
      Fixed the bug by adding a parameter to store_create_info() that will make
      the function print the database name before the table name and used that
      in the calls that write the CREATE statement to the binary log. The database
      name is only printed if it is different than the currently selected database.
      
      The output of SHOW CREATE TABLE has not changed and is still printed without
      the database name.
[19 Aug 2008 11:19] 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/51934

2658 Mats Kindahl	2008-08-19
      Bug #34707: Row based replication: slave creates table within wrong database
      
      The failure was caused by executing a CREATE-SELECT statement that creates a
      table in another database than the current one. In row-based logging, the
      CREATE statement was written to the binary log without the database, hence
      creating the table in the wrong database, causing the following inserts to
      fail since the table didn't exist in the given database.
      
      Fixed the bug by adding a parameter to store_create_info() that will make
      the function print the database name before the table name and used that
      in the calls that write the CREATE statement to the binary log. The database
      name is only printed if it is different than the currently selected database.
      
      The output of SHOW CREATE TABLE has not changed and is still printed without
      the database name.
[7 Oct 2008 18:51] Paul Dubois
Noted in 5.1.29 changelog.

For a CREATE TABLE ... SELECT statement that creates a table in a
database other than the current one, the table could be created in
the wrong database on replication slaves if row-based binary logging
is used. 

Leaving report status unchanged; this is early documentation of an upcoming push into 5.1.29.
[8 Oct 2008 11:56] 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/55737

2860 Mats Kindahl	2008-10-08 [merge]
      Merging BUG#34707 to 6.0-5.1.29-rc.
[8 Oct 2008 12:51] 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/55760

2763 Mats Kindahl	2008-10-08
      Bug #34707: Row based replication: slave creates table within wrong database
      
      The failure was caused by executing a CREATE-SELECT statement that creates a
      table in another database than the current one. In row-based logging, the
      CREATE statement was written to the binary log without the database, hence
      creating the table in the wrong database, causing the following inserts to
      fail since the table didn't exist in the given database.
      
      Fixed the bug by adding a parameter to store_create_info() that will make
      the function print the database name before the table name and used that
      in the calls that write the CREATE statement to the binary log. The database
      name is only printed if it is different than the currently selected database.
      
      The output of SHOW CREATE TABLE has not changed and is still printed without
      the database name.
[8 Oct 2008 12:56] 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/55762

2857 Mats Kindahl	2008-10-08 [merge]
      Merging 5.1-5.1.29-rc with BUG#34707 into 6.0-5.1.29-rc.
[9 Oct 2008 17:46] Bugs System
Pushed into 5.1.30  (revid:mats@sun.com-20081008091500-2cm4uv71h96ia55d) (version source revid:mats@sun.com-20081008091500-2cm4uv71h96ia55d) (pib:4)
[9 Oct 2008 18:19] Paul Dubois
Setting report to NDI pending push into 6.0.x.
[17 Oct 2008 16:44] Bugs System
Pushed into 6.0.8-alpha  (revid:mats@sun.com-20081008114649-j811eollsltr40ke) (version source revid:mats@sun.com-20081008114649-j811eollsltr40ke) (pib:5)
[17 Oct 2008 17:20] Paul Dubois
Noted in 6.0.8 changelog.
[28 Oct 2008 21:04] Bugs System
Pushed into 5.1.29-ndb-6.2.17  (revid:mats@sun.com-20081008091500-2cm4uv71h96ia55d) (version source revid:tomas.ulin@sun.com-20081028140209-u4emkk1xphi5tkfb) (pib:5)
[28 Oct 2008 22:23] Bugs System
Pushed into 5.1.29-ndb-6.3.19  (revid:mats@sun.com-20081008091500-2cm4uv71h96ia55d) (version source revid:tomas.ulin@sun.com-20081028194045-0353yg8cvd2c7dd1) (pib:5)
[1 Nov 2008 9:50] Bugs System
Pushed into 5.1.29-ndb-6.4.0  (revid:mats@sun.com-20081008091500-2cm4uv71h96ia55d) (version source revid:jonas@mysql.com-20081101082305-qx5a1bj0z7i8ueys) (pib:5)
[30 Jan 2009 13:31] Bugs System
Pushed into 6.0.10-alpha (revid:luis.soares@sun.com-20090129165607-wiskabxm948yx463) (version source revid:luis.soares@sun.com-20090129163120-e2ntks4wgpqde6zt) (merge vers: 6.0.10-alpha) (pib:6)
[30 Jan 2009 15:11] Bugs System
Pushed into 5.1.32 (revid:luis.soares@sun.com-20090129165946-d6jnnfqfokuzr09y) (version source revid:mats@mysql.com-20080820084239-1m3rxchskwmeqm1a) (merge vers: 5.1.28) (pib:6)
[17 Feb 2009 14:57] Bugs System
Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 16:45] Bugs System
Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090203133556-9rclp06ol19bmzs4) (merge vers: 5.1.32-ndb-6.3.22) (pib:6)
[17 Feb 2009 18:21] Bugs System
Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090201210519-vehobc4sy3g9s38e) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)
[17 Jun 2010 20:30] Jay Prall
We have reproduced this issue in v5.1.47