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

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
Cysioland
Bear Rating Trainee
Bear Rating Trainee
Posts: 1
Joined: 10 Aug 2016, 00:11

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

Postby Cysioland » 10 Aug 2016, 00:22

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.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

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

Postby fox » 10 Aug 2016, 00:48

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
Bear Rating Trainee
Bear Rating Trainee
Posts: 30
Joined: 01 May 2013, 05:55

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

Postby MacDork » 16 Aug 2016, 02:49

Is there a fix coming for this?

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

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

Postby sleeper_service » 16 Aug 2016, 05:27

MacDork wrote:Is there a fix coming for this?

switch to postgres... fixed.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

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

Postby fox » 16 Aug 2016, 08:03

MacDork wrote:Is there a fix coming for this?


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

MacDork
Bear Rating Trainee
Bear Rating Trainee
Posts: 30
Joined: 01 May 2013, 05:55

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

Postby MacDork » 17 Aug 2016, 02:14

fox wrote:
MacDork wrote: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
Bear Rating Trainee
Bear Rating Trainee
Posts: 30
Joined: 01 May 2013, 05:55

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

Postby MacDork » 17 Aug 2016, 02:34

Ha. I fixed it. Thank you, Cysioland.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

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

Postby fox » 17 Aug 2016, 08:33

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".

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

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

Postby fox » 17 Aug 2016, 09:35

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.


Return to “Support”

Who is online

Users browsing this forum: Bing [Bot] and 10 guests