Working with MySQL Restrictive Mode

Introduction

When some of the MySQL columns in a table are defined with the keyword NOT NULL, you may get an error such as Field 'FIELD_NAME' doesn't have a default value if you fail to specify a value for those columns in an INSERT statement. By default, the MySQL server has a restrictive SQL mode turned on and is controlled by the variable STRICT_TRANS_TABLES. If you're installing an application in your server developed with the mode turned off, you might experience the above problem. Since changing the database schema can be a daunting task, it is always recommended to turn off the restrictive SQL mode. Please note, you should only disable this feature only if the change will not affect your application's logic.

For instance, assume you have a payments table in an application. Payees can either be paid in cash or bankers cheque. To capture all the payment details, the vendor of your database application might have defined additional fields like bank_name and cheque_no which might not be relevant for cash payments.

However, if those optional fields are defined with the keyword NOT NULL any entry to the payments table without the bank_name and cheque_no values will throw the Field 'FIELD_NAME' doesn't have a default value error. In such a case, turning off the STRICT_TRANS_TABLES mode is a good option to avoid getting this error every time you insert a cash record.

You'll set up a sample database in this guide and create a database table with some NOT NULL columns. You'll then try to insert records without providing values for the restricted columns to see if an error will be raised. You'll then turn off the STRICT_TRANS_TABLES mode to see if new records will be accepted without problems.

Prerequisites

To test this tutorial, ensure you've the following:

• An Ubuntu 20.04 server.
• A sudo user.
• A LAMP Stack.

Create a sample Database

Begin by logging into your server. Then, enter the command below to open the MySQL command-line interface.

$ sudo mysql -u root -p

When prompted, enter the root password of your MySQL server and press Enter to proceed. Then, issue the following command to create a sample database.

mysql> CREATE DATABASE sample;

Select the sample database.

mysql> USE sample;

Next, you'll create a payments table. You'll use it to capture payments paid to payees such as employees and vendors. The payment_id is the primary key, and its values will be set automatically since you've included the AUTO_INCREMENT keyword.

The payee column will record the name of the person receiving the payment. Since you might use different payment methods when making payments, you will use the field name payment_method to differentiate the modes of payments. The bank_name and cheque_no are optional fields to be specified only if the payment_method is a BANKERS CHEQUE. Otherwise, the optional fields should have an empty('') value for CASH payments.

Set up the payments table.

mysql> CREATE TABLE payments (
       payment_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
       payee VARCHAR(50),
       payment_method VARCHAR(50),
       bank_name VARCHAR(50) NOT NULL,
       cheque_no VARCHAR(50) NOT NULL
       ) ENGINE = InnoDB;

Once the database schema is in place, you'll test it with some INSERT records with both the restrictive mode turned on and off to see how those settings affect your SQL statements.

Insert Records with STRICT_TRANS_TABLES Turned On

Start by inserting a valid payment record made with a BANKERS CHEQUE. For this insert, you'll define values for all columns, including the bank_name and cheque_no columns, since they are required for BANKERS CHEQUE payments.

Run the INSERT statement below.

mysql> INSERT INTO payments
       (
       payee,
       payment_method,
       bank_name,
       cheque_no
       )
       VALUES
       (
       'JOHN DOE',
       'BANKERS CHEQUE',
       'XYZ BANK',
       '111'
       );

The above statement should succeed without any problem, and you should get an output similar to the one below.

Query OK, 1 row affected (0.01 sec)

Next, try to INSERT a CASH entry into the payments table. This time around, don't define any value for the bank_name and cheque_no columns. In your business logic, these fields should be left out as they are not relevant.

mysql> INSERT INTO payments (
       payee,
       payment_method
       )
       VALUES
       (
       'JOHN DOE',
       'CASH'
       );

Since you've not defined a value for the bank_name and cheque_no column and they were created with the keyword NOT NULL, you should get the error below.

ERROR 1364 (HY000): Field 'bank_name' doesn't have a default value

Although the previous INSERT statement conforms to your business logic, it has failed. If you've imported a database with a lot of tables defined in this manner, you may encounter a lot of problems. If changing the database schema is not possible or might require a lot of time, you may opt to disable the restrictive SQL mode.

Verify and Turn Off the Restrictive sql_mode

First, to disable the STRICT_TRANS_TABLES mode, examine the default sql_mode by running the command below.

mysql> SELECT @@GLOBAL.sql_mode;

You should get a comma-separated value. The STRICT_TRANS_TABLES should be included in the value, meaning it is enabled.

+-----------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

To turn this mode off, you will modify the MySQL configuration file. First, exit from the MySQL command-line interface.

mysql> QUIT;

Then open the /etc/mysql/my.cnf configuration file using nano.

$ sudo nano /etc/mysql/my.cnf

Add the information below to the file. The value of the sql_mode should be the value that you retrieved after running SELECT @@GLOBAL.sql_mode; with the STRICT_TRANS_TABLES item removed.

[mysqld]
sql_mode = ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Save and close the file. Then, restart the MySQL server for the changes to be effected.

$ sudo systemctl restart mysql

Insert Records with STRICT_TRANS_TABLES mode Turned Off

In this step, you'll try to insert records that don't have values for all the defined columns and see if MySQL will suppress the Field 'FIELD_NAME' doesn't have a default value error.

Log in back to your MySQL server

$ sudo mysql -u root -p

Enter your MySQL server's password and hit Enter to proceed. Then, examine the sql_modevalue to see if it was updated.

mysql> SELECT @@GLOBAL.sql_mode;

Your output should show a comma-separated value without the STRICT_TRANS_TABLES as shown below.

+---------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                 |
+---------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Switch to the sample database.

mysql> USE sample;

Run a CASH payment INSERT query and again, don't provide any values for the bank_name and cheque_no columns.

mysql> INSERT INTO payments
       (
       payee,
       payment_method
       )
       VALUES
       (
       'JOHN DOE',
       'CASH'
       );

Your INSERT query should now be successful. However, you'll get some low-impact warnings as shown in the following output.

Query OK, 1 row affected, 2 warnings (0.00 sec)

Retrieve the last warnings.

mysql> SHOW WARNINGS;

You can see the source of the warnings as shown below, but this doesn't affect the record.

+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1364 | Field 'bank_name' doesn't have a default value |
| Warning | 1364 | Field 'cheque_no' doesn't have a default value |
+---------+------+------------------------------------------------+
2 rows in set (0.00 sec)

Try to enter a payment with another payment method such as PayPal.

mysql> INSERT INTO payments
       (
       payee,
       payment_method
       )
       VALUES
       (
       'JOHN DOE',
       'PAYPAL'
       );

The PayPal entry should succeed without any problems.

Query OK, 1 row affected, 2 warnings (0.01 sec)

Run a SELECT statement against the payments table to ensure the records are in place.

mysql> SELECT
       payment_id,
       payee,
       payment_method,
       bank_name,
       cheque_no
       FROM payments;

All your payments should be displayed as shown below.

+------------+----------+----------------+-----------+-----------+
| payment_id | payee    | payment_method | bank_name | cheque_no |
+------------+----------+----------------+-----------+-----------+
|          1 | JOHN DOE | BANKERS CHEQUE | XYZ BANK  | 111       |
|          2 | JOHN DOE | CASH           |           |           |
|          3 | JOHN DOE | PAYPAL         |           |           |
+------------+----------+----------------+-----------+-----------+
3 rows in set (0.00 sec)

Even with the STRICT_TRANS_TABLES mode turned off, you can see that your payment records were inserted into your database table without violating your business/application logic.

Conclusion

In this guide, you've learned how to tweak the value of the sql_mode to suppress the Field 'FIELD_NAME' doesn't have a default value error. Use the knowledge in this guide to turn off the MySQL STRICT_TRANS_TABLES mode if changing your application's database schema is not a practical solution.