Knowledgebase : X-Cart 5 Tips and Tricks

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, AS name1, AS author2, x0_.authorEmail AS authorEmail3, x0_.enabled AS enabled4, x0_.installed AS installed5, x0_.yamlLoaded AS yamlLoaded6, 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,' 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 <user> -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.