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 root user or any admin-privileged

$ mysql -u <username> -p

Step 3: List the active SQL modes and check whether the “STRICT_TRANS_TABLES” in included or not. If it 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.

Post Views: 14

Leave a Reply