Database migration #128 doesn't work on MySQL 5.7.4 onwards

[Cysioland]

For query:

Code: Select all

alter table ttrss_feeds add column feed_language varchar(100)

it gives an error:

Code: Select all

Invalid default value for 'last_updated'

This is due to NO_ZERO_DATE being a default. Amending

Code: Select all

last_updated

field to use

Code: Select all

1970-01-01 00:00:00

instead of

Code: Select all

00-00-00 00:00:00

as a default allows migrations to go through.

[fox]

is the change backwards compatible though? if it is, i can update the schema file in the repository (if you include the full version file in your post), otherwise i think the best course of action here is moving this thread to knowledge base.

[MacDork]

Is there a fix coming for this?

[sleeper_service]

Is there a fix coming for this?

switch to postgres... fixed.

[fox]

Is there a fix coming for this?

this is kind of a stupid question, don't you think?

[MacDork]

Is there a fix coming for this?

this is kind of a stupid question, don't you think?

I'll give you that it's an ignorant question -- that's why I asked. I don't know the answer.

[MacDork]

Ha. I fixed it. Thank you, Cysioland.

[fox]

i'd like to note that ttrss_feeds.last_updated defaults to null (as do several other similar columns). is this not allowed by mysql for some reason now? tt-rss never uses default value of '0000-00-00 00:00:00' or any such variants.

also, chuckled at this:

Code: Select all

As of MySQL 5.7.4, NO_ZERO_DATE is deprecated. In MySQL 5.7.4 through 5.7.7, NO_ZERO_DATE does nothing when named explicitly. Instead, its effect is included in the effects of strict SQL mode. In MySQL 5.7.8 and later, NO_ZERO_DATE does have an effect when named explicitly and is not part of strict mode, as before MySQL 5.7.4. However, it should be used in conjunction with strict mode and is enabled by default. A warning occurs if NO_ZERO_DATE is enabled without also enabling strict mode or vice versa. For additional discussion, see SQL Mode Changes in MySQL 5.7. [


the usual mysql approach to design: nothing ever behaves as displayed. from the developers that gave us the ever brilliant "begin and commit instructions are silently ignored".

[fox]

alright, i think i get it: last_updated HAD a default value of 0 at some point instead of NULL (fixed sometime after Jul 2015), which is what caused this. i guess you can run into this if you upgrade a legacy installation.

i am however not sure if upgrade file 128 is the only place this could be encountered. the code should look like this:

Code: Select all

update ttrss_feeds set last_updated = NULL;
alter table ttrss_feeds modify column last_updated datetime DEFAULT NULL;


given that other people with various obsolete tt-rss versions may run into this in the future, i'll link this thread in the FAQ.