MySQL ALTER TABLE add column: error at another column

Question

ok, i have people table and try run sql-script with the next command:

ALTER TABLE `people`
    ADD COLUMN `name_spelling`
    VARCHAR(255) NULL DEFAULT NULL
    AFTER `last_name`;

Then I'll get a error:

[22001][1292] Data truncation: Incorrect date value:
'0000-00-00' for column 'birth_date' at row 35

At this row birth_date is NULL, but if I try set some date like 2016-05-05 at this column and row and than set NULL back -- all works is correct at this row, but get error on some next null date.

In addition, not all null date values return error.

Does all other field of your database are nullable and are default values ? Cause if not nullable and not are defautl values... you will have this error on each filed. — M.Be
– M.Be, Commented Oct 19, 2016 at 13:56
@sisanared, birth_date created by ALTER TABLE 'people' ADD COLUMN 'birth_date' DATE NULL AFTER 'last_name'; — Samuel Loog
– Samuel Loog, Commented Oct 19, 2016 at 14:03

Hugo St-Arnaud · Accepted Answer · 2016-10-19 14:51:45Z

1

Maybe your MySQL has some option such as the NO_ZERO_DATE SQL mode (I think it's not the only mysql config that prevents 0000-00-00 dates). This would prevent you from using 0000-00-00 as a value. It happened to me before that I had a table already created violating that condition. It will not be editable until you change the value or disable what's preventing you to set 0 dates.

For reference :http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

answered Oct 19, 2016 at 14:51

Hugo St-Arnaud

1695 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chukwuka mark · Accepted Answer · 2018-10-19 02:25:55Z

0

I ran into this issue once and the fix i had was to to edit mysql.cnf file Replace : sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

with this : sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Notice i removed the No_ZEROs in that options.

answered Oct 19, 2018 at 2:25

chukwuka mark

1742 silver badges11 bronze badges

Collectives™ on Stack Overflow

MySQL ALTER TABLE add column: error at another column

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related