Skip to content

Loading…

DDC-214: schema-tool --complete-update throw PDOException on DROP INDEX #2828

Closed
doctrinebot opened this Issue · 21 comments

2 participants

@doctrinebot

Jira issue originally created by user rickdt:

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
        )

)
@doctrinebot

Comment created by rickdt:

I take the index definition from DBAL/Schema/Comparator:207

$table1Indexes = $table1->getIndexes();
$table2Indexes = $table2->getIndexes();
@doctrinebot

Comment created by @beberlei:

Which database platform is this happening on?

@doctrinebot

Comment created by rickdt:

mysql

@doctrinebot

Comment created by @beberlei:

Ok so Mysql creates an index for each foreign key implicitly which is then queryable explicitly.

To fix this the schema comparator "FixSchema" visitor accompanies for that for the MySQL Platform. Do you use your own platform maybe?

@doctrinebot

Comment created by @beberlei:

You could really help me if you could paste the serialized object code for both schema instances for your use-case or a reproduce case.

@doctrinebot

Comment created by rickdt:

Here are the serialized schema you required.

@doctrinebot

Comment created by rickdt:

I only use Doctrine basic feature, no custom platform.

@doctrinebot

Comment created by rickdt:

Can I do anything to help you resolve this issue. This is really a big annoyance not being able to update existing database.

@doctrinebot

Comment created by rickdt:

Attached a test case

Doctrine/Tests/ORM/Functional/Ticket/DDC214Test

@doctrinebot

Comment created by rickdt:

The test case must be run using mysql.

@doctrinebot

Comment created by @beberlei:

hah thats an awesome-ly simple test-case.

thank you very much :-)

@doctrinebot

Comment created by @beberlei:

Yes, i found the issue - however the test-case will not work with me requiring it to have 0 change sqls - there is another bug that is related to not null and default values.

@doctrinebot

Comment created by rickdt:

I did not really intend to do a "clean" unit test. I just wanted allow you reproduce the problem.

Good news you have been able to reproduce the issue.

And again, your great work is really appreciated!

@doctrinebot

Comment created by @beberlei:

This and many more issues have been fixed.

@doctrinebot

Issue was closed with resolution "Fixed"

@doctrinebot

Comment created by rickdt:

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'";
@doctrinebot

Comment created by @beberlei:

Patched, and speedy again! Thank you very much.

@doctrinebot

Comment created by rickdt:

That was fast!

@doctrinebot

Comment created by @jwage:

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?

@doctrinebot

Comment created by romanb:

Seems to fail under postgres, too.

@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.0-BETA1 milestone
@doctrinebot doctrinebot closed this
@doctrinebot doctrinebot added the Bug label
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.