This problem was raised by Misstique over at Guilty Pleasures.

Problem diagnosis

After downloading a database backup prior to upgrading WordPress, the upgrade ran into some problems, and we now want to restore the backup. But a problem occurred:

Cannot delete or update a parent row: a foreign key constraint fails

By default, a database backup will save all of your tables into an SQL file, and it will organise them alphabetically. This is a bit of a problem if you have database constraints where some tables reference other tables before they are created.

How to fix this problem

On looking at the backup file I discovered that two tables are to blame:

1. sz_blog, which references sz_site
2. sz_blog_user, which references sz_blog and sz_email

The solution is to edit the SQL file and ensure that the tables are created in the following order:

1. sz_site
2. sz_email
3. sz_blog
4. sz_blog_user

If you're not confident editing SQL files directly then I suggest asking a technical person to help you out. Let me know if you need any help.

Redoing the import

Once the SQL file has been corrected, you need to do the import again. Unfortunately, as the last import failed, we need to delete the tables and start again.

Go to phpMyAdmin and select all of the tables in the database (making sure you are looking at the right database and you have a current backup!!). Then look for the "with selected" drop down list, and select "Drop".

This will delete all of the tables. Except it won't delete all of them, because there's a foreign key constraint there, too. Argh. Anyway, go back and delete the last two tables - sz_blog and sz_site - before importing the backup once again.

Oh wait... there's another error.

Unfortunately, this wasn't the last of the problems. When I did the import, I got another error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '', 0, '(email address removed for privacy reasons)', '')' at line 1

The query that caused this error was as follows:

INSERT INTO `sz_comment`
VALUES ( 0, 38, 5, '2008-02-28', '', '5', '0', , '', 0, '(email address removed for privacy reasons)', '' ) ;

Look at this: '5', '0', , '', - Note the comma with no value placed before it. This is the field rating_count, which for some reason hasn't exported properly. Perhaps the plugin that uses it hasn't set the value correctly. Odd really, because the default value for this field is '0'.

If we want to import the sz_comment table, we're going to have to update every row in sz_comment with an empty value so it says 0, instead of just a comma. In this case, that's 181 rows to check. Not all of them are missing a value, but quite a few of them are. In the end, I updated 27 rows to resolve this problem.

Again, before we can import, we have to delete the old tables.

But it seems there is another error. sz_posts has the same error as sz_comment, shown above. So we now need to fix that too.

What can we learn from this?

Firstly, developers should be very careful with database constraints where backups are concerned. I'm not sure if there is any way to export the tables in a certain order, but the import certainly didn't work, and most bloggers would not be able to fix this issue on their own.

Secondly, the sz_ prefixes on all of the tables suggests that these problems are caused by a plugin from SezWho. I believe that SezWho has been acquired by JS-Kit, so if you want to continue using a comments plugin along those lines, try using JS-Kit. However, this may not be enough, as it's possible that the SezWho plugin leaves various sz_ tables in your database even after the plugin is removed. If this is the case and you don't mind losing your SezWho data, a much simpler solution to this problem would be to avoid importing any table that starts with sz_. I don't know if there's a way to migrate from SezWho to JS-Kit, but if you're having problems with a database import and you haven't already migrated your data, you'll either need to follow the steps outlined above or wave bye-bye to your comment data.

Have you experienced this problem?

Want to read more posts like this? Subscribe!