| Bug #114542 | Encoding issue when reusing Prepared Statement | ||
|---|---|---|---|
| Submitted: | 3 Apr 8:36 | Modified: | 3 Apr 13:18 |
| Reporter: | Ian Rolewicz | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Prepared statements | Severity: | S3 (Non-critical) |
| Version: | 8.0.36-commercial | OS: | Any (8) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | date data type, encoding, Latin1, prepared statement, utf8mb4 | ||
[3 Apr 12:18]
MySQL Verification Team
Hi Mr. Rolewicz, Thank you for your bug report. We have run your test case (in SQL and not in Java) with the following releases: * mysql-8.0.32-commercial * mysql-8.0.33-commercial * mysql-8.0.34-commercial * mysql-8.0.35-commercial * mysql-8.0.36-commercial * mysql-8.2.0-commercial * mysql-8.3.0-commercial and in each case we have got the following result: s d NULL 2023-03-01 ü NULL NULL 2023-03-01 ü NULL We tried it also on Oracle Linux and macOS. We are simply not able to repeat it with SQL test case. If you think that this is C/J specific problem, please let us know. Since you are using the Enterprise version of our product, you can also raise an SR on the issue. Can't repeat.
[3 Apr 13:10]
Ian Rolewicz
Thank you for your answer. It might be a Connector/J issue as well, the JDBC connection properties "useCursorFetch=true" and "connectionCollation=latin1_swedish_ci" seem to play a part in this issue. As I noted, we do not have direct access to the MySQL Server console, so we used a client (SQuirrel SQL, which uses Connector/J to connect to the MySQL Server) to send the SQL commands to the server. Could you please try to reproduce the issue with the Java test case ?
[3 Apr 13:18]
MySQL Verification Team
Hi, Actually, you have spotted a problem in your own configuration of the client-side. You have set a connection character set wrongly: connectionCollation=latin1_swedish_ci Hence , your client and connection sides are misconfigured. Configuration of the client and connection side is in the domain of the application programmer and DBA. These are the settings that are under your control. If you read our Reference Manual, you will find out that these are settings that are under your control. Hence, this is not our bug. Not a bug.
Description: Hello, The following error appears after upgrading from version 8.0.32-commercial to version 8.0.36-commercial of MySQL Database. A data integration software we use heavily (written in Java) performs multiple INSERTs by creating a Prepared Statement and reusing it multiple times. The MySQL Connector/J is used to dialog with the MySQL Server. With the following conditions, an INSERT will fail with the error "Incorrect string value (HY000, ErrorCode: 1366)" : - the JDBC connection properties "useCursorFetch=true" and "connectionCollation=latin1_swedish_ci" are set - insert rows by reusing the same Prepared Statement multple times - the inserted rows contain at least a VARCHAR field and a DATE field - values for the DATE field are bound using the method PreparedStatement.setTimestamp() instead of the PreparedStatement.setDate() - the target table has a default charset which is different from the connection charset (here we use latin1 for the connection and the target table is utf8mb4) - a first row is inserted with a NULL value bound to the VARCHAR field and a non-NULL value bound to the DATE field - the next row inserted has a value bound to the VARCHAR field that contains a non-ASCII character (like 'ç' or 'ü'). At this point the INSERT fails. We were able to reproduce the error by writing a simple Java sample code that mimics how our data integration software operates. The error was also reproduced with the use of the SQuirreL SQL Client Version 4.2.0 (same MySQL Connector/J version), by issuing plain SQL commands. We do not have direct access to the MySQL Server console. Here is additionnal info : - MySQL Version : 8.0.36-commercial (no such error with 8.0.32-commercial) - Client Program : SQuirreL SQL Client Version 4.2.0 + Sample Java Code (both running on OpenJDK 11) - MySQL Connector/J versions tested : 8.3.0 and 8.0.29 - JDBC connection string : "jdbc:mysql://[host]:[port]/dev1?useCursorFetch=true&connectionCollation=latin1_swedish_ci" How to repeat: Here is the sample code written in Java : ----------------------------------------- import java.sql.*; public class Main { public static void main(String[] args) { try { Class.forName("com.mysql.cj.jdbc.Driver"); } catch (ClassNotFoundException e) { throw new RuntimeException(e); } String dropTrgtSql = "DROP TABLE IF EXISTS dev1.test"; String createTrgtSql = "CREATE TABLE dev1.test (" + " s varchar(50)" + " ,d date" + ") DEFAULT CHARSET=utf8mb4;"; String insertSql = "INSERT INTO test (s, d) VALUES (?, ?)"; try (Connection con = DriverManager .getConnection("jdbc:mysql://[host]:[port]/dev1?useCursorFetch=true&connectionCollation=latin1_swedish_ci" , "user", "password")) { // Execute the drop and create statements Statement stmt = con.createStatement(); stmt.executeUpdate(dropTrgtSql); stmt.executeUpdate(createTrgtSql); // Prepare the insert statements PreparedStatement pstmt = con.prepareStatement(insertSql); // Set the first row data and execute pstmt.setNull(1, java.sql.Types.VARCHAR ); pstmt.setTimestamp(2, Timestamp.valueOf("2020-01-01 00:00:00")); pstmt.executeUpdate(); // Set the second row data and execute pstmt.setString(1, "ü"); pstmt.setNull(2, java.sql.Types.TIMESTAMP); pstmt.executeUpdate(); } catch (SQLException e) { throw new RuntimeException(e); } } } } -------------- Here is the SQL : -------------- USE dev1; CREATE TABLE dev1.test ( s varchar(50), d date ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- standard INSERTs work fine -- INSERT INTO dev1.test (s, d) VALUES (NULL, '2023-03-01 00:00:00'); -- INSERT INTO dev1.test (s, d) VALUES ('ü', NULL); -- Prepared Statement reused fails with encoding error when inserting 'ü' PREPARE ps FROM 'INSERT INTO dev1.test (s, d) VALUES (?,?)'; SET @s1=NULL; SET @d1='2023-03-01 00:00:00'; -- no error if the time component is not given EXECUTE ps USING @s1, @d1; SET @s2='ü'; SET @d2=NULL; -- Error: Incorrect string value: '\xFC' for column 's' at row 1 -- SQLState: HY000 -- ErrorCode: 1366 EXECUTE ps USING @s2, @d2; Suggested fix: Come back to the 8.0.32 behaviour (no encoding error)