14 ms·

Safely Dropping MySQL Tables

64 points by mscccc 4y ago

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 ago
  With 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 ago
  The 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 ago
    You are missing Rust somewhere.
  - itake 4y ago
    OP'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 ago
  If 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 ago
    Oh, 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 ago
  You 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.
noasaservice 4y ago
This is about dropping objects in the MySQL database. It is not about getting rid of MySQL as a database.
donatj 4y ago
To me this is one of the major values of monorepo. A quick grep of the entire ecosystem for a table name and bam, I can drop it.
- wereHamster 4y ago
  won't work if you dynamically concatenate table names from strings! "select * from " + "use" + "rs;"
  - hinkley 4y ago
    Little Bobby Tables would like to have a word with you... I honestly wonder if you could make a functioning database API that either does not accept strings as arguments, or can detect string concatenation and reject it. Not just a builder pattern to greatly discourage it, but a straight up exception on bad input type. Bind variables or GTFO.
    - Arnavion 4y ago
      Sure you can. Just make your transport protocol only support taking in a stored procedure name and parameters for DMLs, and some typed representation for DDLs. But while that prevents people from concatenating strings to form DML queries as a whole, it obviously doesn't prevent the kind of concatenation wereHamster mentioned.
    - hxtk 4y ago
      The book "Building Secure and Reliable Systems" from Google's series on SRE actually talks about two examples of this in C++ and Go which forbid using anything but string literals in the query string of an SQL API. In Go, the solution was very tidy: it aliases string to an unexported internal type that consumers cannot instantiate. String literals can be coerced to that type, but variables that already have type information associated with them are rejected at compile time. The C++ solution was a bit more complicated and involved templates.
      - thrashh 4y ago
mnd999 4y ago
So grepping the query logs then.
- Scarbutt 4y ago
  That's too obvious.
jacquesm 4y ago
This is where the value of documentation comes in, as well as some process around your data model. If you need this to keep you safe you are doing something terribly wrong somewhere.
daenz 4y ago
Every source of state should have this feature. It is always inevitably needed.
skilled 4y ago
Would love to get some detailed, in-depth, and technically rich answers from people who upvoted this nonsensical article. It's literally a 100-word advertisement for a paid product.
- daenz 4y ago
  I didn't upvote it, but I think the product does inspire discussion about why we don't have basic important features like mandatory "last accessed" metadata on external state sources. Think of the number of hours of pain due to someone being "sure" a database/api/resource wasn't used, and then removing it. I'm not really put off by the advert for that reason.
  - throwusawayus 4y ago
    tracking access time (every read) is huge performance bottleneck. especially if reliably persisting this same reason why filesystems are often mounted with noatime attribute !
    - daenz 4y ago
      Yes, the sensible default for this feature would be opt-out, like noatime, in case you need the performance boost and understand the implications of the choice.
      - samlambert 4y ago
        This feature has zero performance impact on your database. It is powered by insights which is served from a separate data store.
        solardev 4y ago
        I think they meant if you were to add this as a default metadata feature into mainline mysql
- tobyjsullivan 4y ago
siddontang 4y ago
Interesting feature, I think we can learn from this in our product. IMO, it is still not safe even we know there are no queries running for this table. You may still meet a scenario that when you type `drop table`, another guy begins to run the query at the same time. As the maintainer of another database, We have been trying our best to improve this scenario too. Early on, we have provided a feature called `recover table` to recover table immediately after you wrongly drop the table. But this still can't avoid affecting current running queries on this table. We call this problem `DDL affects DML`, and now we try to introduce a Table meta lock to guarantee that no any query is running when the DDL executed. We hope we can release this feature in the end of this year.
rawoke083600 4y ago
How does this relate to pt-tools ? percona mysql toolkit ?

64 points by mscccc 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 ago
With 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 ago
The 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 ago
  You are missing Rust somewhere.
- itake 4y ago
  OP'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 ago
If 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 ago
  Oh, 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 ago
You 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.

noasaservice 4y ago

This is about dropping objects in the MySQL database. It is not about getting rid of MySQL as a database.

donatj 4y ago

To me this is one of the major values of monorepo. A quick grep of the entire ecosystem for a table name and bam, I can drop it.

wereHamster 4y ago
won't work if you dynamically concatenate table names from strings! "select * from " + "use" + "rs;"
- hinkley 4y ago
  Little Bobby Tables would like to have a word with you... I honestly wonder if you could make a functioning database API that either does not accept strings as arguments, or can detect string concatenation and reject it. Not just a builder pattern to greatly discourage it, but a straight up exception on bad input type. Bind variables or GTFO.
  - Arnavion 4y ago
    Sure you can. Just make your transport protocol only support taking in a stored procedure name and parameters for DMLs, and some typed representation for DDLs. But while that prevents people from concatenating strings to form DML queries as a whole, it obviously doesn't prevent the kind of concatenation wereHamster mentioned.
  - hxtk 4y ago
    The book "Building Secure and Reliable Systems" from Google's series on SRE actually talks about two examples of this in C++ and Go which forbid using anything but string literals in the query string of an SQL API. In Go, the solution was very tidy: it aliases string to an unexported internal type that consumers cannot instantiate. String literals can be coerced to that type, but variables that already have type information associated with them are rejected at compile time. The C++ solution was a bit more complicated and involved templates.
    - thrashh 4y ago

mnd999 4y ago

So grepping the query logs then.

Scarbutt 4y ago
That's too obvious.

jacquesm 4y ago

This is where the value of documentation comes in, as well as some process around your data model. If you need this to keep you safe you are doing something terribly wrong somewhere.

daenz 4y ago

Every source of state should have this feature. It is always inevitably needed.

skilled 4y ago

Would love to get some detailed, in-depth, and technically rich answers from people who upvoted this nonsensical article. It's literally a 100-word advertisement for a paid product.

daenz 4y ago
I didn't upvote it, but I think the product does inspire discussion about why we don't have basic important features like mandatory "last accessed" metadata on external state sources. Think of the number of hours of pain due to someone being "sure" a database/api/resource wasn't used, and then removing it. I'm not really put off by the advert for that reason.
- throwusawayus 4y ago
  tracking access time (every read) is huge performance bottleneck. especially if reliably persisting this same reason why filesystems are often mounted with noatime attribute !
  - daenz 4y ago
    Yes, the sensible default for this feature would be opt-out, like noatime, in case you need the performance boost and understand the implications of the choice.
    - samlambert 4y ago
      This feature has zero performance impact on your database. It is powered by insights which is served from a separate data store.
      - solardev 4y ago
        I think they meant if you were to add this as a default metadata feature into mainline mysql
tobyjsullivan 4y ago

siddontang 4y ago

Interesting feature, I think we can learn from this in our product. IMO, it is still not safe even we know there are no queries running for this table. You may still meet a scenario that when you type `drop table`, another guy begins to run the query at the same time. As the maintainer of another database, We have been trying our best to improve this scenario too. Early on, we have provided a feature called `recover table` to recover table immediately after you wrongly drop the table. But this still can't avoid affecting current running queries on this table. We call this problem `DDL affects DML`, and now we try to introduce a Table meta lock to guarantee that no any query is running when the DDL executed. We hope we can release this feature in the end of this year.

rawoke083600 4y ago

How does this relate to pt-tools ? percona mysql toolkit ?