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:
|
1 2 3 |
error_reporting( E_ALL ); ini_set( 'display_errors', 1 ); $wgDebugLogFile = "C:/Temp/mediawiki.log"; |
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):
|
1 2 3 4 5 6 7 |
SQL ERROR: Lock wait timeout exceeded; try restarting transaction (localhost) [exception] [679c0a932861e3062c73d359] /wiki/Speciale:CreaUtenza DBQueryError from line 934 of C:\Data\Produzione\MyRete.Wiki\w\includes\db\Database.php: A database error has occurred. Did you forget to run maintenance/update.php after upgrading? See: https://www.mediawiki.org/wiki/Manual:Upgrading#Run_the_update_script Query: SELECT user_id FROM |
user 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 maintenance/update.php 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:
|
1 |
Query: SELECT user_id FROM |
user WHERE user_name = '[USERNAME]' LIMIT 1 LOCK IN SHARE MODE
It definitely seemed like a problem of the
LOCK IN SHARE MODE 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 my.ini file and tried to figure out the cause for that odd MySQL behaviour. The first thing I noticed was a incredibly short max_allowed_packet value, which I raised (for now) at 4M.
|
1 2 3 |
# The maximum size of one packet or any generated or intermediate string, or any parameter sent by the # mysql_stmt_send_long_data() C API function. max_allowed_packet=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 max_allowed_packet 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!
