Upgrade to 5.2.3 Error Duplicate Entry in access table

[srock]

I'm upgrading from 4.2.3 to 5.2.3. I'm currently doing this on a test forum, not live. Wow the upgrade script is slow, is that due to DB size? We can purge data before the live update.

Here is the error I ran into:

521a2 Step #16

Fatal Error Occurred
Duplicate entry '253-0' for key 'userid' on <mydomain>\forums\core\vb\db\query\stored.php : 124

Script: 521a2
Step: 16

I traced it the code in 521a2 Step 16 to a insert statement into the access table. Sure enough there are duplicate entries. 3 userids have multiple records. What's the problem here? Data or schema?

[srock]

I manually dropped the table access_temp. Now it complains access_temp does not exist. access_temp is created in step 14. How do I rollback the upgrade to step 14?

[Mark.B]

You'll need to recreate it manually (this assumes no table prefix, if you have one you'lll need to add it in):

CREATE TABLE access_temp (
                userid INT UNSIGNED NOT NULL DEFAULT '0',
                nodeid SMALLINT UNSIGNED NOT NULL DEFAULT '0',
                accessmask SMALLINT UNSIGNED NOT NULL DEFAULT '0',
                UNIQUE INDEX user_node(userid, nodeid) 

[srock]

Recreated the access_temp table and the upgrade is moving forward now. Weird.

[srock]

Finished the upgrade! Went to admincp to login and got this error:

Database error in vBulletin 5.2.3:

Invalid SQL:
SELECT userid, usergroupid, membergroupids, infractiongroupids, username, password, salt FROM user WHERE username = 'myusername';

MySQL Error : Unknown column 'password' in 'field list'
Error Number : 1054
Request Date : Tuesday, October 25th 2016 @ 03:25:15 PM
Error Date : Tuesday, October 25th 2016 @ 03:25:15 PM
Script : http://<mydomain>/forums/login.php?do=login
Referrer : http://<mydomain>//forums/admincp/
Username : Unregistered
Classname : vB_Database
MySQL Version :

[Mark.B]

Are there ONLY vB5 files in your directory? If you have leftover vB4 files, they will cause issues with the site, such as this.

[srock]

Is there a script to delete all the vb 4 files? Or do I do it manually? Just go by timestamp on the files?

[Mark.B]

No, these should really have been removed before you upgraded.

Simplest way now is this:

1. Take a copy of your /core/includes/config.php file.
2. Backup the whole forum folder.
3. Delete everything in the forum folder (except for attachments, avatars, profile pics, sigpics etc if these are stored in the file system)
4. Re-upload all the default vB5 files (except /core/install/)
5. Put back your copy of /core/includes/config.php

[srock]

No, these should really have been removed before you upgraded.

Simplest way now is this:

1. Take a copy of your /core/includes/config.php file.
2. Backup the whole forum folder.
3. Delete everything in the forum folder (except for attachments, avatars, profile pics, sigpics etc if these are stored in the file system)
4. Re-upload all the default vB5 files (except /core/install/)
5. Put back your copy of /core/includes/config.php

I don't see anything about removing old files in the upgrade instructions. Do you have instructions for this for when I do this on the live system?

[Mark.B]

I don't see anything about removing old files in the upgrade instructions. Do you have instructions for this for when I do this on the live system?

Pretty much the same.
If your attachments, avatars etc are stored in the file system, then *everything* except those folders needs to be removed - but keep a copy of it all. Then upload the vB5 files into there.
If your attachmnts, avatars etc are in the database, then you can just rename your existing folder to something else, and create a new folder with the same name as the old one had. Then put the vB5 files in there. Or if your forum is in the site root, copy all the old vB4 folders and files to somewhere else as a backup, then upload the vB5 files and folders.

You will need to configure /core/includes/config.php with the right details, same as on previous versions.
You will need rename htaccess.txt in the forum root, to .htaccess (note the period / dot at the front of the name).
Leave the front end config.php alone.

And obviously...have a backup of your database before running the upgrade script.