Sql_mode: STRICT_TRANS_TABLES cannot be removed?

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: sql_mode: STRICT_TRANS_TABLES 无法移除？

| username: Qiuchi

[Test Environment for TiDB]

[TiDB Version] 6.1.0
[Reproduction Path]

show variables like 'sql_mode';
Variable_name Value
sql_mode STRICT_TRANS_TABLES 
set global sql_mode = 'ORACLE';

Reconnect to TiDB

show variables like 'sql_mode';
Variable_name Value
sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,ORACLE,STRICT_TRANS_TABLES

Found that STRICT_TRANS_TABLES is still in the configuration

[Encountered Problem: Phenomenon and Impact]
Unable to remove STRICT_TRANS_TABLES, resulting in the inability to automatically trim numeric precision

2
| username: tidb菜鸟一只 | Original post link

SHOW GLOBAL VARIABLES LIKE ‘sql_mode’; Let’s take a look.

3
| username: Qiuchi | Original post link 
set global sql_mode = 'ORACLE';

Reconnect to TiDB

show variables like 'sql_mode';
Variable_name Value
sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,ORACLE,STRICT_TRANS_TABLES 
show global variables like 'sql_mode';
Variable_name Value
sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,ORACLE

Why does the session have STRICT_TRANS_TABLES at the end but the global does not…

4
| username: xingzhenxiang | Original post link

I checked mine, and there isn’t any.

5
| username: Qiuchi | Original post link

Just resolved it. The reason was that we were using JDBC, which automatically adds STRICT_TRANS_TABLES to SQL_MODE.
MySQL Bugs: #23371: Automatic append of STRICT_TRANS_TABLES to the sql_mode.
You need to set the JDBC URL parameter jdbcCompliantTruncation=false.

6
| username: xingzhenxiang | Original post link

I agree, solving it is great.

7
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.