Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

Why a DATE column will not take CURRENT_DATE or CURDATE() as its default

ErrorERROR 1064 (42000): ... near 'CURRENT_DATE' at line 3

Short answer

Before MySQL 8.0.13 a DEFAULT had to be a fixed literal, so a function such as CURRENT_DATE was refused. On 8.0.13 and later it works, but the expression must sit inside its own parentheses: DEFAULT (CURRENT_DATE). Only CURRENT_TIMESTAMP on TIMESTAMP or DATETIME may be written without them.

  • Applies to MySQL 8.0, MySQL 8.4 LTS
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Print the server version. The cause depends on whether it is below or above 8.0.13.

    SELECT VERSION();
  2. 2Match what you see, then jump to its fix

    1. 1VERSION() returns 5.7.x or 8.0.12 or lower.The server is older than 8.0.13Fix
    2. 2The server is 8.0.13 or newer and the statement says DEFAULT CURRENT_DATE or DEFAULT CURDATE() with no outer brackets.The expression is not wrapped in parenthesesFix
    3. 3Parentheses are present and the default still fails; it calls a stored function, a user variable, a parameter or a subquery.The default uses a construct MySQL does not allowFix

What does the error look like?

  • ERROR 1064 (42000): ... near 'CURRENT_DATE' at line 3
  • ERROR 1067 (42000): Invalid default value for 'created_on'

Cause 1 of 3

The server is older than 8.0.13

1Check

The manual for versions before 8.0.13 says a DEFAULT value must be a literal constant, and that you cannot set a date column's default to a function such as NOW() or CURRENT_DATE. The one exception is CURRENT_TIMESTAMP on TIMESTAMP and DATETIME columns.

SELECT VERSION();

2Fix

Upgrade to 8.0.13 or later. If you cannot, change the column to DATETIME or TIMESTAMP with DEFAULT CURRENT_TIMESTAMP, or send CURDATE() in every INSERT.

ALTER TABLE orders
  MODIFY created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP;

INSERT INTO orders (created_on) VALUES (CURDATE());

3Verify

Insert a row without the column and read it back.

SELECT created_at FROM orders ORDER BY id DESC LIMIT 1;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

The expression is not wrapped in parentheses

1Check

Since 8.0.13 a DEFAULT can be an expression, but it has to be enclosed in parentheses so the parser can tell it from a literal. Only CURRENT_TIMESTAMP on TIMESTAMP and DATETIME is exempt. The manual's own DATE example is written DEFAULT (CURRENT_DATE + INTERVAL 1 YEAR), and it lists built-in functions as permitted.

SHOW CREATE TABLE orders\G

2Fix

Add the parentheses, in CREATE TABLE or on an existing column.

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  created_on DATE NOT NULL DEFAULT (CURRENT_DATE)
);

ALTER TABLE orders
  MODIFY created_on DATE NOT NULL DEFAULT (CURDATE());

3Verify

Insert a row that leaves the column out; it receives today's date.

INSERT INTO orders () VALUES ();
SELECT id, created_on FROM orders ORDER BY id DESC LIMIT 1;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

The default uses a construct MySQL does not allow

1Check

The manual allows literals, built-in functions and operators in an expression default. It forbids subqueries, parameters, variables, stored functions and loadable functions. A default may also refer only to columns defined earlier in the table, and not to an AUTO_INCREMENT column.

SHOW CREATE TABLE orders\G

2Fix

Replace the forbidden part with a built-in function, or fill that value in the INSERT statement instead.

-- not allowed: DEFAULT (my_stored_fn())
-- allowed:
d DATE NOT NULL DEFAULT (CURRENT_DATE + INTERVAL 7 DAY)

3Verify

Run the CREATE TABLE again; it finishes without an error.

SHOW CREATE TABLE orders\G

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Write every non-literal default with its own parentheses, even when a shorter form seems to work.
  • Check SELECT VERSION() on each environment before you ship a migration that uses expression defaults.
  • Keep default expressions to built-in functions and operators.

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.