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](https://stackoverflow.com/questions/7024010/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, Feb 08 '21 at 12:38
Which version of mysql? i copied your statements, and did not get an error.... (on MySQL8.0.22) — Luuk, Feb 08 '21 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, Feb 08 '21 at 12:40
can copy copy/paste the complete message, and add this to your question? — Luuk, Feb 08 '21 at 12:41
You are getting error on both: https://www.db-fiddle.com/f/uyFQ9jDQnriqk4QWBErzqc/0 — ikiK, Feb 08 '21 at 12:41
@ikiK Db-fiddle is using an older version i think. For example, take a look here it works with the insert. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=14700e77783fdf7e01a31def615aa035 — ravexu, Feb 08 '21 at 12:45
It indeed throws an error, see: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2df3b421793a7470a321247abec85b47 — Luuk, Feb 08 '21 at 12:47
@a_horse_with_no_name That is the first that works. The second breaks which is `ALTER TABLE users ADD anotherDate datetime DEFAULT (now()) NOT NULL; -- This errors` — ravexu, Feb 08 '21 at 12:47
I do think it is this error: https://stackoverflow.com/questions/36882149/error-1067-42000-invalid-default-value-for-created-at — Luuk, Feb 08 '21 at 12:51
@Luuk I have seen that post. I dont see why that would be causing an error because the timestamp being inserted is never an invalid format? — ravexu, Feb 08 '21 at 12:52
see this dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7ed040c994bc3e5fec04630c8f8bb494 After change of sql_mode, it works — Luuk, Feb 08 '21 at 12:53
Seems like a bug. Because if you change the order of the ALTER statements, you still get the same error. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3d4c4b8d289d874115b072dfd6f89dd1 (these are the kind of quirks that let me seriously doubt the quality of that code base) — , Feb 08 '21 at 12:53
@Luuk Can confirm that works. Why is this the case though? Is it a bug or what sql mode is causing this? — ravexu, Feb 08 '21 at 12:57

Luuk · Accepted Answer · 2021-02-08T13:05:44.467

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)

Can you [please vote to undelete and reopen this question](https://stackoverflow.com/questions/65371880/remove-file-in-dir-where-number-of-matching-pattern-below-a-threshold-on-linux/65372097#65372097) — anubhava, Feb 12 '21 at 18:44

Getting default value error when creating new column

1 Answers1