[CONJ-1055] MySQL Connector Incompatible result from Statement.execute for out-of-range value Created: 2023-02-21  Updated: 2023-03-06

Status: Open
Project: MariaDB Connector/J
Component/s: MySQL compatibility
Affects Version/s: 3.1.2
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Wenqian Deng Assignee: Diego Dupin
Resolution: Unresolved Votes: 0
Labels: Compatibility
Environment:

Windows 11
MariaDB Connector 3.1.2
MySQL Connector 8.0.32
MariaDB 10.11


 Description   

Hey there,

I use different connectors(MySQL / MariaDB Connector) to connect to the same MariaDB. My connection URL is like "jdbc:mariadb://localhost:3366/test?useUnicode=true&characterEncoding=utf8&sessionVariables=storage_engine=InnoDB,sql_mode=''&connectionTimeZone=UTC" and "jdbc:mysql://localhost:3366/test?useUnicode=true&characterEncoding=utf8&sessionVariables=storage_engine=InnoDB,sql_mode=''&connectionTimeZone=UTC"
and the setup SQL file:

DROP DATABASE IF EXISTS database0;
 
CREATE DATABASE database0;
 
USE database0;
 
CREATE OR REPLACE TABLE t0(c0 REAL UNSIGNED UNIQUE, PRIMARY KEY(c0));
 
INSERT INTO t0 VALUES (-1006215717);

I execute this file using Statement.execute().

But the MySQL Connector and MariaDB Connector seem to have different results when executing the last line 'INSERT INTO t0 VALUES (-1006215717);'.
That is, MySQL Connector returns 'com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Out of range value for column 'c0' at row 1' but MariaDB Connector can successfully insert to the table.

I classify this as a bug because MariaDB claims MySQL compatibility at the driver level.

 Comments   
Comment by Wenqian Deng [ 2023-02-28 ]

Well, it seems that the URL setting sql_mode in MySQL J doesn't work. see https://bugs.mysql.com/bug.php?id=23371

Comment by Diego Dupin [ 2023-03-01 ]

Do you really need to explicitly set sql_mode='' ?
This explicilty indicate to remove truncation ? (sql_mode that default to "STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO , NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" on recent server, STRICT_TRANS_TABLES indicate throwing error on truncation)

MySQL connector is doing multiple commands on connection, one command adding 'STRICT_TRANS_TABLES' to sql_mode in every cases.

I think the connector does what we can expect it to do : if sql_mode is explicitly set to '', then no truncation must occurs.

Comment by Wenqian Deng [ 2023-03-06 ]

Thank you for your response. I set sql_mode to '' because I wanted to understand how the database would behave in this scenario and to determine if Mariadb J and MySQL J can remain consistent. The inconsistency here has puzzled me for a long time, and I'm not sure if Mariadb J needs to make changes for this or if some documentation needs to be updated to address this inconsistency.

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