MySQL 5.7.34 not disabling STRICT_TRANS_TABLES whatever I do

All we need is an easy explanation of the problem, so here it is.

I’m trying to disable STRICT_TRANS_TABLES on MySQL 5.7.34 and, no matter what I do, the option is always enabled. I put manually on my.cnf sql_mode=NO_ENGINE_SUBSTITUTION and no other modes, when I restarted the service, the

SELECT @@sql_mode

returned

STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION.

No matter what modes I put, excluding STRICT_TRANS_TABLES, it’s always appending this option to the ones I choose.

But, if I query

SHOW GLOBAL VARIABLES WHERE variable_name = 'sql_mode'

, it shows the value that I put without STRICT_TRANS_TABLES.

I need to disable this, as ODI repositories has this requirements when installing it with MySQL as the DBMS. What am I missing, is it impossible to disable this option in MySQL 5.7.34?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It is definitely possible to change the sql_mode in MySQL 5.7.

I suspect you changed the session setting without changing the global setting. Like this:

mysql> set sql_mode = 'STRICT_TRANS_TABLES';

mysql> select @@sql_mode, @@global.sql_mode;
+---------------------+------------------------+
| @@sql_mode          | @@global.sql_mode      |
+---------------------+------------------------+
| STRICT_TRANS_TABLES | NO_ENGINE_SUBSTITUTION |
+---------------------+------------------------+

mysql> show session variables like 'sql_mode';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+

mysql> show global variables like 'sql_mode';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+

(This example shows the opposite of your settings, the global one is non-strict while my session setting is strict. The point is that they can be different, and setting a session option does not change the global option.)

You said you edited the my.cnf, but there are a few ways this could fail to change the global setting.

You could add the setting under the [client] or [mysql] sections in my.cnf, which would apply only to sessions started by the mysql client, not when other application clients connect. You should make sure to add the line in the [mysqld] section in my.cnf.

Also double-check that there isn’t another line in your my.cnf that also sets the sql_mode after the place where you edited it. It’s not an error for a given variable to be set more than once in the same file, and the last one that sets a value takes priority.

Also you might have an !include or !includedir directive in your my.cnf that reads an additional options file. The sql_mode could be reset in one of those other files. Read https://dev.mysql.com/doc/refman/5.7/en/option-files.html#option-file-inclusions


All that said, I strongly urge you to fix whatever requires ODI to have non-strict mode. Using non-strict mode can allow invalid queries and will truncate data instead of returning an error. For example when strict mode is not enforced, an integer that is too large to fit in a given data type will be "rounded down":

mysql> create table mytable (id int primary key);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into mytable set id = 4000000000;
Query OK, 1 row affected, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'id' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from mytable;
+------------+
| id         |
+------------+
| 2147483647 |
+------------+

That value is the highest value supported by a signed int. So every value higher than 231-1 will be truncated in that way.

The same happens with strings and other data types. Is this what you want? I would call this data corruption.


Re your comment:

You can specify a session variable in your JDBC URL, which should override whatever is configured in my.cnf.

jdbc:mysql://myServer:3306/myschema?user=scott&password=tiger&sessionVariables=sql_mode=NO_ENGINE_SUBSTITUTION

See https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-session.html#cj-conn-prop_sessionVariables

But I still think it should not be necessary.

Firstly, I think you have overlooked some global configuration, and I’ve described several places to look earlier in this answer.

Secondly, it makes no sense that ODI requires non-strict mode. I would open a support request with Oracle to ask why that’s necessary.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply