How to Disable MySQL 5 "Strict Mode" on an Ubuntu 18.04 VPS or Dedicated Server

How to Disable MySQL 5 "Strict Mode" on an Ubuntu 18.04 VPS or Dedicated Server

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:

Hostinger
NZ$5.06 /mo
Starting price
Visit Hostinger
Rating based on expert review
  • User Friendly
    4.7
  • Support
    4.7
  • Features
    4.8
  • Reliability
    4.8
  • Pricing
    4.7
IONOS
NZ$1.69 /mo
Starting price
Visit IONOS
Rating based on expert review
  • User Friendly
    4.5
  • Support
    4.0
  • Features
    4.5
  • Reliability
    4.5
  • Pricing
    4.3
Ultahost
NZ$4.90 /mo
Starting price
Visit Ultahost
Rating based on expert review
  • User Friendly
    4.3
  • Support
    4.8
  • Features
    4.5
  • Reliability
    4.0
  • Pricing
    4.8

How to Backup Your MySQL Database on an Ubuntu 18.04 VPS or Dedicated Server

This article shows you how to back up a MySQL database on a Linux Ubuntu 18.04 s
3 min read
Eliran Ouzan
Eliran Ouzan
Web Designer & Hosting Expert

How To Install Apache, MySQL & PHP on an Ubuntu 18.04 VPS or Dedicated Server

This article shows users how to setup PHP, Apache web server and MySQL database
4 min read
Idan Cohen
Idan Cohen
Marketing Expert

How to Install PhpMyAdmin on a Ubuntu 18.04 VPS or Dedicated Server

This article will describe the process of installing phpMyAdmin on an Ubuntu 18.
3 min read
Eliran Ouzan
Eliran Ouzan
Web Designer & Hosting Expert

How to Install Apache Cassandra on an Ubuntu 18.04 VPS or Dedicated Server

This tutorial will help you install and configure Apache Cassandra (an opensourc
3 min read
Max Ostryzhko
Max Ostryzhko
Senior Web Developer, HostAdvice CTO
HostAdvice.com provides professional web hosting reviews fully independent of any other entity. Our reviews are unbiased, honest, and apply the same evaluation standards to all those reviewed. While monetary compensation is received from a few of the companies listed on this site, compensation of services and products have no influence on the direction or conclusions of our reviews. Nor does the compensation influence our rankings for certain host companies. This compensation covers account purchasing costs, testing costs and royalties paid to reviewers.
Click to go to the top of the page
Go To Top