X-Cart 5.2.x fails under MySQL 5.7.x

X-Cart 5.2.x fails under MySQL 5.7.x

MySQL 5.7.x installs with "ONLY_FULL_GROUP_BY" sql_mode active by default. With this MySQL option active X-Cart 5.2.x fails and throws an error with the famous orange Oops! screen:

 

An exception occurred while executing 'SELECT x0_.moduleID AS moduleID0, x0_.name AS name1, x0_.author AS author2, x0_.authorEmail AS authorEmail3, x0_.enabled AS enabled4, x0_.installed AS installed5, x0_.yamlLoaded AS yamlLoaded6, x0_.date AS date7, x0_.rating AS rating8, x0_.votes AS votes9, x0_.downloads AS downloads10, x0_.price AS price11, x0_.currency AS currency12, x0_.majorVersion AS majorVersion13, x0_.minorVersion AS minorVersion14, x0_.minorRequiredCoreVersion AS minorRequiredCoreVersion15, x0_.revisionDate AS revisionDate16, x0_.packSize AS packSize17, x0_.xcnPlan AS xcnPlan18, x0_.moduleName AS moduleName19, x0_.authorName AS authorName20, x0_.description AS description21, x0_.iconURL AS iconURL22, x0_.pageURL AS pageURL23, x0_.authorPageURL AS authorPageURL24, x0_.dependencies AS dependencies25, x0_.tags AS tags26, x0_.fromMarketplace AS fromMarketplace27, x0_.isLanding AS isLanding28, x0_.landingPosition AS landingPosition29, x0_.isSystem AS isSystem30, x0_.hasLicense AS hasLicense31, x0_.editionState AS editionState32, x0_.editions AS editions33, x0_.xbProductId AS xbProductId34, x0_.isSkin AS isSkin35 FROM xc_modules x0_ WHERE x0_.installed = ? GROUP BY x0_.name, x0_.author' with params [1]: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'xc52x.x0_.moduleID' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

To fix this and make X-cart 5.2.x compatible with MySQL 5.7.x you can do the following:

1. SSH to the server.

2. Login to MySQL by running the following from the command line:

mysql -u -p

3. Run the following MySQL queries in this order:

3.1. This will show which mysql_mode modes are active

SELECT @@mysql_mode; 

3.2. Then run the following repeating all active modes excluding "ONLY_FULL_GROUP_BY"

SET sql_mode="LIST OF MODES";

4. You may need to restart the MySQL server before these changes take effect.

After that X-Cart should load just fine.

 

    • Related Articles

    • X-Cart 4.7.4 extra fields info in products list pages

      With the new X-Cart Classic version - 4.7.4 - the extra fields information by default is not included with products on products list pages. To have this information included there do the following: Open config.php file (or config.local.php if exists) ...

    • What is the cost for modules upgrades?

      Modules upgrades are free within the same cart branch they were purchased for originally. For upgrades between cart branches the cost is 25% of the original price. Installation service for modules upgrades is not discounted.