XF 1.5 Duplicate entry '2' for key 'PRIMARY' when importing

[XnForFanKN]

I am importing from vBulletin 4 into the latest version of Xenforo. When I get to the "Import Users" step, it stops and gives the following error:

********
Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Duplicate entry '2' for key 'PRIMARY' - library/Zend/Db/Statement/Mysqli.php:214

Stack Trace
#0 /home/Xenforo/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/Xenforo/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/Xenforo/library/Zend/Db/Adapter/Abstract.php(574): Zend_Db_Adapter_Abstract->query('INSERT INTO `xf...', Array)
#3 /home/Xenforo/library/XenForo/DataWriter.php(1638): Zend_Db_Adapter_Abstract->insert('xf_user', Array)
#4 /home/Xenforo/library/XenForo/DataWriter.php(1627): XenForo_DataWriter->_insert()
#5 /home/Xenforo/library/XenForo/DataWriter.php(1419): XenForo_DataWriter->_save()
#6 /home/Xenforo/library/XenForo/Model/Import.php(822): XenForo_DataWriter->save()
#7 /home/Xenforo/library/XenForo/Importer/vBulletin.php(1330): XenForo_Model_Import->importUser(2, Array, '')
#8 /home/Xenforo/library/XenForo/Importer/vBulletin.php(1105): XenForo_Importer_vBulletin->_importUser(Array, Array)
#9 /home/Xenforo/library/XenForo/Importer/vBulletin.php(814): XenForo_Importer_vBulletin->_importOrMergeUser(Array, Array)
#10 /home/Xenforo/library/XenForo/Importer/Abstract.php(124): XenForo_Importer_vBulletin->stepUsers(0, Array)
#11 /home/Xenforo/library/XenForo/ControllerAdmin/Import.php(189): XenForo_Importer_Abstract->runStep(Object(XenForo_ControllerAdmin_Import), Object(XenForo_ImportSession), 'users', 0, Array)
#12 /home/Xenforo/library/XenForo/ControllerAdmin/Import.php(241): XenForo_ControllerAdmin_Import->_runStep(Object(XenForo_Importer_vBulletin4x), Object(XenForo_ImportSession), 'users', 0, Array)
#13 /home/Xenforo/library/XenForo/ControllerAdmin/Import.php(184): XenForo_ControllerAdmin_Import->_startStep(Object(XenForo_Importer_vBulletin4x), Object(XenForo_ImportSession), 'users', Array)
#14 /home/Xenforo/library/XenForo/FrontController.php(351): XenForo_ControllerAdmin_Import->actionStartStep()
#15 /home/Xenforo/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#16 /home/Xenforo/admin.php(13): XenForo_FrontController->run()
#17 {main}
********

I looked up the Duplicate entry '2' for key 'PRIMARY' error, and it seemed to say it's a rare thing and to not worry about it. However, it is preventing the import from completing. What can I do to get it to import users? Thank you.

 

[Paul B]

Are you importing into a forum which already has content and retaining IDs?

You can't do both - either retain IDs and import into an empty forum, or don't retain IDs.

 

[XnForFanKN]

Are you importing into a forum which already has content and retaining IDs?

You can't do both - either retain IDs and import into an empty forum, or don't retain IDs.

I'm importing into a brand new Xenforo installation. The only user ID is the one that I had to create when installing the forum. I gave it the same username as ID number 1 on vBulletin in order to keep all the ID numbers the same. Would that maybe cause an issue? Should I have instead chose a totally different name that doesn't exist on vBulletin, such as "Extra Admin"?

 

[Paul B]

Using the same name for member/ID 1 is fine - that is typically how it's done.

Are you sure no other content has been created, even content which has subsequently been deleted?
The importer seems to think there is a member with an ID of 2.

 

[XnForFanKN]

Using the same name for member/ID 1 is fine - that is typically how it's done.

Are you sure no other content has been created, even content which has subsequently been deleted?
The importer seems to think there is a member with an ID of 2.

No, there was no other content created. I knew not to do that from your very well-written importing guide. Thanks for that, BTW. I just installed Xenforo this afternoon and then started the importing process.

 

[XnForFanKN]

One other thing I just noticed. You mentioned that the importer seems to think that a member with the user ID of 2 exists. Well, guess what? You were right, it does exist. However, it is the user that I created during the Xenforo installation process. I hadn't even checked to see the user ID that was assigned to that user as I just assumed it would be user ID number 1, but I just looked in the control panel and it is user ID number 2. How would the original user ID created during the installation process end up as User ID number 2 instead of user ID number 1?

 

[Paul B]

I can only assume something went wrong with the original installation.

The default account created during the installation process is always ID 1.

You will likely have to start over or if you have done a lot of customisation, you may be able to manually edit the database and change the ID to 1 (you may have to reset the incremental count for the table so the next ID is 2, but that may not matter when doing an import and retaining IDs).

 

[XnForFanKN]

I can only assume something went wrong with the original installation.

The default account created during the installation process is always ID 1.

So probably the best thing to do would be to delete the newly created database and all the newly uploaded files and try again? Is there anything I should watch for during the second installation, besides checking the user ID of the default user as soon as I'm able to login to the new control panel?

 

[CvP]

No need to delete files. Just go to install.php and it will ask you to delete install-lock.php. Do so and start a fresh install.

 

[XnForFanKN]

No need to delete files. Just go to install.php and it will ask you to delete install-lock.php. Do so and start a fresh install.

Thanks, CvP .

I tried that, but I still had the same problem. So then I deleted everything...the database and all the files. I then re-created the database, re-uploaded the installation files, and reinstalled Xenforo. Again, the default user ended up with user ID number 2. This is so weird. The only thing I didn't do was re-download the installation files from Xenforo.com. That will be my next step, along with what I already did above for the third time. But the download worked fine before and I don't know why the files would have gotten corrupt, so I'm not really expecting that to make a difference. If it doesn't, how can I finish the import? I will probably have to manually change the user ID to number 1 in phpmyadmin. There aren't any posts yet since it is a clean install, so I don't think I have to worry about posts being labeled with the wrong user ID yet. What tables would I all need to change at this point if I were to change the user ID to user ID 1?

 

[CvP]

Yes, definitely weird.

You can change from xf_user table and edit the config.php to make the new id as super admin. Though I think if you still cant get it work properly, you can always open a support ticket...

 

[Mike]

As a test, can you create a new user in the control panel? What user ID do they get? Is it 4?

 

[XnForFanKN]

Yes, definitely weird.

You can change from xf_user table and edit the config.php to make the new id as super admin. Though I think if you still cant get it work properly, you can always open a support ticket...

Thanks, that might be what I have to do. I would just change the user id in xf_user from 2 to 1. Is there any other table that I would need to make changes on? I'm guessing not, since this is a new installation.

As a test, can you create a new user in the control panel? What user ID do they get? Is it 4?

That's a good idea! I will check that and let you know.

 

[XnForFanKN]

As a test, can you create a new user in the control panel? What user ID do they get? Is it 4?

@Mike I tried creating an additional user through the control panel, and it turned out to be user ID 12. Then I did it again, and it was ID 22. The next ID was 32. The next one was 42. And the next one was 52. I stopped after that.

Also, not that it really matters for this exact issue, but the config file that was automatically created set user ID 1 which doesn't exist as the Super Administrator, so there is no Super Administrator right now. I can very easily fix that of course by putting in one of the other IDs, but I just wanted to mention that as well. It seems like the config file was corrected generated, but the user IDs were not.

 

[CvP]

So your auto increment is set to 10...I guess.

But how :/

 

[XnForFanKN]

So your auto increment is set to 10...I guess.

But how :/

I have no idea why it would be set that way. I'm thinking if I run the following query, it would reset the auto increment:

ALTER TABLE xf_user AUTO_INCREMENT = 1

Although that probably won't work now anymore, since the users are already created. I might have to reinstall again and run that query before creating the default user. I wonder if that would fix the problem of the default user being assigned user ID number 2? Hmm...

 

[CvP]

What is result if you run this query?

SHOW VARIABLES LIKE 'auto_inc%'

 

[dknife]

Since it's InnoDB, is something trying to do an INSERT/UPDATE which would add the auto_increment even though it might not be inserting anything?

 

[XnForFanKN]

What is result if you run this query?

SHOW VARIABLES LIKE 'auto_inc%'

That query returned the following results:

auto_increment_increment 10
auto_increment_offset 2

I'm thinking I could run that other query I mentioned before:

ALTER TABLE xf_user AUTO_INCREMENT = 1

But would that only affect the one xf_user table that I'm listing, or is that going to adversely affect other tables and databases as well that might have the other setting for a reason?

Since it's InnoDB, is something trying to do an INSERT/UPDATE which would add the auto_increment even though it might not be inserting anything?

Not that I know of. Did you mean something with Xenforo or something else on the server?

 

[XnForFanKN]

Update: I'm not sure that it actually is working to change the user ID to 1 in the database. It seemed like it was working, and I thought that the password just wasn't working. But now it looks like something is happening with the user name itself. This is what I do.

First, I go in the xf_user table and change the user_id from 2 to 1. That works fine in phpmyadmin. However, I am then logged out of the account, so I do a password reset. I get the e-mail, click the link, and it shows the user name and gives the option to reset the password. I type in the New Password and Confirm New Password, but then it says "No account specified." So it's like changing the ID somehow makes the user account inaccessible. Also, if I then go to the user profile address I get "The requested member could not be found." Of course, changing the user id back to 2 fixes all this, but I still can't do the import then.

Is there another table I need to edit also in order to fully change the user_id from 2 to 1?