MySQL: Inexplicable Date field dependency

Question

I'm trying to update the default values of two date fields in an existing database. The output of SHOW CREATE TABLE for these two fields is:

  `dateIn` date DEFAULT '0000-00-00',
  `dateDue` date DEFAULT '0000-00-00',

However, when I attempt to update one either through the command line or via phpMyAdmin, I receive an error regarding the other field.

mysql> ALTER TABLE job MODIFY COLUMN dateIn date DEFAULT NULL;
ERROR 1067 (42000): Invalid default value for 'dateDue'

Note that the field in the error is not the one in the command. How can I resolve this issue without destroying data?

Why don't you set the default value for the DateIn Column to something other than NULL? — easleyfixed, Apr 10 '23 at 21:48
Side note - ONLY if you are FORCED to do like Go Daddy websites and HAVE to use phpadmin .. use it .. but in all other cases, MySQL Workbench is FAR FAR FAR superior to use for database stuff to handle things like this with ease. — easleyfixed, Apr 10 '23 at 21:49
In other words, the default in the first thing says "0000-00-00" and your alter statement says DEFAULT NULL .. see the difference? — easleyfixed, Apr 10 '23 at 21:51
Is this [link](https://stackoverflow.com/a/37696251/7534856) solve your problem? — Niyaz, Apr 10 '23 at 21:54
It's because the current sql mode does not accept '0000-00-00' as a valid date. When you try to ALTER one of the columns the DDL for the whole table, in its new state, is evaluated. Change them both at the same time. — user1191247, Apr 10 '23 at 22:09
@easleyfixed, I would be happy to set the default value to ANY valid value. However, no matter what I set, it continually errors out about the other field. On the other hand, using MySQL Workbench, I was able to modify both fields and apply the changes successfully, so thank you for that suggestion! — tufelkinder, Apr 11 '23 at 15:44

score 1 · Accepted Answer · edited Apr 13 '23 at 07:16

1

@easleyfixed's prompt to use MySQL Workbench turned out to solve the problem as it crafted the best SQL to enable me to modify both columns in the same command.

ALTER TABLE job
    MODIFY COLUMN dateIn  DATE DEFAULT NULL,
    MODIFY COLUMN dateDue DATE DEFAULT NULL;

ALTER TABLE Examples

edited Apr 13 '23 at 07:16

user1191247

10,808
2
22
32

answered Apr 11 '23 at 16:02

tufelkinder

1,176
1
15
37

MySQL: Inexplicable Date field dependency

1 Answers1