Skip to content

Loading…

DBAL-598: Schema Comparator is case insensitive for table names #1810

Open
doctrinebot opened this Issue · 1 comment

2 participants

@doctrinebot

Jira issue originally created by user frozenfire:

https://github.com/doctrine/dbal/blob/master/lib/Doctrine/DBAL/Schema/Comparator.php#L102

The schema comparator currently uses strtolower for all table name comparison, so differences in case will not be detected.

When updating a schema using the schema tool, the tool will not perform any modification if the case of a table name has changed.

An example of this case would be when one is writing entities for the ORM, and the naming includes uppercase letters. You generate your schema and it uses the equivalent case for the initial generation. Then, you write Table annotations to change the name of the tables to lowercase. The schema comparator would not detect this, and depending upon the case sensitivity of the SQL engine, this could result in SQL errors.

@doctrinebot

Comment created by dualmon:

This is the closest existing issue I could find to a problem we are having with the schema tool update and case insensitivity. If this is a separate bug, I'd be happy to write it up, but I don't have create privs in Jira yet.

Here's the bug:

If we add a new association, and do php vendor/bin/doctrine-module orm:schema-tool:create, the new association will be created in MySQL information schema REFERENCEDTABLENAME will be created case sensitively.

Example (note second to last column):

| CONSTRAINTCATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMNNAME |
| def | mydb | FKE2C31786CF786E13 | def | mydb | ObservationDiagnosticParameterValue | diagnostic_parameter_valueid | 1 | 1 | mydb | DiagnosticParameterValue | id |

However, if we add a new association, and do php vendor/bin/doctrine-module orm:schema-tool:update, the new association will be created in MySQL information schema REFERENCEDTABLENAME will be created case insensitively.

Example (note second to last column):

| CONSTRAINTCATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMNNAME |
| def | mydb | FKE2C31786CF786E13 | def | mydb | ObservationDiagnosticParameterValue | diagnostic_parameter_valueid | 1 | 1 | mydb | diagnosticparametervalue | id |

In the second case, we then get errors like this:

An exception occurred while executing 'INSERT INTO ObservationDiagnosticParameterValue (observation*id, diagnostic_parameter_value*id) VALUES (?, ?)' with params [1211, 6916]:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`mydb`.`ObservationDiagnosticParameterValue`, CONSTRAINT `FK*E2C31786CF786E13` FOREIGN KEY (`diagnostic_parameter_value*id`) REFERENCES `diagnosticparametervalue` (`id`))

#0 /var/opt/theapp/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php(140): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(PDOException), 'INSERT INTO Obs...', Array)

To fix this, we can alter the affected table like this:

alter table `ObservationDiagnosticParameterValue` drop foreign key `FK_E2C31786CF786E13`;
alter table `ObservationDiagnosticParameterValue` add constraint `FK*E2C31786CF786E13` foreign key (`diagnostic_parameter_value*id`) REFERENCES `DiagnosticParameterValue` (`id`);

The behavior of schema-tool:create and schema-tool:update should not be different.

@doctrinebot doctrinebot added the Bug label
@beberlei beberlei was assigned by doctrinebot
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.