Why a DATE column will not take CURRENT_DATE or CURDATE() as its default
ERROR 1064 (42000): ... near 'CURRENT_DATE' at line 3Short 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.
Fix it fast
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();2Match what you see, then jump to its fix
- 1
VERSION() returns 5.7.x or 8.0.12 or lower.The server is older than 8.0.13Fix - 2
The 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
Parentheses 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
- 1
What does the error look like?
ERROR 1064 (42000): ... near 'CURRENT_DATE' at line 3ERROR 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
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\G2Fix
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
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\G2Fix
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\GConfirmed by dev.mysql.com
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fixDatabases
DBeaver cannot verify the MySQL server certificate
unable to find valid certification path to requested target
DBeaver's MySQL driver is checking the server certificate and Java does not trust the CA that signed it.
2 causes, each with a fix