| Bug #65344 | Truncate partition corrupts table data | ||
|---|---|---|---|
| Submitted: | 16 May 2012 20:33 | Modified: | 18 May 2012 9:33 |
| Reporter: | Radu Chiriac | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S1 (Critical) |
| Version: | 5.5.19 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | TRUNCATE PARTITION | ||
[16 May 2012 20:38]
Radu Chiriac
Things are even much weirder. This was the script I used to reproduce the bug:
CREATE TABLE x (
id bigint NOT NULL AUTO_INCREMENT,
event_time datetime NOT NULL,
PRIMARY KEY (id, event_time)
) ENGINE=InnoDB
partition by list(hour(event_time)) (
partition p0 values in (0),
partition p1 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23)
);
insert into x values (null, '2012-03-04 11:22:33');
-- make sure data really exists in the table:
select * from x;
select event_time, hour(event_time) from x;
alter table x truncate partition p1;
-- BUG: this is where things go weird
-- "select *" and "select event_time, hour(event_time)" returns
-- a record, while "select hour(event_time)" returns no record
select * from x;
select event_time, hour(event_time) from x;
select hour(event_time) from x;
Make a tiny mod: comment out the first "select * from x;" made prior to "ALTER TABLE .. TRUNCATE PARTITION", i.e. make the script be:
drop table if exists x;
CREATE TABLE x (
id bigint NOT NULL AUTO_INCREMENT,
event_time datetime NOT NULL,
PRIMARY KEY (id, event_time)
) ENGINE=InnoDB
partition by list(hour(event_time)) (
partition p0 values in (0),
partition p1 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23)
);
insert into x values (null, '2012-03-04 11:22:33');
-- make sure data really exists in the table:
-- COMMENTED OUT: select * from x;
select event_time, hour(event_time) from x;
alter table x truncate partition p1;
select * from x;
select event_time, hour(event_time) from x;
select hour(event_time) from x;
And now, the "select * from x" ran after truncate partition returns no data! While "select event_time, our(event_time) from x" still returns data:
mysql>
mysql> drop table if exists x;
--------------
drop table if exists x
--------------
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> CREATE TABLE x (
-> id bigint NOT NULL AUTO_INCREMENT,
-> event_time datetime NOT NULL,
-> PRIMARY KEY (id, event_time)
-> ) ENGINE=InnoDB
-> partition by list(hour(event_time)) (
-> partition p0 values in (0),
-> partition p1 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23)
-> );
--------------
CREATE TABLE x (
id bigint NOT NULL AUTO_INCREMENT,
event_time datetime NOT NULL,
PRIMARY KEY (id, event_time)
) ENGINE=InnoDB
partition by list(hour(event_time)) (
partition p0 values in (0),
partition p1 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23)
)
--------------
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into x values (null, '2012-03-04 11:22:33');
--------------
insert into x values (null, '2012-03-04 11:22:33')
--------------
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> -- make sure data really exists in the table:
mysql> -- COMMENTED OUT: select * from x;
mysql> select event_time, hour(event_time) from x;
--------------
select event_time, hour(event_time) from x
--------------
+---------------------+------------------+
| event_time | hour(event_time) |
+---------------------+------------------+
| 2012-03-04 11:22:33 | 11 |
+---------------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> alter table x truncate partition p1;
--------------
alter table x truncate partition p1
--------------
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> select * from x;
--------------
select * from x
--------------
Empty set (0.00 sec)
mysql> select event_time, hour(event_time) from x;
--------------
select event_time, hour(event_time) from x
--------------
+---------------------+------------------+
| event_time | hour(event_time) |
+---------------------+------------------+
| 2012-03-04 11:22:33 | 11 |
+---------------------+------------------+
1 row in set (0.00 sec)
mysql> select hour(event_time) from x;
--------------
select hour(event_time) from x
--------------
Empty set (0.00 sec)
[16 May 2012 23:37]
MySQL Verification Team
Please try latest version. I couldn't repeat with source code.
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
d:\dbs>55
d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.25 Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.5 >USE test
Database changed
mysql 5.5 >CREATE TABLE x (
-> id bigint NOT NULL AUTO_INCREMENT,
-> event_time datetime NOT NULL,
-> PRIMARY KEY (id, event_time)
-> ) ENGINE=InnoDB
-> partition by list(hour(event_time)) (
-> partition p0 values in (0),
-> partition p1 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23)
-> );
Query OK, 0 rows affected (1.01 sec)
mysql 5.5 >insert into x values (null, '2012-03-04 11:22:33');
Query OK, 1 row affected (0.05 sec)
mysql 5.5 >select * from x;
+----+---------------------+
| id | event_time |
+----+---------------------+
| 1 | 2012-03-04 11:22:33 |
+----+---------------------+
1 row in set (0.00 sec)
mysql 5.5 >select event_time, hour(event_time) from x;
+---------------------+------------------+
| event_time | hour(event_time) |
+---------------------+------------------+
| 2012-03-04 11:22:33 | 11 |
+---------------------+------------------+
1 row in set (0.02 sec)
mysql 5.5 >alter table x truncate partition p1;
Query OK, 0 rows affected (0.09 sec)
mysql 5.5 >
mysql 5.5 >-- BUG: this is where things go weird
mysql 5.5 >-- "select *" and "select event_time, hour(event_time)" returns
mysql 5.5 >-- a record, while "select hour(event_time)" returns no record
mysql 5.5 >select * from x;
Empty set (0.00 sec)
mysql 5.5 >select event_time, hour(event_time) from x;
Empty set (0.00 sec)
mysql 5.5 >select hour(event_time) from x;
Empty set (0.00 sec)
mysql 5.5 >
[18 May 2012 9:33]
Radu Chiriac
Hi Miguel,
I've tried with the latest mysql version - 5.5.24 and it worked fine.
Looks like it got fixed in one of 5.5.2? releases - strange that i didn't find a reference to this in release notes.
Thanks!
For the record: here's the output i got on 5.5.24 - same as yours:
--------------
CREATE TABLE x (
id bigint NOT NULL AUTO_INCREMENT,
event_time datetime NOT NULL,
PRIMARY KEY (id, event_time)
) ENGINE=InnoDB
partition by list(hour(event_time)) (
partition p0 values in (0),
partition p1 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23)
)
--------------
ERROR 1050 (42S01): Table 'x' already exists
mysql>
mysql> insert into x values (null, '2012-03-04 11:22:33');
--------------
insert into x values (null, '2012-03-04 11:22:33')
--------------
Query OK, 1 row affected (0.01 sec)
mysql>
mysql> -- make sure data really exists in the table:
mysql> select * from x;
--------------
select * from x
--------------
+----+---------------------+
| id | event_time |
+----+---------------------+
| 1 | 2012-03-04 11:22:33 |
+----+---------------------+
1 row in set (0.00 sec)
mysql> select event_time, hour(event_time) from x;
--------------
select event_time, hour(event_time) from x
--------------
+---------------------+------------------+
| event_time | hour(event_time) |
+---------------------+------------------+
| 2012-03-04 11:22:33 | 11 |
+---------------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> alter table x truncate partition p1;
--------------
alter table x truncate partition p1
--------------
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> -- BUG: this is where things go weird
mysql> -- "select *" and "select event_time, hour(event_time)" returns
mysql> -- a record, while "select hour(event_time)" returns no record
mysql> select * from x;
--------------
select * from x
--------------
Empty set (0.00 sec)
mysql> select event_time, hour(event_time) from x;
--------------
select event_time, hour(event_time) from x
--------------
Empty set (0.00 sec)
mysql> select hour(event_time) from x;
--------------
select hour(event_time) from x
--------------
Empty set (0.00 sec)
mysql>
mysql>
mysql>
mysql> drop table if exists x;
--------------
drop table if exists x
--------------
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> CREATE TABLE x (
-> id bigint NOT NULL AUTO_INCREMENT,
-> event_time datetime NOT NULL,
-> PRIMARY KEY (id, event_time)
-> ) ENGINE=InnoDB
-> partition by list(hour(event_time)) (
-> partition p0 values in (0),
-> partition p1 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23)
-> );
--------------
CREATE TABLE x (
id bigint NOT NULL AUTO_INCREMENT,
event_time datetime NOT NULL,
PRIMARY KEY (id, event_time)
) ENGINE=InnoDB
partition by list(hour(event_time)) (
partition p0 values in (0),
partition p1 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23)
)
--------------
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> insert into x values (null, '2012-03-04 11:22:33');
--------------
insert into x values (null, '2012-03-04 11:22:33')
--------------
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> -- make sure data really exists in the table:
mysql> -- COMMENTED OUT: select * from x;
mysql> select event_time, hour(event_time) from x;
--------------
select event_time, hour(event_time) from x
--------------
+---------------------+------------------+
| event_time | hour(event_time) |
+---------------------+------------------+
| 2012-03-04 11:22:33 | 11 |
+---------------------+------------------+
1 row in set (0.00 sec)
mysql>
mysql> alter table x truncate partition p1;
--------------
alter table x truncate partition p1
--------------
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> select * from x;
--------------
select * from x
--------------
Empty set (0.00 sec)
mysql> select event_time, hour(event_time) from x;
--------------
select event_time, hour(event_time) from x
--------------
Empty set (0.00 sec)
mysql> select hour(event_time) from x;
--------------
select hour(event_time) from x
--------------
Empty set (0.00 sec)
Description: Executing ALTER TABLE ... TRUNCATE PARTITION causes table data to be corrupted. See the steps to reproduce. OS: Linux MySQL version: 5.5.19-log MySQL Community Server (GPL) How to repeat: mysql> CREATE TABLE x ( -> id bigint NOT NULL AUTO_INCREMENT, -> event_time datetime NOT NULL, -> PRIMARY KEY (id, event_time) -> ) ENGINE=InnoDB -> partition by list(hour(event_time)) ( -> partition p0 values in (0), -> partition p1 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23) -> ); -------------- CREATE TABLE x ( id bigint NOT NULL AUTO_INCREMENT, event_time datetime NOT NULL, PRIMARY KEY (id, event_time) ) ENGINE=InnoDB partition by list(hour(event_time)) ( partition p0 values in (0), partition p1 values in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23) ) -------------- Query OK, 0 rows affected (0.01 sec) mysql> mysql> insert into x values (null, '2012-03-04 11:22:33'); -------------- insert into x values (null, '2012-03-04 11:22:33') -------------- Query OK, 1 row affected (0.00 sec) mysql> mysql> -- make sure data really exists in the table: mysql> select * from x; -------------- select * from x -------------- +----+---------------------+ | id | event_time | +----+---------------------+ | 1 | 2012-03-04 11:22:33 | +----+---------------------+ 1 row in set (0.00 sec) mysql> select event_time, hour(event_time) from x; -------------- select event_time, hour(event_time) from x -------------- +---------------------+------------------+ | event_time | hour(event_time) | +---------------------+------------------+ | 2012-03-04 11:22:33 | 11 | +---------------------+------------------+ 1 row in set (0.00 sec) mysql> mysql> alter table x truncate partition p1; -------------- alter table x truncate partition p1 -------------- Query OK, 0 rows affected (0.01 sec) mysql> mysql> -- BUG: this is where things go weird mysql> -- "select *" and "select event_time, hour(event_time)" returns mysql> -- a record, while "select hour(event_time)" returns no record mysql> select * from x; -------------- select * from x -------------- +----+---------------------+ | id | event_time | +----+---------------------+ | 1 | 2012-03-04 11:22:33 | +----+---------------------+ 1 row in set (0.00 sec) mysql> select event_time, hour(event_time) from x; -------------- select event_time, hour(event_time) from x -------------- +---------------------+------------------+ | event_time | hour(event_time) | +---------------------+------------------+ | 2012-03-04 11:22:33 | 11 | +---------------------+------------------+ 1 row in set (0.00 sec) mysql> select hour(event_time) from x; -------------- select hour(event_time) from x -------------- Empty set (0.00 sec)