I'm pretty familiar with the innodb table type.
The problem is that MySQL will allow me to define a table type incorrectly and never corrects me.
create table main (columns go here) with type=inoodb
and it will never throw an error, even though I spelled it wrong. It will then let me declare fks to that table and never complain.
the first complaint / warning I'll get is when I try to roll back a transaction and it informs me that some of my tables aren't the right type.
Now, if I could throw a switch that forced innodb and only innodb tables to be used, that would fix the problem for me. But better error handling here would be nice too. I.e. if you try to declare a table of type inoodb it should error out saying unsupported table type, not just blindly creating a MyISAM table and using that.
But the real issue is lack of things like check constraints and bounds checking. On most flavors of MySQL, this means things like:
mysql> create table test (id int4);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into test values (123456789012302);
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+------------+
| id |
+------------+
| 2147483647 |
+------------+
1 row in set (0.02 sec)
I certainly didn't insert the number 2147483647 into that field, but MySQL put it in for me because it thought that was what I wanted. no warning, no error, nothing, just the wrong data.
In a financial / accounting / ordering system, where getting the right number counts, this is just plain wrong. Plus, it does other weird, non-spec things, like:
mysql> create table test (id1 int, id2 int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values (12,7);
Query OK, 1 row affected (0.00 sec)
mysql> select id1/id2 from test;
+----------+
| id1/id2 |
+----------+
| 1.71 |
+----------+
1 row in set (0.00 sec)
when the answer should be 1. It's integer math. Why did I get a fractional portion. In any SQL compliant database I get back the correct answer.
I don't trust MySQL for math. If there's this many glaring errors that are this easy to find, how many are hiding in seldom used code paths that will jump up to bite my butt later on with rounding errors that cause my accounting to have slowly increasing errors margins.
While triggers and stored procs in PostgreSQL are a great thing, its tendency to "get things right" is a far greater advantage. I don't spend my nights wondering if my data is slowly getting corrupted because of inaccuracies in my database.
