2

I've tried to read MySQL's documentation on metadata locking to figure out what's going on here, but our scenario just doesn't seem to fit.

We're using MySQL 5.5.35. All tables are InnoDB

First, our app was built 9 years ago and was initially really heavy on the stored procedure side (as in everything was done in stored procedures). All stored procedures have their own script that first drops if it exists, then creates the procedure. When we run our migrations, it will source every one of these scripts, effectively dropping and recreating every procedure we have. Included in this process are 3 triggers.

Whenever we have a specific long-running report executing (select statement joining several tables) at the same time we do a migration, we find that everything locks up on the "drop trigger if exists" statement with a "Waiting for table metadata lock" State. Every subsequent query after that gets the same "Waiting for table metadata lock" state, which instantly fills up our max_connections and our server dies. If I kill the report, it's able to finish processing the triggers and all the previously waiting queries and everything returns to normal.

According to the docs, it would make sense that the drop trigger function would have to wait due to the query because it's a DDL statement and that's precisely the point of the metadata lock. What I don't understand is why all of the subsequent DML statements end up waiting for the lock as well.

Can someone explain to me why this is happening?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.