<p>As the link posted by Moto_Nomad indicated, it was an issue with MySQL strict mode GROUP_BY. In my case, I remedied this by using the following guide:</p>

<p><a href="https://serverpilot.io/community/articles/how-to-disable-strict-mode-in-mysql-5-7.html" rel="nofollow">https://serverpilot.io/community/articles/how-to-disable-strict-mode-in-mysql-5-7.html</a></p>

<p>Don't know if this will be of much help to anyone not running a Digital Ocean / ServerPilot stack, but if it saves someone some grief, I hope it helps. :)</p>

<p>Everything is working like a charm. THANK YOU so soooo much, Moto. You have saved my low-fat turkey bacon!</p>

CC BY-SA 3.0
2016-09-29T04:51:24.997Z
+1 for ServerPilot solution
Lindsey D
CC BY-SA 3.0
2016-09-17T02:26:59.050Z
2016-09-17T16:44:36.253Z
2016-09-17T16:44:36.253Z
Dustin C
<p>We just started this process, and I'm having issues migrating. We have a server which we use to build up generic fields and sections, plugins, with dummy content and our developer accounts intact. The way we do fields takes a long time to keep typing in. The idea is that when we deploy a new website, we copy everything to the new site and we have what we need to start content entry.</p>

<p>Here's what I did:</p>

<ol>
<li>Made a copy of the source craft folder and public folder.</li>
<li>Pulled the database from Craft</li>
<li>Uploaded new copy of craft to target server</li>
<li>Replace the config, plugins, templates, and public directories on the target server with the materials from the source.</li>
<li>Create a new database in the Serverpilot backend. (We use SP on all our customer accounts.) We name the database the same, and use the same table prefixes in the settings, as to avoid issues.</li>
<li>In PHPMyAdmin, change the collation of the table to utf8_unicode_ci - as it is on the source server.</li>
<li>Use PHPMyAdmin to upload copy of source SQL to target database.</li>
<li>modify general and db config files to reflect changes</li>
<li>Change permissions on target server App, Storage and Config directories to 774</li>
<li>go to the admin URL and login. </li>
</ol>

<p>I can get to the back end. When I go to the Entries section, and click on add entry, or go into a specific section, I get an error.</p>

<pre><code>Level   profile
Category    system.db.CDbCommand.query
Message end:system.db.CDbCommand.query(SELECT `t`.`id` AS `t0_c0`, `t`.`structureId` AS `t0_c1`, `t`.`fieldLayoutId` AS `t0_c2`, `t`.`name` AS `t0_c3`, `t`.`handle` AS `t0_c4`, `t`.`hasUrls` AS `t0_c5`, `t`.`template` AS `t0_c6`, `t`.`dateCreated` AS `t0_c7`, `t`.`dateUpdated` AS `t0_c8`, `t`.`uid` AS `t0_c9`, `structure`.`id` AS `t1_c0`, `structure`.`maxLevels` AS `t1_c1`, `structure`.`dateCreated` AS `t1_c2`, `structure`.`dateUpdated` AS `t1_c3`, `structure`.`uid` AS `t1_c4` FROM `craft_categorygroups` `t` LEFT OUTER JOIN `craft_structures` `structure` ON (`t`.`structureId`=`structure`.`id`) ORDER BY name)
</code></pre>

<p>If I go to the front page, I get an CDbException error:</p>

<pre><code>CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #48 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'craft.structureelements.root' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. The SQL statement executed was: SELECT `elements`.`id`, `elements`.`type`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_i18n`.`slug`, `elements_i18n`.`uri`, `elements_i18n`.`enabled` AS `localeEnabled`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_n2address`, `content`.`field_n2bannerAlignContentContainer`, `content`.`field_n2allDayEvent`, `content`.`field_n2weatherIcon`, `content`.`field_body`, `content`.`field_n2caption`, `content`.`field_n2careerClassification`, `content`.`field_n2careerEndDate`, `content`.`field_n2careerHoursPerWeek`, `content`.`field_n2careerLocation`, `content`.`field_n2careerStartDate`, `content`.`field_n2contactReasons`, `content`.`field_n2eventDirections`, `content`.`field_n2bannerDisplayOnFrontPage`, `content`.`field_n2eventEnd`, `content`.`field_n2eventRegistrationBegins`, `content`.`field_n2eventRegistrationCutoff`, `content`.`field_n2eventStart`, `content`.`field_n2eventHostedBy`, `content`.`field_n2utilityLink`, `content`.`field_metaDescription`, `content`.`field_n2metaSchemaType`, `content`.`field_n2teamMiniBio`, `content`.`field_n2motto`, `content`.`field_n2description`, `content`.`field_n2phone`, `content`.`field_n2status`, `content`.`field_n2weatherTemp`, `content`.`field_n2eventData`, `content`.`field_n2weatherWindBearing`, `content`.`field_n2weatherWindSpeed`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
FROM `craft_elements` `elements`
JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `craft_content` `content` ON content.elementId = elements.id
JOIN `craft_entries` `entries` ON entries.id = elements.id
JOIN `craft_sections` `sections` ON sections.id = entries.sectionId
LEFT JOIN `craft_structures` `structures` ON structures.id = sections.structureId
LEFT JOIN `craft_structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
WHERE (((elements_i18n.locale = :locale) AND (content.locale = :locale)) AND (elements.id=:elementsid1)) AND (elements.archived = 0)
GROUP BY `elements`.`id`
ORDER BY `lft`, `postDate` DESC LIMIT 1. Bound with :locale='en_us', :elementsid1='2'
</code></pre>

<p>Followed by a MASSIVE stack trace. I've tried exporting from PHPMyAdmin instead, I get the same. I've tried imports and exports from PHPMyMiniAdmin as well, but I'm still throwing the same errors.</p>

<p>I've looked at various articles on how to migrate, but I feel I'm missing something. Are there settings I should be using on import and export in PHPMyAdmin to side-step this? Is there a good step-by-step migration guide for this?</p>

CC BY-SA 3.0
2016-09-17T00:39:44.157Z
Have you setup the new DB user and user privileges correctly?
Moto_Nomad
CC BY-SA 3.0
2016-09-17T01:04:54.723Z
DB user and privs are set the same. They are set by ServerPilot.
Dustin C
CC BY-SA 3.0
2016-09-17T01:50:44.530Z
This might help: http://craftcms.stackexchange.com/questions/12084/getting-this-sql-error-group-by-incompatible-with-sql-mode-only-full-group-by/12106
Moto_Nomad
CC BY-SA 3.0
2016-09-17T02:22:18.413Z
That was correct, actually. -_- Small pain in the butt until we get Craft 3, and easy enough to disable when 3 does come out. :) Thank you!
Dustin C
<error-message>
Migration Errors
# Migration Errors

(Asked by: username_0 on 2016-09-17)

We just started this process, and I'm having issues migrating. We have a server which we use to build up generic fields and sections, plugins, with dummy content and our developer accounts intact. The way we do fields takes a long time to keep typing in. The idea is that when we deploy a new website, we copy everything to the new site and we have what we need to start content entry.
Here's what I did:

Made a copy of the source craft folder and public folder.
Pulled the database from Craft
Uploaded new copy of craft to target server
Replace the config, plugins, templates, and public directories on the target server with the materials from the source.
Create a new database in the Serverpilot backend. (We use SP on all our customer accounts.) We name the database the same, and use the same table prefixes in the settings, as to avoid issues.
In PHPMyAdmin, change the collation of the table to utf8_unicode_ci - as it is on the source server.
Use PHPMyAdmin to upload copy of source SQL to target database.
modify general and db config files to reflect changes
Change permissions on target server App, Storage and Config directories to 774
go to the admin URL and login. 

I can get to the back end. When I go to the Entries section, and click on add entry, or go into a specific section, I get an error.
Level   profile
Category    system.db.CDbCommand.query
Message end:system.db.CDbCommand.query(SELECT `t`.`id` AS `t0_c0`, `t`.`structureId` AS `t0_c1`, `t`.`fieldLayoutId` AS `t0_c2`, `t`.`name` AS `t0_c3`, `t`.`handle` AS `t0_c4`, `t`.`hasUrls` AS `t0_c5`, `t`.`template` AS `t0_c6`, `t`.`dateCreated` AS `t0_c7`, `t`.`dateUpdated` AS `t0_c8`, `t`.`uid` AS `t0_c9`, `structure`.`id` AS `t1_c0`, `structure`.`maxLevels` AS `t1_c1`, `structure`.`dateCreated` AS `t1_c2`, `structure`.`dateUpdated` AS `t1_c3`, `structure`.`uid` AS `t1_c4` FROM `craft_categorygroups` `t` LEFT OUTER JOIN `craft_structures` `structure` ON (`t`.`structureId`=`structure`.`id`) ORDER BY name)

If I go to the front page, I get an CDbException error:
CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #48 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'craft.structureelements.root' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by. The SQL statement executed was: SELECT `elements`.`id`, `elements`.`type`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_i18n`.`slug`, `elements_i18n`.`uri`, `elements_i18n`.`enabled` AS `localeEnabled`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_n2address`, `content`.`field_n2bannerAlignContentContainer`, `content`.`field_n2allDayEvent`, `content`.`field_n2weatherIcon`, `content`.`field_body`, `content`.`field_n2caption`, `content`.`field_n2careerClassification`, `content`.`field_n2careerEndDate`, `content`.`field_n2careerHoursPerWeek`, `content`.`field_n2careerLocation`, `content`.`field_n2careerStartDate`, `content`.`field_n2contactReasons`, `content`.`field_n2eventDirections`, `content`.`field_n2bannerDisplayOnFrontPage`, `content`.`field_n2eventEnd`, `content`.`field_n2eventRegistrationBegins`, `content`.`field_n2eventRegistrationCutoff`, `content`.`field_n2eventStart`, `content`.`field_n2eventHostedBy`, `content`.`field_n2utilityLink`, `content`.`field_metaDescription`, `content`.`field_n2metaSchemaType`, `content`.`field_n2teamMiniBio`, `content`.`field_n2motto`, `content`.`field_n2description`, `content`.`field_n2phone`, `content`.`field_n2status`, `content`.`field_n2weatherTemp`, `content`.`field_n2eventData`, `content`.`field_n2weatherWindBearing`, `content`.`field_n2weatherWindSpeed`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
FROM `craft_elements` `elements`
JOIN `craft_elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `craft_content` `content` ON content.elementId = elements.id
JOIN `craft_entries` `entries` ON entries.id = elements.id
JOIN `craft_sections` `sections` ON sections.id = entries.sectionId
LEFT JOIN `craft_structures` `structures` ON structures.id = sections.structureId
LEFT JOIN `craft_structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
WHERE (((elements_i18n.locale = :locale) AND (content.locale = :locale)) AND (elements.id=:elementsid1)) AND (elements.archived = 0)
GROUP BY `elements`.`id`
ORDER BY `lft`, `postDate` DESC LIMIT 1. Bound with :locale='en_us', :elementsid1='2'

Followed by a MASSIVE stack trace. I've tried exporting from PHPMyAdmin instead, I get the same. I've tried imports and exports from PHPMyMiniAdmin as well, but I'm still throwing the same errors.
I've looked at various articles on how to migrate, but I feel I'm missing something. Are there settings I should be using on import and export in PHPMyAdmin to side-step this? Is there a good step-by-step migration guide for this?

**Tags:** error-message

> **Comments:**
> **username_1**: Have you setup the new DB user and user privileges correctly?  
> **username_0**: DB user and privs are set the same. They are set by ServerPilot.  
> **username_1**: This might help: http://craftcms.stackexchange.com/questions/12084/getting-this-sql-error-group-by-incompatible-with-sql-mode-only-full-group-by/12106  
> **username_0**: That was correct, actually. -_- Small pain in the butt until we get Craft 3, and easy enough to disable when 3 does come out. :) Thank you!  

---

## 1 answers

### Answer by username_0 on 2016-09-17. Score: 3 | Accepted answer

As the link posted by Moto\_Nomad indicated, it was an issue with MySQL strict mode GROUP\_BY. In my case, I remedied this by using the following guide:

<https://serverpilot.io/community/articles/how-to-disable-strict-mode-in-mysql-5-7.html>

Don't know if this will be of much help to anyone not running a Digital Ocean / ServerPilot stack, but if it saves someone some grief, I hope it helps. :)

Everything is working like a charm. THANK YOU so soooo much, Moto. You have saved my low-fat turkey bacon!

> **Comments:**
> **username_2**: +1 for ServerPilot solution  

---

