MySQL Bugs: #62209: InnoDB NOT ACID COMPLIANT ???

Bug #62209	InnoDB NOT ACID COMPLIANT ???
Submitted:	19 Aug 2011 9:09	Modified:	20 Aug 2011 11:29
Reporter:	Morg. What?	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.5.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
I've read so far (mostly from MySQL sources) that in fact triggers in InnoDB cannot be fired on actions internal to the dbms, such as other triggers or perhaps cascades.

If this is correct, doesn't that imply that InnoDB does not meet the "C" criterion of ACID compliance ?

Furthermore, in the behaviour of the engine itself, I've observed that some perfectly standard foreign key declarations are silently ignored by the dbms, while that is not exactly related to ACID, it sorts of point in the same direction of "lack of precision/reliability".

Also, I've read that foreign keys in InnoDB would not be enforced in some cases of actions internal to the dbms (i.e. not statements), is that still true ? 

Because this would be yet another breach of the "C".

So:
a) Are these informations correct and correctly explained here ?
b) Does this not compromise ACID compliancy ?

How to repeat:
Err .. it's your DBMS I believe you have the best examples to show those behaviours.

Obviously I misunderstood the following statement :

MySQL triggers are activated by SQL statements only. They are not activated by changes in views, nor by changes to tables made by APIs that do not transmit SQL statements to the MySQL Server. This means that triggers are not activated by changes in INFORMATION_SCHEMA or performance_schema tables, because these tables are actually views. 

So this does not (fortunately) apply in the case of normal SQL statements, but it still implies that any MySQL Server that processes changes made by APIs is not ACID compliant, is this correct ?

Does this also apply to InnoDB ?

Is it not impossible to create triggers on views in MySQL anyway ?

And here is the other one I was searching for : 

"
Note

Currently, cascaded foreign key actions do not activate triggers.
"

Does this not invalidate ACID compliance ?

In your very first sentence that reads "I've read so far (mostly from MySQL sources) that in fact triggers in InnoDB cannot be fired on actions internal to the dbms, such as other triggers or perhaps cascades" you should remove "such as other triggers".  

This snippeet shows that this is not a problem: 

CREATE DATABASE trigtest;
USE trigtest; 
CREATE TABLE a(id INT);
CREATE TABLE b(id INT);
CREATE TABLE c(id INT);

DELIMITER $$

CREATE TRIGGER a_on_b AFTER INSERT
    ON trigtest.a
    FOR EACH ROW BEGIN
	INSERT INTO b VALUES (1);
    END$$
    
CREATE TRIGGER b_on_c AFTER INSERT
    ON trigtest.a
    FOR EACH ROW BEGIN
	INSERT INTO c VALUES (1);
    END$$

DELIMITER ;

INSERT INTO a VALUES (1);

SELECT * FROM c;
-- note '1' was inserted to table `c` by trigger `b_on_c`

Peter
(not a MySQL person)

We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

MySQL has options which affect its behavior. Bug database is not proper place to discuss them.

Only valid request I see here is complain about triggers which are not called when cascade foreign key operations are used. But this case is already reported as bug #11472 Please subscribe to bug #11472 to get updates when this feature is finally implemented.

Sorry Peter, my first post was unclear and based on half-remembered stuff, which is why I added clarification afterwards.

Sveta, how much would it cost you to admit that in fact InnoDB is NOT ACID compliant and you should remove that from the feature list ?

Feature list says : ACID compliant
Manual remarks say : NOT ACID compliant
I say : is this a bug ?
You say : no, mysql is not acid compliant, and this is not a bug.

Fine by me . but I find it really annoying that I should search for that information when your feature list could simply have listed "ACID 'C' not compliant in the case of triggers or API use".