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.

 

[CvP]

That auto_increment is the issue.

I think XF doesn't force auto increment value and for whatever reason, your server has those values as default.


Yes, there are quite a few tables that you might need to update such as xf_user_authenticate etc. However, I'd advice you to fix the auto_increment issue instead of trying to modify the user id.

 

[CvP]

Try this: delete everything. Start a fresh install. Proceed normally until you reach the step where it asks for you to enter user name/password for admin account.

Open phpmyadmin and run these queries:

SET GLOBAL auto_increment_offset = 1;
SET SESSION auto_increment_offset = 1;
SET GLOBAL auto_increment_increment = 1;
SET SESSION auto_increment_increment = 1;

Once done, run this query to verify

SHOW VARIABLES LIKE 'auto_inc%'

Both values should be 1.


Now enter your admin name/password for XF to create admin account.


After done, verify if your admin account has user_id 1 or not.

 

[XnForFanKN]

Try this: delete everything. Start a fresh install. Proceed normally until you reach the step where it asks for you to enter user name/password for admin account.

Open phpmyadmin and run these queries:

SET GLOBAL auto_increment_offset = 1;
SET SESSION auto_increment_offset = 1;
SET GLOBAL auto_increment_increment = 1;
SET SESSION auto_increment_increment = 1;

Once done, run this query to verify

SHOW VARIABLES LIKE 'auto_inc%'

Both values should be 1.


Now enter your admin name/password for XF to create admin account.


After done, verify if your admin account has user_id 1 or not.

Sounds very good. Thank you for that info. Quick question: There are some other databases on the server in addition to this one. Will running the queries likely affect the other databases as well, or is that just for all the tables in this database? I think most use increment 1 anyway, but I just wanted to check.

 

[CvP]

It will but it should not cause any issues there.

It is really strange to have 2 and 10 for these variables to begin with...

 

[XnForFanKN]

It will but it should not cause any issues there.

It is really strange to have 2 and 10 for these variables to begin with...

Thank you very much for all of your help, CvP. I did what you suggested, and it worked great. Running those queries got the auto increment set correctly, which I was able to confirm with the second query. After that, I set up the default user and it was assigned user ID 1. That allowed the import to run correctly.

Again, thank you very much for your help. I really appreciate it.

 

[CvP]

You are welcome