Mariadb alter table column data type error, wrong column

Question

I am having a bit of a problem with mariadb with the following query

ALTER TABLE archive_maindata CHANGE monthly_income monthly_income decimal(25,4) DEFAULT '0.0000';

but I got this error,

Additional information: Incorrect date value: '0000-00-00' for column 'expiry_date' at row 3'

I can't figure out what it is that I am doing wrong, I am not trying to change the expire_date column as you can see in the query. Any help would be appreciated Thanks in advance

I also don't know what the source of your error is, but setting `'0.0000'` as the default value of a decimal column makes no sense. You should be using a number literal, e.g. just `0.0000`. — Tim Biegeleisen, Nov 07 '19 at 14:33
Also you are changing `monthly_income` why it would care about `expiry_date`? — Ingus, Nov 07 '19 at 14:54
Does this answer your question? [#1292 - Incorrect date value: '0000-00-00'](https://stackoverflow.com/questions/37292628/1292-incorrect-date-value-0000-00-00) — Ingus, Nov 07 '19 at 14:58
no that did not help, I am not trying to enter data and the default for the expiry_date column is NULL. I think that error should only come up if I am trying to enter data. I am fighting with it for two days now — Crome Bash, Nov 07 '19 at 15:10

score 1 · Answer 1 · answered Nov 07 '19 at 15:14

1

Sounds like you updated MariaDB at some point and the default SQL_MODE in the later version defaults to STRICT_TRANS_TABLES which will disallow a date/time that is all zeros. The default changed with MariaDB 10.2.4.

You need to either fix the dates so they are valid or change the SQL_MODE to disable STRICT_TRANS_TABLES. The error you are getting is MariaDB trying to help you by warning you that you have data that is not valid.

answered Nov 07 '19 at 15:14

Dave

5,108
16
30
40

They ones without dates are NULL – Crome Bash Nov 07 '19 at 15:21
the datatype is DATE for expiry_date – Crome Bash Nov 07 '19 at 15:25
NULL Is valid as long as the column is set to allow nulls. The error is reporting that there are values in the column that contain all zeros. You need to change the SQL_MOD **OR** make the rows with all zeros null instead **OR** change the rows with all zeros to a valid date. – Dave Nov 07 '19 at 15:34
That I know, but that column I am not trying to modify and the column is ok as there is nothing in it that should give that error, as far as I can see. – Crome Bash Nov 08 '19 at 03:33

Mariadb alter table column data type error, wrong column

1 Answers1