toolkit.php SQL Error when deleting spam

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
REPOM4N
Registered User
Posts: 32
Joined: Wed May 10, 2006 7:42 pm
Location: Montreal

toolkit.php SQL Error when deleting spam

Post by REPOM4N »

I've been getting this error for ever when I use the Toolkit to delete spam.

From what I can see, the error is in the 'group' section of the DB. I really don't know what to do with this.
Error deleting user's group from groups table:
Line: 1477
File: /PHPbb2/toolkit.php
Query: DELETE FROM `phpbb_groups` WHERE `group_id`=
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
This is the code:

Code: Select all

	// ***************************************************************************
	//
	// Eighth sql query deletes the user from the phpbb_groups table
	//
	// ***************************************************************************

	$sql = "DELETE FROM `$phpbb_groups` WHERE `group_id`=".$row['group_id'];

	if( !$result = mysql_query( $sql ) )

	   {

		die( '<font size="4"><b>Error deleting user\'s group from groups table:</b></font><br /><b>Line:</b> '.__LINE__.'<br /><b>File:</b> '.$_SERVER['PHP_SELF']."<br /><b>Query:</b> $sql<br /><b>MySQL Error:</b> ".mysql_error() );

	   }
The 'group' table looks fine but I have many 'group_id' ; 30 records. I don't use the groups. I just did some tests a while back.

I tried changing this line but it didn't work. 'group_id' is not declared like the other fields.

Code: Select all

	// $sql = "DELETE FROM `$phpbb_groups` WHERE `group_id`=".$row['group_id'];
                to
	$sql = "DELETE FROM `$phpbb_groups` WHERE `group_id`=$group_id";
.$row[] is the only difference between the other SQL statements.

Help, very welcome :)
Image
Easy come, Easy go
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: toolkit.php SQL Error when deleting spam

Post by espicom »

There are three main causes for problems related to groups:

Your PHPBB version is prior to 2.0.18, and your site uses PHP version 5. You will get no error message, but moderator privileges will not "stick". The solution is to update to v2.0.23.

Your PHPBB database is originally from a pre-2.0.10 version of PHPBB, and lacks the "single user groups" that are necessary to make permissions work. You will get an error message (usually a 1064 SQL error), where the SQL statement has a blank value where the group_id is supposed to be. The script in Fix group_id being blank usually fixes this.

You deleted a user that was a moderator of one or more groups, without assigning a new moderator first. Those groups will give you an error about an invalid user_id, and you can not edit them or assign privileges until you have a valid moderator assigned. Sometimes, you can do this by using the Admin Control Panel, Group Admin, Managment, and edit the groups, changing the moderator. Otherwise, you need to use a tool like phpmyadmin to directly edit the groups table, giving the field group_moderator the ID of a non-deleted user. (See also Replacing a deleted group moderator using phpmyadmin)
I'm guessing you're running into problem number 2, and need to run the Fix group_id script...
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer
REPOM4N
Registered User
Posts: 32
Joined: Wed May 10, 2006 7:42 pm
Location: Montreal

Re: toolkit.php SQL Error when deleting spam

Post by REPOM4N »

Hi,

Thanks for the great help!

I tryied solution #2

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);

?>
And that gave me a long list of users with numbers.

That's wierd because I'm sure this thing used to work.

My provider could upgrade to PHP5 but than I'd have to switch server. And that is a all new ball game but I'd get those perks:

* Operating System: Microsoft Windows Server 2003
* IIS v.6.0
* PHP 4.4.6 or PHP 5.2.0
* .Net Framework v.1.1
* .Net Framework v.2.0
* MS SQL Server 2005
* VBscript 7.5
* ODBC mysql driver 3.51.04.00
* Coldfusion MX 7.0.2.142559
* Frontpage 2002

I might have some of those.

Oh ya, when I delete from User Admin Management, no problem.
Last edited by REPOM4N on Tue Dec 30, 2008 4:04 am, edited 1 time in total.
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Re: toolkit.php SQL Error when deleting spam

Post by espicom »

If you move to 5.x on PHP, you'll really want to move to phpBB3, so it can take advantage of it. phpBB2 is not supported on PHP5, although it works in most cases... But it definitely won't work on PHP6!
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer
REPOM4N
Registered User
Posts: 32
Joined: Wed May 10, 2006 7:42 pm
Location: Montreal

Re: toolkit.php SQL Error when deleting spam

Post by REPOM4N »

I understand. If I only had that to do, I'd do it for sure. But for now, I'm just trying to keep the forum alive. Well, revive it anyway.

For that, I removed better_captcha and the User validation (email) to make things easier for the newbie’s. So I have to cleanup a lot.

Also, the Toolkit (the one I have anyway) was created pre phpBB3. It should work. Isn't it?

Later, if I see some life coming back, I'll definitely upgrade everything.

Well, I still have solution number 3 to try out. I'll give you some news after checking this out.

Thanks for everything.
User avatar
ric323
Former Team Member
Posts: 22910
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: toolkit.php SQL Error when deleting spam

Post by ric323 »

REPOM4N wrote:Also, the Toolkit (the one I have anyway) was created pre phpBB3. It should work. Isn't it?
The Starfoxtj Admin Toolkit only works on a phpBB2 database. I don't think he has any plans to do a phpBB3 version.
The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions
REPOM4N
Registered User
Posts: 32
Joined: Wed May 10, 2006 7:42 pm
Location: Montreal

Re: toolkit.php SQL Error when deleting spam

Post by REPOM4N »

When I look at the members list with Toolkit, I have an 'Anonymous' Username set to -1
But when I look right into the DB, it's set to 1
Image
Easy come, Easy go
User avatar
ric323
Former Team Member
Posts: 22910
Joined: Tue Feb 06, 2007 12:33 am
Location: Melbourne, Australia
Name: Ric
Contact:

Re: toolkit.php SQL Error when deleting spam

Post by ric323 »

REPOM4N wrote:When I look at the members list with Toolkit, I have an 'Anonymous' Username set to -1
But when I look right into the DB, it's set to 1
The correct value is -1.
DO NOT REMOVE THAT USERNAME! It is a vital part of phpBB (it's used for all guests viewing your board).
The Knowledge Base contains solutions to many common problems!
How to fix "Doesn't have a default value" and "Incorrect string value: xxx for column 'post_text' " errors.
How to do a clean re-install of the latest phpBB3 version.
Problems with permissions? Read phpBB3 Permissions
Locked

Return to “2.0.x Support Forum”