Batch inserts with duplicate treatment only perform 1 insert

[FunMiles]

connector version 2.5.5

I have been using batch inserts and they've been working fine when not dealing with unique key conflicts.
However, I had to start dealing with conflicts on keys. I have tried both:

 const res = await conn.batch("INSERT IGNORE INTO mytable(a, b, c)" +
                " VALUES(?,?,?);", entries);

and

const res = await conn.batch("INSERT INTO mytable(a,b,c)" +
                " VALUES(?,?,?) ON DUPLICATE KEY UPDATE c='duplicate';", entries);

Where entries is an array of arrays as expected by the batch.
Initially, I allowed duplicate values for the (a,b) pairs, but I needed to enforce uniqueness.
When there was no uniqueness constraint, everything worked fine. With the constraint, suddenly, the insert will go through the work on the duplicates before any unique insert but will stop after inserting the first non-duplicate new data.

If I run multiple times the batch insert with the same entries, the res variable will go something like this:

• first time: { affectedRows: 1, insertId: 11840, warningStatus: 0 }
• second time: { affectedRows: 2, insertId: 11841, warningStatus: 0 }
• third time: { affectedRows: 3, insertId: 11842, warningStatus: 0 }
• etc

Note that if I try:

const n = <some yet not inserted entry number>;
const res = await conn.query("INSERT INTO mytable(a,b,c)" +
                " VALUES(?,?,?),(?,?,?) ON DUPLICATE KEY UPDATE c='duplicate';", [...entries[n], ... entries[n+1]]);

then 2 new rows will be inserted. So it seems the issue is with the 'batch' command not working correctly and not something on the mariadb side of things.

[FunMiles]

Strange thing: I have another table on which the problem does not happen.
Any suggestion on how to diagnose what is going on would be helpful.

[rusher]

That is then a server issue, not a connector one, but in order to identify the issue, I've tryed on my side without reproducing this kind of error.
Could you share the DDL of the offending table and server version ?

[FunMiles]

Thanks for the help, @rusher
The version is a docker version 10.6.4-MariaDB-1:10.6.4+maria~focal-log
The table has the following description:

+-----------+----------+------+-----+---------+----------------+
| Field     | Type     | Null | Key | Default | Extra          |
+-----------+----------+------+-----+---------+----------------+
| id        | int(11)  | NO   | PRI | NULL    | auto_increment |
| fromIATA  | tinytext | NO   | MUL | NULL    |                |
| toIATA    | tinytext | NO   |     | NULL    |                |
| airline   | text     | YES  | MUL | NULL    |                |
| flightNum | int(11)  | YES  |     | NULL    |                |
| depDay    | date     | YES  |     | NULL    |                |
| depTime   | time     | YES  |     | NULL    |                |
| duration  | int(11)  | YES  |     | NULL    |                |
| aircraft  | text     | YES  |     | NULL    |                |
| ops       | longtext | YES  |     | NULL    |                |
| source    | char(1)  | YES  |     | F       |                |
+-----------+----------+------+-----+---------+----------------+

The indices:

MariaDB [flightdb]> show index from flights;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| flights |          0 | id         |            1 | id          | A         |       18571 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| flights |          0 | airline_3  |            1 | airline     | A         |         476 |        2 | NULL   | YES  | BTREE      |         |               | NO      |
| flights |          0 | airline_3  |            2 | flightNum   | A         |       18571 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| flights |          0 | airline_3  |            3 | fromIATA    | A         |       18571 |        4 | NULL   |      | BTREE      |         |               | NO      |
| flights |          0 | airline_3  |            4 | toIATA      | A         |       18571 |        4 | NULL   |      | BTREE      |         |               | NO      |
| flights |          0 | airline_3  |            5 | depDay      | A         |       18571 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| flights |          1 | airline    |            1 | airline     | A         |         476 |        2 | NULL   | YES  | BTREE      |         |               | NO      |
| flights |          1 | airline_2  |            1 | airline     | A         |         476 |        2 | NULL   | YES  | BTREE      |         |               | NO      |
| flights |          1 | airline_2  |            2 | flightNum   | A         |       18571 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| flights |          1 | fromIATA_2 |            1 | fromIATA    | A         |        1238 |        4 | NULL   |      | BTREE      |         |               | NO      |
| flights |          1 | fromIATA_2 |            2 | toIATA      | A         |        9285 |        4 | NULL   |      | BTREE      |         |               | NO      |
| flights |          1 | fromIATA_2 |            3 | depDay      | A         |       18571 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
| flights |          1 | fromIATA   |            1 | fromIATA    | A         |        1238 |      255 | NULL   |      | BTREE      |         |               | NO      |
| flights |          1 | fromIATA   |            2 | toIATA      | A         |        9285 |      255 | NULL   |      | BTREE      |         |               | NO      |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+

And two triggers on the table:

MariaDB [flightdb]> show triggers;
+----------------------+--------+-----------+----------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+
| Trigger              | Event  | Table     | Statement                                          | Timing | Created                | sql_mode                                                                                  | Definer  | character_set_client | collation_connection | Database Collation |
+----------------------+--------+-----------+----------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+
| mark_update          | INSERT | flights   | insert ignore into flight_update values (NEW.id)   | AFTER  | 2021-11-17 18:36:36.94 | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | svelte@% | utf8mb3              | utf8mb3_general_ci   | utf8mb4_general_ci |
| mark_insert          | INSERT | flights   | insert ignore into flight_update values (NEW.id)   | AFTER  | 2021-11-17 21:39:58.78 | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | svelte@% | utf8mb4              | utf8mb4_unicode_ci   | utf8mb4_general_ci |
+----------------------+--------+-----------+----------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------+----------+----------------------+----------------------+--------------------+

[rusher]

ok, reproduced.
Like i've state, this is a server issue and i've found that a corresponding issue exists for server : https://jira.mariadb.org/browse/MDEV-24411

The only possible workaround is to disable connection bulk option when there is triggers involved.

closing issue here. please follow https://jira.mariadb.org/browse/MDEV-24411 for more information