Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

"URL key for specified store already exists." cannot save category #7298

Open
janeblonde opened this Issue Nov 3, 2016 · 56 comments

Comments

@janeblonde
Copy link

janeblonde commented Nov 3, 2016

I've been trying to update/save a specific category but get the message "URL key for specified store already exists." every time.

I don't know if you can replicated it as it's very specific.

Recreate

  1. go to Products > Categories
  2. Select the Category
  3. Click Save

Message: URL key for specified store already exists. comes up and nothing is saved

Preconditions

Magento ver. 2.1.2
PHP 7
MySQL 5.6

Trouble Shooting:**

  1. I've checked the DB table url_rewrite and found no duplicates.
  2. Ran the following queries to clean out possible duplicates (see here):

delete from url_rewrite where entity_type='product' and entity_id NOT IN (Select entity_id from catalog_product_entity);

select count(*) from url_rewrite where entity_type='product' and entity_id NOT IN (Select entity_id from catalog_product_entity);

Each time I've re-ran the indexer via ssh and cleared all caches, nothing has worked.

Any ideas?

@quienti

This comment has been minimized.

Copy link
Contributor

quienti commented Nov 3, 2016

Duplicate #6671

@quynhvv

This comment has been minimized.

Copy link

quynhvv commented Nov 4, 2016

@janeblonde :

That issue because the table catalog_product_entity_varchar has duplicate values of (store_id, value) with attributes: url_key (119) vs url_path (120) See more about these attributes: http://i.prntscr.com/8604a114212f4661a8f52b48576fe6f0.png

Let's run below SQL in your Magento 2 database, you will see the duplicated values:

SELECT * FROM `catalog_product_entity_varchar` WHERE `attribute_id` IN (119, 120) AND `value_id` NOT IN (
    SELECT `value_id` FROM `catalog_product_entity_varchar` WHERE `attribute_id` IN (119, 120) GROUP BY `store_id`, `value`
)

After run that sql, if you see any rows in result, let's find way to delete that records in the table catalog_product_entity_varchar
And then the issue will be solved.

Hope this help.
Regards,
Quynhvv

@frostshoxx

This comment has been minimized.

Copy link

frostshoxx commented Nov 4, 2016

@quynhvv What would be the implication of delete entries from catalog_product_entity_varchar?

@quynhvv

This comment has been minimized.

Copy link

quynhvv commented Nov 4, 2016

@frostshoxx : Let's run below SQL in your database, you will see the records which you have to delete.

SELECT * FROM catalog_product_entity_varchar WHERE attribute_id IN (119, 120) AND value_id NOT IN (
SELECT value_id FROM catalog_product_entity_varchar WHERE attribute_id IN (119, 120) GROUP BY store_id, value
)

@frostshoxx

This comment has been minimized.

Copy link

frostshoxx commented Nov 4, 2016

There's actually nothing return as a result.

@quynhvv

This comment has been minimized.

Copy link

quynhvv commented Nov 4, 2016

@frostshoxx : So, what happened with below SQLs:

SELECT count(value_id) FROM catalog_product_entity_varchar WHERE attribute_id IN (119, 120) GROUP BY store_id, value

vs

SELECT count(value_id) FROM catalog_product_entity_varchar WHERE attribute_id IN (119, 120)

@frostshoxx

This comment has been minimized.

Copy link

frostshoxx commented Nov 4, 2016

@quynhvv the first entry return empty, the 2nd one return 0. Perhaps my store attribute_id for url_key and url_path are not 119, 120?

@veloraven

This comment has been minimized.

Copy link
Contributor

veloraven commented Nov 10, 2016

I'm closing this issue as a duplicate of #6671 .

@veloraven veloraven closed this Nov 10, 2016

@maderlock

This comment has been minimized.

Copy link

maderlock commented Nov 11, 2016

Actually, I think that there are multiple issues here. #6671 has narrowed down to an anchor URL rewrite generator. I suspect that this issue is related but seperate.

@dharake

This comment has been minimized.

Copy link

dharake commented Feb 23, 2017

Hi guys, in my case I received a bucket load of this issue.

Steps to reproduce my findings:

  • I imported products from a Magento 1.9 store to 2.1.4
  • I exported the CSV, cleaned up some data and tried to import it back.
  • I get around 50-100 of these url_rewrite errors (Specified URL key already exists)
  • I go and change every one of the urls in the CSV manually, upload and its imported fine with all my changes (except attribute_set_code, working solution for that issue is available on request).
  • Now, to confirm, if I look at the product url_key in the backend on a product whose url_key I rewrote, "item-a" -> "item-a-1" for example.
  • Export the csv. Reupload the csv with no changes

Result:
Don't you know it, the new url_key "item-a-1" gets the (Specified URL key already exists) error again.

@quynhvv Thanks for the proposed solution. However, I tried your query, returns 0 rows.

@pixiemediaweb

This comment has been minimized.

Copy link

pixiemediaweb commented Feb 26, 2017

Same issue here - using the data migration tool from an M1 CE store, cannot save any categories.

@lkc299

This comment has been minimized.

Copy link

lkc299 commented Feb 28, 2017

I also encountered this issue after I cleaned the magento database.
At last I found the sql script I used was incomplete and some data still remained in database.

https://blog.mdnsolutions.com/magento-2-delete-all-categories/

I used the script suggested in this blog and the problem was solved.

@PauGNU

This comment has been minimized.

Copy link

PauGNU commented Apr 25, 2017

Hi,

In my case, the problem was caused by the interruption of a product import. I just needed to remove all the products that had been imported and after that I was able to save the category. The other solutions proposed in this and other tickets didn't work.

@zaknixon

This comment has been minimized.

Copy link

zaknixon commented Apr 25, 2017

@PauGNU Just FYI for others, this did not work in my case.

@sdasun

This comment has been minimized.

Copy link

sdasun commented Apr 26, 2017

it seems something to do with Unicode characters, @zaknixon do you have Unicode characters in your category names? I'm facing the same issue. I suspect my Unicode text on category name ruined auto generated url key and set it null or Unicode characters instead.

@ezprod

This comment has been minimized.

Copy link

ezprod commented May 10, 2017

Just an FYI I have had this error come up repeatedly as I have uploaded products. Believe it or not the problem is always the same thing. The category cell length can't exceed 250 characters. Any longer and the error appears. I simply duplicate my row and cut the category in half. First half of categories in the first row and the second half in the next one. Then everything uploads fine.

@Tjitse-E

This comment has been minimized.

Copy link

Tjitse-E commented May 16, 2017

@ezprod would you mind sharing the SQL queries that narrowed down the affected products?

@eharvest

This comment has been minimized.

Copy link

eharvest commented May 16, 2017

Magento CE 2.1.0, unable to edit/save some existing product categories!

SELECT * FROM catalog_product_entity_varcharWHEREattribute_idIN (119, 120) ANDvalue_idNOT IN ( SELECTvalue_idFROMcatalog_product_entity_varcharWHEREattribute_idIN (119, 120) GROUP BYstore_id, value ) returns nothing

SELECT count(value_id) FROM catalog_product_entity_varchar WHERE attribute_id IN (119, 120) GROUP BY store_id, value gives me 568 total
SELECT count(value_id) FROM catalog_product_entity_varchar WHERE attribute_id IN (119, 120) gives me 573

What should I go from here?

@ezprod

This comment has been minimized.

Copy link

ezprod commented May 16, 2017

I believe in
catalog_category_entity_varchar

Varchar is limited to 255. It still utilizes this format for categories even though it breaks it into multiple cells as it reads the poles or commas in the import.

@sadeeshmca

This comment has been minimized.

Copy link
Collaborator

sadeeshmca commented May 17, 2017

This Magento 2 bug you can directy fix is changing file

File Path :vendor/magento/module-url-rewrite/Model/Storage/DbStorage.php

Orginal Code:

from

protected function doReplace($urls)
{
    foreach ($this->createFilterDataBasedOnUrls($urls) as $type => $urlData) {
        $urlData[UrlRewrite::ENTITY_TYPE] = $type;
        $this->deleteByData($urlData);
    }
    $data = [];
    foreach ($urls as $url) {
        $data[] = $url->toArray();
    }
    $this->insertMultiple($data);
}

into

protected function doReplace($urls)
{
    foreach ($this->createFilterDataBasedOnUrls($urls) as $type => $urlData) {
        $urlData[UrlRewrite::ENTITY_TYPE] = $type;
        $this->deleteByData($urlData);
    }
    $data = [];
    foreach ($urls as $url) {
        $data[] = $url->toArray();
    }

     /* Add this line : Get rid of rewrite for root Magento category to unduplicate things
    foreach($data as $key =>$info){
            if(isset($info['target_path']) && stristr($info['target_path'],'/category/1') && $info['entity_type']=='product'){
                    unset($data[$key]);
            }
    }

    $this->insertMultiple($data);
}

After insert this line clear cache.

@eharvest

This comment has been minimized.

Copy link

eharvest commented Jun 13, 2017

@sadeeshmca I tried your fix but it didn't work for me as the error persists as I tried to save my category. I couldn't find your suggested file, in my case I edited:
app/code/Magento/UrlRewrite/Model/Storage/DbStorage.php
I have fixed some of these problematic categories by exclude certain products, it looks like those products were created wrong, maybe they been duplicated without changing the url key? I never had such issue until I handed over to the client when they started messing around with duplicate products.

@vikasneoventis

This comment has been minimized.

Copy link

vikasneoventis commented Jun 29, 2017

0
down vote

I was also facing same issue while adding/editing categories.

Firstly I found there was wrong attribute id in "catalog_category_entity_varchar" table for url_key and url_path. Then I checked the exect attribute_id of both attributes ('url_key' and 'url_path').

e.g in "catalog_category_entity_varchar" table it was showing '117' and '118' but exact id was '119' and '120'. Then I found there was some rows in "catalog_category_entity_varchar" table with these (correct id '119','120').

I followed below steps to resolve issue.

firstly I searched rows in "catalog_category_entity_varchar" where attribute id is in (117,118) and exported the data from table.
then I searched rows in "catalog_category_entity_varchar" where attribute id is in (117,118,119,120) and deleted these data (rows) from table.
then I just opened exported file in notpadd++ then I changed attribute ids 117 with 119 and 119 with 120 and import the data again.

Make sure take the backup of the database before doing above steps.

@ifuents

This comment has been minimized.

Copy link

ifuents commented Jul 21, 2017

In my case it was enough to set is_anchor=0 for root magento category (entity_id=1)

@toanthree

This comment has been minimized.

Copy link

toanthree commented Jul 22, 2017

I have struggled with this problem for almost a day.
And finnally i found out about this.

It is because of my data migrated from magento V1 to magento V2.
Some categories have the same url as products. And when we save categories. It throws mysql exception named URL_REWRITE_REQUEST_PATH_STORE_ID (request_path, store_id is unique)

There are my solutions:
The firt one : Clean up your database in table url_rewrite (Change the url_key of all category). You can write UpgradeData script for this solution.

The second one : Remove the duplication data when saving category.
This data is throw in method doReplace($urls) in \vendor\magento\module-url-rewrite\Model\Storage\DbStorage.php file.

protected function doReplace($urls)
    {
        foreach ($this->createFilterDataBasedOnUrls($urls) as $type => $urlData) {
            $urlData[UrlRewrite::ENTITY_TYPE] = $type;
            $this->deleteByData($urlData);
        }
        $data = [];
        foreach ($urls as $url) {
            $data[] = $url->toArray();
        }
        $this->insertMultiple($data);
    } 

After debugging, i found out $data variable have duplicate record. If you want this method to work without any errors. Rewrite this method above to:

protected function doReplace($urls)
    {
        foreach ($this->createFilterDataBasedOnUrls($urls) as $type => $urlData) {
            $urlData[UrlRewrite::ENTITY_TYPE] = $type;
            $this->deleteByData($urlData);
        }
        $data = [];
        $storeId_requestPaths = [];
        foreach ($urls as $url) {
            $storeId = $url->getStoreId();
            $requestPath = $url->getRequestPath();
            // Skip if is exist in the database
            $sql = "SELECT * FROM url_rewrite where store_id = $storeId and request_path = '$requestPath'";
            $exists = $this->connection->fetchOne($sql);

            if ($exists) continue;

            $storeId_requestPaths[] = $storeId . '-' . $requestPath;
            $data[] = $url->toArray();
        }

        // Remove duplication data;
        $n = count($storeId_requestPaths);
        for ($i = 0; $i < $n - 1; $i++) {
            for ($j = $i + 1; $j < $n; $j++) {
                if ($storeId_requestPaths[$i] == $storeId_requestPaths[$j]) {
                    unset($data[$j]);
                }
            }
        }
        $this->insertMultiple($data);
    }

These solutions above both work for me. But the second one is not an ideal solution.
Because, there are wrong url_keys in your database.
And sometime, it will not work as you expected in the front end.

I hope it will help.
Thanks.
Toan Tam

@scher200

This comment has been minimized.

Copy link

scher200 commented Jul 26, 2017

@toanthree I like your second option, works great for me, I only added the last line of the old function, that makes it work for real:

    protected function doReplace($urls)
    {
        foreach ($this->createFilterDataBasedOnUrls($urls) as $type => $urlData) {
            $urlData[UrlRewrite::ENTITY_TYPE] = $type;
            $this->deleteByData($urlData);
        }
        $data = [];
        $storeId_requestPaths = [];
        foreach ($urls as $url) {
            $storeId = $url->getStoreId();
            $requestPath = $url->getRerquestPath();
            $url->setRequestPath($requestPath);

            // Skip if is exist in the database
            $sql = "SELECT * FROM url_rewrite where store_id = $storeId and request_path = '$requestPath'";
            $exists = $this->connection->fetchOne($sql);

            if ($exists) continue;

            $storeId_requestPaths[] = $storeId . '-' . $requestPath;
            $data[] = $url->toArray();
        }

        // Remove duplication data;
        $n = count($storeId_requestPaths);
        for ($i = 0; $i < $n - 1; $i++) {
            for ($j = $i + 1; $j < $n; $j++) {
                if ($storeId_requestPaths[$i] == $storeId_requestPaths[$j]) {
                    unset($data[$j]);
                }
            }
        }

        // remove root links
        foreach( $data as $key => $info ){
            if(  isset($info['target_path']) && ( stristr($info['target_path'],'/category/1') || stristr($info['target_path'],'/category/2') ) && $info['entity_type']=='product' || $info['request_path'] == "" ){
                    unset($data[$key]);
            }
        }

        // create links
        if( count($data) > 0 ){
            // file_put_contents('var/log/url_rewite.log', print_r($data, TRUE), FILE_APPEND); // enable to log
            $this->insertMultiple($data);
        }

    }
@orlangur

This comment has been minimized.

Copy link
Contributor

orlangur commented Aug 2, 2017

@toanthree @scher200 guys, as far as I understood an implementation it is not a solution at all.

By skipping some request paths it simply makes products inaccessible in some stores using their url_key (so, URL Rewrite for such products will not be created and its URL in listing will be something like /catalog/product/view/id/123 instead of proper one).

Please tell me if my observation is wrong as I didn't try this code in action.

@kytan91

This comment has been minimized.

Copy link

kytan91 commented Oct 9, 2017

any solution?

@umarch06

This comment has been minimized.

Copy link

umarch06 commented Oct 20, 2017

@scher200 you will save my life if you explain the loop and tell me purpose of the if statement:

// remove root links
        foreach( $data as $key => $info ){
            if(  isset($info['target_path']) && ( stristr($info['target_path'],'/category/1') || stristr($info['target_path'],'/category/2') ) && $info['entity_type']=='product' || $info['request_path'] == "" ){
                    unset($data[$key]);
            }
        }

        // create links
        if( count($data) > 0 ){
            // file_put_contents('var/log/url_rewite.log', print_r($data, TRUE), FILE_APPEND); // enable to log
            $this->insertMultiple($data);
        }
@sahava314

This comment has been minimized.

Copy link

sahava314 commented Nov 4, 2017

I just want to add one more reason why I received the url key error. In addition to fixing the url key product attributes after a data migration I ALSO had to delete any orphan records in url_rewrite :

delete from url_rewrite where entity_type = 'product' and entity_id not in (select entity_id from catalog_product_entity);

Hope this helps someone. Thank you all for sharing your solutions!

@ludwig-gramberg

This comment has been minimized.

Copy link

ludwig-gramberg commented Nov 8, 2017

what we just discovered is this:

we had custom-redirects for category-urls as a means of redirecting traffic for disabled categories.
when we reenabled the categories the custom entries remained and magento is "blind" and doesnt recognize them and promptly walks into the dreaded duplicate key error.

@harshvardhanmalpani

This comment has been minimized.

Copy link

harshvardhanmalpani commented Nov 16, 2017

@scher200 it is minor typo at getRerquestPath

@trzykas

This comment has been minimized.

Copy link

trzykas commented Dec 18, 2017

I was looking for solution for this problem and i allready found.
I created short movie with doc to fix this problem.
https://www.youtube.com/watch?v=xerkbeiH2jU

Step by step instruction

@NiteshKuyate

This comment has been minimized.

Copy link

NiteshKuyate commented Jul 13, 2018

@sadeeshmca @scher200 @umarch06 Can you please tell me, What is '1' in below which is used in our custom function

'/category/1'

@harshvardhanmalpani

This comment has been minimized.

Copy link

harshvardhanmalpani commented Jul 13, 2018

root Magento category
@NiteshKuyate

@amenk

This comment has been minimized.

Copy link
Contributor

amenk commented Aug 31, 2018

Are those workarounds still need in 2.2.x ?

@amenk

This comment has been minimized.

Copy link
Contributor

amenk commented Aug 31, 2018

Looks like it should be all fixed: #6671 (comment)

@BB-000

This comment has been minimized.

Copy link

BB-000 commented Oct 15, 2018

Still an issue for me, version 2.2.5
( I get the message when trying to edit 'content manager > content lists' )

Edit: fixed by deleting the offending 'contenttype_contentlist' from the 'url_rewrite' table, then clicking 'save' again in admin, which worked, and created new entry in 'url_rewrite' table.

@josefbehr

This comment has been minimized.

Copy link
Contributor

josefbehr commented Nov 8, 2018

Can still reproduce this in 2.2.6 and 2.3. The solution by @toanthree is working for me. We will create a PR, if @toanthree does not want to do it (?).

@josefbehr josefbehr reopened this Nov 8, 2018

@josefbehr josefbehr self-assigned this Nov 8, 2018

@magento-engcom-team

This comment has been minimized.

Copy link
Contributor

magento-engcom-team commented Nov 8, 2018

Hi @josefbehr. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: 👇

  • 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).

    DetailsIf the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

  • 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

  • 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

  • 4. Verify that the issue is reproducible on 2.3-develop branch

    Details- Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
    - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
    - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and stop verification process here!

  • 5. Verify that the issue is reproducible on 2.2-develop branch.

    Details- Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
    - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

  • 6. Add label Issue: Confirmed once verification is complete.

  • 7. Make sure that automatic system confirms that report has been added to the backlog.

@toanthree

This comment has been minimized.

Copy link

toanthree commented Nov 10, 2018

@josefbehr I'll make a PR and add you as reviewer.

@twistedatrocity

This comment has been minimized.

Copy link

twistedatrocity commented Dec 5, 2018

This is still a nightmare issue in 2.1.16

It seems several things can cause this duplicate condition.... importing of data of course as mentioned already and also using the "Save & Duplicate" feature on a product. Magento allows url key to be duplicated at times and if this happens only once on a single product.... then every product added after that will not get a proper url key rewrite... it silently fails at that point when creating new products and destroys SEO as all those newly added products do not have the friendly url.

Only after manually finding the duplicates in the product table and correcting them will it start working again for many products. However it will then also fall back to another instance of the category rewrite bug and will stick on a certain product requiring furthers manual digging in the database and hacks.

To then go fix the remaining bugged products I have to set single store mode and then they will save. Fortunately my client only has one store at the moment, however future plans are for multi store mode.

@PiroozMB

This comment has been minimized.

Copy link

PiroozMB commented Dec 6, 2018

any update on this? this is a crazy issue with no definite solution in magento. why does magento have so many bugs still not fixed correctly. pfff

@PiroozMB

This comment has been minimized.

Copy link

PiroozMB commented Dec 6, 2018

@veloraven I see you everywhere just closing the threads and doing nothing else. could you not do that please until it is definitely fixed.

@kevinvarley

This comment has been minimized.

Copy link

kevinvarley commented Jan 15, 2019

I have seen this behaviour on a 2.2.6 install too.

@toanthree Your solution appears to resolve the issue locally. However, the solution makes use of the method "createFilterDataBasedOnUrls" which is marked as deprecated. Is there any workaround for this? Do we know when the method will be removed?

@wcladesh

This comment has been minimized.

Copy link

wcladesh commented Feb 14, 2019

Hello,
I got also the same issue in Magento 2.2.4 after data migration from Magento1.9.1.0.
I have checked file

vendor/magento/module-url-rewrite/Model/Storage/DbStorage.php

and found that in function doReplace(array $urls) ,$this->deleteOldUrls($urls); is not working properly.
So I have resolve this issue with help of forums

 //$this->deleteOldUrls($urls);

        //~ $data = [];
        //~ foreach ($urls as $url) {
            //~ $data[] = $url->toArray();
        //~ }
        
        
       /*Custom Functinality Used for delete old Url ,we need to override it*/  
			$data = [];
			$storeId_requestPaths = [];
			foreach ($urls as $url) {
				$storeId = $url->getStoreId();
				$requestPath = $url->getRequestPath();
				// Skip if is exist in the database
				$sql = "SELECT * FROM url_rewrite where store_id = $storeId and request_path = '$requestPath'";
				$exists = $this->connection->fetchOne($sql);

				if ($exists) continue;

				$storeId_requestPaths[] = $storeId . '-' . $requestPath;
				$data[] = $url->toArray();
			}

			// Remove duplication data;
			$n = count($storeId_requestPaths);
			for ($i = 0; $i < $n - 1; $i++) {
				for ($j = $i + 1; $j < $n; $j++) {
					if ($storeId_requestPaths[$i] == $storeId_requestPaths[$j]) {
						unset($data[$j]);
					}
				}
			}

Now issue is resolved and working fine.

@themoonwatcher

This comment has been minimized.

Copy link

themoonwatcher commented Feb 16, 2019

After a migration and a week digging into the problem the only thing that worked for me was https://www.safemage.com/url-optimization-after-migration-magento-2.html

I had to downgrade to 2.2.7 to use it. It says it works on 2.3 but it does not.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.