5 ms·
Isn't the safe way to do this is to rename the table and watch for errors before performing the drop? Queries fail on the old table name and can easily be recov
by itake 4y ago
Isn't the safe way to do this is to rename the table and watch for errors before performing the drop? Queries fail on the old table name and can easily be recovered by reverting the name change.
- samlambert 4y agoWith https://planetscale.com/features/rewind https://planetscale.com/features/rewind you can just drop the table and bring it back if you have errors.
- robertlagrant 4y agoThe safest way is to migrate to Postgres and then do what you like with your MySQL instance. (I just wanted to win the prize for the most HNey comment ever!)
- SnowHill9902 4y agoYou are missing Rust somewhere.
- itake 4y agoOP's solution is objectively worse, because it only reports access recency. Just because the table was or wasn't used recently, doesn't mean its safe to drop. The code change may of landed the day before that disabled accessing that table, but the tool would warn that the table was still in use. A quarterly cron job may access the table and go undetected if you look purely at the recency. The rename strategy protects the data and offers easy rollback. This is a false heuristic.
- grogers 4y agoIf you happen to still be using it then you'd get a lot of errors right away though. If you are extremely paranoid you can do better by migrating to a new user that doesn't have access to the table. Switching to the new user would be a normal rolling deploy, so you'd only start with 1/N errors, and your normal rollback process gets automatically triggered. Probably not worth the hassle though.
- jjeaff 4y agoOh, that's clever. I have so rarely utilized the user management system in MySQL beyond just creating a user with the necessary permissions for each application. I hadn't ever thought of using new users to do canary deployments.
- mathnode 4y agoYou are spot on. And more importantly, a roll back procedure. In this case, RENAME TABLE x TO y, and watch or wait for a process to fail in test/QA/UAT/etc. Have a DROP ready, but also a RENAME back. A production quality MySQL or MariaDB installation should have enabled binary logging and maybe delayed replication to handle any issues.