Hi there,
I'm finding that DELETE IGNORE doesn't actually ignore all errors when using
InnoDb and trying to remove rows that would result in a foreign key error.
I've checked the docs and think that what I'm doing should work, but
doesn't - I'm using server 4.1.4-gamma:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 820 to server version: 4.1.4-gamma-standard-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> DROP TABLE IF EXISTS b,a;
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE a
-> (
-> id INTEGER NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY(id)
-> )
-> ENGINE=InnoDb;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE b
-> (
-> aid INTEGER NOT NULL,
-> PRIMARY KEY(aid),
-> FOREIGN KEY (aid) REFERENCES a(id)
-> )
-> ENGINE=InnoDb;
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO a() VALUES();
Query OK, 1 row affected (0.29 sec)
mysql> INSERT INTO b SELECT * FROM a;
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> INSERT INTO a(id) VALUES(1024);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM a;
+------+
| id |
+------+
| 1 |
| 1024 |
+------+
2 rows in set (0.00 sec)
mysql> SELECT * FROM b;
+-----+
| aid |
+-----+
| 1 |
+-----+
1 row in set (0.00 sec)
mysql> DELETE IGNORE FROM a;
ERROR 1105 (HY000): Unknown error
mysql>
I'm actually being lazy and just trying to mop up rows that are not
referenced anymore. I would use ON DELETE CASCADE, except that the
constraint is pointing the wrong way around in my application (I may have to
fix this).
Anyone know if DELETE IGNORE should work? Will it work in later releases of
4.1, although I don't see reference to this problem in the 4.1.5-gamma
changelog?
Thanks,
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
