Error on upgrading: Error 1005: Can't create table 'lkktnkcw_doquest.qa_userfavorites' (errno: 150)

Question

Hi,

I was upgrading to the current version and got the following error upon upgrading the database.

I have no plugins installed, was running a Portuguese language pack and the database user permissions are correct... 

Thanks in advance

 

Running query: UNLOCK TABLES ... 

Running query: 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(userid) ON DELETE CASCADE) ENGINE=InnoDB CHARSET=utf8 ... 

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(userid) ON DELETE CASCADE) ENGINE=InnoDB CHARSET=utf8

Error 1005: Can't create table 'lkktnkcw_doquest.qa_userfavorites' (errno: 150)

 

Answer 1

It sounds like you may have modified the type of the userid column in the qa_users table. It should be INT UNSIGNED NOT NULL AUTO_INCREMENT to match the column in the table qa_userfavorites that is being created when this error happens. Can you please check that?

Comments

I really dind´t modify anything, but I read something about MyISAM and InnoDB database type and that seems to have fixed it. Although I am getting an error message when I try to change some setting, not sure if its realted (but the changes do take effect): Warning: Cannot modify header information - headers already sent by (output started at /home/lkktnkcw/public_html/xxxxxxx/perguntas/qa-lang/pt/qa-lang-question.php:1) in /home/lkktnkcw/public_html/xxxxxx/perguntas/qa-include/qa-base.php on line 1315

---

First, changing the tables from InnoDB to MyISAM will indeed cause this problem, since MyISAM doesn't support foreign keys, which are used to ensure consistency between tables at the database level.

As for the other problem you mention, it looks like the qa-lang/pt/qa-lang-question.php language file has some extra characters at the start. Probably a UTF-8 byte-order mark. You should be able to remove this with a text editor.