XF 1.1 Error when trying to manually add a user

[Renada]

Hi,

When trying to manually add a user to my site I get this error:

Mysqli statement execute error : Duplicate entry '207' for key 'PRIMARY'
 
    Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
    Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
    Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 574
    Zend_Db_Adapter_Abstract->insert() in XenForo/DataWriter.php at line 1591
    XenForo_DataWriter->_insert() in XenForo/DataWriter.php at line 1580
    XenForo_DataWriter->_save() in XenForo/DataWriter.php at line 1381
    XenForo_DataWriter->save() in XenForo/ControllerAdmin/User.php at line 481
    XenForo_ControllerAdmin_User->actionSave() in XenForo/FrontController.php at line 310
    XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 132
    XenForo_FrontController->run() in /home/xxxxx/xxxxxxx.com/public_html/community/forums/admin.php at line 13

Anyone know what's causing this?

Thanks
Renada

 

[Jake Bunce]

Did you ever manually delete users from the xf_user table? In doing so you would have left behind joined records in other tables listed here:

http://xenforo.com/community/threads/script-to-create-a-new-user.21286/#post-270084

That can result in duplicate key errors later on. You need to cleanup the orphaned records or manually increase the autoincrement value in the xf_user table to avoid future collisions.

 

[Renada]

Eeeek, yes I did, cleaning up hundreds of spam registrations Are there any queries I can run to clean this up or do I have to do this manually?

 

[Jake Bunce]

Eeeek, yes I did, cleaning up hundreds of spam registrations Are there any queries I can run to clean this up or do I have to do this manually?

Manually. Check for the highest used user_id in xf_user and then prune all records higher than that in the other tables.

edit - but even still, there are many other tables that reference user_ids. For example, if any of those users had private messages then you might get a situation where new users inherit those old PMs. The moral is, don't manually delete user records from the database. I suggest banning them instead.

edit 2 - or increase the autoincrement value like I said.

 

[Renada]

Thanks Jake

It's a small, fairly inactive site that I run for a few friends and I'm pretty sure I had manual authorisation on. One day I got hit by so many spambot registrations that my host closed my site down. Editing the database to remove them all was apparently a bad choice Have since installed a couple of spam checkers.

Thanks again for your help