[CONJ-1106] executeBatch() uses same sequence for all records Created: 2023-08-31  Updated: 2023-09-06  Resolved: 2023-09-06

Status: Closed
Project: MariaDB Connector/J
Component/s: batch
Affects Version/s: 3.2.0, 3.1.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Francisco Canovas Camino Assignee: Diego Dupin
Resolution: Duplicate Votes: 1
Labels: None

Attachments: PNG File sequence.png    
Issue Links:
Problem/Incident
is caused by MDEV-23864 BULK insert wrong values when command... Open

 Description   

We use the driver of mariadb to do a batch insert with the following query:

  INSERT INTO PROCESS_ENTITY_STATUS(PROCESS_ENTITY_STATUS_ID, PROCESS_ID, \
 
  ENTITY_ID, DATA_ID, STATUS, ATTEMPTS, LAST_TRANSACTION_ID, LAST_TRANSACTION_ATTEMPT, CREATED_DATE, CREATED_USER ) \
 
  VALUES ((SELECT NEXTVAL(SEQ_PROCESS_ENTITY_STATUS)),:processId, \
 
  :entityId, :dataId, :status, :attempts, :lastTransactionId, :lastTransactionAttempt, \
 
  :createdDate, :createdUser)

We use a sequence for one of the values:

SELECT NEXTVAL(SEQ_PROCESS_ENTITY_STATUS)

What we found out, is that when the insert is done we see that the three records we are inserting in batch mode, have the same sequence value. Find attached the file.

This does not happen with the version 2.7.10 (we have confirmed it), it is happening from 3.0.3 above.

 Comments   
Comment by Diego Dupin [ 2023-08-31 ]

it seems related to https://jira.mariadb.org/projects/MDEV/issues/MDEV-23864
Until MDEV-23864 resolution, workaround is to disable useBulkStmts option , using option string like

jdbc:mariadb://somehost/db?user=myUser&useBulkStmts=false

and please vote for MDEV-23864 reolution

Comment by Julio Gomez [ 2023-09-04 ]

@diego.dupin I am not sure if that's the same as MDEV-23864, because with the same MariaDB server instance, the 2.7.x JDBC driver (maven coordinates: org.mariadb.jdbc:mariadb-java-client:2.7.x) works OK with the aforementioned operation but with 3.x driver (maven coordinates: org.mariadb.jdbc:mariadb-java-client:3.x.y) it does not work as expected. So I guess it's not a server-related issue or it's not a server-only related issue.

This is affecting our productive projects.

Comment by Diego Dupin [ 2023-09-04 ]

In 2.7.x version, option `useBulkStmts` default to false, so doesn't use BULK and MDEV-23864.

Comment by Julio Gomez [ 2023-09-05 ]

Thanks for your explanation @Diego Dupin. It's clear now. Any estimate for the bugfix?

Comment by Diego Dupin [ 2023-09-06 ]

That's a server side issue, better ask on MDEV or propose a fix for that

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