I am getting a MySQL insert error in database - "Invalid default value"

Question

When I execute

ALTER TABLE foodapp_order
ADD COLUMN customer_ar VARCHAR(15) AFTER customer_name;

Then I am getting this error:

Error

SQL query:

ALTER TABLE foodapp_order ADD COLUMN customer_ar VARCHAR(15) AFTER customer_name

MySQL said: Documentation f#1067 - Invalid default value for 'order_date'

Possible duplicate of [Invalid default value for 'dateAdded'](https://stackoverflow.com/questions/9005766/invalid-default-value-for-dateadded) — Rogério Dec, May 23 '18 at 14:15
the error seems to refer to a column which is not mentioned in your query. So I guess the issue is with something existing in your table, which you haven't shown us. — ADyson, May 23 '18 at 14:20
Local Databases/zb_server/foodapp_order/ http://localhost/phpmyadmin/tbl_sql.php?db=zb_server&table=foodapp_order&token=9792f4eda35aa1d9cce5f79fdabbefc4 Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available. Your SQL query has been executed successfully. show variables like 'sql_mode' sql_mode STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,E... — Kuldeep Raj, May 23 '18 at 14:20

score 0 · Answer 1 · answered May 23 '18 at 14:14

0

As seen from the error message, it seems to be an issue with the current schema of the table and the column order_date. Check for any type mismatch with order_date default value, from the schema.

answered May 23 '18 at 14:14

ragav ramachandran

368
2
8

score 0 · Answer 2 · edited Jun 01 '22 at 12:03

0

A problem is there with something else. You may have the order_date column with some default value.

The format of default value may be something like

order_date default '0000-00-00'

Make sure you have corrected the "NO_ZERO_DATE" strict setting.

In strict mode, don't allow '0000-00-00' as a valid date. Hence failing.

Refer NO_ZERO_DATE

edited Jun 01 '22 at 12:03

Peter Mortensen

30,738
21
105
131

answered May 23 '18 at 14:15

Red Boy

5,429
3
28
41

score 0 · Answer 3 · edited Jun 01 '22 at 12:02

0

When I am using

set global sql_mode="NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

this setting is working fine.

edited Jun 01 '22 at 12:02

Peter Mortensen

30,738
21
105
131

answered May 23 '18 at 14:52

Kuldeep Raj

791
1
7
29

I am getting a MySQL insert error in database - "Invalid default value"

3 Answers3