Getting default value error when creating new column

Question

I have the code below but for some reason in mysql it gives error when running the second alter table statement to add a new column. The error i get is Invalid default value for 'start'

If i comment out the second alter table it works because the data gets inserted.

CREATE TABLE users (
  id int unsigned NOT NULL PRIMARY KEY,
  name varchar(255) NOT NULL
);



ALTER TABLE users ADD start datetime DEFAULT (DATE_SUB(now(), INTERVAL -1 DAY)) NOT NULL;

insert into users (id, name) values (1, 'usera'), (2, 'usera');

ALTER TABLE users ADD anotherDate datetime DEFAULT (now()) NOT NULL; -- This errors

select * from users;

Full error is: ERROR 1067 42000 Invalid default value for 'start' Fiddle to show error: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2df3b421793a7470a321247abec85b47

Does this answer your question? MySQL datetime default time with interval You can not do that in default column. You need to set it as datetime and do calculations on insert. — ikiK
– ikiK, Commented Feb 8, 2021 at 12:38
Which version of mysql? i copied your statements, and did not get an error.... (on MySQL8.0.22) — Luuk
– Luuk, Commented Feb 8, 2021 at 12:39
@ikiK No because that answer says it cant be done at all. However i am able to do it because the insert statement works. Its just the second alter throws error. — ravexu
– ravexu, Commented Feb 8, 2021 at 12:40
see this dbfiddle: dbfiddle.uk/… After change of sql_mode, it works — Luuk
– Luuk, Commented Feb 8, 2021 at 12:53

Luuk · Accepted Answer · 2021-02-08 13:05:44Z

3

I started with sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION':

mysql> ALTER TABLE users ADD start datetime DEFAULT (DATE_SUB(now(), INTERVAL -1 DAY)) NOT NULL;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> insert into users (id, name) values (1, 'usera'), (2, 'usera');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> ALTER TABLE users ADD anotherDate datetime DEFAULT (now()) NOT NULL;
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> set sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_IN_DATE,NO_ZERO_DATE';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> ALTER TABLE users ADD anotherDate2 datetime DEFAULT (now()) NOT NULL;
ERROR 1067 (42000): Invalid default value for 'start'
mysql>

Both NO_ZERO_IN_DATE and NO_ZERO_DATE are deprecated according to https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html#sqlmode_no_zero_in_date

this is on MySQL 8.0.22

EDIT: oops, i must read it myself 😉

mysql> set sql_mode = 'STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message
                                                                   |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

edited Feb 8, 2021 at 13:05

answered Feb 8, 2021 at 12:59

Luuk

15.4k5 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ravexu Over a year ago

Thanks that was it

anubhava Over a year ago

Can you please vote to undelete and reopen this question

Collectives™ on Stack Overflow

Getting default value error when creating new column

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related