This content originally appeared on DEV Community and was authored by Nobu
What happened
I was asked to investigate data in the production environment. Before the investigation, I was using MySQL Workbench to delete unnecessary data on the production DB.
When investigating the data in the production DB, I mistakenly executed delete instead of select in SQL in MySQL Workbench, and deleted an entire table.
DELETE FROM posts;
ON DELETE CASCADE was set for the parent table, so four more table data were lost one after another.
If you would like to know more about ON DELETE CASCADE.
https://www.geeksforgeeks.org/mysql-on-delete-cascade-constraint/
Table structure (table names are given as examples and may differ slightly from the actual ones)
Correct settings
When a user is deleted from the users table, the records in the child tables linked to that user_id are also deleted.
comments table
CONSTRAINT `comments_ibfk_1 ` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
likes table
CONSTRAINT `likes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
points table
CONSTRAINT `points_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
posts table
CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
Incorrect setting
When you delete an entire child table posts table of the users table, the users records linked to post_id are also deleted.
Furthermore, the child table records linked to that user_id are also deleted in a chain reaction.
users table
CONSTRAINT `users_ibfk _1` FOREIGN KEY (`comment_id`) REFERENCES `comments` (`id`) ON DELETE CASCADE
CONSTRAINT `users_ibfk_2` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE
CONSTRAINT `users_ibfk_3` FOREIGN KEY (`like_id`) REFERENCES `likes` (`id`) ON DELETE CASCADE
CONSTRAINT `users_ibfk_4` FOREIGN KEY (`point_id`) REFERENCES `points` (`id`) ON DELETE CASCADE
comments table
CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
likes table
CONSTRAINT `likes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
points table
CONSTRAINT `points_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
posts table
CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
Dealing with the problem
This time, we took a backup of RDS every day, so I was able to restore it to its original data.
The problem occurred at about 10:45 that morning, and I was able to restore RDS at about 13:00. The restoration time may vary depending on the amount of data.
It happened during a meeting, so I was able to report the problem immediately.
Backups are very important.
Measures to prevent a recurrence
- Only senior engineers or managers have editing rights (delete, etc.) to the production environment DB, and other members have read-only rights.
- It was bad to have ON DELETE CASCADE set in the parent table at first, so I removed it from the parent table. I don't know how it was implemented, but it's clearly an anti-pattern, so I removed it.
Thoughts
Since I became an engineer, I had never made a big mistake until this incident, so I think I was pretty relaxed.
Also, during a meeting, we needed to investigate the data, and it was not good that I looked at the production database while talking.
When I executed DELETE, my mind went blank,
but afterwards my colleague encouraged me by saying, "I've had a lot of experiences like that. Don't worry about it," which made me happy.
I think that this is how we grow as engineers, step by step, even as we make mistakes, so I hope I can use this failure as a stepping stone to grow myself!
This content originally appeared on DEV Community and was authored by Nobu
Nobu | Sciencx (2024-07-21T05:29:29+00:00) Deleted a table in production and lost four more table data with ON DELETE CASCADE. Retrieved from https://www.scien.cx/2024/07/21/deleted-a-table-in-production-and-lost-four-more-table-data-with-on-delete-cascade/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.