Database upgrade error (Table 'qa_messages' was not locked with LOCK TABLES) when upgrading from 1.6.3 to 1.8.0

Question

On trying to upgrade my website from 1.6.3 to 1.8.0, I got the following error.

Database query error 1100
Table 'qa_messages' was not locked with LOCK TABLES

ALTER TABLE qa_messages ADD COLUMN tohidden TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AFTER fromhidden

I ignored the error and reloaded the page. The upgrade continued with few more similar errors around locks and finally got stuck at qa_iplimits. At this point, even after refreshing the page multiple times, I kept getting the same error.

The version of mysql that I am using is around 4 years old.

Can someone please help in resolving this error?

Answer 1

I think we may be missing some "LOCK TABLES" queries. Do you know what version your database is current at? (In the qa_options table, look at the db_version field.) From your description I'd guess at 63.

If you look in qa-include/db/install.php underneath "case 63" (line 1521) you'll probably need to add a table lock query below any calls to qa_db_upgrade query that use "ALTER TABLE". For example line 1531-1532 may need to look like this:

qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query('ALTER TABLE ^iplimits MODIFY ip ' . $definitions['iplimits']['ip']);
qa_db_upgrade_query($locktablesquery);
qa_db_upgrade_query('UPDATE ^iplimits SET ip = UNHEX(HEX(CAST(ip AS UNSIGNED)))');

Let me know if that solves that stage of the upgrade (it may stop again on later stages).