Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-598

Schema Comparator is case insensitive for table names

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Schema Managers
    • Security Level: All
    • Labels:
      None

      Description

      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.

        Activity

        Hide
        Jeremiah Small added a comment -

        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 REFERENCED_TABLE_NAME will be created case sensitively.

        Example (note second to last column):

        CONSTRAINT_CATALOG 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_COLUMN_NAME
        def mydb FK_E2C31786CF786E13 def mydb ObservationDiagnosticParameterValue diagnostic_parameter_value_id 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 REFERENCED_TABLE_NAME will be created case insensitively.

        Example (note second to last column):

        CONSTRAINT_CATALOG 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_COLUMN_NAME
        def mydb FK_E2C31786CF786E13 def mydb ObservationDiagnosticParameterValue diagnostic_parameter_value_id 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.

        Show
        Jeremiah Small added a comment - 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 REFERENCED_TABLE_NAME will be created case sensitively. Example (note second to last column): CONSTRAINT_CATALOG 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_COLUMN_NAME def mydb FK_E2C31786CF786E13 def mydb ObservationDiagnosticParameterValue diagnostic_parameter_value_id 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 REFERENCED_TABLE_NAME will be created case insensitively. Example (note second to last column): CONSTRAINT_CATALOG 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_COLUMN_NAME def mydb FK_E2C31786CF786E13 def mydb ObservationDiagnosticParameterValue diagnostic_parameter_value_id 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.

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Justin Martin
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated: