Database error in 1.6 beta 2

Question

I tried to upgrade Q2A but it failed due to an error. 

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_userlevels (userid INT UNSIGNED NOT NULL, entitytype CHAR(1) CHARACTER SET ascii NOT NULL, entityid INT UNSIGNED NOT NULL, level TINYINT UNSIGNED, UNIQUE userid (userid, entitytype, entityid), KEY entitytype (entitytype, entityid), CONSTRAINT qa_userlevels_ibfk_1 FOREIGN KEY (userid) REFERENCES qa_users(userid) ON DELETE CASCADE) ENGINE=InnoDB CHARSET=utf8

Error 1005: Can't create table 'qa.qa_userlevels' (errno: 150)

 

How can I fix this?

Comments

This should not happen unless you have been modifying the Q2A database? Is it possible you switched tables from InnoDB to MyISAM, or changed some column definitions, or removed some indexes?

---

I always minded "Engine Type(ET)". Q2A ET is InnoDB. Most online hosts support InnoDB. However, in local host(XAMPP, UniformServer, etc), we often can't use InnoDB by default. When InnoDB is not supported, MySQL adopts default storage engine (MyISAM). The technique to upload all data(files and database) to online server is common after we develop site at local host. If InnoDB is indispensable to movement condition of Q2A, I recommend that you prescribe engine type in page below.

http://www.question2answer.org/install.php

What do you think?

---

I haven't modified the database personally, but it is possible that a plugin might've.

Answer 1

I'm troubled with this problem, too.

 

Reference article:

http://www.question2answer.org/qa/24641/upgrade-error-1-6b1

 

If your site is on dedicated server or VPS that can change the variable of MySQL, you may deal by adding variable to my.cnf below?

init_connect='SET foreign_key_checks = 0'

Don't forget to enable it when you're done:

init_connect='SET foreign_key_checks = 1'

Answer 2

OK I have solved the issue. sama55 is completely correct. I wrote some PHP code to set the foreign key check to 0 in qa-db-install.php

Here is the code from lines 1364-1381 (changed parts are highlighted):

case 54:

qa_db_query_sub("SET FOREIGN_KEY_CHECKS=0;");

qa_db_upgrade_query('UNLOCK TABLES');

 

qa_db_upgrade_query(qa_db_create_table_sql('userlevels', array(

'userid' => $definitions['userlevels']['userid'],

'entitytype' => $definitions['userlevels']['entitytype'],

'entityid' => $definitions['userlevels']['entityid'],

'level' => $definitions['userlevels']['level'],

'UNIQUE userid (userid, entitytype, entityid)',

'KEY entitytype (entitytype, entityid)',

QA_FINAL_EXTERNAL_USERS ? null : 'CONSTRAINT ^userlevels_ibfk_1 FOREIGN KEY (userid) REFERENCES ^users(userid) ON DELETE CASCADE',

)));

 

$locktablesquery.=', ^userlevels WRITE';

qa_db_upgrade_query($locktablesquery);

qa_db_query_sub("SET FOREIGN_KEY_CHECKS=1;");

break;

 

Comments

Good job!!  edward.
Answer of edward is best answer!

---

OK, I have implemented something similar to this for the final Q2A 1.6 release.