Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-214

schema-tool --complete-update throw PDOException on DROP INDEX

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Critical Critical
    • Resolution: Fixed
    • Affects Version/s: 2.0-ALPHA4
    • Fix Version/s: 2.0-BETA1
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None

      Description

      Here is the stack trace

      SchemaTool: exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1025 Error on rename of './fna_common/#sql-665_60b' to './fna_common/fna_owned_insurance' (errno: 150)' in /home/eric/Zend/workspaces/DefaultWorkspace7/fna/lib/Doctrine/DBAL/Connection.php:571
      Stack trace:
      #0 /home/eric/Zend/workspaces/DefaultWorkspace7/fna/lib/Doctrine/DBAL/Connection.php(571): PDO->query('DROP INDEX fna_...')
      #1 /home/eric/Zend/workspaces/DefaultWorkspace7/fna/lib/Doctrine/ORM/Tools/SchemaTool.php(537): Doctrine\DBAL\Connection->execute('DROP INDEX fna_...')
      #2 /home/eric/Zend/workspaces/DefaultWorkspace7/fna/lib/Doctrine/ORM/Tools/Cli/Tasks/SchemaToolTask.php(217): Doctrine\ORM\Tools\SchemaTool->updateSchema(Array, false)
      #3 /home/eric/Zend/workspaces/DefaultWorkspace7/fna/lib/Doctrine/ORM/Tools/Cli/CliController.php(190): Doctrine\ORM\Tools\Cli\Tasks\SchemaToolTask->run()
      #4 /home/eric/Zend/workspaces/DefaultWorkspace7/fna/application/scripts/doctrine/doctrine.php(19): Doctrine\ORM\Tools\Cli\CliController->run(Array)
      #5 /home/eric/Zend/workspaces/DefaultWorkspace7/fna/application/scripts/doctrine/doctrine(4): include('/home/eric/Zend...')
      #6 {main}
      
      #0 /home/eric/Zend/workspaces/DefaultWorkspace7/fna/lib/Doctrine/ORM/Tools/Cli/CliController.php(190): Doctrine\ORM\Tools\Cli\Tasks\SchemaToolTask->run()
      #1 /home/eric/Zend/workspaces/DefaultWorkspace7/fna/application/scripts/doctrine/doctrine.php(19): Doctrine\ORM\Tools\Cli\CliController->run(Array)
      #2 /home/eric/Zend/workspaces/DefaultWorkspace7/fna/application/scripts/doctrine/doctrine(4): include('/home/eric/Zend...')
      #3 {main}
      

      I think the problem is in the Schema Comparator. It generate a DROP for a foreign key index.

      If you compare the index definition from database and from metadata you will see that the foreign key index is present only in the definition obtained from database.

      Database :

      Array
      (
          [primary] => Doctrine\DBAL\Schema\Index Object
              (
                  [_columns:protected] => Array
                      (
                          [0] => id
                      )
      
                  [_isUnique:protected] => 1
                  [_isPrimary:protected] => 1
                  [_name:protected] => PRIMARY
              )
      
          [fna_client_id] => Doctrine\DBAL\Schema\Index Object
              (
                  [_columns:protected] => Array
                      (
                          [0] => fna_client_id
                      )
      
                  [_isUnique:protected] =>
                  [_isPrimary:protected] =>
                  [_name:protected] => fna_client_id
              )
      
      )
      

      From metadata :

      Array
      (
          [primary] => Doctrine\DBAL\Schema\Index Object
              (
                  [_columns:protected] => Array
                      (
                          [0] => id
                      )
      
                  [_isUnique:protected] => 1
                  [_isPrimary:protected] => 1
                  [_name:protected] => primary
              )
      
      )
      
      1. both_schema_print_r
        1.35 MB
        Eric Durand-Tremblay
      2. DDC214Test.php
        1 kB
        Eric Durand-Tremblay
      3. from_schema
        305 kB
        Eric Durand-Tremblay
      4. to_schema
        128 kB
        Eric Durand-Tremblay

        Activity

        Hide
        Eric Durand-Tremblay added a comment -

        This is VERY VERY VERY Great!!!

        I experienced serious perfomance issue with the query (getListTableForeignKeysSql) :

          $sql = "SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, ".
                       "k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ ".
                       "FROM information_schema.key_column_usage k /*!50116 ".
                       "INNER JOIN information_schema.referential_constraints c ON k.`CONSTRAINT_NAME` = c.constraint_name AND ".
                       "  c.constraint_name = k.constraint_name AND ".
                       "  c.table_name = k.table_name */ WHERE k.table_name = '$table'";
        

        I think this is a bug in mysql server (5.1.37-1ubuntu5).

        I changed the query a little bit and it work great :

          $sql = "SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, ".
                       "k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ ".
                       "FROM information_schema.key_column_usage k /*!50116 ".
                       "INNER JOIN information_schema.referential_constraints c ON k.`CONSTRAINT_NAME` = c.constraint_name AND ".
                       "  c.constraint_name = k.constraint_name AND ".
                       "  c.table_name = '$table' */ WHERE k.table_name = '$table'";
        
        Show
        Eric Durand-Tremblay added a comment - This is VERY VERY VERY Great!!! I experienced serious perfomance issue with the query (getListTableForeignKeysSql) : $sql = "SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, " . "k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ " . "FROM information_schema.key_column_usage k /*!50116 " . "INNER JOIN information_schema.referential_constraints c ON k.`CONSTRAINT_NAME` = c.constraint_name AND " . " c.constraint_name = k.constraint_name AND " . " c.table_name = k.table_name */ WHERE k.table_name = '$table'" ; I think this is a bug in mysql server (5.1.37-1ubuntu5). I changed the query a little bit and it work great : $sql = "SELECT DISTINCT k.`CONSTRAINT_NAME`, k.`COLUMN_NAME`, k.`REFERENCED_TABLE_NAME`, " . "k.`REFERENCED_COLUMN_NAME` /*!50116 , c.update_rule, c.delete_rule */ " . "FROM information_schema.key_column_usage k /*!50116 " . "INNER JOIN information_schema.referential_constraints c ON k.`CONSTRAINT_NAME` = c.constraint_name AND " . " c.constraint_name = k.constraint_name AND " . " c.table_name = '$table' */ WHERE k.table_name = '$table'" ;
        Hide
        Benjamin Eberlei added a comment -

        Patched, and speedy again! Thank you very much.

        Show
        Benjamin Eberlei added a comment - Patched, and speedy again! Thank you very much.
        Hide
        Eric Durand-Tremblay added a comment -

        That was fast!

        Show
        Eric Durand-Tremblay added a comment - That was fast!
        Hide
        Jonathan H. Wage added a comment -

        The test for this issue is failing for me under mysql.

        
        There was 1 failure:
        
        1) Doctrine\Tests\ORM\Functional\Ticket\DDC214Test::testCompanyModel
        Failed asserting that <integer:8> matches expected <integer:0>.
        
        /Users/jwage/Sites/doctrine2-orm/tests/Doctrine/Tests/ORM/Functional/Ticket/DDC214Test.php:77
        /Users/jwage/Sites/doctrine2-orm/tests/Doctrine/Tests/ORM/Functional/Ticket/DDC214Test.php:54
        
        FAILURES!
        Tests: 736, Assertions: 2260, Failures: 1, Skipped: 6.
        
        

        Any ideas?

        Show
        Jonathan H. Wage added a comment - The test for this issue is failing for me under mysql. There was 1 failure: 1) Doctrine\Tests\ORM\Functional\Ticket\DDC214Test::testCompanyModel Failed asserting that <integer:8> matches expected <integer:0>. /Users/jwage/Sites/doctrine2-orm/tests/Doctrine/Tests/ORM/Functional/Ticket/DDC214Test.php:77 /Users/jwage/Sites/doctrine2-orm/tests/Doctrine/Tests/ORM/Functional/Ticket/DDC214Test.php:54 FAILURES! Tests: 736, Assertions: 2260, Failures: 1, Skipped: 6. Any ideas?
        Hide
        Roman S. Borschel added a comment -

        Seems to fail under postgres, too.

        Show
        Roman S. Borschel added a comment - Seems to fail under postgres, too.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Eric Durand-Tremblay
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: