| Bug #22865 | CREATE ... SELECT is logged too late | ||
|---|---|---|---|
| Submitted: | 30 Sep 2006 20:54 | Modified: | 7 Feb 2007 12:05 |
| Reporter: | Andrei Elkin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.1.12-BK | OS: | Linux (Linux) |
| Assigned to: | Mats Kindahl | CPU Architecture: | Any |
[1 Oct 2006 5:35]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[2 Oct 2006 13:24]
Andrei Elkin
regression test
Attachment: bug22865.test (application/octet-stream, text), 711 bytes.
[16 Nov 2006 13:34]
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/15421 ChangeSet@1.2309, 2006-11-16 14:34:28+01:00, mats@romeo.(none) +9 -0 BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE' from log): When row-based logging is used, the CREATE-SELECT is written as two parts: as a CREATE TABLE statement and as the rows for the table. For both transactional and non-transactional tables, the CREATE TABLE statement was written to the transaction cache, as were the rows, and on statement end, the entire transaction cache was written to the binary log if the table was non-transactional. For transactional tables, the events were kept in the transaction cache until end of transaction (or statement that were not part of a transaction). For the case when AUTOCOMMIT=0 and we are creating a transactional table using a create select, we would then keep the CREATE TABLE statement and the rows for the CREATE-SELECT, while executing the following statements. On a rollback, the transaction cache would then be cleared, which would also remove the CREATE TABLE statement. Hence no table would be created on the slave, while there is an empty table on the master. This relates to BUG#22865 where the table being created exists on the master, but not on the slave during insertion of rows into the newly created table. This occurs since the CREATE TABLE statement were still in the transaction cache until the statement finished executing, and possibly longer if the table was transactional. This patch changes the behaviour of the CREATE-SELECT statement by writing the CREATE TABLE statement directly to the binary log as soon as it is created, then the rows that are inserted into the table, and on error a DROP table is written to the binary log. For transactional tables, the intermediate rows are not written to the binary log, but for non-transactional tables, the rows are written to the binary log immediately (hence, changes to non-transactional tables are propagated to the slave as soon as enough rows are collected to form a rows event). On the master, the table is locked for the duration of the CREATE-SELECT statement, but this behaviour is not replicated to the slave. Hence, it is possible to manipulate the table contents or the table definition in the time between the CREATE TABLE statement has arrived to the slave and the first rows event for the table is executed by the slave, at which point the table is locked for writing.
[1 Dec 2006 8:43]
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/16277 ChangeSet@1.2309, 2006-12-01 09:43:06+01:00, mats@kindahl-laptop.dnsalias.net +11 -0 BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE' from log): When row-based logging is used, the CREATE-SELECT is written as two parts: as a CREATE TABLE statement and as the rows for the table. For both transactional and non-transactional tables, the CREATE TABLE statement was written to the transaction cache, as were the rows, and on statement end, the entire transaction cache was written to the binary log if the table was non-transactional. For transactional tables, the events were kept in the transaction cache until end of transaction (or statement that were not part of a transaction). For the case when AUTOCOMMIT=0 and we are creating a transactional table using a create select, we would then keep the CREATE TABLE statement and the rows for the CREATE-SELECT, while executing the following statements. On a rollback, the transaction cache would then be cleared, which would also remove the CREATE TABLE statement. Hence no table would be created on the slave, while there is an empty table on the master. This relates to BUG#22865 where the table being created exists on the master, but not on the slave during insertion of rows into the newly created table. This occurs since the CREATE TABLE statement were still in the transaction cache until the statement finished executing, and possibly longer if the table was transactional. This patch changes the behaviour of the CREATE-SELECT statement by writing the CREATE TABLE statement directly to the binary log as soon as it is created, then the rows that are inserted into the table, and on error a DROP table is written to the binary log, provided: - the table was created, - the table didn't exist prior to the statement, - we are replicating row-based, and - it was not a temporary table. For transactional tables, the intermediate rows are not written to the binary log, but for non-transactional tables, the rows are written to the binary log immediately (hence, changes to non-transactional tables are propagated to the slave as soon as enough rows are collected to form a rows event).
[1 Dec 2006 9:45]
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/16286 ChangeSet@1.2309, 2006-12-01 10:36:30+01:00, mats@kindahl-laptop.dnsalias.net +11 -0 BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE' from log): When row-based logging is used, the CREATE-SELECT is written as two parts: as a CREATE TABLE statement and as the rows for the table. For both transactional and non-transactional tables, the CREATE TABLE statement was written to the transaction cache, as were the rows, and on statement end, the entire transaction cache was written to the binary log if the table was non-transactional. For transactional tables, the events were kept in the transaction cache until end of transaction (or statement that were not part of a transaction). For the case when AUTOCOMMIT=0 and we are creating a transactional table using a create select, we would then keep the CREATE TABLE statement and the rows for the CREATE-SELECT, while executing the following statements. On a rollback, the transaction cache would then be cleared, which would also remove the CREATE TABLE statement. Hence no table would be created on the slave, while there is an empty table on the master. This relates to BUG#22865 where the table being created exists on the master, but not on the slave during insertion of rows into the newly created table. This occurs since the CREATE TABLE statement were still in the transaction cache until the statement finished executing, and possibly longer if the table was transactional. This patch changes the behaviour of the CREATE-SELECT statement by writing the CREATE TABLE statement directly to the binary log as soon as it is created, then the rows that are inserted into the table, and on error a DROP table is written to the binary log, provided: - the table was created, - the table didn't exist prior to the statement, - we are replicating row-based, and - it was not a temporary table. For transactional tables, the intermediate rows are not written to the binary log, but for non-transactional tables, the rows are written to the binary log immediately (hence, changes to non-transactional tables are propagated to the slave as soon as enough rows are collected to form a rows event).
[12 Dec 2006 19:50]
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/16853 ChangeSet@1.2309, 2006-12-12 20:50:29+01:00, mats@romeo.(none) +10 -0 BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE' from log): When row-based logging is used, the CREATE-SELECT is written as two parts: as a CREATE TABLE statement and as the rows for the table. For both transactional and non-transactional tables, the CREATE TABLE statement was written to the transaction cache, as were the rows, and on statement end, the entire transaction cache was written to the binary log if the table was non-transactional. For transactional tables, the events were kept in the transaction cache until end of transaction (or statement that were not part of a transaction). For the case when AUTOCOMMIT=0 and we are creating a transactional table using a create select, we would then keep the CREATE TABLE statement and the rows for the CREATE-SELECT, while executing the following statements. On a rollback, the transaction cache would then be cleared, which would also remove the CREATE TABLE statement. Hence no table would be created on the slave, while there is an empty table on the master. This relates to BUG#22865 where the table being created exists on the master, but not on the slave during insertion of rows into the newly created table. This occurs since the CREATE TABLE statement were still in the transaction cache until the statement finished executing, and possibly longer if the table was transactional. This patch changes the behaviour of the CREATE-SELECT statement by adding an implicit commit at the end of the statement when creating non-temporary tables. Hence, non-temporary tables will be written to the binary log on completion, and in the even of AUTOCOMMIT=0, a new transaction will be started. Temporary tables do not commit an ongoing transaction: neither as a pre- not a post-commit. The events for both transactional and non-transactional tables are saved in the transaction cache, and written to the binary log at end of the statement.
[19 Dec 2006 11:05]
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/17151 ChangeSet@1.2309, 2006-12-19 12:04:58+01:00, mats@romeo.(none) +10 -0 BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE' from log): When row-based logging is used, the CREATE-SELECT is written as two parts: as a CREATE TABLE statement and as the rows for the table. For both transactional and non-transactional tables, the CREATE TABLE statement was written to the transaction cache, as were the rows, and on statement end, the entire transaction cache was written to the binary log if the table was non-transactional. For transactional tables, the events were kept in the transaction cache until end of transaction (or statement that were not part of a transaction). For the case when AUTOCOMMIT=0 and we are creating a transactional table using a create select, we would then keep the CREATE TABLE statement and the rows for the CREATE-SELECT, while executing the following statements. On a rollback, the transaction cache would then be cleared, which would also remove the CREATE TABLE statement. Hence no table would be created on the slave, while there is an empty table on the master. This relates to BUG#22865 where the table being created exists on the master, but not on the slave during insertion of rows into the newly created table. This occurs since the CREATE TABLE statement were still in the transaction cache until the statement finished executing, and possibly longer if the table was transactional. This patch changes the behaviour of the CREATE-SELECT statement by adding an implicit commit at the end of the statement when creating non-temporary tables. Hence, non-temporary tables will be written to the binary log on completion, and in the even of AUTOCOMMIT=0, a new transaction will be started. Temporary tables do not commit an ongoing transaction: neither as a pre- not a post-commit. The events for both transactional and non-transactional tables are saved in the transaction cache, and written to the binary log at end of the statement.
[21 Dec 2006 8:29]
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/17255 ChangeSet@1.2309, 2006-12-21 09:29:02+01:00, mats@romeo.(none) +10 -0 BUG#22864 (Rollback following CREATE... SELECT discards 'CREATE TABLE' from log): When row-based logging is used, the CREATE-SELECT is written as two parts: as a CREATE TABLE statement and as the rows for the table. For both transactional and non-transactional tables, the CREATE TABLE statement was written to the transaction cache, as were the rows, and on statement end, the entire transaction cache was written to the binary log if the table was non-transactional. For transactional tables, the events were kept in the transaction cache until end of transaction (or statement that were not part of a transaction). For the case when AUTOCOMMIT=0 and we are creating a transactional table using a create select, we would then keep the CREATE TABLE statement and the rows for the CREATE-SELECT, while executing the following statements. On a rollback, the transaction cache would then be cleared, which would also remove the CREATE TABLE statement. Hence no table would be created on the slave, while there is an empty table on the master. This relates to BUG#22865 where the table being created exists on the master, but not on the slave during insertion of rows into the newly created table. This occurs since the CREATE TABLE statement were still in the transaction cache until the statement finished executing, and possibly longer if the table was transactional. This patch changes the behaviour of the CREATE-SELECT statement by adding an implicit commit at the end of the statement when creating non-temporary tables. Hence, non-temporary tables will be written to the binary log on completion, and in the even of AUTOCOMMIT=0, a new transaction will be started. Temporary tables do not commit an ongoing transaction: neither as a pre- not a post-commit. The events for both transactional and non-transactional tables are saved in the transaction cache, and written to the binary log at end of the statement.
[22 Dec 2006 14:11]
Mats Kindahl
Bug solved by adding an implicit commit at the end of all CREATE TABLE ... SELECT ... statements. Documentation needs to be updated to reflect this; but see BUG#22864 for more information.
[7 Feb 2007 12:05]
MC Brown
An entry has been added to the 5.1.15 changelog, and the notes on 'Statements that issue an implicit commit' have been updated to reflect the change of behaviour in this statement.
Description: CREATE table new engine=myisam ... SELECT should binlog its work, CREATE a new table, at the end of the statement regardless of autocommit status of the session. If not, a concurrent connection can successfully modify the new table and leave a record in binlog, and that can be before CREATE .. SELECT running session finally issues COMMIT. In such case binlog would have insert new values () create table new () records in that wrong order. Note, only combination of myisam table type and @@session.binlog_format=statement provides the correct logging. Other combinations (myisam or innodb) deliver incorrect sequence of records into binlog. There is a related Bug#22862. How to repeat: drop table if exists t; reset master; connection1: connection2 /*1*/ set autocommit=0; /*2*/ create table t (a int) engine=innodb \ select (1) as b; /*3*/ insert into t2 values(1,1) /*4* commit; show binlog events; # will list insert,create Suggested fix: Perhaps, create ... selected is not honored as implicitly committing.