[CONJ-1086] Batch insert inserts only the first row when there is a trigger Created: 2023-06-09  Updated: 2023-06-29

Status: Open
Project: MariaDB Connector/J
Component/s: JDBC 4.2 compatibility
Affects Version/s: 3.0.10, 3.1.4
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Elyorbek Ibrokhimov Assignee: Diego Dupin
Resolution: Unresolved Votes: 0
Labels: Compatibility, bug

Issue Links:
Problem/Incident
is caused by MDEV-24411 Trigger doesn't work correctly with b... Stalled

 Description   

Stackoverflow question

I have a trigger that inserts into sku_price_history table after inserting into sku table:
DELIMITER $$
CREATE TRIGGER trig_after_sku_insert
AFTER INSERT
ON sku FOR EACH ROW
BEGIN
INSERT INTO sku_price_history(sku_id, action_type, purchase_price, selling_price, base_margin_rate,
vat, vat_type, tax_type, selling_price_started_at, updated_by)
VALUES (NEW.id, 'CREATE', NEW.purchase_price, NEW.selling_price, NEW.base_margin_rate,
NEW.vat, NEW.vat_type, NEW.tax_type, NOW(), NEW.created_by);
END $$
DELIMITER ;

When I run batch inserts into sku table using PreparedStatement.addBatch() and PreparedStatement.executeBatch() it inserts only the first row. However, if I remove the trigger all the rows are inserted correctly.



 Comments   
Comment by Diego Dupin [ 2023-06-09 ]

MariaDB use a special command for batch COM_STMT_BULK_EXECUTE that permit super fast batching.
Issue corresponds to MDEV-24411.

As a workaround, you can disable bulk option by setting `&useBulkStmts=false` to connection string.

Generated at Thu Feb 08 03:20:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.