[CONJ-1035] PreparedStatement Insert regression - java.sql.SQLSyntaxErrorException: (conn=952) Incorrect datetime value: '2022-12-16 16:21:25.7134102022-12-16 16:21:25.713410' Created: 2022-12-16  Updated: 2022-12-23  Resolved: 2022-12-21

Status: Closed
Project: MariaDB Connector/J
Component/s: Other
Affects Version/s: 3.1.0
Fix Version/s: 3.1.1

Type: Bug Priority: Critical
Reporter: Cheong Chung Onn Assignee: Diego Dupin
Resolution: Fixed Votes: 0
Labels: regression
Environment:

Ubuntu 20.04, Mariadb 10.6.11, Java 17

Issue Links:
Duplicate
is duplicated by CONJ-1029 After upgrading to MariaDB JDBC Drive... Closed
is duplicated by CONJ-1036 org.mariadb.jdbc.client.socket.impl.P... Closed

 Description   

Use PreparedStatement to insert 152 tuples of values of 3 elements with contains a LocalDateTime element. Mariadb returns a SQLSyntaxErrorException. The same code works for version 3.0.8.

However, the insert statement will work for values right up to 151 tuples.

Here is the preparestatement string generated from the Scala code listed below.

INSERT INTO Test (`name`, `description`, `created`) VALUES (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?,?,?), (?, ?, ?)

Sample code are written in Scala 3.

```
/*
CREATE TABLE `Test` (
`name` varchar(255) NOT NULL,
`description` varchar(255) DEFAULT NULL,
`created` datetime DEFAULT Now(),
PRIMARY KEY (`name`)
);
*/

val count = 151
val insert = """INSERT INTO Test (`name`, `description`, `created`) VALUES """
val params = "(?,?,?), ".repeat(count) + "(?, ?, ?)"
val indexPlaceholderStatement = insert + params //See above for the actual statement string
val preparedStatement = connection.prepareStatement(indexPlaceholderStatement, java.sql.Statement.RETURN_GENERATED_KEYS)
val now: Any = java.time.LocalDateTime.now
0 to count foreach { i =>
val offset = i * 3
preparedStatement.setString(offset + 1, s"name ${i}")
preparedStatement.setString(offset + 2, s"desc ${i} ")
preparedStatement.setObject(offset + 3, now)
}
preparedStatement.executeUpdate()
connection.commit()
```

 Comments   
Comment by Diego Dupin [ 2022-12-21 ]

thanks for the reproductible case, it helps a lot !
It's corrected with https://github.com/mariadb-corporation/mariadb-connector-j/commit/7399a26026b5562296fe1a104793a7ad8de6905a

correction is available using snapshot : 

<repositories>
 
    <repository>
 
        <id>sonatype-nexus-snapshots</id>
 
        <name>Sonatype Nexus Snapshots</name>
 
        <url>https://oss.sonatype.org/content/repositories/snapshots</url>
 
    </repository>
 
</repositories>
 
 
 
<dependencies>
 
    <dependency>
 
        <groupId>org.mariadb.jdbc</groupId>
 
        <artifactId>mariadb-java-client</artifactId>
 
        <version>3.1.1-SNAPSHOT</version>
 
    </dependency>
 
</dependencies>

Comment by Cheong Chung Onn [ 2022-12-21 ]

I am glad you have help solved it. BTW, we had the same problem for java.time.LocalTime as well. I hope it is the same category of problem.

Again, many thanks resolving the bug

Comment by Cheong Chung Onn [ 2022-12-21 ]

Hi Diego, I just tested using the snapshot and push it to 1500 tuples and it works! Thanks for the speed fix.

Comment by Diego Dupin [ 2022-12-21 ]

about LocalTime, yes, that is the same kind of problem

Comment by Cheong Chung Onn [ 2022-12-21 ]

Noted with thanks!

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