upgrade from v1.41 to v1.50 beta issue

Question

Question2Answer was unable to perform the installation query below. Please check the user in the config file has CREATE and ALTER permissions

CREATE TABLE qa_messages (messageid INT UNSIGNED NOT NULL AUTO_INCREMENT, fromuserid INT UNSIGNED NOT NULL, touserid INT UNSIGNED NOT NULL, content VARCHAR(8000) NOT NULL, format VARCHAR(20) CHARACTER SET ascii NOT NULL, created DATETIME NOT NULL, PRIMARY KEY (messageid), KEY fromuserid (fromuserid, touserid, created)) ENGINE=InnoDB CHARSET=utf8

Error 1100: Table 'qa_messages' was not locked with LOCK TABLES

Comments

That's weird, since it's not possible to lock a table before it has been created! What version of MySQL are you using, and can you also please check if there is currently a qa_messages table in your Q2A database?

---

I am having the same problem with a different table.

Question2Answer was unable to perform the installation query below. Please check the user in the config file has CREATE and ALTER permissions:

CREATE TABLE qa_userfavorites (userid INT UNSIGNED NOT NULL, entitytype CHAR(1) CHARACTER SET ascii NOT NULL, entityid INT UNSIGNED NOT NULL, nouserevents TINYINT UNSIGNED NOT NULL, PRIMARY KEY (userid, entitytype, entityid), KEY userid (userid, nouserevents), KEY entitytype (entitytype, entityid, nouserevents), CONSTRAINT qa_userfavorites_ibfk_1 FOREIGN KEY (userid) REFERENCES qa_users_users(userid) ON DELETE CASCADE) ENGINE=InnoDB CHARSET=utf8

Error 1100: Table 'qa_userfavorites' was not locked with LOCK TABLES

At this point the table 'qa_userfavorites' does not exists

Than I go and run the query 'CREATE TABLE qa_userfavorites...' manually in mysql, the table is created successfuly. I run the install script again and get the error:

Error 1050: Table 'qa_userfavorites' already exists

If I delete the 'qa_userfavorites' table and run the install script again I get the first error (Error 1100: Table 'qa_userfavorites' was not locked with LOCK TABLES) again


I am using the database master user, so full permissions granted:
Mysql 5.5.9
PHP 5.3.6

Answer 1

OK, I think I've found the problem. This is a change of behavior in MySQL 5.5:

http://bugs.mysql.com/bug.php?id=58927

Basically, you can't create a table while some tables are locked in the same database. I didn't catch this because I'm using earlier versions of MySQL.

The solution (for now) is a little inconvenient:

• Open qa-include/qa-db-install.php in a text editor.
• Search for the text case 31:
• Between there and the end of the case statements, add qa_db_upgrade_query('UNLOCK TABLES'); before every appearance of qa_db_upgrade_query(qa_db_create_table_sql(...

There should be 9 such occurrences in total, with the last in case 47:

Please let me know in a comment whether this solves the problem, so I can make the appropriate fixes in the second beta of Q2A 1.5. Thanks!

Comments

Solved @gidgreen!

I put 'qa_db_upgrade_query('UNLOCK TABLES');' ontop of every case statement and it updated perfectly.

Thanks!

---

Great - thanks for letting me know.