MySQL's INSERT IGNORE INTO & foreign keys

[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 think the simplest, mysql solution to the problem is joining into the foreign key table to verify constraints:

INSERT INTO child (parent_id, value2, value3)
SELECT p.id, @new_value2, @new_value3
FROM parent p WHERE p.id = @parent_id

But it seems strange that you want to throw away records based on missing foreign keys. I, for instance, prefer having INSERT IGNORE error on foreign key checks.

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