Problem related to PHPBB forum

I add 10 records to phpbb_users table in phpbb forum. 11th record store user_id as '2147483647' instead of autoincrementing it to '11'

Now when next record is added to the phpbb_users table, it shows the following error:

1062 - Duplicate entry '2147483647' for key 1

insert into phpbb_users (username, user_regdate, user_email) values ('rg', '1145418044', '')

Can anyone help me? Why user_id field is not autoincrementing?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you check your database (phpmyadmin?) and see what is stated in "Next auto-increment value" in the table structure page?

aynsoftAuthor Commented:
Next Autoindex is  2,147,483,647
Initially it is 9 but now even if i delete records still next autoindex remains 2,147,483,647
aynsoftAuthor Commented:
If i recreate table even than after 10 records it automatically change next autoindex to 2,147,483,647
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Richard DavisSenior Web DeveloperCommented:
Just a note here for you. PHPBB does NOT use auto-incrementing on it's primary key for the user table nor much else for that matter., so it's extremely easy to cross records and create duplicate key errors. I'm not sure why they would complicate things so much by not using that feature, but that is the source of your problem. So, in response to Roonaan's comment, checking the next auto-increment value isn't going to help much as there is none to check. Good suggestion though.

PHPBB manages all their table's primary keys internally and not through the database's facilities.
As for your problem, I'm getting the impression that you are adding these user records manually in the table, is this correct?

If so, this will never work as there are, I believe 2 or 3 other tables that get updated/inserted into during the addition of a new user. I actually wrote a routine that automatically adds a user to my PHPBB forum after successfully registering on my main site so that they don't have to register for the forum also. This involved the following inserts;

//phpbb doesn't use auto increment on its user table so we will assign the same ID
//used for the site's members table in gg_tblMembers
$sql = "INSERT INTO `ggbb_users` (user_id,user_active,username,user_password,user_session_time,user_session_page,user_lastvisit,user_regdate,user_level,user_posts,user_timezone,user_style,user_lang,user_dateformat,user_new_privmsg,user_unread_privmsg,user_last_privmsg,user_emailtime,user_viewemail,user_attachsig,user_allowhtml,user_allowbbcode,user_allowsmile,user_allowavatar,user_allow_pm,user_allow_viewonline,user_notify,user_notify_pm,user_popup_pm,user_rank,user_avatar,user_avatar_type,user_email,user_icq,user_website,user_from,user_sig,user_sig_bbcode_uid,user_aim,user_yim,user_msnm,user_occ,user_interests,user_actkey,user_newpasswd) VALUES ('" . $mid . "','1','". $_POST['login_id'] ."','". md5($_POST['login_pw']) ."','','','','" . time() . "','','','','','','','','','','','1','','1','1','1','1','1','1','','','','','1','','" . $_POST['email_addr_1'] . "','','','" . $loc . "','','','','','','','','','');";
$db->sql_query($sql);   // <-- my own db class caller to execute the query
//setup msgboard respective user group record
$sql = "INSERT INTO `ggbb_user_group` ( group_id, user_id, user_pending ) VALUES ( '" . $mid . "','" . $mid . "','0' );";
//create respective msgboard group record
$sql = "INSERT INTO `ggbb_groups` ( group_id, group_type, group_name, group_description, group_moderator, group_single_user ) VALUES ( '" . $mid . "','1','','Personal User','0','1' );";

So, as you can see, adding a user into just the user table is not going to be enough to get you rolling.

Hope this helped :)

Good Luck

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard QuadlingSenior Software DeveloperCommented:
phpBB gets the next user id by ...

$sql = "SELECT MAX(user_id) AS total FROM " . USERS_TABLE;
if ( !($result = $db->sql_query($sql)) )
      message_die(GENERAL_ERROR, 'Could not obtain next user_id information', '', __LINE__, __FILE__, $sql);

if ( !($row = $db->sql_fetchrow($result)) )
      message_die(GENERAL_ERROR, 'Could not obtain next user_id information', '', __LINE__, __FILE__, $sql);
$user_id = $row['total'] + 1;

If you have PHPMyAdmin installed can you try this query on the phbb_users table?

select max(user_id) as total from phpbb_users

If it returns a big figure, then this is odd. You may need to run some sort of diagnostic or reindexing tool on the db.
aynsoftAuthor Commented:
Definately something to be done as total(user_id ) is too high.

what is $mid? Is its value incremented by you at creation of new user?
Do you mean i have to enter user_id value by myself instead of using autoincrement feature. I am trying to do that.
Richard QuadlingSenior Software DeveloperCommented:
Richard QuadlingSenior Software DeveloperCommented:
Ah. That's adrian_brooks own code.
aynsoftAuthor Commented:
Thanks adrian_brooks
 for helping me to solve the problem.
Richard DavisSenior Web DeveloperCommented:
Hey, glad to be of a help for you.

As for the previous things you asked, yeah...what I pasted above is actually my own code that I wrote to add a user to my forum tables after they activate their account on my site.

$mid is my own variable which stands for 'Member ID'

What I basically had done, was let them signup in my site's registration, which is auto-incremented in my tables, I then did a query when they validated their email, which grabbed their new id record value and assigned that to $mid, which as you can see, now becomes the exact same record in the forum. This now makes it really streamlined when it comes to deleting accounts as all I have to do then is to delete any records where the ID values all equal the same thing.

Glad I could help you figure that out even though I should have explained all this then, instead of now. Sorry about that, guys.

Happy Coding :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.