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

[doctrinebot]

Jira issue originally created by user jack88:

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);

[doctrinebot]

• relates to DBAL-743: [GH-476] Fix foreign key propagation on non-supporting MySQL table engines

[doctrinebot]

Comment created by @deeky666:

[~jack88] 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?

[doctrinebot]

Comment created by jack88:

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

[doctrinebot]

Comment created by @deeky666:

[~jack88] 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.

[doctrinebot]

Comment created by @deeky666:

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.

[doctrinebot]

Comment created by @deeky666:

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.

[doctrinebot]

Comment created by @ocramius:

After discussion with [~deeky666], 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)

[doctrinebot]

Issue was closed with resolution "Won't Fix"