4

Magento fails to generate the sitemap.xml. I get Unable to generate the sitemap..

enter image description here

I get this exception:

2019-02-25T15:00:59+00:00 DEBUG (7): Exception message: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NULLurl_rewrite.id_path LIKE 'category/%'
 INNER JOIN `magecatalog_category_enti' at line 2, query was: SELECT `main_table`.`entity_id`, `url_rewrite`.`request_path` FROM `magecatalog_category_entity` AS `main_table`
 LEFT JOIN `magecore_url_rewrite` AS `url_rewrite` ON url_rewrite.category_id=main_table.entity_id AND url_rewrite.is_system=1 AND url_rewrite.store_id = 1 AND url_rewrite.category_id IS NOT NULLurl_rewrite.id_path LIKE 'category/%'
 INNER JOIN `magecatalog_category_entity_int` AS `t1_is_active` ON main_table.entity_id=t1_is_active.entity_id AND t1_is_active.store_id=0
 LEFT JOIN `magecatalog_category_entity_int` AS `t2_is_active` ON t1_is_active.entity_id = t2_is_active.entity_id AND t1_is_active.attribute_id = t2_is_active.attribute_id AND t2_is_active.store_id = 1 WHERE (main_table.path LIKE '1/2/%') AND (t1_is_active.attribute_id='42') AND ((IF(t2_is_active.value_id > 0, t2_is_active.value, t1_is_active.value))=1)
Trace: #0 /home/company/live/lib/Varien/Db/Statement/Pdo/Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 /home/company/live/app/code/core/Zend/Db/Statement.php(291): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 /home/company/live/lib/Zend/Db/Adapter/Abstract.php(480): Zend_Db_Statement->execute(Array)
#3 /home/company/live/lib/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT `main_ta...', Array)
#4 /home/company/live/lib/Varien/Db/Adapter/Pdo/Mysql.php(504): Zend_Db_Adapter_Pdo_Abstract->query('SELECT `main_ta...', Array)
#5 /home/company/live/app/code/core/Mage/Sitemap/Model/Resource/Catalog/Abstract.php(164): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select))
#6 /home/company/live/app/code/core/Mage/Sitemap/Model/Resource/Catalog/Category.php(80): Mage_Sitemap_Model_Resource_Catalog_Abstract->_loadEntities()
#7 /home/company/live/app/code/core/Mage/Sitemap/Model/Sitemap.php(154): Mage_Sitemap_Model_Resource_Catalog_Category->getCollection('1')
#8 /home/company/live/app/code/core/Mage/Adminhtml/controllers/SitemapController.php(255): Mage_Sitemap_Model_Sitemap->generateXml()
#9 /home/company/live/app/code/core/Mage/Core/Controller/Varien/Action.php(418): Mage_Adminhtml_SitemapController->generateAction()
#10 /home/company/live/app/code/core/Mage/Core/Controller/Varien/Router/Standard.php(254): Mage_Core_Controller_Varien_Action->dispatch('generate')
#11 /home/company/live/app/code/core/Mage/Core/Controller/Varien/Front.php(172): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
#12 /home/company/live/app/code/core/Mage/Core/Model/App.php(365): Mage_Core_Controller_Varien_Front->dispatch()
#13 /home/company/live/app/Mage.php(684): Mage_Core_Model_App->run(Array)
#14 /home/company/live/index.php(70): Mage::run('', 'store')
#15 {main}

The error only appears in our live shop, in the test shop it works fine, even if I checkout the live shop branch in git and the staging area is clean

The permission of the sitemap.xml is set to -rw-rw-rw in both shops.

1

There was an error in your SQL:

AND url_rewrite.category_id IS NOT NULLurl_rewrite.id_path LIKE 'category/%'

should looks like:

AND url_rewrite.category_id IS NOT NULL AND url_rewrite.id_path LIKE 'category/%'

Possible there was some kind of modifications made by some third-party observer. Take a look at modules installed in your system and check all its observers.

Another modifications could exists inside the app/code/local directory (using replace of the core class). Check it too.

  • If it is really because of code problem, then why does it work on my dev where the same branch is checked out? The staging areas are clean and nothing is there – Black Mar 1 at 20:33
  • 1
    My thougths ... this schould not happen with unmodified code. – sv3n Mar 1 at 23:39
1

I tracked down the exact problem code line.

It is inside the function getCollection() from the class Mage_Sitemap_Model_Resource_Catalog_Category:

public function getCollection($storeId)
{
    /* @var $store Mage_Core_Model_Store */
    $store = Mage::app()->getStore($storeId);
    if (!$store) {
        return false;
    }

    $this->_select = $this->_getWriteAdapter()->select()
        ->from($this->getMainTable())
        ->where($this->getIdFieldName() . '=?', $store->getRootCategoryId());

    $categoryRow = $this->_getWriteAdapter()->fetchRow($this->_select);
    if (!$categoryRow) {
        return false;
    }

    $this->_select = $this->_getWriteAdapter()->select()
        ->from(array('main_table' => $this->getMainTable()), array($this->getIdFieldName()))
        ->where('main_table.path LIKE ?', $categoryRow['path'] . '/%');

    $storeId = (int)$store->getId();

    /** @var $urlRewrite Mage_Catalog_Helper_Category_Url_Rewrite_Interface */
    $urlRewrite = $this->_factory->getCategoryUrlRewriteHelper();
    $urlRewrite->joinTableToSelect($this->_select, $storeId);    // <-- Problem is here

    $this->_addFilter($storeId, 'is_active', 1);

    return $this->_loadEntities();
}

I workaround it by overriding the method in my own extension and changing the function to this:

public function getCollection($storeId)
{
    /* @var $store Mage_Core_Model_Store */
    $store = Mage::app()->getStore($storeId);
    if (!$store) {
        return false;
    }

    $this->_select = $this->_getWriteAdapter()->select()
        ->from($this->getMainTable())
        ->where($this->getIdFieldName() . '=?', $store->getRootCategoryId());

    $categoryRow = $this->_getWriteAdapter()->fetchRow($this->_select);
    if (!$categoryRow) {
        return false;
    }

    $this->_select = $this->_getWriteAdapter()->select()
        ->from(array('main_table' => $this->getMainTable()), array($this->getIdFieldName()))
        ->where('main_table.path LIKE ?', $categoryRow['path'] . '/%');

    $storeId = (int)$store->getId();

    /** @var $urlRewrite Mage_Catalog_Helper_Category_Url_Rewrite_Interface */
    $urlRewrite = $this->_factory->getCategoryUrlRewriteHelper();
    $urlRewrite->joinTableToSelect($this->_select, $storeId);

    // Workaround to fix "Unable to generate sitemap" error only happening on live store
    $this->_select = str_replace("IS NOT NULLurl_rewrite.id_path", "IS NOT NULL AND url_rewrite.id_path", $this->_select);

    $this->_addFilter($storeId, 'is_active', 1);

    return $this->_loadEntities();
}

I am still confused why this only happens in my live store even though the code on my dev store is 1:1 the same.

  • I've checked in magento 1.9.3.2 and 1.9.4 - in the sql query for category URLs generation the condition "category_id IS NOT NULL" does not exist. Check your Mage_Catalog_Helper_Category_Url_Rewrite::joinTableToSelect() method. If this class doesn't used then something replaced this rewrite helper class using config - see Mage_Catalog_Model_Factory::getCategoryUrlRewriteHelper(). – sergei.sss Mar 2 at 8:29
  • The config partially forms using the database - with the identical code we can have different behaviour between the live and dev sites. – sergei.sss Mar 2 at 8:36
  • Case 2: It is possible to change magento config using files from app/etc directory - we can add this code to app/etc/local.xml inside the <config> : <global> <catalog> <category> <url_rewrite> <helper>catalog/category_url_rewrite_my_class</helper> </url_rewrite> </category> </catalog> </global> and rewrite model will be replaced to own class. Very often the developers adds these configuration files to .gitignore and these files drop out of comparison. – sergei.sss Mar 2 at 9:18
  • @sergei.sss, I know, I checked the gitignore but nothing relevant is getting ignored. The app/etc/local.xml does not contain such code – Black Mar 2 at 9:31
0

Try below to see what is actually causing the error before applying the above solution!;

Open the file;

app/code/core/Mage/Adminhtml/controllers/SitemapController.php

Find the line:

$this->_getSession()->addException($e, Mage::helper('sitemap')->__('Unable to generate the sitemap.'));

And replace it with:

$this->_getSession()->addException($e, $e->getMessage());

and try to see if you can run the sitemap again. hope this should be able to help better!

  • lol, how should this solve the bug? You are just changing the way how the error message is outputted. – Black Mar 20 at 10:42

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.