trigger for DELETE prevention

[ghazel]

Since DELETE cannot be propagated during a migration, I think it would be neat to throw an error when a DELETE is attempted. Something like:

CREATE TRIGGER mytable_delete_block BEFORE DELETE ON mytable FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'DELETE is not allowed during a migration';

Then afterwards:

DROP TRIGGER mytable_delete_block;

The SIGNAL command is only valid on on MySQL >= 5.5, but there are a number of hacks which would work on >= 5.0 ( http://stackoverflow.com/questions/229765/triggers-that-cause-inserts-to-fail-possible ).

I'm probably going to go implement this, but I wanted to file a ticket about it in case there were thoughts or feedback.

[freels]

I could see this being useful, as long as it defaults to off. Eventually I'd like to see how hard it would be to do triggers-based change tracking, which would remove this limitation altogether.

[ghazel]

Why default off? The goal here is to protect against developer error/unawareness of the limitation. They're probably also likely to not remember/know about the option.

Is there a case where DELETE during a migration is valid?

I guess I could start looking in to propagating the DELETE to the new_ table instead of throwing an error..

[freels]

I don't believe there's a case where a DELETE during migration is valid. However, triggers have performance implications, and this process can already be touchy. Additionally, this would still result in a server-side error if this is turned on, which, while better than eventually ignoring deletes, is still not necessarily good. The safest way to handle this would be to design your application to never send a DELETE as part of user interaction, but rather tombstone the row. (This also helps for auditing purposes later.)

This tool already requires a more advanced understanding of MySQL and rails to use safely, so I'd rather err on the side of predictable performance rather than protecting developers from themselves, hence the desire to make this optional. If it definitely doesn't affect MySQL performance, then I could see this being the default.

[ghazel]

Hm. I would be surprised if a DELETE trigger significantly affected the performance of non-DELETE operations. But, I agree with your point about errors being only marginally better than ignoring operations. Re-architecting the application to tombstone the row and fixing all the queries to exclude rows marked as deleted is quite a bit more work than making the migration more robust.

So, which of these is preferred?

• Propagate DELETE automatically via trigger.
• Lock a user-space MySQL lock like "migration_in_progress" which can be checked by the application. (I would personally use this in an ActiveRecord extension and queue the deletions until migration is complete)