Can't add column to existing table in MySQL-CodePudding

Trying to add a column to an existing table using the following query:

ALTER TABLE users ADD exp_date DATE NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER paidthru;

Getting this error:

ALTER TABLE users ADD exp_date DATE NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER paidthru; MySQL said: Documentation

#1067 - Invalid default value for 'exp_date'

CodePudding user response：

You are adding a DATE column but giving it a DATETIME default which is incompatible.

Use DEFAULT CURRENT_DATE instead or change your field to DATETIME

CodePudding user response：

CURRENT_DATE is not a valid default, unless you use the new expression default syntax in MySQL 8.0:

mysql> create table users (id serial primary key);
Query OK, 0 rows affected (0.03 sec)

mysql> alter table users add exp_date date not null default current_date;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'current_date' at line 1

Whoops! Try again, with extra parentheses:

mysql> alter table users add exp_date date not null default (current_date);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table users\G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `exp_date` date NOT NULL DEFAULT (curdate()),
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Looks like it accepted it, but it replaced CURRENT_DATE with CURDATE(). These are the same.