Bug #71070 | kill of truncate table will lead to binary log written while rows remains | ||
---|---|---|---|
Submitted: | 4 Dec 2013 4:05 | Modified: | 14 May 2014 15:44 |
Reporter: | Guangpu Feng | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Replication | Severity: | S2 (Serious) |
Version: | 5.6.14,5.5.18, 5.6.15 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | binlog, truncate |
[4 Dec 2013 4:05]
Guangpu Feng
[4 Dec 2013 6:13]
Guangpu Feng
this bug will break replication
[4 Dec 2013 14:58]
qinglin zhang
HA_ERR_WRONG_COMMAND may mislead people, so you can offer another errorno instead
[12 Dec 2013 11:21]
MySQL Verification Team
Hello Guangpu, Thank you for the bug report and contribution. Thanks, Umesh
[14 May 2014 15:44]
Jon Stephens
This issue is fixed in MySQL 5.5.38, 5.6.19, and 5.7.5. Documented fix in the corresponding changelogs as follows: When used on a table employing a transaction storage engine, a failed TRUNCATE TABLE was still written to the binary log and thus replayed on the slave. This could lead to inconsistency when the master retained data that was removed on the slave. Now in such cases TRUNCATE TABLE is logged only when it completes successfully. Closed.
[31 May 2014 14:05]
Laurynas Biveinis
$ bzr log -r 4620 ------------------------------------------------------------ revno: 4620 committer: Sujatha Sivakumar <sujatha.sivakumar@oracle.com> branch nick: Bug17942050_mysql-5.5 timestamp: Tue 2014-04-15 15:17:25 +0530 message: Bug#17942050:KILL OF TRUNCATE TABLE WILL LEAD TO BINARY LOG WRITTEN WHILE ROWS REMAINS Problem: ======== When truncate table fails while using transactional based engines even though the operation errors out we still continue and log it to binlog. Because of this master has data but the truncate will be written to binary log which will cause inconsistency. Analysis: ======== Truncate table can happen either through drop and create of table or by deleting rows. In the second case the existing code is written in such a way that even if an error occurs the truncate statement will always be binlogged. Which is not correct. Binlogging of TRUNCATE TABLE statement should check whether truncate is executed "transactionally or not". If the table is transaction based we log the TRUNCATE TABLE only on successful completion. If table is non transactional there are possibilities that on error we could have partial changes done hence in such cases we do log in spite of errors as some of the lines might have been removed, so the statement has to be sent to slave. Fix: === Using table handler whether truncate table is being executed in transaction based mode or not is identified and statement is binlogged accordingly.
[28 Jul 2014 9:37]
zhang simon
error= table_ref->table->file->ha_truncate(); if (error) { ..... } DBUG_RETURN(TRUNCATE_OK); in a situation such as raw disk row_truncate_table_for_mysql will return DB_ERROR and convert_error_code_to_mysql will return -1. then DBUG_RETURN(TRUNCATE_OK) incorrectly. Suggested fix: === modified file 'sql/sql_truncate.cc' --- sql/sql_truncate.cc 2014-04-15 09:47:25 +0000 +++ sql/sql_truncate.cc 2014-07-28 09:02:20 +0000 @@ -243,7 +243,7 @@ DBUG_RETURN(TRUNCATE_FAILED_SKIP_BINLOG); error= table_ref->table->file->ha_truncate(); - if (error) + if (error != 0) { table_ref->table->file->print_error(error, MYF(0)); /* @@ -252,7 +252,7 @@ donot binlog the statment. Only in non transactional engine we binlog inspite of errors. */ - if (error == HA_ERR_WRONG_COMMAND || + if (error == HA_ERR_WRONG_COMMAND || error == -1 table_ref->table->file->has_transactions()) DBUG_RETURN(TRUNCATE_FAILED_SKIP_BINLOG); else