Fix MySQL ERROR 1364: Field doesn't have a default value on INSERT

The situation

You're running an INSERT that worked fine on your old dev machine — or an older MySQL version — and now it blows up:

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

Nothing changed in the query. The column exists. Yet MySQL refuses the insert. Strict SQL mode is the culprit — and it almost always bites you after a server upgrade or environment migration.

Why this happens

Starting with MySQL 5.7, STRICT_TRANS_TABLES became part of the default sql_mode. Before that, omitting a NOT NULL column with no default just got you a silent empty string or zero. With strict mode on, MySQL rejects the insert instead.

The column throwing the error is usually:

• Declared NOT NULL
• Has no DEFAULT value defined
• Not included in your INSERT statement

Check the column definition:

SHOW CREATE TABLE your_table\G

If you see something like:

`created_by` varchar(100) NOT NULL,

— no DEFAULT, and NOT NULL — that's your culprit.

Quick fix: include the column in your INSERT

The fastest fix: just provide the missing value.

-- Before (failing)
INSERT INTO orders (product_id, quantity) VALUES (42, 3);

-- After (fixed)
INSERT INTO orders (product_id, quantity, created_by) VALUES (42, 3, 'system');

If the column should always have a sensible default, set it in the schema so you never have to remember it at query time.

Permanent fix: add a DEFAULT to the column

When the column should always fall back to something sensible, bake that default into the schema:

ALTER TABLE orders
  MODIFY COLUMN created_by VARCHAR(100) NOT NULL DEFAULT 'system';

Timestamp columns get this treatment constantly:

ALTER TABLE orders
  MODIFY COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE orders
  MODIFY COLUMN updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

After the ALTER, your original INSERT runs without touching a single line of query code.

Alternative: make the column nullable

Sometimes "no value" is a perfectly valid state for that column. Drop the NOT NULL constraint:

ALTER TABLE orders
  MODIFY COLUMN created_by VARCHAR(100) DEFAULT NULL;

Omitting it from INSERT now silently stores NULL — which strict mode is fine with.

Emergency workaround: disable strict mode for the session

Sometimes you need that INSERT to go through right now — mid-migration, say, or when altering the table isn't an option. Disable strict mode for the current session only; other connections stay unaffected:

SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));

-- Now run your failing INSERT
INSERT INTO orders (product_id, quantity) VALUES (42, 3);

-- Restore strict mode (or just close the connection)
SET SESSION sql_mode = @@GLOBAL.sql_mode;

Don't treat this as a real fix. Strict mode exists to catch data problems before they hit the database. Turn it off globally and MySQL starts silently storing empty strings and zeros for fields that should never be empty.

Check and change the global sql_mode

See what's currently active:

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

To make the change permanent across all connections, edit the MySQL config — typically /etc/mysql/mysql.conf.d/mysqld.cnf or /etc/my.cnf depending on your distro:

[mysqld]
sql_mode = "ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Then restart:

sudo systemctl restart mysql

Removing STRICT_TRANS_TABLES is a real trade-off. You're swapping data safety for compatibility. Only go this route after auditing what those "silent" inserts would actually store in your tables.

Application-level: set defaults before INSERT

Application code hitting this error needs the value set before the query runs — not patched around after. In Python with SQLAlchemy:

from datetime import datetime

new_order = Order(
    product_id=42,
    quantity=3,
    created_by=current_user.username or 'system',
    created_at=datetime.utcnow()
)
db.session.add(new_order)
db.session.commit()

In PHP PDO:

$stmt = $pdo->prepare(
    "INSERT INTO orders (product_id, quantity, created_by) VALUES (?, ?, ?)"
);
$stmt->execute([42, 3, $user ?? 'system']);

Verify the fix worked

Run these to confirm everything landed correctly:

-- Re-run the original failing INSERT
INSERT INTO orders (product_id, quantity) VALUES (42, 3);

-- Should return: Query OK, 1 row affected

-- Check the row
SELECT * FROM orders ORDER BY id DESC LIMIT 1;

If you altered the column, check that the new default shows in the schema:

SHOW COLUMNS FROM orders LIKE 'created_by';
-- Field      | Type         | Null | Key | Default | Extra
-- created_by | varchar(100) | NO   |     | system  |

Root cause summary

• MySQL 5.7+ enabled strict mode by default — this is the most common trigger
• Columns marked NOT NULL without a DEFAULT must appear in every INSERT
• Best fix: add a DEFAULT to the column, or include it explicitly in your query
• Session-level sql_mode override is a last resort — not a permanent solution