4 ms·

I don't know anything about Aurora and maybe I'm missing something. But why not just wrap everything in a TRANSACTION and then do a ROLLBACK if there's an issue

I don't know anything about Aurora and maybe I'm missing something. But why not just wrap everything in a TRANSACTION and then do a ROLLBACK if there's an issue?

dx034 8y ago
That's for the cases where you only realize the mistake after the commit. Often because other queries suddenly fail.
- awgupta 8y ago
  Also, generally DDL statements like DROP TABLE typically autocommit.
wiredone 8y ago
Because nearly everyone I know has some memory where at a less experienced point in their engineering career they wrote: delete from ImportantTable where id = 1234 ... and forgot to select the second line in some query editor before running.
- ben174 8y ago
  There is usually an option to disable delete statements which do not contain a where clause. And if you really want to run that statement you'd need to write: delete from ImportantTable where 1 = 1; But yea, I get your point.
  - mark-wagner 8y ago
    For mysql it is "--i-am-a-dummy". I wish SQL supported putting the where clause first: "where X = Y update..."
ben509 8y ago
This would be for deploying a database migration that hoses something. For instance, we had a migration that touched a bunch of tables and, due to a triggered procedure, it blew away all the modification dates. TBH, I'm not sure how useful it is compared to the normal PITR. My usual guidance on recovering after a database fuck up is "put the app into maintenance mode and the database in read-only, do your investigation, run PITR, port user activity to the backup, do spot checks to determine everything got through, roll the app back to before the change, take the old database down, point the app at the new database, bring the system back online." I'm sure a manager is thinking, "oh, we can be back online in 5 minutes, amazing!" But just because something broke doesn't mean users stopped hitting your database! You can't just hit Undo and throw all that out!
- awgupta 8y ago
  You can also clone the database volume in Aurora and then just backtrack one of the volumes. That should help you ensure you have a version available for forensic analysis.
tanilama 8y ago
First, Transaction is not free, there is overhead comes with it. Second, it could be upstream software bug, that writes wrong data to the DB, you can possibly overwrite that.

I don't know anything about Aurora and maybe I'm missing something. But why not just wrap everything in a TRANSACTION and then do a ROLLBACK if there's an issue?

dx034 8y ago

That's for the cases where you only realize the mistake after the commit. Often because other queries suddenly fail.

awgupta 8y ago
Also, generally DDL statements like DROP TABLE typically autocommit.

wiredone 8y ago

Because nearly everyone I know has some memory where at a less experienced point in their engineering career they wrote: delete from ImportantTable where id = 1234 ... and forgot to select the second line in some query editor before running.

ben174 8y ago
There is usually an option to disable delete statements which do not contain a where clause. And if you really want to run that statement you'd need to write: delete from ImportantTable where 1 = 1; But yea, I get your point.
- mark-wagner 8y ago
  For mysql it is "--i-am-a-dummy". I wish SQL supported putting the where clause first: "where X = Y update..."

ben509 8y ago

This would be for deploying a database migration that hoses something. For instance, we had a migration that touched a bunch of tables and, due to a triggered procedure, it blew away all the modification dates. TBH, I'm not sure how useful it is compared to the normal PITR. My usual guidance on recovering after a database fuck up is "put the app into maintenance mode and the database in read-only, do your investigation, run PITR, port user activity to the backup, do spot checks to determine everything got through, roll the app back to before the change, take the old database down, point the app at the new database, bring the system back online." I'm sure a manager is thinking, "oh, we can be back online in 5 minutes, amazing!" But just because something broke doesn't mean users stopped hitting your database! You can't just hit Undo and throw all that out!

awgupta 8y ago
You can also clone the database volume in Aurora and then just backtrack one of the volumes. That should help you ensure you have a version available for forensic analysis.

tanilama 8y ago

First, Transaction is not free, there is overhead comes with it. Second, it could be upstream software bug, that writes wrong data to the DB, you can possibly overwrite that.