Strict mode is responsible for controlling how missing values are handled when creating or updating new records on a MySQL database.
In MySQL strict mode is enabled by default and this behavior causes nagging warnings when a user attempts to save records that have blank values on some fields. Sometimes, the records are not saved/update at all and this can harm the normal operation of a website or web application.
Disabling MySQL strict mode allows queries to be executed with the missing or invalid values and in most cases, it is the preferred behavior by most database administrators. Sometimes, disabling MySQL strict mode may cause unexpected behavior e.g. concatenating values that exceed the length value defined in behavior
However, If you are running various software applications, disabling MySQL strict mode is a requirement. Otherwise, any insert command will raise an error like, “Field ‘<field_name>’ doesn’t have a default valueâ€
In this guide, we will cover the steps for disabling the default MySQL ‘STRICT_TRANS_TABLES’behavior.
Prerequisites
- An Ubuntu 18.04 VPS server
- MySQL community server
- A non-root user with sudo privileges
Special Note: Consult with Hostadvice’s Best Linux Hosting Services page or Best VPS Hosting page to find the top web hosting providers in these categories.
Step 1: Check if strict mode is enabled
We first need to establish if strict mode is enabled on the MySQL server. To check this, type the command below on a terminal server:
$ sudo mysql -u root -p
Enter your MySQL database root password and press Enter.
Then, you will need to run the query below on the MySQl command prompt:
$ SHOW VARIABLES LIKE 'sql_mode';
A table is displayed on the screen with some sql_mode values separated with commas as shown below. If you find a value like “STRICT_TRANS_TABLESâ€, then, MySQL strict mode is enabled.
Step 2: Create a new configuration file using a nano editor
Using a nano editor, create a new configuration file under the /etc/mysql/conf.d/ directory. The new configuration file will override the default MySQL configuration file.
Use the command below:
$ sudo nano /etc/mysql/conf.d/disable_strict_mode.cnf
Then, enter the text below on the text editor:
[mysqld] sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Press CTRL +X and Y to save the changes.
Step 3: Restart MySQL
When you make any change to any MySQL configuration directory, you should restart MySQL service for the changes to take effect using the command below:
$ sudo service mysql restart
Step 4: Confirming the change
Log to your MySQL server one more time using the command below:
$ sudo mysql -u root -p
Enter your root password and press Enter.
Run the query below one more time on the MySQl command prompt:
$ SHOW VARIABLES LIKE 'sql_mode';
As you can see from the screenshot below, the value ‘STRICT_TRANS_TABLES’ in the list of sql_mode values and this means it has been disabled successfully.
Conclusion
That is the basic procedure of disabling the MySQL strict mode on your Ubuntu 18.04 server. Remember, it is advisable to disable the mode only if you have an application that demands the change. This applies to applications that save values to a database on columns that take empty values with no defaults defined.
A common case is an ‘address 2’ field which although is present in most registration forms, it may not be mandatory to enter it. We hope this guide will assist you in troubleshooting your MySQL errors.
Check out the top 3 Dedicated server hosting services:
- Want suggestions about Best dedicated servers hosting? Click here and read more.
