PDA

View Full Version : Database error after impex run


RichieBoy67
15 Feb 2015, 00:05
I did a very large impex import on a site basically just vb4 to vb4. Now when someone tries to register on the site I am getting

Duplicate entry '###' for key 'PRIMARY'

ozzy47
15 Feb 2015, 00:25
What is the last userid on the site, and what is the Next autoindex value for the user table?

RichieBoy67
15 Feb 2015, 00:34
Yeah, I was just trying to rebuilt the indexes using mysql-schema

Will this work?

ALTER TABLE `user` CHANGE `userid` `userid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;

ozzy47
15 Feb 2015, 00:39
What are the values?

RichieBoy67
15 Feb 2015, 00:42
The user base is very small and at 252, auto increment is at 253.

ozzy47
15 Feb 2015, 00:44
Then that value is correct.

What is the full error the users are getting?

ForceHSS
15 Feb 2015, 00:44
ALTER TABLE `user`
MODIFY COLUMN `userid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT FIRST ;

ALTER TABLE `user`
AUTO_INCREMENT=253;

RichieBoy67
15 Feb 2015, 00:45
Database error in vBulletin 4.2.2: Invalid SQL: INSERT INTO userfield (userid) VALUES (252); MySQL Error : Duplicate entry '252' for key 'PRIMARY' Error Number : 1062

--------------- Added 1423961206 at 1423961206 ---------------

ALTER TABLE `user`
MODIFY COLUMN `userid` int(10) UNSIGNED NOT NULL AUTO_INCREMENT FIRST ;

Yeah, That is what I posted above. I wasn't sure it was correct though.

ozzy47
15 Feb 2015, 00:47
That's strange, I wonder why it is trying to set 252 when the auto increment is at 253 :confused:

ForceHSS
15 Feb 2015, 00:48
Database error in vBulletin 4.2.2: Invalid SQL: INSERT INTO userfield (userid) VALUES (252); MySQL Error : Duplicate entry '252' for key 'PRIMARY' Error Number : 1062

--------------- Added 15 Feb 2015 at 00:46 ---------------



Yeah, That is what I posted above. I wasn't sure it was correct though.
Mine is a little different I also update above post after reading your others

ozzy47
15 Feb 2015, 00:49
Read over this, http://www.vbulletin.com/docs/html/mysql_duplicatekey

RichieBoy67
15 Feb 2015, 00:49
ok, well this has been happening so every time someone new registers the number goes up.

This was earlier

Database error in vBulletin 4.2.2: Invalid SQL: INSERT INTO userfield (userid) VALUES (251); MySQL Error : Duplicate entry '251' for key 'PRIMARY' Error Number : 1062

--------------- Added 1423961420 at 1423961420 ---------------

Read over this, http://www.vbulletin.com/docs/html/mysql_duplicatekey

They have the same query I posted above. I'll try it.

ozzy47
15 Feb 2015, 00:51
I would also repair all the tables via the ACP

ForceHSS
15 Feb 2015, 00:52
You need to check to see what the highest number is then set it one above see if that helps edit my above querys if needed if people keep registering turn it off until you fix it

RichieBoy67
15 Feb 2015, 00:57
Thanks guys, I'll try this.

ozzy47
15 Feb 2015, 00:59
Hopefully it will sort it.

RichieBoy67
15 Feb 2015, 01:05
Nope, I just ran those and ran repair and I tried a couple test reg and got this:



Database error in vBulletin 4.2.2: Invalid SQL: INSERT INTO userfield (userid) VALUES (255); MySQL Error : Duplicate entry '255' for key 'PRIMARY' Error Number : 1062

--------------- Added 14 Feb 2015 at 20:12 ---------------

Ok, I think i found the issue though I am not sue how to fix it just yet.

I see in his user list he only has 126 users now but the database is at 254. I'm not sure if there were some that were imported twice or something like that.

ozzy47
15 Feb 2015, 01:14
Ok in the user table, in the operations tab set the auto_increment to 260, and see what happens.

bridge2heyday
15 Feb 2015, 01:16
Set the auto_increment for userfield the same as user table ..

ozzy47
15 Feb 2015, 01:19
There is no auto_increment value on the userfield table.

bridge2heyday
15 Feb 2015, 01:19
Sorry , userfield doesn't have AUTO_INCREMENT , I guess you deleted some records from user table and didn't delete them from userfield .. check the records there

RichieBoy67
15 Feb 2015, 01:20
Ok guys, getting a little confused with this.

I deleted my test users so right now the latest user is 251. What should I set the auto increment to?

bridge2heyday
15 Feb 2015, 01:20
There is no auto_increment value on the userfield table.

Yes , got it late :)

RichieBoy67
15 Feb 2015, 01:20
Sorry , userfield doesn't have AUTO_INCREMENT , I guess you deleted some records from user table and didn't delete them from userfield .. check the records there

I did not delete any user records directly from the database.

bridge2heyday
15 Feb 2015, 01:22
Ok guys, getting a little confused with this.

I deleted my test users so right now the latest user is 251. What should I set the auto increment to?
your problem is with userfield table .. what is the last userid there .. is there records in userfield table that doen't exist or user table ?

ozzy47
15 Feb 2015, 01:22
Ok guys, getting a little confused with this.

I deleted my test users so right now the latest user is 251. What should I set the auto increment to?

What is the last userid in the userfield table?

--------------- Added 1423963370 at 1423963370 ---------------

Ok, to many cooks, I am outta here.

RichieBoy67
15 Feb 2015, 01:22
Interesting, userfield goes up to 367

--------------- Added 1423963438 at 1423963438 ---------------

What is the last userid in the userfield table?

--------------- Added 14 Feb 2015 at 20:22 ---------------

Ok, to many cooks, I am outta here.

Thanks so much for the help chef. :)

--------------- Added 1423963521 at 1423963521 ---------------

So I take it i just delete those userfield entries?

bridge2heyday
15 Feb 2015, 01:32
Delete them or set AUTO_INCREMENT for user table greater than the last id in userfield table .. and you should check for the reason of this issue .

RichieBoy67
15 Feb 2015, 01:35
Ok, that worked.

Thanks guys.

I am still confused how it happened but something went wrong with the Impex import I believe. I initially imported the site months ago to rebuilt the entire site and I just recently reimported the data by running the delete option to delete the initial import. I guess it did not delete so well.

I am not sure why I was so focused on the user table and did not even think to look at the userfield table. :) That is what you guys are here for I guess. lol

Thank you so much!