v1.4 to 1.5BETA can't create table

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_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 'q2ask2.qa_userfavorites' (errno: 150)

Answer 1

This is a problem with the foreign key definition. Did you ever make any manual modifications to the Q2A user database? Can you provide the output of:

SHOW CREATE TABLE qa_users\G

Comments

i have same error.

output(SHOW CREATE TABLE qa_users):

qa_users, CREATE TABLE `qa_users` (
  `userid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `created` datetime NOT NULL,
  `createip` int(10) unsigned NOT NULL,
  `email` varchar(80) NOT NULL,
  `handle` varchar(20) NOT NULL,
  `avatarblobid` bigint(20) unsigned DEFAULT NULL,
  `avatarwidth` smallint(5) unsigned DEFAULT NULL,
  `avatarheight` smallint(5) unsigned DEFAULT NULL,
  `passsalt` binary(16) DEFAULT NULL,
  `passcheck` binary(20) DEFAULT NULL,
  `level` tinyint(3) unsigned NOT NULL,
  `loggedin` datetime NOT NULL,
  `loginip` int(10) unsigned NOT NULL,
  `written` datetime DEFAULT NULL,
  `writeip` int(10) unsigned DEFAULT NULL,
  `emailcode` char(8) CHARACTER SET ascii NOT NULL DEFAULT '',
  `sessioncode` char(8) CHARACTER SET ascii NOT NULL DEFAULT '',
  `sessionsource` varchar(16) CHARACTER SET ascii DEFAULT '',
  `flags` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`userid`),
  KEY `email` (`email`),
  KEY `handle` (`handle`),
  KEY `level` (`level`)
) ENGINE=MyISAM AUTO_INCREMENT=96 DEFAULT CHARSET=utf8

Answer 2

This could happen if you switched the qa_users table to MyISAM, rather than leaving it as InnoDB. This creates a problem for foreign keys that reference the table.

 

Two solutions:

 

1. Convert qa_users back to InnoDB

 

2. Remove the foreign key in the definition by changing this in qa-db-install.php:

 

'CONSTRAINT ^userfavorites_ibfk_1 FOREIGN KEY (userid) REFERENCES ^users(userid) ON DELETE CASCADE'

 

... to ...

 

null

 

You will have to do similar things with subsequent problematic upgrade steps if you choose this route.

 

Another solution might be to replace InnoDB with MyISAM in the source code of qa-db-install.php, if you want all tables to by MyISAM.