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]