CODEWITHSUNDEEP
mysql
ravexu
ravexu

Reputation: 675

Getting default value error when creating new column

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

Upvotes: 0

Views: 779

Answers (1)

Luuk
Luuk

Reputation: 14968

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)

Upvotes: 3

Related Questions