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

Details

Type: Bug
Status: Resolved
Priority: 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
        )

)

Options
- Sort By Name
- Sort By Date
- Ascending
- Descending
- Download All

Attachments

both_schema_print_r

21/Dec/09 1:22 PM

1.35 MB

Eric Durand-Tremblay
DDC214Test.php

03/Feb/10 7:02 PM

1 kB

Eric Durand-Tremblay
from_schema

21/Dec/09 1:22 PM

305 kB

Eric Durand-Tremblay
to_schema

21/Dec/09 1:22 PM

128 kB

Eric Durand-Tremblay

Activity

Ascending order - Click to sort in descending order

Hide

Permalink

Eric Durand-Tremblay added a comment - 16/Dec/09 6:11 PM

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

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

Show

Eric Durand-Tremblay added a comment - 16/Dec/09 6:11 PM I take the index definition from DBAL/Schema/Comparator:207 $table1Indexes = $table1->getIndexes(); $table2Indexes = $table2->getIndexes();

Hide

Permalink

Benjamin Eberlei added a comment - 16/Dec/09 6:23 PM

Which database platform is this happening on?

Show

Benjamin Eberlei added a comment - 16/Dec/09 6:23 PM Which database platform is this happening on?

Hide

Permalink

Eric Durand-Tremblay added a comment - 16/Dec/09 6:27 PM

mysql

Show

Eric Durand-Tremblay added a comment - 16/Dec/09 6:27 PM mysql

Hide

Permalink

Benjamin Eberlei added a comment - 19/Dec/09 3:41 PM

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?

Show

Benjamin Eberlei added a comment - 19/Dec/09 3:41 PM 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?

Hide

Permalink

Benjamin Eberlei added a comment - 19/Dec/09 3:43 PM

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.

Show

Benjamin Eberlei added a comment - 19/Dec/09 3:43 PM 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.

Hide

Permalink

Eric Durand-Tremblay added a comment - 21/Dec/09 1:22 PM

Here are the serialized schema you required.

Show

Eric Durand-Tremblay added a comment - 21/Dec/09 1:22 PM Here are the serialized schema you required.

Hide

Permalink

Eric Durand-Tremblay added a comment - 21/Dec/09 1:23 PM

I only use Doctrine basic feature, no custom platform.

Show

Eric Durand-Tremblay added a comment - 21/Dec/09 1:23 PM I only use Doctrine basic feature, no custom platform.

Hide

Permalink

Eric Durand-Tremblay added a comment - 03/Feb/10 6:23 PM

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

Show

Eric Durand-Tremblay added a comment - 03/Feb/10 6:23 PM Can I do anything to help you resolve this issue. This is really a big annoyance not being able to update existing database.

Hide

Permalink

Eric Durand-Tremblay added a comment - 03/Feb/10 7:02 PM

Attached a test case

Doctrine/Tests/ORM/Functional/Ticket/DDC214Test

Show

Eric Durand-Tremblay added a comment - 03/Feb/10 7:02 PM Attached a test case Doctrine/Tests/ORM/Functional/Ticket/DDC214Test

Hide

Permalink

Eric Durand-Tremblay added a comment - 03/Feb/10 7:03 PM

The test case must be run using mysql.

Show

Eric Durand-Tremblay added a comment - 03/Feb/10 7:03 PM The test case must be run using mysql.

Hide

Permalink

Benjamin Eberlei added a comment - 03/Feb/10 8:31 PM

hah thats an awesome-ly simple test-case.

thank you very much

Show

Benjamin Eberlei added a comment - 03/Feb/10 8:31 PM hah thats an awesome-ly simple test-case. thank you very much

Hide

Permalink

Benjamin Eberlei added a comment - 03/Feb/10 10:56 PM

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.

Show

Benjamin Eberlei added a comment - 03/Feb/10 10:56 PM 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.

Hide

Permalink

Eric Durand-Tremblay added a comment - 04/Feb/10 2:32 PM

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!

Show

Eric Durand-Tremblay added a comment - 04/Feb/10 2:32 PM 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!

Hide

Permalink

Benjamin Eberlei added a comment - 07/Feb/10 7:38 AM

This and many more issues have been fixed.

Show

Benjamin Eberlei added a comment - 07/Feb/10 7:38 AM This and many more issues have been fixed.

Hide

Permalink

Eric Durand-Tremblay added a comment - 09/Feb/10 1:31 PM

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 - 09/Feb/10 1:31 PM 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

Permalink

Benjamin Eberlei added a comment - 09/Feb/10 1:48 PM

Patched, and speedy again! Thank you very much.

Show

Benjamin Eberlei added a comment - 09/Feb/10 1:48 PM Patched, and speedy again! Thank you very much.

Hide

Permalink

Eric Durand-Tremblay added a comment - 09/Feb/10 1:55 PM

That was fast!

Show

Eric Durand-Tremblay added a comment - 09/Feb/10 1:55 PM That was fast!

Hide

Permalink

Jonathan H. Wage added a comment - 25/May/10 1:37 PM

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 - 25/May/10 1:37 PM 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

Permalink

Roman S. Borschel added a comment - 06/Jun/10 5:38 PM

Seems to fail under postgres, too.

Show

Roman S. Borschel added a comment - 06/Jun/10 5:38 PM Seems to fail under postgres, too.

People

Assignee:

Benjamin Eberlei

Reporter:

Eric Durand-Tremblay

Vote (0)

Watch (1)

Dates

Created:

16/Dec/09 6:07 PM

Updated:

14/Jun/10 9:17 AM

Resolved:

07/Feb/10 7:38 AM