How to Delete Huge Number of Rows in mysql
While delete the high volume rows in mysql it will pop on reference other tables dependency even. Delete will fail on foreign key issue.
Error could be the following,
Cannot delete or update a parent row: a foreign key constraint fails…FOREIGN KEY (`xxxxxxx_id`) REFERENCES `xxxxx` (`id`))
Solution:
1. Disable foreign checks
mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
2. Run deletion
mysql> delete from dummytable where password=”deleterow”;
Query OK, 1500 rows affected (0.03 sec)
3. Enable foreign key checks
mysql> SET FOREIGN_KEY_CHECKS=1;
Query OK, 0 rows affected (0.00 sec)
Tags: mysql,technews,mytecharticle,knowledge articles,database
Add Comment