4

Got the below error while updating the catalog configuration.

Something went wrong while saving this configuration: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'dbname.url_rewrite' doesn't exist, query was: UPDATE url_rewrite SET request_path = ? WHERE (url_rewrite_id = 62)

One thing I noticed is the table shown in the error doesn't have the table prefix. This is a migrated website from Magento 1.9.1.0 and we use table prefix.

Am I doing something wrong? or is this a core issue?

Update: I guess it is a bug. I just removed the prefix for url_rewrite table alone and the save works great. But after save it throws a similar error for SELECT.

Steps I followed

  1. Login to admin back-end
  2. Click on Stores -> Configuration
  3. click on Catalog -> Catalog
  4. Open Search Engine Optimization
  5. Remove the .html suffix for both product and category
  6. hit Save
  7. I see the above error

What I did next

  1. I login to my phpMyAdmin
  2. Renamed the table by removing the mag_ prefix for url_rewrite table
  3. I flushed the cache in another browser and hit save in the current browser
  4. Now the save works but when the page refreshed it shows the table mag_url_rewite doesn't exist.

It looks clearly a bug for me... Sorry if I might be wrong...

  • It looks like the setup didn't go correctly and you are missing some tables. Try removing app/etc/env.php (move it to another location) and install store again with new database. – Zefiryn Jan 14 '16 at 23:02
  • @Zefiryn. I did a fresh migration one more time and the result is same. – Malaiselvan Jan 15 '16 at 0:09
  • @Malaiselvan What tool/script do you use to migrate database? Do you also install Magento 2 with prefix? – Max Pronko Jan 15 '16 at 21:46
  • @MaxPronko I use the Magento core data migration tool as-is. My 1.9.1 store doesnt have any external module apart from the theme... It was very smooth and nice using the core Magento data migration tool. – Malaiselvan Jan 15 '16 at 21:47
  • @Malaiselvan do you see url_rewrite table after fresh Magento 2 installation? – Max Pronko Jan 15 '16 at 21:49
5

Thank you for exception.log data. You are right that url_rewrite table usage is hard-coded in the Magento\Catalog\Model\System\Config\Backend\Catalog\Url\Rewrite\Suffix class. So when Search Engine Optimization configuration it triggers the class updateSuffixForUrlRewrites() method where the constant isn't wrapped with the getTableName() method call:

        $this->connection->update(
            DbStorage::TABLE_NAME,
            $bind,
            $this->connection->quoteIdentifier(UrlRewrite::URL_REWRITE_ID) . ' = ' . $urlRewrite->getUrlRewriteId()
        );

There are 2 options on how to fix this issue:

  1. Plugin for the Magento\Catalog\Model\System\Config\Backend\Catalog\Url\Rewrite\Suffix:: updateSuffixForUrlRewrites() method. For this we have to inject all dependencies which Suffix has in it's contructor.
  2. Update backend_model node for the CatalogUrlRewrite\etc\adminhtml\system.xml fields with id category_url_suffix and product_url_suffix in your system.xml file:

enter image description here

This option still requires you to fix it in your custom class.

  1. Pull request into Magento 2 repository with the fix. You might wait a while until it will be available in stable release.

My recommendation is to go with 1 or 2.

  • Thanks for your effort to find the cause of the issue and possible options. As a temporary solution I plan to remove the table prefix in Magento2.0 while do the migration from 1.9.1. Once Megento team fix the issue, I will find a way to put the table prefix back. – Malaiselvan Jan 15 '16 at 23:46
2

Here a quick / temporary fix in vendor/magento/module-catalog/Model/System/Config/Backend/Catalog/Url/Rewrite/Suffix.php

1 - Add appResource in the constructor

{
    parent::__construct($context, $registry, $config, $cacheTypeList, $resource, $resourceCollection, $data);
    $this->urlRewriteHelper = $urlRewriteHelper;
    $this->connection = $appResource->getConnection();
    $this->appResource = $appResource;  // ###### Here ######
    $this->urlFinder = $urlFinder;
    $this->storeManager = $storeManager;
}

2 - Replace the name of the table in updateSuffixForUrlRewrites()

$this->connection->update(
    $this->appResource->getTableName(DbStorage::TABLE_NAME),    // ###### Here ######
    $bind,
    $this->connection->quoteIdentifier(UrlRewrite::URL_REWRITE_ID) . ' = ' . $urlRewrite->getUrlRewriteId()
);

Full file :

<?php
/**
 * Copyright © 2016 Magento. All rights reserved.
 * See COPYING.txt for license details.
 */

/**
 * Url rewrite suffix backend
 */
namespace Magento\Catalog\Model\System\Config\Backend\Catalog\Url\Rewrite;

use Magento\CatalogUrlRewrite\Model\CategoryUrlPathGenerator;
use Magento\CatalogUrlRewrite\Model\CategoryUrlRewriteGenerator;
use Magento\CatalogUrlRewrite\Model\ProductUrlPathGenerator;
use Magento\CatalogUrlRewrite\Model\ProductUrlRewriteGenerator;
use Magento\Framework\App\ResourceConnection;
use Magento\Store\Model\ScopeInterface;
use Magento\UrlRewrite\Model\Storage\DbStorage;
use Magento\UrlRewrite\Service\V1\Data\UrlRewrite;

/**
 * @SuppressWarnings(PHPMD.CouplingBetweenObjects)
 */
class Suffix extends \Magento\Framework\App\Config\Value
{
    /** @var \Magento\UrlRewrite\Helper\UrlRewrite */
    protected $urlRewriteHelper;

    /** @var \Magento\Store\Model\StoreManagerInterface */
    protected $storeManager;

    /** @var \Magento\UrlRewrite\Model\UrlFinderInterface */
    protected $urlFinder;

    /** @var \Magento\Framework\DB\Adapter\AdapterInterface */
    protected $connection;

    /**
     * @param \Magento\Framework\Model\Context $context
     * @param \Magento\Framework\Registry $registry
     * @param \Magento\Framework\App\Config\ScopeConfigInterface $config
     * @param \Magento\Framework\App\Cache\TypeListInterface $cacheTypeList
     * @param \Magento\UrlRewrite\Helper\UrlRewrite $urlRewriteHelper
     * @param \Magento\Store\Model\StoreManagerInterface $storeManager
     * @param ResourceConnection $appResource
     * @param \Magento\UrlRewrite\Model\UrlFinderInterface $urlFinder
     * @param \Magento\Framework\Model\ResourceModel\AbstractResource|null $resource
     * @param \Magento\Framework\Data\Collection\AbstractDb|null $resourceCollection
     * @param array $data
     * @SuppressWarnings(PHPMD.ExcessiveParameterList)
     */
    public function __construct(
        \Magento\Framework\Model\Context $context,
        \Magento\Framework\Registry $registry,
        \Magento\Framework\App\Config\ScopeConfigInterface $config,
        \Magento\Framework\App\Cache\TypeListInterface $cacheTypeList,
        \Magento\UrlRewrite\Helper\UrlRewrite $urlRewriteHelper,
        \Magento\Store\Model\StoreManagerInterface $storeManager,
        \Magento\Framework\App\ResourceConnection $appResource,
        \Magento\UrlRewrite\Model\UrlFinderInterface $urlFinder,
        \Magento\Framework\Model\ResourceModel\AbstractResource $resource = null,
        \Magento\Framework\Data\Collection\AbstractDb $resourceCollection = null,
        array $data = []
    ) {
        parent::__construct($context, $registry, $config, $cacheTypeList, $resource, $resourceCollection, $data);
        $this->urlRewriteHelper = $urlRewriteHelper;
        $this->connection = $appResource->getConnection();
        $this->appResource = $appResource;                                                          // Updated here #####################################################
        $this->urlFinder = $urlFinder;
        $this->storeManager = $storeManager;
    }

    /**
     * Check url rewrite suffix - whether we can support it
     *
     * @return $this
     */
    public function beforeSave()
    {
        $this->urlRewriteHelper->validateSuffix($this->getValue());
        return $this;
    }

    /**
     * @return $this
     */
    public function afterSave()
    {
        if ($this->isValueChanged()) {
            $this->updateSuffixForUrlRewrites();
        }
        return parent::afterSave();
    }

    /**
     * Update suffix for url rewrites
     *
     * @return $this
     */
    protected function updateSuffixForUrlRewrites()
    {
        $map = [
            ProductUrlPathGenerator::XML_PATH_PRODUCT_URL_SUFFIX => ProductUrlRewriteGenerator::ENTITY_TYPE,
            CategoryUrlPathGenerator::XML_PATH_CATEGORY_URL_SUFFIX => CategoryUrlRewriteGenerator::ENTITY_TYPE,
        ];
        if (!isset($map[$this->getPath()])) {
            return $this;
        }
        $dataFilter = [UrlRewrite::ENTITY_TYPE => $map[$this->getPath()]];
        $storesIds = $this->getStoreIds();
        if ($storesIds) {
            $dataFilter[UrlRewrite::STORE_ID] = $storesIds;
        }
        $entities = $this->urlFinder->findAllByData($dataFilter);
        $oldSuffixPattern = '~' . preg_quote($this->getOldValue()) . '$~';
        $suffix = $this->getValue();
        foreach ($entities as $urlRewrite) {
            $bind = $urlRewrite->getIsAutogenerated()
                ? [UrlRewrite::REQUEST_PATH => preg_replace($oldSuffixPattern, $suffix, $urlRewrite->getRequestPath())]
                : [UrlRewrite::TARGET_PATH => preg_replace($oldSuffixPattern, $suffix, $urlRewrite->getTargetPath())];
            $this->connection->update(
                $this->appResource->getTableName(DbStorage::TABLE_NAME),                            // Updated here #####################################################
                $bind,
                $this->connection->quoteIdentifier(UrlRewrite::URL_REWRITE_ID) . ' = ' . $urlRewrite->getUrlRewriteId()
            );
        }
        return $this;
    }

    /**
     * @return array|null
     */
    protected function getStoreIds()
    {
        if ($this->getScope() == 'stores') {
            $storeIds = [$this->getScopeId()];
        } elseif ($this->getScope() == 'websites') {
            $website = $this->storeManager->getWebsite($this->getScopeId());
            $storeIds = array_keys($website->getStoreIds());
            $storeIds = array_diff($storeIds, $this->getOverrideStoreIds($storeIds));
        } else {
            $storeIds = array_keys($this->storeManager->getStores());
            $storeIds = array_diff($storeIds, $this->getOverrideStoreIds($storeIds));
        }
        return $storeIds;
    }

    /**
     * @param array $storeIds
     * @return array
     */
    protected function getOverrideStoreIds($storeIds)
    {
        $excludeIds = [];
        foreach ($storeIds as $storeId) {
            $suffix = $this->_config->getValue($this->getPath(), ScopeInterface::SCOPE_STORE, $storeId);
            if ($suffix != $this->getOldValue()) {
                $excludeIds[] = $storeId;
            }
        }
        return $excludeIds;
    }
}

Note : I am a total noob in Magento2, this is a quick fix, and obviously not the PROPER WAY to nail it. I posted this in order to help people who will be in my current position..

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.