Is it possible to turn off strict SQL mode in RDS for MySQL v8.0?
Is it possible to turn off strict SQL mode in RDS for MySQL v8.0? I noticed it has a fixed value of STRICT_TRANS_TABLES even though the parameter sql_mode is only set on NO_ENGINE_SUBSTITUTION
I'm asking because MySQL already deprecated NO_ZERO_DATE, NO_ZERO_IN_DATE and announced that in the future they will be merged into strict mode which potentially could break our applications.
- Language
- English
asked 3 years ago4.7K views
- Newest
- Most votes
- Most comments
Strict mode is OFF by default on RDS MySQL 8, we have sql_mode=NO_ENGINE_SUBSTITUTION only. As long as you do not add any of STRICT_TRANS_TABLES (when merged) or NO_ZERO_DATE or NO_ZERO_IN_DATE in your sql_mode, it should not break your application in the future.
We recommend however to use strict mode when possible to avoid subtle data inconsistencies in the future.
Relevant content
asked 2 years ago
But when I run against the instance: "show variables like 'sql_mode'"
I get the value: "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"
even though in the param group only the value NO_ENGINE_SUBSTITUTION was defined
You might be looking at your session variables. Does executing these queries show differences?
If they do differ, then your client connection may be setting a custom SQL mode during connection. You will need to make sure your application does not do the same.
Thanks, indeed my JDBC client added it on session start. I solved it by setting the "jdbcCompliantTruncation" key to "false" in the driver properties.