Learn why this exception is thrown in MySQL and how to bypass it.

The general error 1364 is basically triggered when you are try to insert/update a record on your database where a field, namely the one that is triggering the exception cannot be null, however you are trying to set its value as null (you are not providing a value for it). Normally, this exception is only triggered when you are using MySQL in Strict mode, you can get the mode that your MySQL server is using with the following query:

SELECT @@sql_mode;

If you face this exception, it's probably because you have the strict mode enabled (STRICT_TRANS_TABLES). In this article, we'll share with you 2 ways to prevent this exception from appearing easily.

A. Logic Solution

To prevent this exception, simply provide a value for the field that is triggering the exception or define that the field can be null, so when you try to insert a record, the value will be null but the error won't be thrown, for example with a column that cannot be null, the following query would throw the exception:

INSERT INTO user (column_a, column_b) VALUES ("Value First Column", NULL);

So you could alter the column to make the column nullable:

ALTER TABLE YourTable MODIFY column_b VARCHAR(255) DEFAULT NULL;

And that's it, the exception shouldn't appear anymore.

B. Server Side Solution

If you are unable to define by yourself the value of the column because you are using a framework or other kind of logic that first inserts the record with null and then it's updated, you may rely on the easy way that is removing the strict mode of MySQL. This is someway not recommended as this prevents for example the insertion of an empty value in a column that cannot be null throwing the mentioned exception. All happens basically as a measure of control that prevents that your logic fails and prevents you from writing flappy code, where you may overlook wrong written logic etc.

In other cases, the logic that you wrote works on your local environment, because probably you don't have set the strict mode in there, but on your production server it doesn't work because of this mode. In this case, you can disable the strict mode modifying the configuration file of MySQL (my.ini in Windows or my.cnf in Linux) setting the sql_mode to an empty string or removing theĀ STRICT_TRANS_TABLES option that sets MySQL in Strict Mode and having theĀ NO_ENGINE_SUBSTITUTION mode enabled:

[mysqld]
sql-mode=""
# Or
# sql-mode="NO_ENGINE_SUBSTITUTION"

Save changes, restart the mysql service and the error won't appear anymore, instead the column will be set to null although it's not allowed.

Happy coding !


Senior Software Engineer at Software Medico. Interested in programming since he was 14 years old, Carlos is a self-taught programmer and founder and author of most of the articles at Our Code World.

Sponsors