v1.5: Error upgrading database

Question

I have an error while trying to upgrade the Q2A database. But the user definitely has the permissions listed, and I also tried using the root user but get the same error:

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

ALTER TABLE qa_posts CHANGE COLUMN type type ENUM('Q', 'A', 'C', 'Q_HIDDEN', 'A_HIDDEN', 'C_HIDDEN', 'Q_QUEUED', 'A_QUEUED', 'C_QUEUED', 'NOTE') NOT NULL, ADD COLUMN updatetype CHAR(1) CHARACTER SET ascii AFTER updated, ADD COLUMN closedbyid INT UNSIGNED AFTER selchildid, ADD KEY closedbyid (closedbyid), ADD CONSTRAINT qa_posts_ibfk_4 FOREIGN KEY (closedbyid) REFERENCES qa_posts(postid)

Error 1005: Can't create table 'pokemondb.#sql-437_e8' (errno: 121)

 

Answer 1

Managed to solve this in the end. For some reason there is already a constraint named qa_posts_ibfk_4 so I removed that using:

ALTER TABLE `qa_posts` DROP FOREIGN KEY `qa_posts_ibfk_4`;

And the upgrade worked.

 

EDIT: after checking information_schema.TABLE_CONSTRAINTS it looks like my qa_posts table has several different names to a fresh install of 1.5:

mine: PRIMARY, qa_posts_ibfk_4, qa_posts_ibfk_5, qa_posts_ibfk_6
default: PRIMARY, qa_posts_ibfk_1, qa_posts_ibfk_2, qa_posts_ibfk_3, qa_posts_ibfk_4

Gideon, do you have a SQL query that will reset all the constraints?

Comments

Sorry about this. Very early versions of Q2A didn't name constraints explicitly, so it's possible that MySQL chose a different name to what was expected. Anyway I don't think it's possible to rename CONSTRAINTs in MySQL, so you'd need to use ALTER TABLE to DROP them, then ADD them again. You can see examples of the syntax in qa-db-install.php.