-
-
Notifications
You must be signed in to change notification settings - Fork 224
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Support truncating tables with foreign keys #113
Comments
@Seldaek turning off FK checks in fixtures? Sounds seriously broken... What about the other engines? Is it just a MySQL bug? |
we need to somehow generalize this, its a MySQL problem AFAIK, not sure about other databases. We need to reproduce this in DBAL for sure. |
the big issue in MySQL is that they do FK checks after every statement rather than at the end of the transaction. so yeah most other RDBMS support so called deferred FKs and therefore do not suffer from this. |
Sorry I should have clarified. This is not about disabling FK checks for the whole loading of fixtures, the only thing we need is to disable them while running the ORMPurger because it does TRUNCATE and that is not supported with FK checks enabled. |
This really is a poking issue. Almost all my CI projects are now failing after an update to MySQL >= 5.5.7 (the version where this behavior of TRUNCATE was introduced). I hope that you can find a solution in a timely manner since more and more people will be upgrading mysql for sure. |
+1 this need to be resolved |
+1 |
The only way to fiddle with this issue (as both MySQL won't fix it nor doctrine DBAL willing to add another vendor specific rule) for me was to create a special DBAL driver and use this during fixture loading. https://coderwall.com/p/staybw/workaround-for-1701-cannot-truncate-a-table-referenced-in-a-foreign-key-constraint-using-doctrine-fixtures-load-purge-with-truncate gives some hints. In Symfony I ended up using a config_fixtures.yml, passing the doctrine:fixtures:load env=fixtures where this special Driver is used config_fixtures.yml ...
doctrine:
dbal:
driver_class: AppBundle\DBAL\Driver Driver.php <?php
namespace AppBundle\DBAL;
use Doctrine\DBAL\Driver\PDOMySql\Driver as BaseDriver;
class Driver extends BaseDriver
{
/**
* {@inheritdoc}
*/
public function getDatabasePlatform()
{
return new Platform();
}
} Platform.php <?php
namespace AppBundle\DBAL;
use Doctrine\DBAL\Platforms\MySqlPlatform;
class Platform extends MySqlPlatform
{
/**
* {@inheritdoc}
*/
public function getTruncateTableSQL($tableName, $cascade = false)
{
return sprintf('SET foreign_key_checks = 0;TRUNCATE %s;SET foreign_key_checks = 1;', $tableName);
}
} |
@fschaeffer With your workaround, I get the following error:
My doctrine dbal config: doctrine:
dbal:
driver_class: AppBundle\DBAL\Driver
default_connection: default
connections:
default:
driver: pdo_mysql
host: "%database_host%"
port: "%database_port%"
dbname: "%database_name%"
user: "%database_user%"
password: "%database_password%"
charset: UTF8
powerdns:
driver: pdo_mysql
host: "%powerdns_host%"
port: "%powerdns_port%"
dbname: "%powerdns_name%"
user: "%powerdns_user%"
password: "%powerdns_password%"
charset: UTF8
types:
json: Sonata\Doctrine\Types\JsonType Any idea? |
Ok I get it, it's because I have multiple connections definitions. I have to specify it on each connection: doctrine:
dbal:
default_connection: default
connections:
default:
driver_class: AppBundle\DBAL\Driver
driver: pdo_mysql
host: "%database_host%"
port: "%database_port%"
dbname: "%database_name%"
user: "%database_user%"
password: "%database_password%"
charset: UTF8 |
I had to override the loadFixtures method of the LiipFunctionalTestBundle with this stuff to avoid blowing up mysql:
Now obviously it could be integrated in the bundle (cc @lsmith77) but ideally it would be part of this library itself so everyone benefits. Not sure how/where to best do this though, so I'm dropping this here hoping someone will pick it up :)
The text was updated successfully, but these errors were encountered: