Safely dropping MySQL tables
By David Graham |
Dropping or removing an unused table from a database schema can be challenging. Even after triple checking that all of your apps have migrated away from querying the table, there may still be that one rogue script that accesses it.
Running the dreaded DROP TABLE statement can cause a host of unintentional problems if that table is still being used elsewhere. It completely erases the table definition, partitions, and the data in that table. Before you drop a table, you should double (or triple!) check when a table was last queried.
When was the table you want to drop last accessed?
You can manually check when a table was last accessed, but it's a bit complicated. The following query will show you the last time a table was written to, but not read:
SELECT update_time FROM information_schema.tables WHERE table_name='tablename'
To see time last accessed in general, you can use the audit plugin for MySQL Enterprise edition that allows you to see which users ran which queries per connection. The Audit Record itself has some customization options, though is limited.
To see when a table was last modified, you can also check when the given <tablename>.ibd file was updated. Or you can check the .frm file for DDL changes, which can give you the last known modification.
So, you do have some options to find the last time a table was modified, but the solutions aren't very straightfoward. Doing this each time you want to drop a table could drastically delay your team's speed to production.
Using PlanetScale to safely drop MySQL tables
At PlanetScale, our mission is to create the most scalable, developer-friendly database platform. Dropping tables is never fun, but we wanted to make the process as stress-free as possible. To accomplish this, we built an in-dashboard feature that checks if tables are truly unused during deploy requests and warns you if the table to be dropped was recently queried.
Identifying table usage with Insights
On top of warning you, we also want to help you find when and where the table is being queried. If you run into this warning, you can use Insights, our in-dashboard query monitoring tool, to help identify where the table is being queried.
With Insights, you can narrow down your analysis to individual query performance. We also surface SQL comments on queries, so you can tag your queries with additional information to track down where they came from.
Instrumenting queries with comment tags can help you identify which application is still using the table. Once you remove the query from any remaining applications, you can confidently drop the table.
Queries against individual tables can always be found by going to your Insights page and using the table:<name> query syntax in the filter input box, as shown below. This reveals how many dependencies there are on the table before attempting to drop it.
Try it out
Hopefully this addition will make cleaning up unused tables a little less stressful. For more information about how to use Insights, check out our documentation.
We love hearing from you! If you have any questions or feedback, don’t hesitate to contact us.