
MySQL Bug #11472: Triggers not executed following foreign key updates/deletes - gyosko
https://bugs.mysql.com/bug.php?id=11472
======
pilif
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.

~~~
SFjulie1
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
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.

~~~
SFjulie1
For apple: jailbreak go to home dir apt-get install sqllitex.y apt-get install
bash find list all the files / dir find the contacts read the man of sqllite
and have it tell gracefully the content of the table Something like this:
[http://beauty-of-
imagination.blogspot.ca/2014/03/backuping-m...](http://beauty-of-
imagination.blogspot.ca/2014/03/backuping-mails-from-iphone.html)

Well it is true mysql is fast ... when it is not a problem.

They sacrificed correctness (stuff about big O notation in worst case) for
benchmarks... and it works ... amazingly well as long as you are not under
heavy load or give up on integrity, or relationship, or correctness.

Amazing. As long as you store data that need not to be transactional, or
relational, or needing integrity mysql is fast. When data matters, or load is
heavy mysql is just not there. It is chaotic, inconsistent, unicode
retarded... full of pits... and resource greedy.

Sure, if you are a masochist, or if you fear to loose your job and need to
elevate pain to the rank of art, then mysql is alright.

I have been sysadmin as long as developer and on both sides of the track mysql
is insanely not consistent. (sysadmin: 400 config parameters, replication that
fear any butterfly flapping its wings near a network cable, dev: collation
behaviour, (clumsy) LDAP integration for auth, inconsistencies...).

I even prefer MSsql to mysql. I have been working almost only in linux
environment since 2000, and I still think MySQL is crap. 10 years of horror
stories with mysql made me hate it as much as taking LSD before trying to fill
your taxes.

~~~
spotman
There is no SQL triggers in the article you post here. Confused at what your
trying to say here. Maybe you can elaborate?

~~~
SFjulie1
well if you ask politely sqlite to show the tables and paginates, then you
have the trigger.

For a short vademecum I was not going to add noise in the post.

------
Twirrim
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&...](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)

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
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-...](http://mysqlserverteam.com/removal-and-deprecation-
in-mysql-5-7/)

We are currently looking into bug 199. The patch itself is probably too large
to backport into a GA release.

------
morgo
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
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
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
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.

------
xyby
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
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.

------
mianos
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.

------
zzzeek
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.

------
icelancer
Classic:

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

------
cremno
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)

------
reisub
Does anyone know if this is fixed in MariaDB?

~~~
AnkhMorporkian
No, it is not.

[https://mariadb.com/kb/en/mariadb/trigger-
limitations/](https://mariadb.com/kb/en/mariadb/trigger-limitations/)

~~~
51Cards
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.

------
walrus
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
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.

~~~
protomyth
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

------
tzakrajs
See you all in another 10 years.

