Re: Problem with rownum

On Thu, 13 Jan 2005 22:37:08 -0800, DA Morgan <damorgan_at_x.washington.edu> wrote:

>Don't get me wrong MySQL has its place and I have used it where
>appropriate but a line-of-business transactional system? Not while I
>value what little reputation I have.
>
>Got any problems with this: http://sql-info.de/mysql/gotchas.html in
>terms of accuracy?

 That looks like a pretty decent list of MySQL quirks.

>When I refer to referential integrity I don't mean the fact that they
>finally figured out what it is but rather the full purpose it serves in
>a "real" database. Take a look, for example, at item 3.2 at the link
>above. That to me is not referential integrity.

 Like it says, fixed in 4.0.18 (February 2004), and 4.1 is the current version now. I haven't got 4.1 handy, just 4.0.21, which prevents the drop (but with an inappropriate error message):

mysql> create table t1 (c int not null primary key) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec)

mysql> create table t2 (c int not null primary key, index(c),

    -> foreign key (c) references t1(c)) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t2 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table t1;
ERROR 1051: Unknown table 't1'

mysql> drop table t2;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.04 sec)

 And besides, the original problem is only during DDL, not it ignoring foreign keys during DML. Basically it looks like it used to always have the equivalent of "cascade constraints" turned on. And now it doesn't.

 Now, the one thing that puts me off MySQL is not transactions, RI (in the sense of foreign keys) or backup (at least not any more), but the issue described by 1.13 from that site:

 http://sql-info.de/mysql/gotchas.html#1_13

 I see in 4.1 they've at least put a warning mechanism in, but it should be raising errors.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool