Bug #50418 DROP PARTITION does not interact with transactions

• Submitted: 18 Jan 2010 15:19
• Modified: 22 Sep 2010 10:11
• Reporter: Mattias Jonsson
• Status: Duplicate
• Category: MySQL Server: Partitions
• Severity: S1 (Critical)
• Version: 5.1+, 5.5.99
• OS: Any
• Assigned to: Assigned Account
• CPU Architecture: Any

[18 Jan 2010 15:19] Mattias Jonsson

Description:
DROP PARTITION does not wait or break concurrent transactions running in other threads, which breaks ACID (Isolation) on the other transactions.

How to repeat:
CREATE TABLE t1 (
id INT AUTO_INCREMENT NOT NULL,
name CHAR(50) NOT NULL,
myDate DATE NOT NULL,
PRIMARY KEY (id, myDate),
INDEX idx_date (myDate)
) ENGINE=InnoDB
PARTITION BY RANGE ( TO_DAYS(myDate) ) (
PARTITION p0 VALUES LESS THAN (734028),
PARTITION p1 VALUES LESS THAN (734029),
PARTITION p2 VALUES LESS THAN (734030),
PARTITION p3 VALUES LESS THAN MAXVALUE
) ;
INSERT INTO t1 VALUES 
(NULL, 'Lachlan', '2009-09-13'),
(NULL, 'Clint', '2009-09-13'),
(NULL, 'John', '2009-09-14'),
(NULL, 'Dave', '2009-09-14'),
(NULL, 'Jeremy', '2009-09-15'),
(NULL, 'Scott', '2009-09-15'),
(NULL, 'Jeff', '2009-09-16'),
(NULL, 'Joe', '2009-09-16');
SET AUTOCOMMIT=0;
SELECT * FROM t1 FOR UPDATE;
id	name	myDate
1	Lachlan	2009-09-13
2	Clint	2009-09-13
3	John	2009-09-14
4	Dave	2009-09-14
5	Jeremy	2009-09-15
6	Scott	2009-09-15
7	Jeff	2009-09-16
8	Joe	2009-09-16
UPDATE t1 SET name = 'Mattias' WHERE id = 7;
SELECT * FROM t1 WHERE id = 7;
id	name	myDate
7	Mattias	2009-09-16
# Connection con1
ALTER TABLE t1 DROP PARTITION p3;
SHOW WARNINGS;
Level	Code	Message
# Connection default
SELECT * FROM t1;
id	name	myDate
1	Lachlan	2009-09-13
2	Clint	2009-09-13
3	John	2009-09-14
4	Dave	2009-09-14
# Note that there are no errors or warnings that someone else have made changes.
COMMIT;
# No errors/warnings here either
DROP TABLE t1;

Suggested fix:
Before removing any (possibly used) partition, take an exclusive lock on either the partition (if possible) or on the table (i.e. name lock or any other exclusive lock).

[18 Jan 2010 20:19] Sveta Smirnova

Thank you for the report.

Verified as described.

[10 Feb 2010 12:39] Mattias Jonsson

Also related to bug#49907.

[11 Jun 2010 0:28] Mattias Jonsson

fix for 5.5 is included in the proposed patch for bug#53676 / bug#53770 here:
http://lists.mysql.com/commits/110773

[25 Aug 2010 9:22] Bugs System

Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)

[30 Aug 2010 8:30] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:[e-mail]-20100830082732-n2eyijnv86exc5ci) (version source revid:[e-mail]-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)

[30 Aug 2010 8:33] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100830082745-n6sh01wlwh3itasv) (version source revid:[e-mail]-20100830082745-n6sh01wlwh3itasv) (pib:21)

[22 Sep 2010 10:11] Mattias Jonsson

Fixed in 5.5 by the patch for bug#53676.