?
Solved

Problem related to PHPBB forum

Posted on 2006-04-18
10
Medium Priority
?
2,429 Views
Last Modified: 2013-12-12
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', 'rg@rg.com')

Can anyone help me? Why user_id field is not autoincrementing?
0
Comment
Question by:aynsoft
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 49

Expert Comment

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

-r-
0
 

Author Comment

by:aynsoft
ID: 16484616
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
0
 

Author Comment

by:aynsoft
ID: 16484679
If i recreate table even than after 10 records it automatically change next autoindex to 2,147,483,647
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Accepted Solution

by:
Richard Davis earned 1000 total points
ID: 16484951
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' );";
$db->sql_query($sql);
                        
//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' );";
$db->sql_query($sql);                        


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
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486053
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.
0
 

Author Comment

by:aynsoft
ID: 16486814
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.
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486899
$mid?
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 16486910
Ah. That's adrian_brooks own code.
0
 

Author Comment

by:aynsoft
ID: 16512760
Thanks adrian_brooks
 for helping me to solve the problem.
0
 
LVL 12

Expert Comment

by:Richard Davis
ID: 16512786
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 :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month14 days, 15 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question