How to disable Strict Mode of MySQL and MariaDB
How to disable Strict Mode of MySQL and MariaDB
The strict mode in MySQL or MariaDB is a mode that limits us and does not allow us to carry out bad practices, such as inserting a large number of characters when the limit is low in a certain field. Doing this can have two consequences depending on the version we use, for example it can cut our data and show a warning, or the declaration will not be executed and it will show us an error, and neither of the two is necessarily to our liking.
Check if 'Strict Mode' is activated
For this, we access MySQL through the terminal with our username and password or with some software such as phpMyAdmin on a web server, HeidiSQL on Windows, or DBeaver on Linux, Mac, and Windows. We execute the following commands as appropriate.
From a terminal, change root for the user we will use, it will ask us for a password:
mysql -u root -p -e 'SHOW VARIABLES LIKE "sql_mode";'
From HeidiSQL, phpMyAdmin o DBeaver:
SHOW VARIABLES LIKE "sql_mode";
Terminal output:
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
HeidiSQL output:
| Variable_name | Value |
|---|---|
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
In any case, if the text STRICT_TRANS_TABLES or STRICT_ALL_TABLES appears, it means that strict mode is enabled.
Deactivate the 'Strict Mode' temporarily or permanently
There are two ways to disable this mode, the first is temporary and the second is permanent. The temporary way could be useful for debugging or for executing problematic SQL statements. Instead the permanent solution could be useful when we use systems that require it, such as when installing Directus (a Headless CMS), which in the documentation mentions that it is necessary to deactivate this mode.
Procedure: It is recommended that when deactivating this mode, it is necessary to copy the other modes that were activated, and only subtract STRICT_TRANS_TABLES and STRICT_ALL_TABLES, the modes must be separated with comma and without spaces.
Temporarily disabled
Taking into account that NO_ENGINE_SUBSTITUTION was already activated and we do not want to deactivate it, we only execute the first line of each code, in case of not having any previous mode, we use the last line of each code according to each case.
To disable it from the terminal temporarily, we just have to run the following command (replacing root with our user):
mysql -u root -p -e 'SET sql_mode = 'NO_ENGINE_SUBSTITUTION';'
We can also use the following if we don't have any mode activated:
mysql -u root -p -e 'SET sql_mode = '';'
To do it from a manager, execute the declaration directly:
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
Or we can use the following if we don't have any mode activated:
SET sql_mode = '';
Important: If we deactivate it temporarily there is a limitation, and that is that in the case of using the Terminal we have to execute the queries that require the strict mode deactivated in that same command separated by semicolons. In the case of using a MySQL Shell, it is a little more flexible and allows us to keep the mode deactivated for the duration of the Shell session. Now in the case of managers, queries must also be executed in the same request.
Permanently disabled
This is a little bit easier, we just have to edit our my.cnf file or equivalent, in Debian the file is in the path /etc/mysql/my.cnf and on Windows with MariaDB it is found in C:\Program Files\MariaDB 10.3\data\my.ini, sometimes it can be in /etc/mysql/mariadb.conf.d/50-server.cnf in the case of MariaDB.
In this file we look for option sql_mode, and we remove STRICT_TRANS_TABLES and STRICT_ALL_TABLES. If the file or variable does not exist we create it and set it as empty in the [mysqld] section.
If we have other modes activated that we do not want to deactivate:
sql_mode = "NO_ENGINE_SUBSTITUTION"
If the option does not exist or if we want to disable all modes:
sql_mode = ""
Note: If the sql_mode option does not exist, the modes that are set by default are activated, and in the case of STRICT_TRANS_TABLES, in the latest versions it is activated by default.
