| 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: | |
| Category: | MySQL Server: Row Based Replication ( RBR ) | Severity: | S2 (Serious) |
| Version: | 5.1,6.0 | OS: | Any |
| Assigned to: | Mats Kindahl | CPU Architecture: | Any |
[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
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