#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102

    Error 1701 sqlstate 42000 er_truncate_illegal_fk


    Hello everyone,

    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...
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    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?
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    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.



    http://dev.mysql.com/doc/refman/5.6/en/truncate-table.html
  6. #4
  7. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    This does appear to be different from 5.0:

    http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

    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.
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    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???
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    So is a delete cascade the best and fastest way to handle this for data in a Parent / child tables linked by a FK?

IMN logo majestic logo threadwatch logo seochat tools logo