MySQL Bugs: #118284: batch update occasionally reports SQLSyntaxErrorException

Bug #118284	batch update occasionally reports SQLSyntaxErrorException
Submitted:	28 May 7:15	Modified:	30 May 11:30
Reporter:	hx x	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.0.21	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
The batch update sql works fine most of the time, but occasionally there will be a SQLSyntaxErrorException. I can guarantee that my sql is correct.

this is my log
 ==>  Preparing: update user_test_v2 set age = ?, address = ? where id = ? ; update user_test_v2 set age = ?, address = ? where id = ? ; update user_test_v2 set age = ?, address = ? where id = ? ; update user_test_v2 set age = ?, address = ? where id = ? ; update user_test_v2 set age = ?, address = ? where id = ? ; update user_test_v2 set age = ?, address = ? where id = ? ; update user_test_v2 set age = ?, address = ? where id = ? ; update user_test_v2 set age = ?, address = ? where id = ? ; update user_test_v2 set age = ?, address = ? where id = ? ; update user_test_v2 set age = ?, address = ? where id = ? ;
 
 ==> Parameters: 2(Integer), null, 5509013510124226446(Long), 2(Integer), null, 5509013510124226422(Long), 2(Integer), null, 5509013510130870381(Long), 2(Integer), null, 5509013510130870379(Long), 2(Integer), null, 5509013510124226428(Long), 2(Integer), null, 2(Long), 2(Integer), null, 3(Long), 2(Integer), null, 4(Long), 2(Integer), null, 5(Long), 2(Integer), null, 6(Long)

Batch update sql can run normally most of the time, but occasionally there will be an error, prompting SQLSyntaxErrorException
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4] with root cause
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

How to repeat:
mysql-connector-java 8.0.21
mysql server 8.0.21

1. Connect to Mysql such as  jdbc:mysql://ip:3306/demo?useSSL=false&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=Hongkong&autoReconnect=true&rewriteBatchedStatements=true
2.Set breakpoint on com.mysql.cj.jdbc.ClientPreparedStatement#execute :346 (Message sendPacket = ((PreparedQuery<?>) this.query).fillSendPacket();)

SQL such as
<update id="updateUser">
        <foreach collection="userTests" item="userTest" index="index" separator=";">
            update user_test_v2
            set
                age = #{userTest.age},
                address = #{userTest.address}
            where id = #{userTest.id}
        </foreach>
    </update>

Suggested fix:
I found other bugs indicating that there may be similar problems when useServerPrepStmts = true cachePrepStmts = true, such as https://bugs.mysql.com/bug.php?id=101560 ,https://bugs.mysql.com/bug.php?id=96623 ,but my config  useServerPrepStmts = false cachePrepStmts = false, and I have the same problem when I upgraded to 9.1.0, so I want to confirm the specific cause and how to solve this problem

useServerPrepStmts = false cachePrepStmts = false

Attachment: 43389ee1866f4a129be82b1ee8bea9b5.png (image/png, text), 179.47 KiB.

I found that when I hit a breakpoint, the position of queryPacket would change. For example, the position of my normal SQL was 3841, but the position at the breakpoint was fixed at 1024, causing SQL truncation and an error.

com.mysql.cj.protocol.a.NativeProtocol#sendQueryPacket add log

Attachment: 1748509586734.png (image/png, text), 80.48 KiB.

com.mysql.cj.protocol.a.SimplePacketSender#send add log

Attachment: 1748509460787.png (image/png, text), 83.59 KiB.

normal sql position log

Attachment: 1748508076532.png (image/png, text), 30.14 KiB.

error sql position log ,position is always 1024

Attachment: 1748508059799.png (image/png, text), 30.70 KiB.

Hello,

Thank you for the bug report.
Could you please provide test case to reproduce this issue at our end?

Regards,
Ashwini Patil

mysql-connector-java 8.0.21
mysql server 8.0.21

How to repeat:

Set breakpoint to stay for 5seconds on com.mysql.cj.jdbc.ClientPreparedStatement#execute :346 (Message sendPacket = ((PreparedQuery<?>) this.query).fillSendPacket();)
and 
com.mysql.cj.protocol.a.NativeProtocal#sendQueryPacket:920(LazyString query = new LazyString(queryBuf,1,(oldPacketPosition-1));

java code
@Test
    public void test() throws SQLException, ClassNotFoundException {
        Connection con = null;
        PreparedStatement ps = null;
        Class.forName("com.mysql.cj.jdbc.Driver");
        con = DriverManager.getConnection("jdbc:mysql://ip:3306/demo?useSSL=false&characterEncoding=utf-8&allowMultiQueries=true&serverTimezone=Hongkong&autoReconnect=true&rewriteBatchedStatements=true","mysql_test","K5fjFZJo");
        List<Long> a = new ArrayList<>();
        a.add(5509013510124226446L);
        a.add(5509013510124226446L);
        a.add(5509013510124226422L);
        a.add(5509013510124226422L);
        a.add(5509013510130870381L);
        a.add(5509013510130870381L);
        a.add(5509013510124226422L);
        a.add(5509013510124226422L);
        a.add(5509013510130870379L);
        a.add(5509013510130870379L);
        a.add(5509013510130870381L);
        a.add(5509013510130870381L);
        a.add(5509013510124226428L);
        a.add(5509013510124226428L);
        a.add(5509013510130870379L);
        a.add(5509013510130870379L);
        a.add(2L);
        a.add(2L);
        a.add(3L);
        a.add(3L);
        a.add(4L);
        a.add(4L);

        ps = con.prepareStatement("update user_test_v2 set age = ?, address = ? where id = ?");
        for (Long id : a) {
            ps.setInt(1, 2);
            ps.setString(2, "3");
            ps.setLong(3, id);
            ps.addBatch();
        }
        ps.executeBatch();

        System.out.println(ps.getUpdateCount());
    }

DDL

	
CREATE TABLE `user_test_v2` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `mobile` varchar(100) DEFAULT NULL,
  `age` int(20) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Thank you for the test case, but the code you are looking at is not at the latest version. Please try with MySQL Connector/J 9.3.0 e report back the results.

I tried version 9.3.0 and had the same problem. In addition, my server is 8.0.21. Will there be other problems if I use the connector of version 9+?

I ran your test case using MySQL Connector/J 9.3.0 with MySQL Server 9.3.0, and it works fine on my end.

In general, the connector and server versions don’t need to match exactly. The recommendation is to use the latest available connector version—currently MySQL Connector/J 9.3.0—which is intended for use with all supported MySQL server series.

To answer your question: MySQL Connector/J 9.3.0 may or may not work reliably with MySQL Server 8.0.21. There’s no official guarantee of compatibility. In cases like this, the connector can usually establish a connection and execute queries, but certain features may not function correctly or may be entirely unsupported.

Could you help me try it with version 8.0.21? Also, is there a breakpoint during execution?

I'm sorry, I can't do that. It must be reproducible using latest releases, otherwise we will always say the solution is to upgrade to latest.

Yes, I did use the debugger with the breakpoints you mentioned, and no issues there either. I must warn you, though, that while in a breakpoint, reading from some objects—namely packet buffers and similar—can cause internal pointers to move leading to wrong subsequent reads. I'm not saying this is the case, but you could be causing the syntax error by doing this.

OK, thanks. By the way, when you breakpoint, will the position be fixed at 1024 like mine?

You are welcome.

No, I didn't see that.