SQL ERROR W DEBUG

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
-=DARKMAN=-
Registered User
Posts: 13
Joined: Fri Dec 27, 2002 3:00 pm
Location: Croatia

SQL ERROR W DEBUG

Post by -=DARKMAN=- »

I'm having this error message when trying to register new user:

Code: Select all

General Error 
  
Could not insert data into groups table

DEBUG MODE

SQL Error : 1062 Duplicate entry '8388607' for key 1

INSERT INTO mastergroups (group_name, group_description, group_single_user, group_moderator) VALUES ('', 'Personal User', 1, 0)

Line : 524
File : C:\wwwroot\phpBB2\includes\usercp_register.php
I cannot solve this, but here is some more details:
in phpMyAdmin I have

Code: Select all

table mastergroups running on localhost
   Row Statistic : Statements Value 
Format dynamic  
Rows 150  
Row length ø 24  
Row size  ø 58 Bytes  
Next Autoindex 8,388,607 
And last 2 rows inside has

Code: Select all

    190 1   Personal User 0 1 
8388607 1   Personal User 0 1
Now it seams prety ovious that next number should be 191, but it's not!
And it doesent go up by 1 on next registration? why?

please also note that auto_increment is set on table..

Code: Select all

group_id  mediumint(8)   No    auto_increment  Change  Drop  Primary  Index  Unique  Fulltext 
And also that it has nothing to do with php files.. I tested it on CLEAN unpacked latest version of phpBB but with my current database!

greetz from neigh. Croatia ;)
HELP :(
User avatar
Techie-Micheal
Security Consultant
Posts: 19511
Joined: Sun Oct 14, 2001 12:11 am
Location: In your servers

Post by Techie-Micheal »

I'm guessing you converted. What's more, I'm guessing you converted from UBB. Is the swami correct?
Proven Offensive Security Expertise. OSCP - GXPN
-=DARKMAN=-
Registered User
Posts: 13
Joined: Fri Dec 27, 2002 3:00 pm
Location: Croatia

Post by -=DARKMAN=- »

yes.. how did you guesed? 8O

Amm.. it worked fine for about year.. now started to fuss me =0)
User avatar
Techie-Micheal
Security Consultant
Posts: 19511
Joined: Sun Oct 14, 2001 12:11 am
Location: In your servers

Post by Techie-Micheal »

That's why they call me the Techie. :D

Actually, people have had this problem before. http://www.phpbb.com/phpBB/search.php is a good place to start. :P I really hate to say this but I can't help you with conversions. There is a fix posted in this forum (aka, the Support forum in case this gets moved)
Proven Offensive Security Expertise. OSCP - GXPN
User avatar
gussie
Registered User
Posts: 92
Joined: Wed Nov 27, 2002 1:26 am

I hope these help you

Post by gussie »

-=DARKMAN=-
Registered User
Posts: 13
Joined: Fri Dec 27, 2002 3:00 pm
Location: Croatia

Post by -=DARKMAN=- »

I've fixed error by dumping these two tables and edited them manualy.. I found that '8388607' part was in middle of users_group table and I was actualy expecting it to be the last..

After user registered with that goup_id (?) all next users had group_id of null so I had many problems with updating info of user or deleting user.

I've fixed other users with this script i found somewhere inside this forum(someone may find it usefull)

Code: Select all

<?php 
//*****  check users and user groups ****// 

define('IN_PHPBB', true); 
$phpbb_root_path = './'; 
include($phpbb_root_path . 'extension.inc'); 
include($phpbb_root_path . 'common.'.$phpEx); 
include($phpbb_root_path . 'includes/functions_search.'.$phpEx); 

// Start session management 
$userdata = session_pagestart($user_ip, PAGE_SEARCH); 
init_userprefs($userdata); 
// End session management 

$sql = "SELECT user_id, username 
    FROM " . USERS_TABLE ." 
    WHERE user_id > 0"; 
if ( !($result = $db->sql_query($sql)) ) 
{ 
    message_die(GENERAL_ERROR, 'Could not obtain user list', '', __LINE__, __FILE__, $sql); 
} 

$liste =''; 
while ( $row = $db->sql_fetchrow($result) ) 
{ 
   $username = $row['username']; 
   $user_id = $row['user_id']; 
   $usergroup = ''; 
    
   $sql1 = "SELECT ug.group_id 
          FROM " . USER_GROUP_TABLE ." ug, ". GROUPS_TABLE. " g  
          WHERE ug.user_id = $user_id 
            AND ug.group_id = g.group_id 
            AND g.group_single_user  = 1 
            "; 
              
   if ( ($result1 = $db->sql_query($sql1)) ) 
   { 
       $row1 = $db->sql_fetchrow($result1); 
          $usergroup =( ( $row1['group_id'] != '' ) ? $row1['group_id'] : 'User has no user group'.$row1 ); 
          
   } 

          if (!($row1['group_id'] != '')) 
          { 
              
         $sql2 = "SELECT MAX(group_id) AS total 
            FROM " . GROUPS_TABLE; 
         if ( !($result2 = $db->sql_query($sql2)) ) 
         { 
            message_die(GENERAL_ERROR, 'Could not obtain next group_id information', '', __LINE__, __FILE__, $sq2l); 
         } 

         if ( !($row2 = $db->sql_fetchrow($result2)) ) 
         { 
            message_die(GENERAL_ERROR, 'Could not obtain next group_id information', '', __LINE__, __FILE__, $sql2); 
         } 
         $group_id = $row2['total'] + 1; 

$sql3 = "INSERT INTO " . GROUPS_TABLE . " (group_id, group_name, group_description, group_single_user, group_moderator) 
            VALUES ($group_id, '', 'Personal User', 1, 0)"; 
         if ( !($result3 = $db->sql_query($sql3, BEGIN_TRANSACTION)) ) 
         { 
            message_die(GENERAL_ERROR, 'Could not insert data into groups table', '', __LINE__, __FILE__, $sql3); 
         } 

         $sql4 = "INSERT INTO " . USER_GROUP_TABLE . " (user_id, group_id, user_pending) 
            VALUES ($user_id, $group_id, 0)"; 
         if( !($result4 = $db->sql_query($sql4, END_TRANSACTION)) ) 
         { 
            message_die(GENERAL_ERROR, 'Could not insert data into user_group table', '', __LINE__, __FILE__, $sql4); 
         } 

              
             $usergroup = $usergroup.', adding user group '.$group_id; 
          } 


   $liste .= ( ( $liste != '' ) ? '<br> ' : '' ) . $username.' <b>'.$usergroup.'</b>'; 
} 

message_die(GENERAL_MESSAGE,'Users:<br>'.$liste); 

?>
Instructions:
Copy and paste this code to fix.php , put it inside root folder of your phpBB and execute it!
Locked

Return to “2.0.x Support Forum”