Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL error 1364: Field 'id' doesn't have a default value on INSERT

ErrorERROR 1364 (HY000): Field 'id' doesn't have a default value

Short answer

Error 1364 means the INSERT left out a NOT NULL column that has no default, and strict SQL mode (the default) refuses to guess. For an id column the usual fix is to make it AUTO_INCREMENT. Otherwise supply the value in the INSERT, or change the mapping so it does.

  • Applies to MySQL 8.4, MySQL 8.0, Hibernate 6, Spring Boot 3
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Look at how the id column is defined and which SQL mode is active.

    SHOW CREATE TABLE tbl_name\G
    SELECT @@SESSION.sql_mode;
  2. 2Match what you see, then jump to its fix

    1. 1SHOW CREATE TABLE shows id NOT NULL with no AUTO_INCREMENTThe id column is not AUTO_INCREMENTFix
    2. 2The column named in the message is not the id: the error names whichever column was left outStrict SQL mode is on and a NOT NULL column is simply missing from the INSERTFix
    3. 3The error comes from a JPA save() callHibernate is told to generate ids, but the table cannot do itFix

What does the error look like?

  • ERROR 1364 (HY000): Field 'id' doesn't have a default value
  • Field '%s' doesn't have a default value (the server's message pattern; %s is the column name)

Cause 1 of 3

The id column is not AUTO_INCREMENT

1Check

MySQL generates the id when you omit it only if the column is AUTO_INCREMENT. A missing value is a NOT NULL column with no explicit DEFAULT, and strict mode turns that into an error.

SHOW CREATE TABLE tbl_name\G

2Fix

Restate the full definition with AUTO_INCREMENT. MODIFY drops attributes you do not repeat, so copy the type and other attributes from the output above (the type here is an example).

ALTER TABLE tbl_name MODIFY id BIGINT NOT NULL AUTO_INCREMENT;

3Verify

Insert a row without id; MySQL assigns the next number.

INSERT INTO tbl_name (name) VALUES ('test');
SELECT LAST_INSERT_ID();

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

Strict SQL mode is on and a NOT NULL column is simply missing from the INSERT

1Check

The default SQL mode in MySQL 8.4 includes STRICT_TRANS_TABLES, so an INSERT that omits a NOT NULL column without a default is an error. Without strict mode MySQL would insert the implicit default and only warn.

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

2Fix

The correct fix is to give the column a value or a DEFAULT in the table. Turning strict mode off hides data errors, so use it only as a stopgap.

ALTER TABLE tbl_name MODIFY status VARCHAR(20) NOT NULL DEFAULT 'new';

3Verify

Repeat the INSERT.

INSERT INTO tbl_name (name) VALUES ('test');

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

Hibernate is told to generate ids, but the table cannot do it

1Check

Hibernate's IDENTITY strategy maps the id to an identity or auto_increment column, so the column must really be AUTO_INCREMENT. The AUTO strategy selects SEQUENCE, TABLE or UUID by id type and database, so it does not guarantee an auto_increment column.

// entity
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

2Fix

Use IDENTITY for a MySQL auto_increment id, and fix the table with the ALTER above if it was created by hand.

ALTER TABLE tbl_name MODIFY id BIGINT NOT NULL AUTO_INCREMENT;

3Verify

Call save() on a new entity and read the generated id.

SELECT id FROM tbl_name ORDER BY id DESC LIMIT 1;

Confirmed by docs.hibernate.org

Did this fix it?

How do I stop it coming back?

  • Define every id as AUTO_INCREMENT (or a generated identity) in the migration that creates the table.
  • Keep strict mode on in every environment, so the error shows in development and not only in production.
  • Let migrations create the schema instead of editing tables by hand.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.