Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

PDOStatement===FALSE: help?

LincLinc Detroit Admin
edited December 2009 in Vanilla 2.0 - 2.8
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?

Comments

  • 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
  • LincLinc Detroit Admin
    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: }
    </
  • LincLinc Detroit Admin
    edited December 2009
    Even better. When I run the query manually, it returns the correct data set, which begins like this (UserID | RoleID):
    1 16 4 32 5 16 6 8 7 16 8 8
    The imported data set begins like this:
    0 2 1 16 6 8 8 8 9 8 10 8Which 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.
  • @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?
  • LincLinc Detroit Admin
    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.
  • LincLinc Detroit Admin
    edited 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.
  • No worries, glad you got it running in the end!
Sign In or Register to comment.