PDOStatement===FALSE: help?

Linc · December 2009

In my vBulletin importer, I've got an SQL query that maps old usergroup IDs to their new RoleIDs and inserts the new relationships in GDN_UserRole.

If I truncate the GDN_UserRole table, I can run the query fine in phpmyadmin. If I try to run it through Garden, it throws this error:

FATAL ERROR IN: Gdn_Database.Query();
"Duplicate entry '1-16' for key 1"
insert into GDN_UserRole (UserID, RoleID)
         select u.userid, r.RoleID
         from vb_user u
         inner join GDN_Role r
            on u.usergroupid = r.VbulletinImportID
LOCATION: /storage/websites/forum.icrontic.com/library/database/class.database.php
> 212:       }
> 213: 
> 214:       if ($PDOStatement === FALSE) {
> 215:          trigger_error(ErrorMessage($this->GetPDOErrorMessage($this->Connection()->errorInfo()), $this->ClassName, 'Query', $Sql), E_USER_ERROR);         
> 216:       }

So $PDOStatement === FALSE after the query for some reason. However, if I open up the database, it shows that it has, in fact, put 5,627 rows into the GDN_UserRole table. Running the query manually produces 5,642 records with no error. If I check the results after running it manually, the pair 1-16 only shows up one time.

What am I doing wrong?

benno · December 2009

Can you change your SQL select statement to a SELECT DISTINCT... E.g:

select distinct u.userid, r.RoleID
from vb_user u
inner join GDN_Role r
on u.usergroupid = r.VbulletinImportID

Linc · December 2009

It does precisely the same thing. I'm pretty confident a duplicate record CAN'T exist, so I don't understand why PDO is returning the error.

FATAL ERROR IN: Gdn_Database.Query();
"Duplicate entry '1-16' for key 1"
insert into GDN_UserRole (UserID, RoleID)
         select distinct u.userid, r.RoleID
         from vb_user u
         inner join GDN_Role r
            on u.usergroupid = r.VbulletinImportID
LOCATION: /storage/websites/forum.icrontic.com/library/database/class.database.php
> 210:          $PDOStatement = $this->Connection()->query($Sql);
> 211:       }
> 212: 
> 213:       if ($PDOStatement === FALSE) {
>>> 214:          trigger_error(ErrorMessage($this->GetPDOErrorMessage($this->Connection()->errorInfo()), $this->ClassName, 'Query', $Sql), E_USER_ERROR);
> 215:       }

</

Linc · December 2009

Even better. When I run the query manually, it returns the correct data set, which begins like this (UserID | RoleID):

The imported data set begins like this:

Which is clearly wrong, because there is no user with ID #0, and members 4, 5, and 7 have disappeared. Not only is it throwing an error, it's executing the query with a different result. (Yes, both sets were ordered by UserID)

This smells like a day destroyer.

Going to go put the kettle on and buckle down.

benno · December 2009

@Lincoln - I've setup a test locally here and popullated a dummy table with 96,000 records, did a test run a few times and it went in every time no problem.

<?php if (!defined('APPLICATION')) exit();

class TestController extends GardenController {
   
  public function Index() {
    $Database = Gdn::Database();
    $PDO = $Database->Connection();
    $Construct = $Database->Structure();
    $SQL = $Database->SQL();

    $Database->Query("insert into GDN_UserRole (UserID, RoleID) select userid, roleid from ben_test");
    
    echo "Done.";
    exit;
  }
   
}

A quick Google and a look around StackOverflow suggests that it can be caused by a corrupt table. Have you tried doing a repair on the table or rebuilding your database (SQL dump out -> then recreate)?

Maybe try to SELECT all the data in the GDN_UserRole table, loop through it all and write it a dummy text file in the /cache folder just to make sure something else isnt sticking records in there before your INSERT query runs?

Linc · December 2009

I drop all the Garden tables and reinstall before every import run. Maybe it's the vBulletin user table. I'll check that first, then try killing the import right before that query executes and check the database. This happened after I transitioned the importer from using an imported UserID to keeping the same UserID, so perhaps it's related.

Linc · December 2009

The UserRole table wasn't getting emptied before I tried the import. There WAS a record 1-16.... ME! Thanks for leading me in the right direction, @benno. I was way off-track.

This was also the core of FoxyFred's Vanilla import problem too.

benno · December 2009

No worries, glad you got it running in the end!

PDOStatement===FALSE: help?

Comments