Bug #46780 lock table write, then create trigger, but failed

• Submitted: 18 Aug 2009 10:15
• Modified: 18 Aug 2009 13:33
• Reporter: Zhifeng Ke
• Status: Can't repeat
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 5.0.38
• OS: Linux
• CPU Architecture: Any
• Tags: lock; trigger

[18 Aug 2009 10:15] Zhifeng Ke

Description:
I lock a table with 'lock table t1 write' through command-line client, done;
Then I create a after delete trigger on t1.
It just stopped without any output as waiting for lock , while the db server was still running.

I don't think it is a bug , it is so obvious. But I can't get any useful information for a whole day, so I post it here just for help.  

How to repeat:
1 create two tables
CREATE TABLE test_table1 (
column1 BIGINT NOT NULL,
column2 INT NOT NULL,
column3 VARCHAR(255),
column4 TEXT,
column5 INT DEFAULT 0,
PRIMARY KEY (column1))
ENGINE = InnoDB
DEFAULT CHARSET=gbk;

CREATE TABLE test_table2 LIKE test_table1

2 get table lock
LOCK TABLES test_table1 WRITE

3 create the trigger
CREATE TRIGGER test_trigger AFTER DELETE ON test_table1
FOR EACH ROW
DELETE FROM test_table2 WHERE (column1 = OLD.column1);

[18 Aug 2009 13:33] Sveta Smirnova

Thank you for the report.

This is not repeatable since version 5.1 which is GA and recommended to use now. Please upgrade.