Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-795

Error: "database schema is not in sync“ after Create/Update MyISAM tables

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Minor Minor
    • Resolution: Won't Fix
    • Affects Version/s: 2.4.1
    • Fix Version/s: None
    • Component/s: Schema Managers
    • Security Level: All
    • Labels:
      None
    • Environment:
      debian 6.0.3, MySQL 5.1.49, PHP 5.3.3-7+squeeze3 with Suhosin-Patch (cli)

      Description

      After create or update MyIsam tables: (doctrine orm:schema-tool:create doctrine orm:schema-tool:update --force)

      When i check the schema with doctrine orm:validate-schema i get the error message:

      doctrine orm:validate-schema
      [Database] FAIL - The database schema is not in sync with the current mapping file.
      

      The reason for this is, that Doctrine tries to add a foreign key contraint to myisam-table:

      doctrine orm:schema-tool:update --dump-sql 
      
      ...
      ALTER TABLE addresses ADD CONSTRAINT FK_6FCA7516A76ED395 FOREIGN KEY (user_id) REFERENCES users (id);
      

        Issue Links

          Activity

          Hide
          Steve Müller added a comment -

          Jacek Hensoldt This issue should have been fixed in DBAL in commit: https://github.com/doctrine/dbal/commit/f99f6edde118dda70fe1c2c2f3dee31b6536a335
          Can you please check if the problem still exists with the current master branch?

          Show
          Steve Müller added a comment - Jacek Hensoldt This issue should have been fixed in DBAL in commit: https://github.com/doctrine/dbal/commit/f99f6edde118dda70fe1c2c2f3dee31b6536a335 Can you please check if the problem still exists with the current master branch?
          Hide
          Jacek Hensoldt added a comment - - edited

          unfortunately it does not work

          I have two tables with these definitions:

          /**
           * @Entity @Table(name="addresses", options={"engine"="MyISAM"})
           **/
          
          /**
           * @Entity @Table(name="users", options={"engine"="MyISAM"})
           **/
          

          But aufter create I still get the same error message.

          I inserted at line 661 this code:

          //lib/Doctrine/DBAL/Platforms/MySqlPlatform.php

          $hasOption = $diff->fromTable->hasOption('engine') ? 'YES' : 'NO';
          echo "Engine hasOption?:".$hasOption;
          echo "Engine: ".$engine;
          

          Output:

          Engine hasOption?:NO
          Engine: INNODB

          The hasOption()-check is working correctly, the problem is, that the engine-option is not set

          $diff->fromTable->getOption('engine')

          would throw an exception

          Show
          Jacek Hensoldt added a comment - - edited unfortunately it does not work I have two tables with these definitions: /** * @Entity @Table(name= "addresses" , options={ "engine" = "MyISAM" }) **/ /** * @Entity @Table(name= "users" , options={ "engine" = "MyISAM" }) **/ But aufter create I still get the same error message. I inserted at line 661 this code: //lib/Doctrine/DBAL/Platforms/MySqlPlatform.php $hasOption = $diff->fromTable->hasOption('engine') ? 'YES' : 'NO'; echo "Engine hasOption?:" .$hasOption; echo "Engine: " .$engine; Output: Engine hasOption?:NO Engine: INNODB The hasOption()-check is working correctly, the problem is, that the engine-option is not set $diff->fromTable->getOption('engine') would throw an exception
          Hide
          Steve Müller added a comment -

          Jacek Hensoldt Confirmed your issue. The problem is that custom table options like the table engine on MySQL don't get reverse engineered by Doctrine currently. Therefore the comparator recognizes differences between the online and offline table which in reality are not there.

          Show
          Steve Müller added a comment - Jacek Hensoldt Confirmed your issue. The problem is that custom table options like the table engine on MySQL don't get reverse engineered by Doctrine currently. Therefore the comparator recognizes differences between the online and offline table which in reality are not there.
          Hide
          Steve Müller added a comment -

          Okay it's not a comparator issue as it currently does not compare table options at all. The comparator will detect foreign key changes because the online table does not have any foreign keys but your mapping propagates foreign keys through the relation. This cannot be detected in the comparator but has to be evaluated in the platform (which is already fixed). Then I guess the only issue is that the table does not get reverse engineered with the table options set.

          Show
          Steve Müller added a comment - Okay it's not a comparator issue as it currently does not compare table options at all. The comparator will detect foreign key changes because the online table does not have any foreign keys but your mapping propagates foreign keys through the relation. This cannot be detected in the comparator but has to be evaluated in the platform (which is already fixed). Then I guess the only issue is that the table does not get reverse engineered with the table options set.
          Hide
          Steve Müller added a comment -

          This is tricky. We need to introspect the table options with the schema manager. Currently reverse engineering the online schema always results in table objects being created without table options. Therefore the comparator will always detect changes. We need to introduce something like AbstractSchemaManager::listTableOptions() and AbstractPlatform::getListTableOptionsSQL(). The latter could potentially be "empty" as currently only MySQL uses table options. Don't know what to do with those platforms. Throw "unsupported" exception? Return empty SQL? Both introduce problems in the schema manager. Throwing exception needs exception handling in the schema manager or another supports*() method in the AbstractPlatform to check against. Returning empty SQL is rather sloppy implementation and also needs handling in the schema manager.

          Show
          Steve Müller added a comment - This is tricky. We need to introspect the table options with the schema manager. Currently reverse engineering the online schema always results in table objects being created without table options. Therefore the comparator will always detect changes. We need to introduce something like AbstractSchemaManager::listTableOptions() and AbstractPlatform::getListTableOptionsSQL(). The latter could potentially be "empty" as currently only MySQL uses table options. Don't know what to do with those platforms. Throw "unsupported" exception? Return empty SQL? Both introduce problems in the schema manager. Throwing exception needs exception handling in the schema manager or another supports*() method in the AbstractPlatform to check against. Returning empty SQL is rather sloppy implementation and also needs handling in the schema manager.
          Hide
          Marco Pivetta added a comment -

          After discussion with Steve Müller, I decided that this won't be fixed.

          Numerous reasons behind this:

          • MyISAM is not really something we support, since the MySQL platform is built around InnoDB assumptions
          • This goes into specifics of your persistence layer, and it is not really required for the schema tools to support it
          • This will increase the workload for supporting exotic storage layers that we can't really maintain (it's like pandora's box)
          Show
          Marco Pivetta added a comment - After discussion with Steve Müller , I decided that this won't be fixed. Numerous reasons behind this: MyISAM is not really something we support, since the MySQL platform is built around InnoDB assumptions This goes into specifics of your persistence layer, and it is not really required for the schema tools to support it This will increase the workload for supporting exotic storage layers that we can't really maintain (it's like pandora's box)

            People

            • Assignee:
              Marco Pivetta
              Reporter:
              Jacek Hensoldt
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: