📋

Description

MySQL Error 1067 occurs when a column definition attempts to set a default value that is incompatible with the column's data type, constraints, or the version of MySQL being used. This typically happens during `CREATE TABLE` or `ALTER TABLE` operations, preventing the schema modification from completing successfully.

💬

Error Message

Invalid default value for '%s'

🔍

Known Causes

4 known causes

⚠️

Data Type Mismatch

The specified default value cannot be implicitly converted to the column's defined data type (e.g., assigning a string to an integer column).

⚠️

Invalid Expression as Default

Attempting to use a non-constant expression or function (like `NOW()` in older MySQL versions for `DATETIME` or a mathematical calculation) as a default value where only literal constants are allowed.

⚠️

Value Out of Range

The default value provided exceeds the permissible range (min/max) for the column's specific data type (e.g., setting '300' as default for a `TINYINT` column).

⚠️

Conflicting NOT NULL Constraint

Defining a column as `NOT NULL` without an explicit default value, or attempting to set `NULL` as the default for a `NOT NULL` column.

🛠️

Solutions

5 solutions available

1. Fix DATETIME Default Value easy

Use valid default for datetime columns

1

Use CURRENT_TIMESTAMP for default

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2

For MySQL < 5.6, use TIMESTAMP instead

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

3

Or allow NULL and set in application

CREATE TABLE posts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_at DATETIME NULL
);

2. Fix Invalid Date Format easy

Use correct date format for default value

1

Use valid date format

-- Wrong:
CREATE TABLE events (event_date DATE DEFAULT '2024/01/01');

-- Right:
CREATE TABLE events (event_date DATE DEFAULT '2024-01-01');

2

For datetime with time

-- Wrong: DEFAULT '2024-01-01 25:00:00'
-- Right: DEFAULT '2024-01-01 23:59:59'

3. Fix BLOB/TEXT Default Value medium

BLOB and TEXT types cannot have defaults

1

Remove default from BLOB/TEXT

-- Wrong:
CREATE TABLE posts (content TEXT DEFAULT '');

-- Right:
CREATE TABLE posts (content TEXT);

2

Use VARCHAR if default needed

CREATE TABLE posts (
  content VARCHAR(65535) DEFAULT ''
);

3

Or set default in application code

INSERT INTO posts (content) VALUES (COALESCE(@content, ''));

4. Fix Invalid Enum Default easy

Default must be one of the enum values

1

Use valid enum value as default

-- Wrong:
CREATE TABLE users (status ENUM('active','inactive') DEFAULT 'pending');

-- Right:
CREATE TABLE users (status ENUM('active','inactive','pending') DEFAULT 'pending');

5. Disable Strict Mode (Not Recommended) advanced

Relax validation in development only

1

Check current SQL mode

SELECT @@sql_mode;

2

Remove strict modes for session

SET SESSION sql_mode = '';

🔗

Related Errors

5 related errors

MySQL Error 1067: Invalid Default Column Value