Invalid default value for 'date_available'

Question

I am trying to alter catalog_no, but while altering it I am getting error regarding 'date_available' column

My SQL for altering catalog_no is:

ALTER TABLE `product` CHANGE `catalog_no` `catalogno` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

But this is giving me error #1067- Invalid default value for 'date_available'

What is the reason for this error and how can I solve it, Please help.

Thanks

does date_available exist on the same table? is there a trigger on this table? — Paul Maxwell, Dec 09 '17 at 04:30
what default is set for that column? and what data type is it (exactly) — Paul Maxwell, Dec 09 '17 at 04:32
date_available -> data_type = date -> default_value = 0000-00-00 catalog_no ->data_type = VARCHAR(255) -> default_value = None — Pankti Shah, Dec 09 '17 at 04:35

score 5 · Accepted Answer · answered Dec 09 '17 at 04:35

5

The problem is because of sql_modes. Please check your current sql_modes by command:

show variables like 'sql_mode' ;

And remove the sql_mode "NO_ZERO_IN_DATE,NO_ZERO_DATE" to make it work.

This is the default sql_mode in mysql new versions.

answered Dec 09 '17 at 04:35

Vijay Arun

No, This is the default sql_mode in mysql new versions – Vijay Arun Dec 09 '17 at 04:41

1 Answers1