[CONJ-1047] Version 2.7.8 vs Version 3.x.y - ERROR 1406 Created: 2023-01-31  Updated: 2023-03-13

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

Type: Bug Priority: Major
Reporter: Liviu Coman Assignee: Diego Dupin
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Hi,

I'm using mariadb jdbc connector 2.7.8 to access a mariadb 10.3.23
and I was considering upgrading to latest version 3.x.z.

Unfortunately I've noticed that ERROR 1406 is not triggered on 3.x.z.

Consider the table marks:
mysql> SELECT * FROM marks;
---------------------------------------

name percentage email

---------------------------------------

Bipin 84% bipin_p@gmail.com
Deepak 78% cool_deepak@gmail.com
Rohith 82% rohith_s@gmail.com
Sujit 94% sujit_jos@gmail.com

---------------------------------------

Error I'm trying to produce with a junit:
mysql> INSERT INTO marks VALUES('&name','&percentage','&email');
ERROR 1406 (22001): Data too long for column 'percentage' at row 1

Issue found on 3.0.x and 3.1.x
Issue not found on 2.7.x

 Comments   
Comment by Liviu Coman [ 2023-01-31 ]

I'm using spring-jdbc and I can't find this line for 3.x.z

2023-01-31 14:55:45,778 [main] [] DEBUG support.SQLErrorCodeSQLExceptionTranslator:407 - Translating SQLException with SQL state '22001', error code '1406', message [(conn=1289) Data too long for column

Comment by Diego Dupin [ 2023-02-02 ]

Could you share @sql_mode value ? (i.e. result of "select @@sql_mode;")
it might be that STRICT_TRANS_TABLES is missing.

2.x was ensuring that state, issuing an additional command for each connection, but now that this is default for all supported server version, choice is to have fast connection without any additional command.

Comment by Liviu Coman [ 2023-03-13 ]

on 2.7.8 I see this output

2023-03-13 16:31:17,275 [main] [] DEBUG dao.TokensDaoIT:87 - sql_mode IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

on 3.1.2 I see this output

2023-03-13 16:51:47,998 [main] [] DEBUG dao.TokensDaoIT:87 - sql_mode IGNORE_SPACE

Comment by Liviu Coman [ 2023-03-13 ]

in order to use jdbc driver version 3.1.2 following session variable "sql_mode" has to be set

database.url=jdbc:mariadb://127.0.0.1:3306/test?useFastDateParsing=false&useUnicode=true&characterEncoding=UTF-8&sessionVariables=max_statement_time=5,sql_mode='IGNORE_SPACE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

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