8 ms·

MySQL Bug #11472: Triggers not executed following foreign key updates/deletes

102 points by gyosko 11y ago

reisub 11y ago
Does anyone know if this is fixed in MariaDB?
- AnkhMorporkian 11y ago
  No, it is not. https://mariadb.com/kb/en/mariadb/trigger-limitations/ https://mariadb.com/kb/en/mariadb/trigger-limitations/
  - 51Cards 11y ago
    Probably because they don't want to break their compatibility with mySQL, a decision of theirs I highly appreciate. Being able to drop Maria in for mySQL without having to rework a lot of things has been a huge time saver.
tzakrajs 11y ago
See you all in another 10 years.
pilif 11y ago
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. 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. When feature break this way, people start to distrust them and best practices get adopted that discourage using them, killing the features all together. 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. 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). Or, of course, use a database system whose features work correctly and the principle of least surprise is in effect. PostgreSQL is one of these, by the way.
- kailuowang 11y ago
  > 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 I agree with most of what you said, but in many many use cases data integrity can be maintained in application code (a statically typed system helps), it simply can't be true that all those applications running on NoSql database will have messy data and/or cleanup-scripts.
  - spotman 11y ago
    furthermore, foreign keys are not performant at mass scale on both pg or mysql or any traditional RDBMS. if your trying to do several thousand ops per second, the vector of index contention greatly goes up if there is FKs littered all over your schema. nosql or sql large platforms almost always have some amount of work always ongoing to clean, shuttle, and maintain data integrity. that doesn't make me not a fan of them; for lower transaction rate applications ( which most probably are ), they are a small time investment to have some sanity checking. but it's a right tool for the job thing. this is yet another reason why people dislike FKs in best practice stuff. Edit: Hi down-voters. Curious to hear why I'm being down voted and possibly be offer a rebuttal?
    - ak39 11y ago
      Foreign keys are not convenience artifacts. They are essential for enforcing data correctness. One doesn't simply opt out of their usage simply because one is "not a fan" of foreign keys.
      - spotman 11y ago
        What if it is the difference between your schema working under high transaction rates or not? Or, the difference between buying much more database hardware and having smaller shards to avoid index contention. (which, is $$) Facebooks mysql architecture (at least used to, likely still is) based on this: https://backchannel.org/blog/friendfeed-schemaless-mysql https://backchannel.org/blog/friendfeed-schemaless-mysql Note the simple schema, and lack of FKs. FKs are rarely present in extremely high transactions per second systems that operate on the same few tables. This is due to index contention and locking. I say this with lots of experience working on different production systems that see hundreds of thousands of transactions per second. I have yet to see one arrive at these kind of numbers using foreign keys, unless it's something like a giant shared hosting platform, that is operating on hundreds and hundreds of different tables. (therefore, less index contention) Often, in these shops, data correctness is not validated in realtime in this way, but often in a way that is not in the critical path of answering queries. (more like, eventually consistent). Some places have entire teams for this. To your point on correctness, my experience ranges a lot in areas where correctness can be eventually consistent. For something like healthcare or banking, you would rather spend the money on way more hardware, because you can't afford correctness to be off. However, with things like globally scaled social apps, this is just not the case. Most people tend to still use Oracle in those situations. Which is legions slower , and legions more expensive than mysql or pgsql.
icelancer 11y ago
Classic: [30 Jun 2005 19:04] Dmitry Lenev We will fix this in 5.1
walrus 11y ago
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.
- nightski 11y ago
  My guess is MySQL has quite a few users who are not technically capable of fixing a deep and involved bug in the heart of a database engine.
  - deleted 11y ago
    [deleted]
  - protomyth 11y ago
    and I would imagine some of the people filing bugs are people who are paying for support given MySQL and, later, Oracle's business model
- lucb1e 11y ago
  As someone who had issues with a 4+ year old bug in FileZilla, can confirm. First time I developed anything serious in C++ and it took me only two evenings to develop a patch. Then some back and forth on the bugtracker, but it made it in soon after. (For those interested, it's the ability to create a new, empty file on the server.)
  - breakingcups 11y ago
    Surely the two issues aren't of the same complexity.
    - deleted 11y ago
      [deleted]
    - lucb1e 11y ago
      I don't know how the trigger system is structured under the hood, but I could imagine it being somewhat comparative if you can properly call them. At least one of the commenters could have tried something or asked for pointers where to find it, that triggers others to actually work on it or at least help them work on it.
Twirrim 11y ago
It would be really awesome if MySQL crew could spend a few months (half a year?) working on paper-cuts: 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 https://bugs.mysql.com/search.php?search_for=&status=Active&... That's all the active bugs against MySQL. Note that this 10 year old bug isn't the oldest. This is the oldest, from March 2003: https://bugs.mysql.com/bug.php?id=199 https://bugs.mysql.com/bug.php?id=199 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. https://bugs.mysql.com/bug.php?id=3052 https://bugs.mysql.com/bug.php?id=3052 This one is great. ROLLBACK in a stored procedure doesn't close the cursor. 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.
- morgo 11y ago
  There has been a lot of focus on this for MySQL 5.7. Just take a look at the list of deprecations and changes: http://mysqlserverteam.com/removal-and-deprecation-in-mysql-5-7/ http://mysqlserverteam.com/removal-and-deprecation-in-mysql-... We are currently looking into bug 199. The patch itself is probably too large to backport into a GA release.
  - deleted 11y ago
    [deleted]
- Nemcue 11y ago
  As if suits ever wanted anything else but new sexy features.
cremno 11y ago
In a few days #20786 will have its 9th birthday. https://www.youtube.com/watch?v=oAiVsbXVP6k https://www.youtube.com/watch?v=oAiVsbXVP6k https://bugs.mysql.com/bug.php?id=20786 https://bugs.mysql.com/bug.php?id=20786
morgo 11y ago
This is caused by a layering problem in MySQL itself: - Triggers are at the SQL Layer - Foreign keys are implemented natively by InnoDB at the storage engine layer It is not as easy to fix as it sounds.
- kstrauser 11y ago
  It's probably not, but I'd rather not have the feature at all than think that it's there and working but actually not doing what I've asked it to. The current short-term fix would be to make it raise an error when you try to create such a trigger, so at least no one would be using it for now.
riffraff 11y ago
Firefox's issue #106400 (osx keychain integration) will turn 14 this year, AppEngine's #3091 (servlet 3.0 support) has already turned 5. I guess every project as a few "we'll fix it some day" tickets.
- jessaustin 11y ago
  Somehow those seem less important. Maybe it's because I cut my teeth on Sybase and Oracle rather than noSQL, but if triggers don't work, I would really hesitate to call that a RDBMS.
- needusername 11y ago
  I don't think it's fair to compare MySQL to GAE/J. MySQL is actively developed by a company, marked and sold with support.
xyby 11y ago
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. MariaDB documents "foreign keys do not activate triggers" as the standard behavior: https://mariadb.com/kb/en/mariadb/trigger-limitations/ https://mariadb.com/kb/en/mariadb/trigger-limitations/ 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.
- LoSboccacc 11y ago
  Well what does SQL standard say about that? Application can just not update, so there is no need for windows level of compatibility management Personally never jumped on the mysql train and can say I'm really glad for that.
zzzeek 11y ago
triggers...bah. how about CHECK constraints? (http://stackoverflow.com/a/2115641/34549 http://stackoverflow.com/a/2115641/34549) The irony is that the workaround for CHECK constraints being silently ignored is to use a trigger.
mianos 11y ago
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: http://bugs.mysql.com/bug.php?id=9090 http://bugs.mysql.com/bug.php?id=9090 "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.

102 points by gyosko 11y ago

reisub 11y ago

Does anyone know if this is fixed in MariaDB?

AnkhMorporkian 11y ago
No, it is not. https://mariadb.com/kb/en/mariadb/trigger-limitations/ https://mariadb.com/kb/en/mariadb/trigger-limitations/
- 51Cards 11y ago
  Probably because they don't want to break their compatibility with mySQL, a decision of theirs I highly appreciate. Being able to drop Maria in for mySQL without having to rework a lot of things has been a huge time saver.

tzakrajs 11y ago

See you all in another 10 years.

pilif 11y ago

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. 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. When feature break this way, people start to distrust them and best practices get adopted that discourage using them, killing the features all together. 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. 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). Or, of course, use a database system whose features work correctly and the principle of least surprise is in effect. PostgreSQL is one of these, by the way.

kailuowang 11y ago
> 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 I agree with most of what you said, but in many many use cases data integrity can be maintained in application code (a statically typed system helps), it simply can't be true that all those applications running on NoSql database will have messy data and/or cleanup-scripts.
- spotman 11y ago
  furthermore, foreign keys are not performant at mass scale on both pg or mysql or any traditional RDBMS. if your trying to do several thousand ops per second, the vector of index contention greatly goes up if there is FKs littered all over your schema. nosql or sql large platforms almost always have some amount of work always ongoing to clean, shuttle, and maintain data integrity. that doesn't make me not a fan of them; for lower transaction rate applications ( which most probably are ), they are a small time investment to have some sanity checking. but it's a right tool for the job thing. this is yet another reason why people dislike FKs in best practice stuff. Edit: Hi down-voters. Curious to hear why I'm being down voted and possibly be offer a rebuttal?
  - ak39 11y ago
    Foreign keys are not convenience artifacts. They are essential for enforcing data correctness. One doesn't simply opt out of their usage simply because one is "not a fan" of foreign keys.
    - spotman 11y ago
      What if it is the difference between your schema working under high transaction rates or not? Or, the difference between buying much more database hardware and having smaller shards to avoid index contention. (which, is $$) Facebooks mysql architecture (at least used to, likely still is) based on this: https://backchannel.org/blog/friendfeed-schemaless-mysql https://backchannel.org/blog/friendfeed-schemaless-mysql Note the simple schema, and lack of FKs. FKs are rarely present in extremely high transactions per second systems that operate on the same few tables. This is due to index contention and locking. I say this with lots of experience working on different production systems that see hundreds of thousands of transactions per second. I have yet to see one arrive at these kind of numbers using foreign keys, unless it's something like a giant shared hosting platform, that is operating on hundreds and hundreds of different tables. (therefore, less index contention) Often, in these shops, data correctness is not validated in realtime in this way, but often in a way that is not in the critical path of answering queries. (more like, eventually consistent). Some places have entire teams for this. To your point on correctness, my experience ranges a lot in areas where correctness can be eventually consistent. For something like healthcare or banking, you would rather spend the money on way more hardware, because you can't afford correctness to be off. However, with things like globally scaled social apps, this is just not the case. Most people tend to still use Oracle in those situations. Which is legions slower , and legions more expensive than mysql or pgsql.

icelancer 11y ago

Classic: [30 Jun 2005 19:04] Dmitry Lenev We will fix this in 5.1

walrus 11y ago

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.

nightski 11y ago
My guess is MySQL has quite a few users who are not technically capable of fixing a deep and involved bug in the heart of a database engine.
- deleted 11y ago
  [deleted]
- protomyth 11y ago
  and I would imagine some of the people filing bugs are people who are paying for support given MySQL and, later, Oracle's business model
lucb1e 11y ago
As someone who had issues with a 4+ year old bug in FileZilla, can confirm. First time I developed anything serious in C++ and it took me only two evenings to develop a patch. Then some back and forth on the bugtracker, but it made it in soon after. (For those interested, it's the ability to create a new, empty file on the server.)
- breakingcups 11y ago
  Surely the two issues aren't of the same complexity.
  - deleted 11y ago
    [deleted]
  - lucb1e 11y ago
    I don't know how the trigger system is structured under the hood, but I could imagine it being somewhat comparative if you can properly call them. At least one of the commenters could have tried something or asked for pointers where to find it, that triggers others to actually work on it or at least help them work on it.

Twirrim 11y ago

It would be really awesome if MySQL crew could spend a few months (half a year?) working on paper-cuts: 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 https://bugs.mysql.com/search.php?search_for=&status=Active&... That's all the active bugs against MySQL. Note that this 10 year old bug isn't the oldest. This is the oldest, from March 2003: https://bugs.mysql.com/bug.php?id=199 https://bugs.mysql.com/bug.php?id=199 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. https://bugs.mysql.com/bug.php?id=3052 https://bugs.mysql.com/bug.php?id=3052 This one is great. ROLLBACK in a stored procedure doesn't close the cursor. 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.

morgo 11y ago
There has been a lot of focus on this for MySQL 5.7. Just take a look at the list of deprecations and changes: http://mysqlserverteam.com/removal-and-deprecation-in-mysql-5-7/ http://mysqlserverteam.com/removal-and-deprecation-in-mysql-... We are currently looking into bug 199. The patch itself is probably too large to backport into a GA release.
- deleted 11y ago
  [deleted]
Nemcue 11y ago
As if suits ever wanted anything else but new sexy features.

cremno 11y ago

In a few days #20786 will have its 9th birthday. https://www.youtube.com/watch?v=oAiVsbXVP6k https://www.youtube.com/watch?v=oAiVsbXVP6k https://bugs.mysql.com/bug.php?id=20786 https://bugs.mysql.com/bug.php?id=20786

morgo 11y ago

This is caused by a layering problem in MySQL itself: - Triggers are at the SQL Layer - Foreign keys are implemented natively by InnoDB at the storage engine layer It is not as easy to fix as it sounds.

kstrauser 11y ago
It's probably not, but I'd rather not have the feature at all than think that it's there and working but actually not doing what I've asked it to. The current short-term fix would be to make it raise an error when you try to create such a trigger, so at least no one would be using it for now.

riffraff 11y ago

Firefox's issue #106400 (osx keychain integration) will turn 14 this year, AppEngine's #3091 (servlet 3.0 support) has already turned 5. I guess every project as a few "we'll fix it some day" tickets.

jessaustin 11y ago
Somehow those seem less important. Maybe it's because I cut my teeth on Sybase and Oracle rather than noSQL, but if triggers don't work, I would really hesitate to call that a RDBMS.
needusername 11y ago
I don't think it's fair to compare MySQL to GAE/J. MySQL is actively developed by a company, marked and sold with support.

xyby 11y ago

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. MariaDB documents "foreign keys do not activate triggers" as the standard behavior: https://mariadb.com/kb/en/mariadb/trigger-limitations/ https://mariadb.com/kb/en/mariadb/trigger-limitations/ 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.

LoSboccacc 11y ago
Well what does SQL standard say about that? Application can just not update, so there is no need for windows level of compatibility management Personally never jumped on the mysql train and can say I'm really glad for that.

zzzeek 11y ago

triggers...bah. how about CHECK constraints? (http://stackoverflow.com/a/2115641/34549 http://stackoverflow.com/a/2115641/34549) The irony is that the workaround for CHECK constraints being silently ignored is to use a trigger.

mianos 11y ago

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: http://bugs.mysql.com/bug.php?id=9090 http://bugs.mysql.com/bug.php?id=9090 "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.