Error 1701 sqlstate 42000 er_truncate_illegal_fk
Currently we are taking data from a 5.0.51a MySQL open source DB and trying to load that data into MySQL 5.6.10 open source DB
getting the following when trying to Truncate a table with a Foriegn key constraints....
What is the best way to do this...
2013-05-17 15:05:13: INFORMATION - Calculating QTXN database size...
2013-05-17 15:05:18: INFORMATION - QTXN database size: 7915 positions, 94811 total transactions.
2013-05-17 15:05:18: ERROR - query 'TRUNCATE TABLE Position;' failed with error code 1701
2013-05-17 15:05:18: INFORMATION - Finished QTXN load
2013-05-17 15:05:18: ERROR - Process Failed, Error Code = 1701
The task abended at this point and the load failed.
Error: 1701 SQLSTATE: 42000 (ER_TRUNCATE_ILLEGAL_FK)
Message: Cannot truncate a table referenced in a foreign key constraint (%s)
Now we chose to use Truncate because it was faster that using the drop table and recreate. What is the best way to overcome this?
Thanks in advance...
Also this same code for loading the data worked perfectly in 5.0.51a, but it fails in 5.6.10
so if I created any table with two rows as the parent table and the child table had two rows with one being a foreign key tied to the parent table and tried to truncate the parent table...how do I do this in 5.6.10?
Note: these are Innodb tables with parent-child foreign key relationships
Here is some data I found, if this is the case how might I do this in 5.6.10...?
13.1.33. TRUNCATE TABLE Syntax
TRUNCATE [TABLE] tbl_name
TRUNCATE TABLE empties a table completely. It requires the DROP privilege.
Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, it bypasses the DML method of deleting data. Thus, it cannot be rolled back, it does not cause ON DELETE triggers to fire, and it cannot be performed for InnoDB tables with parent-child foreign key relationships.
Although TRUNCATE TABLE is similar to DELETE, it is classified as a DDL statement rather than a DML statement. It differs from DELETE in the following ways in MySQL 5.6:
Truncate operations drop and re-create the table, which is much faster than deleting rows one by one, particularly for large tables.
Truncate operations cause an implicit commit, and so cannot be rolled back.
Truncation operations cannot be performed if the session holds an active table lock.
TRUNCATE TABLE fails for an InnoDB table if there are any FOREIGN KEY constraints from other tables that reference the table. Foreign key constraints between columns of the same table are permitted.
This does appear to be different from 5.0:
In the case that FOREIGN KEY constraints reference the table, InnoDB deletes rows one by one and processes the constraints on each one. If the FOREIGN KEY constraint specifies DELETE CASCADE, rows from the child (referenced) table are deleted, and the truncated table becomes empty. If the FOREIGN KEY constraint does not specify CASCADE, the TRUNCATE TABLEstatement deletes rows one by one and stops if it encounters a parent row that is referenced by the child, returning this error:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign
key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1`
FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
This is the same as a DELETE statement with no WHERE clause.
So what is the best way to import data from external flat files and etc, into a 5.6.10 opensource database? Should we use a delete cascade on both the parent and child tables and then import???
So is a delete cascade the best and fastest way to handle this for data in a Parent / child tables linked by a FK?