Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+1 vote
1.2k views
in Q2A Core by
edited by
I've upgraded from 1.0 to 1.2 and was prompted to upgrade the database. With 5 update steps remaining, I got this error:

----------
Warning: Cannot modify header information - headers already sent by (output started at [my path]/qa-include/qa-db-install.php:695) in [my path]/qa-include/qa-install.php on line 68

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 ADD CONSTRAINT qa_posts_ibfk_2 FOREIGN KEY (parentid) REFERENCES qa_posts(postid), ADD COLUMN categoryid SMALLINT UNSIGNED, ADD KEY categoryid (categoryid, type, created), ADD CONSTRAINT qa_posts_ibfk_3 FOREIGN KEY (categoryid) REFERENCES qa_categories(categoryid) ON DELETE SET NULL

Error 1452: Cannot add or update a child row: a foreign key constraint fails (`pokemondb`.`#sql-57f_b8`, CONSTRAINT `qa_posts_ibfk_2` FOREIGN KEY (`parentid`) REFERENCES `qa_posts` (`postid`))
----------

The user definitely has CREAT/ALTER permissions. Any ideas what may be causing this? I'm don't know much about foreign key constraints and the like.

1 Answer

+1 vote
by
 
Best answer
This is likely to be because one of the posts has a parentid which does not match the postid of another post, perhaps because of some manual table editing that was done (such as deleting rows), or it could just be a data inconsistency that crept in somehow. If you like, these are "orphaned" rows.

In any event you can find the problematic rows using this query:

select child.postid,child.parentid,parent.postid from qa_posts as child left join qa_posts as parent on parent.postid=child.parentid where parent.postid is null and child.parentid is not null;

The output from that might show some rows with a non-NULL second column, and a NULL third column. Those represent rows in the qa_posts table which have a parentid that doesn't match any other post.

You can delete those rows, using the first column of the output, which is their postid.
by
Thanks, that worked. I had deleted a few things manually. I do have another problem now... I can't log in! I'm using the same database on localhost as the live site but the same email/pass doesn't work. (I'm assuming it would be the same for the users too?)
by
Are you still seeing this problem? None of the upgrades have changes the way in which the login information is stored in the database, so it's a little surprising.
by
Still not working for some reason. I exported my live database (v1.0) and imported into my local database (v1.0 at the time) then ran the upgrade script. I'm using the same username/password that works fine on the live site.
asked Aug 9, 2010 in Q2A Core by
edited Aug 9, 2010 by
Best method to import/export the database?
by
The problem is most likely that the passsalt column is not being imported correctly, because it is in a raw binary form that is likely to get mangled by a textual import/export operation. Using mysqldump is likely to solve it.
by
I just came back to say the same thing! I checked my tables and noticed a difference in the passsalt fields. It's odd though since the other binary fields work perfectly well. I'll look into using mysqldump, thanks!
by
OK turns out it was a problem with mismatching phpMyAdmin versions (only 0.0.1 of a difference though!) Updating both live and local to same version worked fine.
...