if you rely on triggers for data integrity, then I guess this is one more case where MySQL is letting you down and allowing your data to get corrupted unless you're really careful.<p>While it's not always good design to use triggers for this, sometimes, it's a valid reason to use them for integrity checking or enforcing. Having `on delete` triggers not run for some delete's is violating the principle of least surprise.<p>When feature break this way, people start to distrust them and best practices get adopted that discourage using them, killing the features all together.<p>Using a database that does not have this misfeature, both triggers and foreign keys are perfectly safe to use, but because the one database that is the most widely used has issues like the one described here, you often hear the recommendation to not use triggers or even foreign keys.<p>Yes, you can potentially move the logic normally contained in either of them into the application, but as you deal with more concurrency and as you start accumulating bugs, sooner or later your data will be messy and you will need to add cleanup-scripts, or, heaven forbid, work around messy data in your application logic (don't. do. that. it's the path to the dark side of unmaintainability).<p>Or, of course, use a database system whose features work correctly and the principle of least surprise is in effect.<p>PostgreSQL is one of these, by the way.
It would be really awesome if MySQL crew could spend a few months (half a year?) working on paper-cuts:<p><a href="https://bugs.mysql.com/search.php?search_for=&status=Active&severity=&limit=10&order_by=&cmd=display&direction=ASC&os=0&phpver=&bug_age=0" rel="nofollow">https://bugs.mysql.com/search.php?search_for=&status=Active&...</a><p>That's all the active bugs against MySQL. Note that this 10 year old bug isn't the oldest.<p>This is the oldest, from March 2003: <a href="https://bugs.mysql.com/bug.php?id=199" rel="nofollow">https://bugs.mysql.com/bug.php?id=199</a>
Note someone submitted a fix for that over a year ago (and verified under the OCA in November) but it still hasn't shipped. This is a bug that should never have taken 11 years to fix.<p><a href="https://bugs.mysql.com/bug.php?id=3052" rel="nofollow">https://bugs.mysql.com/bug.php?id=3052</a> This one is great. ROLLBACK in a stored procedure doesn't close the cursor.<p>MySQL, for all its strengths, often feels like there is a bunch of typical geeks running the show. Lots of focus on the 'sexy' new features, not so much focus on keeping the lights on.
This is caused by a layering problem in MySQL itself:<p>- Triggers are at the SQL Layer<p>- Foreign keys are implemented natively by InnoDB at the storage engine layer<p>It is not as easy to fix as it sounds.
Firefox's issue #106400 (osx keychain integration) will turn 14 this year, AppEngine's #3091 (servlet 3.0 support) has already turned 5.<p>I guess every project as a few "we'll fix it some day" tickets.
The question is if one should "fix" this at all. If you change the behavior from "foreign keys do not activate triggers" to "foreign keys activate triggers", everyone who uses triggers will have to audit their applications for potential problems. And for large applications, that can be a lot of work.<p>MariaDB documents "foreign keys do not activate triggers" as the standard behavior:<p><a href="https://mariadb.com/kb/en/mariadb/trigger-limitations/" rel="nofollow">https://mariadb.com/kb/en/mariadb/trigger-limitations/</a><p>Personally, I am grateful for software that changes as seldom as possible. I don't want to spend time on "updating" my application because something down in the stack changed.
Mysql has its uses. Keeping a list of people you don't know on a social network is a great use case. Recording cat video URLS is another. Keeping track of money is not a good use case for mysql. Simple as that.
My favourite is this one is the correlated subquery bad query plan selection: <a href="http://bugs.mysql.com/bug.php?id=9090" rel="nofollow">http://bugs.mysql.com/bug.php?id=9090</a> "It's not a bug, it's a feature". You can always find a way to work around this stuff, or you can use postgres or another db that considers thi stuff important.
triggers...bah. how about CHECK constraints? (<a href="http://stackoverflow.com/a/2115641/34549" rel="nofollow">http://stackoverflow.com/a/2115641/34549</a>) The irony is that the workaround for CHECK constraints being silently ignored is to use a trigger.
In a few days #20786 will have its 9th birthday.<p><a href="https://www.youtube.com/watch?v=oAiVsbXVP6k" rel="nofollow">https://www.youtube.com/watch?v=oAiVsbXVP6k</a><p><a href="https://bugs.mysql.com/bug.php?id=20786" rel="nofollow">https://bugs.mysql.com/bug.php?id=20786</a>
I don't understand some of the people commenting on the bug report. If they really want it fixed, then they should fix it. Making rude comments isn't going to make it go any faster.