MySQL Error 1364 can now be fixed with any of these methods provided in this article. At Bobcares, as part of our MySQL Support Service, we answers all MySQL inquiries, large or small.

When will we see ‘MySQL Error 1364’?

If a query or statement tries to insert/update a record without a value for a specific column that is NOT NULL, MySQL server throws the Error 1364. We can say that the MySQL server throws this error because there wasn’t a NOT NULL column value during the insertion process.

This exception only occurs when using MySQL in Strict mode. We can find out easily what mode the MySQL server is running in by executing the following command:

SELECT @@sql_mode;

How To Fix MySQL Error 1364?

Let’s see the ways our Support team suggests to fix this error.

1. Logic Solution: When trying to insert a record, the value will be null but the error won’t be thrown if you provide a value for the field that is causing the exception or specify that the field can be null. For instance, 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);

   Now we alter the column to make the column nullable.

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

   The MySQL Error 1364 will not appear again.
2. Server Side Solution: Uninstalling MySQL’s strict mode is a simple solution. By changing the sql_mode setting to an empty string or by removing the STRICT_TRANS_TABLES option that puts MySQL in Strict Mode and enabling the NO_ENGINE_SUBSTITUTION mode, we can disable the strict mode in MySQL’s configuration file (my.ini in Windows or my.cnf in Linux).

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

   Now save the changes, restart MySQL, and the MySQL Error 1364 won’t show up again.
3. Using AUTO_INCREMENT: When no other starting number is specified, MySQL’s AUTO INCREMENT function assigns a numeric value to each column starting at 1 and increases that value by 1 with each additional insert. Let’s take an example:

   CREATE TABLE employee_details(
     emp_id int ,
     emp_enroll_no varchar(255) NOT NULL,
     emp_firstName varchar(255) DEFAULT NULL,
     emp_lastName varchar(255) DEFAULT NULL,
     primary key(emp_id)
   );

   Here the columns, emp_id and emp_enroll_no both cannot be NULL. Then insert a record with the command without specifying any value for column emp_id.

   INSERT INTO employee_details (emp_enroll_no,emp_firstName,emp_lastName) VALUES("1-N","Henry","Smith");

   Since we left emp_id blank in the insert statement, the output reveals the MySQL Error 1364 with the following message response: Error Code 1364. So we can fix it by ALTER query. Any insert after executing the below statement will assign a value to emp_id starting with 1 and increments by 1 in successive inserts.

   ALTER TABLE employee_details MODIFY emp_id int NOT NULL AUTO_INCREMENT;

   Now executing the insert statement again without specifying any value for column emp_id will result in a successful insert this time.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To conclude, Our Support team briefly explains about the MySQL Error 1364. The article also includes some of the simple solutions to fix the error.