| Bug #58147 | ALTER TABLE w/ TRUNCATE PARTITION fails - but the statement is written to binlog | ||
|---|---|---|---|
| Submitted: | 11 Nov 2010 20:00 | Modified: | 18 Dec 2010 10:50 |
| Reporter: | Victor Kirkebo | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S3 (Non-critical) |
| Version: | 5.5.7-rc | OS: | Any |
| Assigned to: | Mattias Jonsson | CPU Architecture: | Any |
[11 Nov 2010 21:27]
Davi Arnaut
This behavior is to work around the fact there is no way to rollback a truncate partition statement. Instead, the statement gets logged as long as the engine method is invoked. This behavior is similar for TRUNCATE, ALTER TABLE, etc. We could filter out some errors, but it could turn out to be error prone.
[11 Nov 2010 21:31]
Davi Arnaut
But my guess is that we could improve this a bit for TRUNCATE PARTITION. Perhaps the partition engine can signal when a underlying engine was actually involved in the partial execution of the statement.
[19 Nov 2010 10:11]
Victor Kirkebo
I checked some other ALTER TABLE...PARTITION statements such as: DROP PARTITION, REORGANIZE PARTITION, ANALYZE PARTITION, CHECK PARTITION, OPTIMIZE PARTITION, REBUILD PARTITION, REPAIR PARTITION When using a non-existing partition name value these all return the error: "ERROR 1507 (HY000): Error in list of partitions to <operation name>". None of these statements get written to the binlog after failing with this error message. This seems to be consistent with the behavior in 5.1 (I checked with 5.1.53). For the sake of consistency one might argue that TRUNCATE PARTITION should: 1) Not be written to the binlog when failing when using non-existing partition names. 2) Fail with the same error as the other ALTER TABLE .... <op> PARTITION statements. I.e. "ERROR 1507 (HY000): Error in list of partitions to <operation name>".
[19 Nov 2010 12:15]
Davi Arnaut
The previous "consistent" behavior was just a side effect or partitioning being incrusted with the processing of regular DDL commands. The point that any changes cannot be rolled back is still ignored. If some operation that involves transactional state is done before this error is given, things will break horribly for replication. So, someone would need to ensure that nothing happens before the particular errors that do not cause the statement to be replicated. Too error prone for my taste, whilst there isn't any bad consequence in replicating the statement. Anyway, this is up to partitioning guys.
[19 Nov 2010 13:07]
Victor Kirkebo
Regarding my previous comment: I tested those other ALTER TABLE statements on both 5.5.7-rc and 5.1.53 and the results are the same in both versions. If it is the case that changes might happen that cannot be rolled back in any of those other ALTER TABLE statements too - then one could argue that it is not correct that those statements do not get binlogged.
[19 Nov 2010 17:59]
Sveta Smirnova
Thank you for the report. Verified as described.
[1 Dec 2010 21:48]
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/125719 3158 Mattias Jonsson 2010-12-01 Bug#58147: ALTER TABLE w/ TRUNCATE PARTITION fails but the statement is written to binlog TRUNCATE PARTITION was written to the binlog even if it failed before calling any partition's truncate function. Solved by adding an argument to truncate_partition, to flag if it should be written to the binlog or not. It should be written to the binlog when a call to any partitions truncate function is done. @ mysql-test/r/partition_binlog.result New result file @ mysql-test/t/partition_binlog.test New test file, including DROP PARTITION binlog test @ sql/ha_partition.cc Added argument to avoid binlogging failed truncate_partition that have not yet changed any data. @ sql/ha_partition.h Added argument to avoid excessive binlogging @ sql/sql_partition_admin.cc Avoid to binlog TRUNCATE PARTITION if it fails before any partition has tried to truncate.
[3 Dec 2010 9: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/125908 3164 Mattias Jonsson 2010-12-03 [merge] merge of bug#58147, including rename of the new argument, to_binlog -> binlog_stmt.
[3 Dec 2010 10:23]
Mattias Jonsson
pushed into mysql-trunk-bugfixing and mysql-5.5-bugteam
[5 Dec 2010 12:38]
Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)
[13 Dec 2010 7:56]
Jon Stephens
Issue doesn't appear in any 5.6 release. Still waiting for push to 5.5 tree. Set status = Need Merge.
[17 Dec 2010 12:51]
Bugs System
Pushed into mysql-5.5 5.5.9 (revid:georgi.kodinov@oracle.com-20101217124733-p1ivu6higouawv8l) (version source revid:mattias.jonsson@oracle.com-20101203093329-fzrzpujzdgsv8g3f) (merge vers: 5.5.8) (pib:24)
[18 Dec 2010 10:50]
Jon Stephens
Documented bugfix in the 5.5.9 changelog as follows:
A failed ALTER TABLE ... TRUNCATE PARTITION statement was still
written to the binary log.
Closed.
Description: When trying to truncate a non-existing partition with ALTER TABLE <table name> TRUNCATE PARTITION <partition name> this will fail with the error message: ERROR 1567 (HY000): Incorrect partition name. In 5.5.7-rc the failing ALTER TABLE statement still gets written to the binog. This does not happen in 5.5.7-m3. However, this does not seem to affect replication (no error reported for slave) in 5.5.7-rc. (I'm running tests with replication where the ALTER TABLE statement appears in the binlog). The issues here are: 1) The failing statement should not be written to the binlog in 5.5.7-rc. 2) Why is this not affecting replication? Could it be that replication is failing to replicate this type of statements? 3) I have only checked this specific ALTER TABLE statement. Could there be other types of failing ALTER TABLE statements that are not handled correctly, i.e. written to the binlog when they should not have been? How to repeat: Here's a sample run. Please substitute the parameter values for binlog file and binlog position used in the "show binlog events..." statement below accordingly. mysql> select @@version; +--------------+ | @@version | +--------------+ | 5.5.7-rc-log | +--------------+ 1 row in set (0.00 sec) mysql> show master status; +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | master-bin.000002 | 19096568 | | | +-------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) mysql> create database tst; Query OK, 1 row affected (0.00 sec) mysql> use tst; Database changed mysql> create table t1(id int) PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (100), PARTITION pmax VALUES LESS THAN (MAXVALUE)); Query OK, 0 rows affected (0.01 sec) mysql> alter table t1 truncate partition p1; ERROR 1567 (HY000): Incorrect partition name mysql> show binlog events in 'master-bin.000002' from 19096568; +-------------------+----------+------------+-----------+-------------+------------------------------------------------------ ----------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +-------------------+----------+------------+-----------+-------------+------------------------------------------------------ ----------------------------------------------------------------------------------------+ | master-bin.000002 | 19096568 | Query | 1 | 19096649 | create database tst | | master-bin.000002 | 19096649 | Query | 1 | 19096840 | use `tst`; create table t1(id int) PARTITION BY RANGE (id) (PARTITION p0 VALUES LESS THAN (100), PARTITION pmax VALUES LESS THAN (MAXVALUE)) | | master-bin.000002 | 19096840 | Query | 1 | 19096938 | use `tst`; alter table t1 truncate partition p1 | +-------------------+----------+------------+-----------+-------------+------------------------------------------------------ ----------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)