1

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?

  • 1
    Have you setup the new DB user and user privileges correctly? – Moto_Nomad Sep 17 '16 at 0:39
  • DB user and privs are set the same. They are set by ServerPilot. – Dustin C Sep 17 '16 at 1:04
  • 1
    This might help: craftcms.stackexchange.com/questions/12084/… – Moto_Nomad Sep 17 '16 at 1:50
  • 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 Sep 17 '16 at 2:22
3

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!

|improve this answer|||||
  • 1
    +1 for ServerPilot solution – Lindsey D Sep 29 '16 at 4:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.