[Answered] Duplicate Key errors

[Trevor Hannant]

When backing your existing database up, backup software (depending on what you use) may add a line like this to the script for each of the tables that exist in the version you're currently running:

DROP TABLE IF EXISTS PREFIX_TABLE;

vB4 introduces new tables and new columns to existing tables. If you choose to go back to a 3.x version of the software and restore your database to the same database you were using, your backup script will drop (delete) all the 3.x tables, recreate and fill them with the data you had at the time you backed up. However, your restore will not delete the new vB4 tables that were created during the upgrade script.

As a result, when you go to upgrade again and the upgrade script tries to create and add data to these tables, it can't and gives you the error shown:

Database error in vBulletin 3.8.4:
 
Invalid SQL:
INSERT INTO forum_tagcontent
            (tagid, contenttypeid, contentid, userid, dateline)
        SELECT tagid, 2, threadid, userid, dateline
        FROM forum_tagthread;
 
MySQL Error   : Duplicate entry '[I]n-n-nnnn[/I]' for key 1
Error Number  : 1062

where n = a number

To counteract this, if restoring your database we highly recommend doing the following:

1. Create a new database and restore to this one
2. Update config.php to point to the new database (you may need to amend the login details also if that database uses a different db username and/or password)

If after trying this you still receive the above error, please feel free to post her for additional assistance.

[Jake Bunce]

One of my upgrade customers had this problem, but they no longer had a backup that didn't include the excess vB4 tables. I had to manually identify and remove the excess tables that didn't belong. I came up with this list of tables to remove:

action
attachmentcategory
attachmentcategoryuser
bbcode_video
cache
cacheevent
cms_article
cms_category
cms_grid
cms_layout
cms_layoutwidget
cms_navigation
cms_node
cms_nodecategory
cms_nodeconfig
cms_nodeinfo
cms_permissions
cms_rate
cms_sectionorder
cms_widget
cms_widgetconfig
cms_widgettype
contentpriority
contenttype
filedata
indexqueue
package
picturelegacy
route
searchcore
searchcore_text
searchgroup
searchgroup_text
searchlog
stylevar
stylevardfn
tagcontent
templatemerge

These are the excess tables which result from restoring a vB3.8 backup on top of a vB4 database. These tables need to be removed from the vB3.8 database before you can upgrade to vB4 in order to avoid the error in the first post. Of course the ideal is to restore the vB3 backup into an empty database to avoid this problem altogether.

[Jake Bunce]

And here is a big query to delete all of these excess tables if you are unable to restore a backup and need to clean up your current database so you can upgrade again:

DROP TABLE action,attachmentcategory,attachmentcategoryuser,bbcode_video,cache,cacheevent,cms_article,cms_category,cms_grid,cms_layout,cms_layoutwidget,cms_navigation,cms_node,cms_nodecategory,cms_nodeconfig,cms_nodeinfo,cms_permissions,cms_rate,cms_sectionorder,cms_widget,cms_widgetconfig,cms_widgettype,contentpriority,contenttype,filedata,indexqueue,package,picturelegacy,route,searchcore,searchcore_text,searchgroup,searchgroup_text,searchlog,stylevar,stylevardfn,tagcontent,templatemerge;

Note that this query contains all excess tables. Your database might not contain every single table in which case this query will generate an error, but it will still delete the other tables.

[Trevor Hannant]

If your vB database tables use a prefix, remember to add this ahead of each of the table names above