How to Disable STRICT_TRANS_TABLES in MySQL (Temporarily & Permanently)

Introduction

STRICT_TRANS_TABLES is one of the SQL modes in MySQL that enforces strict data validation rules. When STRICT_TRANS_TABLES is enabled, MySQL rejects invalid values and generates an error, rather than converting them. If a value is missing or invalid, the entire INSERT or UPDATE query is aborted, and no data is inserted. If STRICT_TRANS_TABLES is disabled, MySQL attempts to adjust invalid values to the closest valid ones or uses default values for missing data, allowing the query to succeed but potentially inserting incorrect or unintended values.

Prerequisites

1. A sudo-privileged SSH user credential
2. MySQL “root” or any admin-privileged credential

Implementation

Step 1: Log in to the server via SSH

$ ssh username@IP

Step 2: Connect to MySQL via the root user or any admin-privileged user

$ mysql -u <username> -p

Step 3: List the active SQL modes and check whether “STRICT_TRANS_TABLES” is included or not. If it is included, then we can consider that it is enabled

>SELECT @@GLOBAL.sql_mode;

Step 4: To disable STRICT_TRANS_TABLES temporarily by updating the SQL mode for the current session or globally until the server restarts

(I) For the current session

>SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, ‘STRICT_TRANS_TABLES’, ”);

(II) For the global session (until restart)

>SET GLOBAL sql_mode = REPLACE(@@GLOBAL.sql_mode, ‘STRICT_TRANS_TABLES’, ”);

Step 5: To permanently disable the “STRICT_TRANS_TABLES“, edit the MySQL configuration. Navigate to the [mysqld] section and remove STRICT_TRANS_TABLES from the list of SQL modes.

$ vi /etc/mysql/mysql.conf.d

Step 6: Restart the MySQL service

$ systemctl restart mysql

Conclusion

Disabling STRICT_TRANS_TABLES can help resolve strict data handling issues for applications requiring more flexible data insertion rules.

FAQ

1. How do I check if STRICT_TRANS_TABLES is enabled in MySQL?

Run the following command:

SELECT @@GLOBAL.sql_mode;

If STRICT_TRANS_TABLES appears in the output, it is enabled in the global SQL mode.

You can also check the SQL mode for your current session:

SELECT @@SESSION.sql_mode;

2. How do I disable STRICT_TRANS_TABLES temporarily?

To disable it only for the current MySQL session, run:

SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'STRICT_TRANS_TABLES', '');

The change applies only to the current session.

3. How do I disable STRICT_TRANS_TABLES globally?

Run:

SET GLOBAL sql_mode = REPLACE(@@GLOBAL.sql_mode, 'STRICT_TRANS_TABLES', '');

This changes the global setting, but the change may not persist after the MySQL server is restarted.

Related Articles:

https://pheonixsolutions.com/blog/configuring-mysql-to-listen-on-a-private-ip-address/

https://pheonixsolutions.com/blog/how-to-disable-lock-a-mysql-user-account-from-cli/

https://pheonixsolutions.com/blog/how-to-copy-a-table-from-one-database-to-another-in-mysql/

Talk to our experts:

Looking for the right technology solution for your business? Our experts can help with web hosting, domain registration, DevOps and cloud, software development, web applications, mobile applications, and enterprise solutions. Get in touch with our team here.