Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

We've moved the forum!

Please use forum.silverstripe.org for any new questions (announcement).
The forum archive will stick around, but will be read only.

You can also use our Slack channel or StackOverflow to ask for help.
Check out our community overview for more options to contribute.

Data Model Questions /

Moderators: martimiz, Sean, Ed, biapar, Willr, Ingo, swaiba

Migrating to MySQL 5.7


Go to End


3 Posts   2681 Views

Avatar
matthewhaigh

Community Member, 1 Post

3 March 2017 at 12:40am

Edited: 03/03/2017 12:42am

Hi all,

We have been trying to migrate one of our Silverstripe websites to a new server running MySQL 5.7. We have already updated our SS version to 3.5.3.

We've been experiencing issues with the sql_mode=only_full_group_by, example of error below:

[User Error] Uncaught SS_DatabaseException: Couldn't run query: SELECT DISTINCT "Download"."ClassName", "Download"."LastEdited", "Download"."Created", "Download"."Title", "Download"."ShowOnInstruments", "Download"."Restricted", "Download"."Index", "Download"."Slug", "Download"."FileID", "Download"."InstrumentID", "Download"."CategoryID", "Download"."ID", CASE WHEN "Download"."ClassName" IS NOT NULL THEN "Download"."ClassName" ELSE 'Download' END AS "RecordClassName" FROM "Download" WHERE ("CategoryID" = ?) GROUP BY FileID ORDER BY "Download"."Index" ASC, "Download"."Title" ASC Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'bolton-surgical.Download.ClassName' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
GET /downloads/
Line 55 in /srv/users/bolton/apps/bolton-surgical/public/framework/model/connect/DBConnector.php

We have already disabled strict sql mode in MySQL 5.7 on our server, which points to something inside SS overriding this setting.

Has anyone had experience with this issue? I did notice this page on the SS GitHub page: https://github.com/silverstripe/silverstripe-framework/issues/5451 but I don't think it actually shares a solution?

thanks

Matt.

Avatar
dave.m

Community Member, 19 Posts

12 July 2017 at 4:53pm

Hit same issue today with SS 3.6.0 & MySQL 5.7.18

SELECT DISTINCT YEAR("Date") AS "Year", MONTH("Date") AS "Month"
FROM "SiteTree_Live"
LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN PortalUpdatePage_Live ON PortalUpdatePage_Live.ID = SiteTree_Live.ID
WHERE (SiteTree_Live.ParentID = ?)
 AND ("SiteTree_Live"."ClassName" IN (?, ?))
 AND ("SiteTree_Live"."SubsiteID" IN (0))
 AND ("SiteTree_Live"."Locale" = 'en_NZ')
 AND ("Date" IS NOT NULL)
GROUP BY YEAR("Date"), MONTH("Date")
ORDER BY "Date" DESC

Avatar
dave.m

Community Member, 19 Posts

12 July 2017 at 6:25pm

My issue was in CWP "DatedUpdateHolder" in the ExtractMonths function.
Changed to:

$dates = $updates->dataQuery()
			->groupby('YEAR("Date")')
			->groupby('MONTH("Date")')
			//->sort('Date', 'DESC')
            ->sort('Year', 'DESC')
            ->sort('Month', 'DESC')
			->query()
			->setSelect(array(
				'Year' => 'YEAR("Date")',
				'Month' => 'MONTH("Date")',
			))
			->addWhere('"Date" IS NOT NULL')
			->execute();