MySQL Lock Wait Timeout Exceeded Error in InnoDB Tables – How to fix it

MySQL Master-Master Replication setup in 5 easy steps

Today I was installing the latest (at the time of writing) MediaWiki build, which is 1.27.1, to a customer, together with all the bundled plugins and add-ins, following more or less the same steps i described here (installing within IIS), here (install WikiEditor) and also here (import Wikipedia templates). Needless to say, I did everything using the main administrator account, which gets created during the MediaWiki installation phase. As usual, I also created the database using InnoDB tables, the new MySQL engine which supports table and row-based locking and is also suggested by MediaWiki as the proper choice for performance and reliability.

After doing all that, just like I did a number of other times, I moved to the special pages and I started to create the other users… And there, right after POSTing the first new user form, I was greeted by a strange (and rather obscure) HTTP Error 500.

I quickly edited the LocalSettings.php file and added the following line to show (and also log) the issue:

Just FYI, I usually add these three out of habit but the last line is the only important one there, since it logs every MediaWiki debug message which is what you need to read to fix almost any MW-related issue.

To keep it short, this is what I found there (relevant lines only):

WHERE user_name = ‘[USERNAME]’ LIMIT 1 LOCK IN SHARE MODE
Function: MediaWiki\Auth\TemporaryPasswordPrimaryAuthenticationProvider::testUserExists
Error: 1205 Lock wait timeout exceeded; try restarting transaction (localhost)
I wasn’t surpised to see an error on Special:CreateUser page, since that’s where the form was. However, I really couldn’t explain the Lock wait timeout error: how could that happen?

Needless to say, the given suggestion of running the   script was completely useless: I never “upgraded” anything, just installed the official, stable 1.27.1 release. I did execute that nonetheless, just to be sure it wasn’t some bug in the installation procedure, but nothing changed at all.

Right after that, I tried connecting to the MySQL service instance and execute the above SELECT statement manually,  just to ensure that the lock actually occurred… and so it was! The process went into a stale state ight after executing this:

WHERE user_name = ‘[USERNAME]’ LIMIT 1 LOCK IN SHARE MODE
It definitely seemed like a problem of the    statement, because the query was completing normally if I removed it.

I tried to look online, but couldn’t find anything about that, so I opened the   file and tried to figure out the cause for that odd MySQL behaviour. The first thing I noticed was a incredibly short   value, which I raised (for now) at 4M.

Guess what? As soon as I did that, everything went OK and MediaWiki managed to successfully complete the new user creation task. I still have to understand why I didn’t receive a packet-size error warning and how table-locking and   size are related, but at least I managed to get it working. I sincerely hope that my experience will allow other MySQL and MediaWiki users to save their precious time!

 

RELATED POSTS

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies.

View all posts by Ryan