MySQL: unexplained 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 try to update one of the fields via the command line or phpMyAdmin, I get an error about the other field.

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

Please note that the fields in the error are not fields in the command. How can I solve this problem without destroying my data?

Answer 1

@easleyfixed Using MySQL Workbench's hints solved the problem as it crafted the optimal SQL to allow 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 Example p>