Data Base error when creating new user!!
 

Not sure what is going on. I get this message after creating a user or when a user is registering. However, the registration actually goes through if you check users in admincp. How can I fix this?

***
Database error in vBulletin 4.2.1:

Invalid SQL:
INSERT INTO userfield
(field1, field2, field3, field4, userid)
VALUES
('', '', '', '', 138);

MySQL Error : Duplicate entry '138' for key 1
Error Number : 1062
Request Date : Sunday, September 22nd 2013 @ 07:48:45 PM
Error Date : Sunday, September 22nd 2013 @ 07:48:46 PM
Script : XXXXXXXXXXXXXXXX/user.php?do=update
Referrer : XXXXXXXXXXXXXXXXXXXXX/user.php?do=add
IP Address : XXXXXXXXXXXXXXXXXX
Username : XXXXXXXXXXXXXXXX
Classname : vB_Database
MySQL Version : 5.0.96-log
***

Do a repair on the user table.

Its most likely a hack causing it to create that issue tho.

No it started before I added any mods. I did merge over from phpbb3. I have tried to repair the tables and it comes back saying its fine. Now, it seems I am getting this error (looks like it happened after I created an admin account on the back end_ - never saw before...

Warning: Invalid argument supplied for foreach() in [path]/login.php(119) : eval()'d code on line 4

When I reload the page it goes away...

What php version are you running?

PHP Version: 5.2.17

Here are some additional errors logged to my email:

Database error in vBulletin 4.2.1:

Invalid SQL:
INSERT INTO userfield
(userid)
VALUES
(136);

MySQL Error : Duplicate entry '136' for key 1
Error Number : 1062
Request Date : Tuesday, September 10th 2013 @ 09:12:46 PM
Error Date : Tuesday, September 10th 2013 @ 09:12:46 PM
Script : XXXXXXXXXXXXXXXXX/register.php?do=addmember
Referrer : XXXXXXXXXXXXXXXXXXX/register.php
IP Address : XXXXXXXXXXXX
Username : XXXXXXXXXX
Classname : vB_Database
MySQL Version :


Database error in vBulletin 4.2.1:

Invalid SQL:
INSERT INTO userfield
(userid)
VALUES
(135);

MySQL Error : Duplicate entry '135' for key 1
Error Number : 1062
Request Date : Tuesday, September 10th 2013 @ 08:29:22 PM
Error Date : Tuesday, September 10th 2013 @ 08:29:22 PM
Script : XXXXXXXXXXXXXXXX/register.php?do=addmember
Referrer : XXXXXXXXXXXXXXXXXXXXX/register.php
IP Address : XXXXXXXXXXXXX
Username : XXXXXXXXXXXXXXX
Classname : vB_Database
MySQL Version :

Database error in vBulletin 4.2.1:

Invalid SQL:
INSERT INTO userfield
(userid)
VALUES
(134);

MySQL Error : Duplicate entry '134' for key 1
Error Number : 1062
Request Date : Tuesday, September 10th 2013 @ 06:35:31 PM
Error Date : Tuesday, September 10th 2013 @ 06:35:31 PM
Script : XXXXXXXXXXXXXXX/register.php?do=addmember
Referrer : XXXXXXXXXXXXXXXXXXX/register.php
IP Address : XXXXXXXXXXXXXXX
Username : XXXXXXXXXXXXXX
Classname : vB_Database
MySQL Version :

So you are not running this mod? https://vborg.vbsupport.ru/showthread.php?t=296383

Warning: Invalid argument supplied for foreach() in [path]/login.php(119) : eval()'d code on line 4

Ok I fixed this part, it was the firewall mod, forgot to put in an ip address...

However, the database error continues, and started from the very beginning... help...

--------------- Added [DATE]1379989223[/DATE] at [TIME]1379989223[/TIME] ---------------

Yea, fixed the warning. The big thing is my sql error... thx

Maybe this will help you, http://www.vbulletin.com/docs/html/mysql_duplicatekey

Ok, so I just copy that list and run them in sql? Should I back up my db before doing this? Thx

Yeah I would defiantly do a backup before running those, just to be on the safe side.

Ok I ran it and got this error:

Error

SQL query:

ALTER TABLE `picture` CHANGE `pictureid` `pictureid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ;

MySQL said: Documentation
#1146 - Table 'XXXXXXX.picture' doesn't exist

--------------- Added [DATE]1379990377[/DATE] at [TIME]1379990377[/TIME] ---------------

Do I need to rerun the commands or did they all execute except for this one, and what does that error mean?

--------------- Added [DATE]1379990804[/DATE] at [TIME]1379990804[/TIME] ---------------

I ran the diagnostics and got this (I have uploaded a couple of mods, the spamomatic and admin firewall so I'm not sure if the changes prompt these "errors"):


index.php File does not contain expected contents
phpinfo.php File not recognized as part of vBulletin
robots.txt File not recognized as part of vBulletin
server.php File not recognized as part of vBulletin
tracker.php File not recognized as part of vBulletin
webformmailer.php File not recognized as part of vBulletin
cpnav_impex.xml File not recognized as part of vBulletin

Ignore it, I don't believe that exists anymore, it probably did at the time of the writing of that document.

Did you run the commands 1 at a time?

No, all together. Is that a problem?

No, but it may have stalled on the one that errored out, try running them again without the,

Ok, I will post the ones that pass and fail

PASS:

ALTER TABLE `adminhelp` CHANGE `adminhelpid` `adminhelpid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `adminlog` CHANGE `adminlogid` `adminlogid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `adminmessage` CHANGE `adminmessageid` `adminmessageid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `announcement` CHANGE `announcementid` `announcementid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `attachment` CHANGE `attachmentid` `attachmentid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `attachmentpermission` CHANGE `attachmentpermissionid` `attachmentpermissionid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `avatar` CHANGE `avatarid` `avatarid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `bbcode` CHANGE `bbcodeid` `bbcodeid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `calendar` CHANGE `calendarid` `calendarid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `calendarcustomfield` CHANGE `calendarcustomfieldid` `calendarcustomfieldid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `calendarmoderator` CHANGE `calendarmoderatorid` `calendarmoderatorid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `calendarpermission` CHANGE `calendarpermissionid` `calendarpermissionid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `cron` CHANGE `cronid` `cronid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `cronlog` CHANGE `cronlogid` `cronlogid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `event` CHANGE `eventid` `eventid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `forum` CHANGE `forumid` `forumid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `forumpermission` CHANGE `forumpermissionid` `forumpermissionid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `groupmessage` CHANGE `gmid` `gmid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `holiday` CHANGE `holidayid` `holidayid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `icon` CHANGE `iconid` `iconid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `infraction` CHANGE `infractionid` `infractionid` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `infractionban` CHANGE `infractionbanid` `infractionbanid` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `infractiongroup` CHANGE `infractiongroupid` `infractiongroupid` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `infractionlevel` CHANGE `infractionlevelid` `infractionlevelid` INT ( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `imagecategory` CHANGE `imagecategoryid` `imagecategoryid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `language` CHANGE `languageid` `languageid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `mailqueue` CHANGE `mailqueueid` `mailqueueid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `moderator` CHANGE `moderatorid` `moderatorid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `moderatorlog` CHANGE `moderatorlogid` `moderatorlogid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `paymentapi` CHANGE `paymentapiid` `paymentapiid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `paymentinfo` CHANGE `paymentinfoid` `paymentinfoid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `paymenttransaction` CHANGE `paymenttransactionid` `paymenttransactionid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `phrase` CHANGE `phraseid` `phraseid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `plugin` CHANGE `pluginid` `pluginid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;

--------------- Added [DATE]1379992409[/DATE] at [TIME]1379992409[/TIME] ---------------

FAIL:

ALTER TABLE `picture` CHANGE `pictureid` `pictureid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `search` CHANGE `searchid` `searchid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `word` CHANGE `wordid` `wordid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;

PASS:

ALTER TABLE `picturecomment` CHANGE `commentid` `commentid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pm` CHANGE `pmid` `pmid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pmtext` CHANGE `pmtextid` `pmtextid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `poll` CHANGE `pollid` `pollid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `pollvote` CHANGE `pollvoteid` `pollvoteid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `post` CHANGE `postid` `postid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `postedithistory` CHANGE `postedithistoryid` `postedithistoryid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `productcode` CHANGE `productcodeid` `productcodeid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `productdependency` CHANGE `productdependencyid` `productdependencyid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `profilefield` CHANGE `profilefieldid` `profilefieldid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `ranks` CHANGE `rankid` `rankid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `reminder` CHANGE `reminderid` `reminderid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `reputation` CHANGE `reputationid` `reputationid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `reputationlevel` CHANGE `reputationlevelid` `reputationlevelid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `rssfeed` CHANGE `rssfeedid` `rssfeedid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `smilie` CHANGE `smilieid` `smilieid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `style` CHANGE `styleid` `styleid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `subscribeevent` CHANGE `subscribeeventid` `subscribeeventid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `subscribeforum` CHANGE `subscribeforumid` `subscribeforumid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `subscribethread` CHANGE `subscribethreadid` `subscribethreadid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `subscription` CHANGE `subscriptionid` `subscriptionid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `subscriptionlog` CHANGE `subscriptionlogid` `subscriptionlogid` MEDIUMINT( 8 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `subscriptionpermission` CHANGE `subscriptionpermissionid` `subscriptionpermissionid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `template` CHANGE `templateid` `templateid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `templatehistory` CHANGE `templatehistoryid` `templatehistoryid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `thread` CHANGE `threadid` `threadid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `threadrate` CHANGE `threadrateid` `threadrateid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `upgradelog` CHANGE `upgradelogid` `upgradelogid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `user` CHANGE `userid` `userid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `useractivation` CHANGE `useractivationid` `useractivationid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `userchangelog` CHANGE `changeid` `changeid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `usergroup` CHANGE `usergroupid` `usergroupid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `usergroupleader` CHANGE `usergroupleaderid` `usergroupleaderid` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `usergrouprequest` CHANGE `usergrouprequestid` `usergrouprequestid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `usernote` CHANGE `usernoteid` `usernoteid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `usertitle` CHANGE `usertitleid` `usertitleid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `userpromotion` CHANGE `userpromotionid` `userpromotionid` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;

Yeah the three that failed don't exist anymore, so now try and add a user, and register and see what happens.

Received this error:

Database error in vBulletin 4.2.1:

Invalid SQL:
INSERT INTO userfield
(field1, field2, field3, field4, userid)
VALUES
('', '', '', '', 139);

MySQL Error : Duplicate entry '139' for key 1
Error Number : 1062
Request Date : Monday, September 23rd 2013 @ 08:17:28 PM
Error Date : Monday, September 23rd 2013 @ 08:17:28 PM
Script : XXXXXXXXXXXXXXXX/user.php?do=update
Referrer : XXXXXXXXXXXXXXX/user.php?do=add
IP Address : XXXXXXXXX
Username : XXXXXXXXXXXXXX
Classname : vB_Database
MySQL Version : 5.0.96-log

--------------- Added [DATE]1379992947[/DATE] at [TIME]1379992947[/TIME] ---------------

These are the fields that I have for table "userfield":

userid temp field1 field2 field3 field4

--------------- Added [DATE]1379993257[/DATE] at [TIME]1379993257[/TIME] ---------------

Checked usefield table:

Table Op Msg_type Msg_text
XXXX.userfield check status OK

--------------- Added [DATE]1379993575[/DATE] at [TIME]1379993575[/TIME] ---------------

OK, I think I have issues and don't know how to fix it... it seems the userid table has MASSIVE duplicates. For example user number 3 is the same user at number 134 and 394 and 524. This is for all users!!! I have probably triple userids for those that are actually registered. How do I fix this???

I don't know, best thing would be to ask at vB.com.

Ok, I have 134 users and 647 entries in the userfield table!!!

Yeah you got some issues there, best bet is to ask at vb.com, they are more involved with this sort of thing.

Well that ain't gonna happen, my account expired. Can't post over there. ANYONE???? Really need help!!!

Your first post doesnt state that you dont have hacks/addons installed.

A simple repair of the table will fix the dupe key issue.

If a repair in phpmyadmin dont work, run a myisamchk via the command line if you have shell access to the server.

But that is normally caused by hacks, its not a vbulletin issue causing that.

Hopefully someone more knowledgeable than me in this sort of thing can give you a hand here.

The ONLY mods I installed are admin firewall and spam-o-matic.

In both the userfield and usertextfield tables I have 647 entries with multiple duplicates in some of the fields that keep repeating.

In the userlist table it is empty.

In the user table I have 134 entries which is what shows in the back end for the amount of users...

The errors were happening BEFORE I installed the mods, I did a merge over from phpbb3 and IMPEX kept crapping out, and VB.com told me to run IMPEX again. Now I don't have support from them, over a month ago... I wonder if the several times I ran impex created DUPLICATES in the tables? How do I fix this?

--------------- Added [DATE]1379995503[/DATE] at [TIME]1379995503[/TIME] ---------------

Good GOD!!! It looks like that is what happened!! Everytime I ran IMPEX it would give errors on importing from phpbb3. Had to keep running the options until it gave no errors.

http://www.vbulletin.com/forum/forum...database-error

OK, so what do I need to do to fix this cleanly?

Basically what I want to attempt to do is rerun the database import - fresh from my phpb3.

Do I need to uninstall the two VB mods I added, then upload my original db (backup) back to the server and run impex again?

I just got done with my temlate (custom) and do NOT want to have to start over on that...

Any help appreciated!! Thanks.

Once you complete the impex import, you should have it remove the session & the temp id's it used.

If your getting errors during the impex process, then certainly DONT rerun the import steps over and over, thats not going to fix the issue.

I would start over fresh & dont use the admincp firewall, simply use a htaccess protection, nothing more needed for it, but neither of those will protect you from a sql injection anyways.

Thanks! Ok, last night I removed the three mod addons I had with vbulletin. I uploaded the old forum (phpbb3) files and database up to the server.

So, at this point, wipe the database through IMPEX and start over the import?

I know I have some large import files on my old forum. A couple were around 55 megs. How do I make sure that those files xfer to VBulletin? Also, I know last time when I was running certain imports like "users" it would show that most xferred over but I had errors. It never told me what the errors were and so I would rerun the import. Maybe someone can help me on this. What do you do when IMPEX shows an error? How do you know what the error is exactly?

Also, since I am bringing in the DB from my old forum software, do I need to upload the "install" folder for VBulletin that was removed after I installed the VBulletin software? Thanks again!

Yes you would start fresh

New Vb DB with a normal install
Fresh copy of the import DB
Then run Impex & stop when it errors, dont run it again.

You would need to post the actual errors if it produces a error, we dont know what they are.

Ok, I don't know where to find the errors for impex. The upload screen (in the past) just showed that 100 users merged and 50, for example, did not, but I don't know why. I uploaded the old db and forum software onto the server. I wonder if I should go back into the old forum and make everyone a registered user and delete the custom ranks I had installed in phpbb3? Then create new ranks in vbulletin and change the users at that point? Thoughts on that? Or will I mess anything up? Also, what are the DEFAULT user settings offered in vbulletin that will hopefully merge with phpb3? Thx

Try enabling debug in the impex config file, my guess its going to be a bad email or blank email for those users.

Thanks. Assuming that that may be the issue, A) how do I fix this, and B) How do I get those users to import without having to redo the import and end up with duplicate entries in the DB??

Also, is there a method in IMPEX to allow very large imports of files? I noticed during the last time I did the merge, it crapped out on a couple of file (pdf) uploads that were between 40-50mb. Thanks again!

The amount of data a script imports or the amount of time a script runs for is based on your servers php settings, some mysql settings can also be changed to hel pas well as limit what the db itself is using, not Impex.

If your on a vps/dedicated server those are easy to change around, if your on a shared server your going to be limited as to what you can change, most hosts allow for a local php.ini in a customers account to change most settings of php for that user alone, they most likely allow php flags in a htaccess as well for making those changes, but your not going to be able to change any mysql settings.

Thanks. Changed the config files in IMPEX, increasing page refresh, which I guess from what others have said takes a load off the server. Anyways, I figured out that the 51 users that show errors may have been ad bots that were previously in my phpbb3 table. Counted the users transferred over and those in the phpbb3 db, minus the adbots, and those comprise the 51 "users" that failed due to no email. It looks like 1 private message failed, when uploading that portion of the process. Not sure why. It looks like some of the smilies may have failed too, once again, not sure why...

Didn't have time, but my next process will be uploading attachments.

Yea, the debug will give you ideas or point you in a better direction, glad you got it resolved, happy vb'ing....

OK, tried loading attachments. It crapped out after the fourth one, something about memory limit. The only errors that seem to be stored in impex was the email error. Not this error or the private message error.

How do I get rather large files to upload? Also, did the first 4 load? I don't see them in attachments section of the admincp, but I don't want duplicates. Thanks!

--------------- Added [DATE]1380245286[/DATE] at [TIME]1380245286[/TIME] ---------------

Ok, I have increased php.ini to 192M and it still craps out. Is there a way to flush the memory while this is uploading files? Or perhaps a way to run IMPEX outside of VBulletin? Maybe that would reduce overhead...

Upload limits for php are set in the php.ini as well, you will need to adjust both of those too.

Id also up the timeout for how long a script can run in php.ini

You cant run impex from outside vb.