Migration Errors

Question

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:

1. Made a copy of the source craft folder and public folder.
2. Pulled the database from Craft
3. Uploaded new copy of craft to target server
4. Replace the config, plugins, templates, and public directories on the target server with the materials from the source.
5. 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.
6. In PHPMyAdmin, change the collation of the table to utf8_unicode_ci - as it is on the source server.
7. Use PHPMyAdmin to upload copy of source SQL to target database.
8. modify general and db config files to reflect changes
9. Change permissions on target server App, Storage and Config directories to 774
10. 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?

Answer 1

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!