search

gocom / danpu

Optimized MySQL dump library written in PHP
MIT License
62 stars 15 forks source link

Cannot delete or update a parent row: a foreign key constraint fails #7

Open tvb opened 10 years ago

tvb commented 10 years ago

I am getting this error while trying to import my dump created by danpu:

SQLSTATE[23000]: Integrity constraint violation: 1217 Cannot delete or update a parent row: a foreign key constraint fails

SHOW ENGINE INNODB STATUS; shows me: Error: Cannot drop table db.tableX because it is referenced by db.tableY

Now, I know I can work around it by setting foreign_key_checks to false. https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_foreign_key_checks

But imo it's a dirty solution and I would like to find a proper solution instead. Ideas?

gocom commented 10 years ago

Actually disabling foreign_key_checks is also what the C-based mysqldump script (the one that ships with MySQL) does too. Looking at its source, its not able to do anything else than disable foreign key constraints.

Outside of disabling key constraints, the other and only option (that leads to somewhat might-break-at-any-second low-level code) is ordering all statements according to constraints, which I'm not sure if it's even fully possible (its for normal table references). This means that inserts, trigger/table/view/event creations and trigger/table/view/event drops will be all in mixed order. I'm not aware any project that actually even does this.

The only way of getting table constraint referencing order is by reading INFORMATION_SCHEMA.KEY_COLUMN_USAGE and writing a custom sorter handler that returns tables in constraint order. MySQL doesn't have its own extension for accessing key constraints (Danpu uses those documented SHOW extensions, which means simpler code and b/f compatibility, albeit if we wanted to support anything non-MySQL like we would have to access schema tables directly... and handle with vendor inconstancies).

As ordering goes, Danpu has same issues as the official mysqldump script. While both allow importing over existing databases, neither should if the database contains anything other than tables or views. Neither drops events/triggers before inserting rows, leading into duplicate data and/or error. Then again, it can be used as a feature.

We'll have to change the defaults for 3.0.0 release, and correct some of the logic for 2.7.0 (move trigger/event dropping to the top and add support for drop table -- as its new feature might as well change the create table if exits to drop-and-create as with tables/views/triggers/events -- offers some consistency too).