Bug #14479 locking issues with create/drop triggers.
Submitted: 30 Oct 2005 0:45 Modified: 30 Nov 2005 10:22
Reporter: Jeff C Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.15 OS:Linux (RHEL3)
Assigned to: CPU Architecture:Any

[30 Oct 2005 0:45] Jeff C
Description:
I've had locking issues, where all queries are "locked" and stay that until I restart mysql.

The general idea is.. have an active database and load triggers ... You will get locking issues.

If that doesn't work, make an error in the trigger on purpose and try reloading.

What I'm doing is 

mysql database < triggers.sql

I received this message from a "use database"
'RROR 1102 (42000) at line 301: Incorrect database name 'database

(Why the line is mangled is beyond me.)

Shortly after running that and having it error, all queries are set to locked until i restart mysql.

I'm working on an extact test case, however I just thought it was serious enough to report it.

Thanks,
Jeff

How to repeat:
On a semi-active database... run :
mysql database < triggers.sql

Where that file has :

drop trigger ins_table;

create trigger ......
.....

-- If that doesn't work... make an error in the "triggers.sql" and rerun it.. guaranteed that all queries will be 'locked' forever until you restart.

Suggested fix:
Analyze the locking when using drop trigger/create trigger and when an error is generated while creating a trigger.
[30 Oct 2005 10:22] Valeriy Kravchuk
Thnak you for a problem report. Please, provide the repeatable test case, if any (looks like you have one already) and reopen this bug report. You may upload the files as private using File tab.

If you have no exact sequence of actions that leads to this state each and every times, answer the following questions, please:

- What is the exact content of that triggers.sql? 
- What is the structure and content of tables referenced in it? 
- What do you mean by that "semi-active" database? 
- Is there something unusual in your error log?

Please, send the results of the SHOW PROCESSLIST command executed while you have this "locking".
[1 Dec 2005 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".