Running Sitemap XML v3.9.6 on zc157c with strict MySQL. Running the admin tool to create the base sitemaps results in a MySQL fatal error:
[03-Jan-2022 21:23:52 Europe/Berlin] Request URI: /mysite/index.php?main_page=sitemapxml&rebuild=yes, IP address: 127.0.0.1
#1 trigger_error() called at [C:\xampp\htdocs\mysite\includes\classes\db\mysql\query_factory.php:171]
#2 queryFactory->show_error() called at [C:\xampp\htdocs\mysite\includes\classes\db\mysql\query_factory.php:143]
#3 queryFactory->set_error() called at [C:\xampp\htdocs\mysite\includes\classes\db\mysql\query_factory.php:270]
#4 queryFactory->Execute() called at [C:\xampp\htdocs\mysite\includes\modules\pages\sitemapxml\sitemapxml_products_reviews.php:30]
#5 include(C:\xampp\htdocs\mysite\includes\modules\pages\sitemapxml\sitemapxml_products_reviews.php) called at [C:\xampp\htdocs\mysite\includes\templates\template_default\sitemapxml\tpl_main_page.php:21]
#6 require(C:\xampp\htdocs\mysite\includes\templates\template_default\sitemapxml\tpl_main_page.php) called at [C:\xampp\htdocs\mysite\index.php:94]
--> PHP Fatal error: 1055:'canadianllama157.rd.languages_id' isn't in GROUP BY :: SELECT r.products_id, MAX(r.date_added) AS date_added, MAX(r.last_modified) AS last_modified, GREATEST(MAX(r.date_added), IFNULL(MAX(r.last_modified), '0001-01-01 00:00:00')) AS last_date, rd.languages_id
FROM reviews r
LEFT JOIN reviews_description rd ON (r.reviews_id = rd.reviews_id),
products p
WHERE p.products_id=r.products_id
AND p.products_status=1
AND r.status = 1
AND rd.languages_id IN (1,2)
GROUP BY r.products_id ORDER BY last_date DESC ==> (as called by) C:\xampp\htdocs\mysite\includes\modules\pages\sitemapxml\sitemapxml_products_reviews.php on line 30 <== in C:\xampp\htdocs\mysite\includes\classes\db\mysql\query_factory.php on line 171.
I was able to correct the issue via update to /includes/modules/pages/sitemapxml/sitemapxml_products_reviews.php, adding the highlighted change to the GROUP BY clause:
<?php
/**
* Sitemap XML
*
* @package Sitemap XML
* @copyright Copyright 2005-2015 Andrew Berezin eCommerce-Service.com
* @copyright Copyright 2003-2015 Zen Cart Development Team
* @copyright Portions Copyright 2003 osCommerce
* @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
* @version $Id: sitemapxml_products_reviews.php, v 1.1 31.01.2015 15:29:52 AndrewBerezin $
*/
echo '<h3>' . TEXT_HEAD_PRODUCTS_REVIEWS . '</h3>';
$last_date = $db->Execute("SELECT MAX(GREATEST(r.date_added, IFNULL(r.last_modified, '0001-01-01 00:00:00'))) AS last_date
FROM " . TABLE_REVIEWS . " r
WHERE r.status = '1'");
$table_status = $db->Execute("SHOW TABLE STATUS LIKE '" . TABLE_REVIEWS . "'");
$last_date = max($table_status->fields['Update_time'], $last_date->fields['last_date']);
if ($sitemapXML->SitemapOpen('products_reviews', $last_date)) {
[B]//-bof-20220103-lat9-Correcting MySQL fatal error, missing rd.languages_id from GROUP BY[/B]
$sql = "SELECT r.products_id, MAX(r.date_added) AS date_added, MAX(r.last_modified) AS last_modified, GREATEST(MAX(r.date_added), IFNULL(MAX(r.last_modified), '0001-01-01 00:00:00')) AS last_date, rd.languages_id
FROM " . TABLE_REVIEWS . " r
LEFT JOIN " . TABLE_REVIEWS_DESCRIPTION . " rd ON (r.reviews_id = rd.reviews_id),
" . TABLE_PRODUCTS . " p
WHERE p.products_id=r.products_id
AND p.products_status=1
AND r.status = 1
AND rd.languages_id IN (" . $sitemapXML->getLanguagesIDs() . ")
GROUP BY r.products_id[B], rd.languages_id[/B]" .
(SITEMAPXML_PRODUCTS_REVIEWS_ORDERBY != '' ? " ORDER BY " . SITEMAPXML_PRODUCTS_REVIEWS_ORDERBY : '');
[B]//-eof-20220103-lat9[/B]
$reviews = $db->Execute($sql);
/*
if (zen_not_null($result['last_modified']) ){
$lastmod = $reviews->fields['last_modified'];
} else {
$lastmod = $reviews->fields['date_added'];
}
*/
$sitemapXML->SitemapSetMaxItems($reviews->RecordCount());
while (!$reviews->EOF) {
$sitemapXML->writeItem(FILENAME_PRODUCT_REVIEWS, 'products_id=' . $reviews->fields['products_id'], $reviews->fields['languages_id'], $reviews->fields['last_date'], SITEMAPXML_PRODUCTS_REVIEWS_CHANGEFREQ);
$reviews->MoveNext();
}
$sitemapXML->SitemapClose();
unset($reviews);
}
// EOF
