Registration issue

[CaptainAwesome]

When someone goes through the registration process, after step 2 of 2 where you pick your user name etc and click "Complete Registration" i get the following message:

Database error
The Forums database has encountered a problem.
Please try the following:
Load the page again by clicking the Refresh button in your web browser.
Open the <site> home page, then try to open another page.
Click the Back button to try another link.
The <site> forum technical staff have been notified of the error, though you may contact them if the problem persists.

We apologise for any inconvenience.

----------------------------------------------------------------

And the User ID's are actually created. The thing is with the message above, most people will think the process failed plus it looks unprofessional.

Any idea how to fix this? I don't know if the anti-spammer measures posted here in this forum are behind the problem.

[WEBDosser]

need to error in full.

[ozzy47]

Yes the full error from your server error logs would help.
What version of vB4 are you running?
What PHP version are you running?

But we can see if this is from a mod, by doing the following.

Open your includes/config.php file and below<?php add this line:

define('DISABLE_HOOKS', true);

So it looks like this:
<?php
define('DISABLE_HOOKS', true);
/*================================================= =====================*\
|| ################################################## ################## ||
|| # vBulletin 4.2.2

Then try and register and see what happens.

[CaptainAwesome]

I haven't made the changes you mentioned ozzy as i thought i would post the error first. I'm a total newbie at this stuff and want to avoid creating more problems.

I went and set up the error reporting thing (told you i was a newbie!) and recreated the problem. This is what i was emailed. I removed my IP and site address, but everything else is everything i got sent to me:

Database error in vBulletin 4.2.2:

Invalid SQL:
INSERT INTO userfield
(field7, field8, userid)
VALUES
('Yes', 'Yes', 50966);

MySQL Error : Duplicate entry '50966' for key 'PRIMARY'
Error Number : 1062
Request Date : Saturday, August 23rd 2014 @ 05:56:20 PM
Error Date : Saturday, August 23rd 2014 @ 05:56:21 PM
Script : http://<site>/forum/newsignup.php?do=addmember
Referrer : http://<site>/forum/newsignup.php?do=checkdate
IP Address : <IP address>
Username : test4
Classname : vB_Database
MySQL Version :

[ozzy47]

That error is most commonly caused by plugins or addons that incorrectly set auto increment fields within vBulletin.

I would suggest reading this, https://www.vbulletin.com/docs/html/main/mysql_duplicatekey

Specifically the query starting as:
ALTER TABLE `user` CHANGE `userid`

[CaptainAwesome]

I can't seem to run any queries. I adjusted the config file as follows:

// ****** USERS WITH QUERY RUNNING PERMISSIONS ******
// The users specified here will be allowed to run queries from the control panel.
// See the above entries for more information on the format.
// Please note that the ability to run queries is quite powerful. You may wish
// to remove all user IDs from this list for security reasons.
$config['SpecialUsers']['canrunqueries'] = '<myuserid>';

But every time i go to maintenance>Execute SQL Query i get this:

You are not authorized to execute SQL queries

[Lynne]

If your userid is 1, then that line would read as:

$config['SpecialUsers']['canrunqueries'] = '1';

Is that what you have?

[CaptainAwesome]

If your userid is 1, then that line would read as:

$config['SpecialUsers']['canrunqueries'] = '1';

Is that what you have?

Thanks, that worked. I had the ID name there.

[CaptainAwesome]

That error is most commonly caused by plugins or addons that incorrectly set auto increment fields within vBulletin.

I would suggest reading this, https://www.vbulletin.com/docs/html/main/mysql_duplicatekey

Specifically the query starting as:
ALTER TABLE `user` CHANGE `userid`

Am i suppose to run them all, because the one line you mentioned didn't work. I copied and pasted the line as follows:

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

And when i ran it, i got the screen attached.

[CaptainAwesome]

ok, i ran all of them and got the following message (screenshot attached).

Any idea how to fix the problem?

[Lynne]

Try it without the apostrophes.

[CaptainAwesome]

Try it without the apostrophes.

No difference.

--------------- Added 1408926117 at 1408926117 ---------------

I think i have identified the problem. I just need some advice on how to clean up the database to avoid screwing things up.

I went into the userid table and it shows a total of 80953 results. It may ahve something to do with when i done a partial import and then an additional import. The error log shows:

MySQL Error : Duplicate entry '50966' for key 'PRIMARY'

I can't select all the rows in one go, and don't want to have to select 30,000 results one by one.

So what would be the best way to fix this? I'm thinking export it, delete it, and then import it. Being inexperienced, i thought i would check with you more experienced people and ask for some guidance on how to best do this. The number of users is very low so re-creating the database shouldn't be a big deal.

[Lynne]

Check the auto_increment for the user table and make sure it is set to a number greater than any userid in the table. Then make sure the userfield table doesn't have a row with a userid greater than the auto_increment either.

[CaptainAwesome]

How would i do that? Google didn't throw up anything useful.

[Lynne]

I use phpMyAdmin to access my database and check things like the auto_increment.

[CaptainAwesome]

After some stumbling around and googling, i found where i edited the auto_increment and it all works fine now.

Thanks for your help.