MySQL's INSERT IGNORE INTO & foreign keys

Why in MySQL, INSERT IGNORE INTO does not change the foreign key constraint 

errors into warnings?

I'm trying to insert a number of records into a table and I expect MySQL to leave out 

the ones that result in error, any error, and insert the rest. Does anyone have any 

suggestions?

And the SET FOREIGN_KEY_CHECKS = 0; is not my answer. Because I expect the

 rows which defy the constraints not to be inserted at all.

 Answers

[NEW ANSWER]

Thanks to @NeverEndingQueue for bringing this up. It seems MySQL has finally 

fixed this issue. I'm not sure which version this problem was first fixed in, but right 

now I tested with the following version and the problem is not there anymore:

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.22                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.22                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

To be clear:

mysql> INSERT IGNORE INTO child
    -> VALUES
    ->     (NULL, 1)
    ->     , (NULL, 2)
    ->     , (NULL, 3)
    ->     , (NULL, 4)
    ->     , (NULL, 5)
    ->     , (NULL, 6);
Query OK, 4 rows affected, 2 warnings (0.03 sec)
Records: 6  Duplicates: 2  Warnings: 2

To better understand the meaning of this last query and why it shows the problem 

is fixed, please continue with the old answer below.

[OLD ANSWER]

My solution is a work around to the problem and the actual solution will always be

 fixing the problem within the MySQL itself.

The following steps solved my problem:

a. Consider having the following tables and data:

mysql>
CREATE TABLE parent (id INT AUTO_INCREMENT NOT NULL
                     , PRIMARY KEY (id)
) ENGINE=INNODB;

mysql>
CREATE TABLE child (id INT AUTO_INCREMENT
                    , parent_id INT
                    , INDEX par_ind (parent_id)
                    , PRIMARY KEY (id)
                    , FOREIGN KEY (parent_id) REFERENCES parent(id)
                        ON DELETE CASCADE
                        ON UPDATE CASCADE
) ENGINE=INNODB;

mysql>
INSERT INTO parent
VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);

mysql>
SELECT * FROM parent;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+

b. Now we need to delete some of the rows to demonstrate the problem:

mysql>
DELETE FROM parent WHERE id IN (3, 5);

c. PROBLEM: The problem arises when you try to insert the following child rows:

mysql>
INSERT IGNORE INTO child
VALUES
    (NULL, 1)
    , (NULL, 2)
    , (NULL, 3)
    , (NULL, 4)
    , (NULL, 5)
    , (NULL, 6);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERE
NCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

mysql>
SELECT * FROM child;
Empty set (0.00 sec)

Even though the IGNORE keyword is used, but MySQL cancels the the requested 

operation because the generated error is not turned into warning (as it supposed to). 

Now that the problem is obvious, let's see how can we execute the last insert into

 statement without facing any error.

d. SOLUTION: I'm going to wrap the insert into statement by some other constant 

statements which are neither dependent on the records inserted, nor on their number.

mysql>
SET FOREIGN_KEY_CHECKS = 0;

mysql>
INSERT INTO child
VALUES
    (NULL, 1)
    , (NULL, 2)
    , (NULL, 3)
    , (NULL, 4)
    , (NULL, 5)
    , (NULL, 6);

mysql>
DELETE FROM child WHERE parent_id NOT IN (SELECT id FROM parent);

mysql>
SET FOREIGN_KEY_CHECKS = 1;

I know that this is not optimum but as long as MySQL has not fixed the problem,

 this is the best I know. Especially since all the statements can be executed in one 

request if you use mysqli library in PHP.

---

I believe INSERT IGNORE is intended to ignore errors from the server layer, not the 

storage engine layer. So it will help for duplicate key errors (it's primary use case) and 

certain data conversions, but not foreign key errors, which come from the storage engine layer.

As for your specific requirement:

I'm trying to insert a number of records into a table and I expect MySQL to

 leave out the ones that produce error, any error, and insert the rest. 

Does anyone have any suggestions?

For that I recommend using mysql -f to force it to keep running despite any errors. 

So for example, if you have a file like this:

insert into child (parent_id, ...) values (bad_parent_id, ...);
insert into child (parent_id, ...) values (good_parent_id, ...);

Then you can load that file like so, which will insert the good rows and ignore the error

 from the bad rows:

mysql -f < inserts.sql

---

INSERT IGNORE is just a workaround for the lazy. You shouldn't be inserting duplicates 

records in the first place. Besides a Primary/Unique is not the same as a Foreign key.

 And have in mind that IGNORE will also ignore other error and warnings (division by zero,

 data truncations), which usually is not a good thing.

In this case, and almost every time, it makes more sense to use REPLACE instead of 

INSERT IGNORE. Another option is ON DUPLICATE KEY UPDATE.

In your case, if you can't remove the missing parents before the insert, you can manage 

the same thing with a temporary table like this:

create temporary table tmpTable (col1, col2, ...);
insert into tmpTable values (row1), (row2), ...; -- same insert you already had
alter table tmpTable add key (foreignColumnName); -- only if # of rows is big
insert into table select null /* AI id */, col1, col2 ...
  from tmpTable join parentTable on foreignColumnName = parent.id;