6 ms·

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 yo

by 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.
      - ak39 11y ago
        >To your point on correctness, my experience ranges a > lot in areas where correctness can be eventually consistent. "Eventually consistent"? Explain. (Do you mean in an ACID transaction?)
SFjulie1 11y ago
Apple uses MVC with sqlite for the contacts. The view is cocoa, but every events int the GUI are calling triggers in sqlite. It makes apple's application simple stupid and efficient. And they have triggers on foreign keys... with sqlite. ... It is not postgres in one of it, it is much more mysql is not a correct rdbms. None of the other databases have this flaw. Mysql is to RDBMS what mongo is to NoSQL, nowhere short of achieving anything that matters correctly. EDIT: or better: Mysql is a realistic in the Hollywood way implementation of a RDBMS
- spotman 11y ago
  mysql is good for very high transaction rate platforms, that mostly rely on simple features. for this, it generally outperforms most of its competition in the traditional RDBMS space, and why companies like twitter and facebook use it. mysql likes simple, well designed queries. postgres usually outperforms mysql for more complex operations, but is not as fast as mysql for simple ones. sqlite is really only appropriate for things like mobile or desktop applications or things with generally low concurrency requirements. i would be careful throwing the baby out with the bath water in your dismissal of mysql, but thats just my opinion:) can you shed some more detail on that apple uses triggers with sqlite? that is interesting to me, and would like to learn more.
  - contingencies 11y ago
    sqlite is really only appropriate for things like mobile or desktop applications or things with generally low concurrency requirements. I think what you mean here is low concurrent write requirements. (Edit in response to below: Isn't sqlite file oriented and read-only opens eschew locking? Thus replication via network filesystems should be adequate, no?)
    - spotman 11y ago
      Yes, agreed, if we are talking about straight concurrency only. Other things like replication, are also not there, so it's not what I would choose for a website database, but if you can serve all your reads from one server, and don't care about high availability, then go for it.

by pilif 11y ago

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.
    - ak39 11y ago
      >To your point on correctness, my experience ranges a > lot in areas where correctness can be eventually consistent. "Eventually consistent"? Explain. (Do you mean in an ACID transaction?)

SFjulie1 11y ago

Apple uses MVC with sqlite for the contacts. The view is cocoa, but every events int the GUI are calling triggers in sqlite. It makes apple's application simple stupid and efficient. And they have triggers on foreign keys... with sqlite. ... It is not postgres in one of it, it is much more mysql is not a correct rdbms. None of the other databases have this flaw. Mysql is to RDBMS what mongo is to NoSQL, nowhere short of achieving anything that matters correctly. EDIT: or better: Mysql is a realistic in the Hollywood way implementation of a RDBMS

spotman 11y ago
mysql is good for very high transaction rate platforms, that mostly rely on simple features. for this, it generally outperforms most of its competition in the traditional RDBMS space, and why companies like twitter and facebook use it. mysql likes simple, well designed queries. postgres usually outperforms mysql for more complex operations, but is not as fast as mysql for simple ones. sqlite is really only appropriate for things like mobile or desktop applications or things with generally low concurrency requirements. i would be careful throwing the baby out with the bath water in your dismissal of mysql, but thats just my opinion:) can you shed some more detail on that apple uses triggers with sqlite? that is interesting to me, and would like to learn more.
- contingencies 11y ago
  sqlite is really only appropriate for things like mobile or desktop applications or things with generally low concurrency requirements. I think what you mean here is low concurrent write requirements. (Edit in response to below: Isn't sqlite file oriented and read-only opens eschew locking? Thus replication via network filesystems should be adequate, no?)
  - spotman 11y ago
    Yes, agreed, if we are talking about straight concurrency only. Other things like replication, are also not there, so it's not what I would choose for a website database, but if you can serve all your reads from one server, and don't care about high availability, then go for it.