Page 1 of 1

SQL ERROR W DEBUG

Posted: Fri Dec 27, 2002 3:05 pm
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 :(

Posted: Fri Dec 27, 2002 3:07 pm
by Techie-Micheal
I'm guessing you converted. What's more, I'm guessing you converted from UBB. Is the swami correct?

Posted: Fri Dec 27, 2002 3:09 pm
by -=DARKMAN=-
yes.. how did you guesed? 8O

Amm.. it worked fine for about year.. now started to fuss me =0)

Posted: Fri Dec 27, 2002 3:21 pm
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)

I hope these help you

Posted: Sun Dec 29, 2002 3:09 am
by gussie

Posted: Sun Dec 29, 2002 4:49 am
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!