SPEAKER 2 / 25

Daniele Rutigliano CEO of the digital agency Aproweb.

Author of the best seller “E-commerce

vincente” published by Hoepli.

Speaker at Smau, Be-Wizard! 2015,

WebUpdate.

Professor at LUM University.

ISSUE 3 / 25

Wrong URLs (with the URL rewrites)

/catalog/category/view/id/652 (it is not being rewritten!)

/brand-1/old-url-key.html (it is different from the real url key!)

/brand-1/new/product.html (the category “New” no longer exists)

Ect...

ISSUE 4 / 25

Catalog URL Rewrites required/blocked for 3 years

ISSUE 5 / 25

The error

Catalog URL Rewrites index process unknown error:exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '38494500_1423597414-0-1' for key 'UNQ_CORE_URL_REWRITE_ID_PATH_IS_SYSTEM_STORE_ID'' in /var/www/vhosts/domain.com/httpdocs/lib/Zend/Db/Statement/Pdo.php:228

(The motive of this issue was a wrong import from old websites)

FIXED THE ISSUE OF THE URL REWRITES 6 / 25

Select and remove duplicate values

SELECT DISTINCT ('sku') as 'sku',COUNT('sku') as 'skuCount', 'entity_id' FROM 'catalog_product_entity'GROUP BY 'sku' HAVING 'skuCount' > 1;

Source: www.albertomariarossi.it/howto-solve-url-rewrite-indexing-errors-in-magento/

FIXED THE ISSUE OF THE URL REWRITES 7 / 25

Select and remove duplicate values

SELECT url_key,COUNT(url_key) AS NumOccurrencesFROM catalog_product_flat_1GROUP BY url_key HAVING (COUNT(url_key) > 1);

Source: www.albertomariarossi.it/howto-solve-url-rewrite-indexing-errors-in-magento/

FIXED THE ISSUE OF THE URL REWRITES 8 / 25

Select and remove duplicate values

SELECT name,COUNT(name) AS NumOccurrencesFROM catalog_product_flat_1GROUP BY name HAVING ( COUNT(name) > 1 );

Source: www.albertomariarossi.it/howto-solve-url-rewrite-indexing-errors-in-magento/

FIXED THE ISSUE OF THE URL REWRITES 9 / 25

Backup and truncate the table: “core_url_rewrite”

+

FIXED THE ISSUE OF THE URL REWRITES 10 / 25

The result

THE NEW ISSUE 11 / 25

... meantime in Google Search Console

404 pages

THE NEW ISSUE 12 / 25

How to fix 237.627 “404 pages”??

THE WAYS 13 / 25

First way: manually in the Magento backend?

THE WAYS 14 / 25

First way: manually in the Magento backend?

THE WAYS 15 / 25

Second way: in the .htaccess file?

redirect 301 /old-name /new-nameredirect 301 /old-name2 /new-name2redirect 301 /old-name3 /new-name3redirect 301 /old-name4 /new-name4redirect 301 /old-name5 /new-name5 …

X 237.627 times!

THE WAYS 16 / 25

Second way: in the .htaccess file?

redirect 301 /old-name /new-nameredirect 301 /old-name2 /new-name2redirect 301 /old-name3 /new-name3redirect 301 /old-name4 /new-name4redirect 301 /old-name5 /new-name5 …

X 237.627 times!

THE WAYS 17 / 25

Third way: building redirect 301 dynamically!

FROM THE BACKUP TO THE NEW DATABASE

THE WAYS 18 / 25

Create a database with 2 “core_url_rewrite” tables

TEST DATABASE

Old “core_url_rewrite” → Rename to “old”

New “core_url_rewrite” → Rename to “new”

THE WAYS 19 / 25

Found the duplicate path

UPDATE old, new SET old.description=2

WHERE old.request_path = new.request_path

2 is my flag

THE WAYS 20 / 25

Delete the duplicate path

DELETE FROM old WHERE description =2

THE WAYS 21 / 25

Update and join the same products

UPDATE old, new SET old.options='RP', old.target_path=new.request_path, old.category_id=NULL, old.product_id=NULL,old.is_system=0,old.id_path = CONCAT(old.id_path,'-APR'), old.description=1 WHERE old.product_id = new. product_id AND old.store_id=new.store_id

Redirect 301

It is my check

1 is my flag

THE WAYS 22 / 25

A little cleaning

DELETE FROM old WHERE description is NULL;

DELETE FROM old WHERE target_path=request_path;

UPDATE old SET description = NULL;

ALTER TABLE old DROP COLUMN url_rewrite_id;

RENAME TABLE old TO core_url_rewrite;

(before it was 1)

THE WAYS 23 / 25

Import

FROM THE TEST DB TO THE NEW DATABASE

Import the core_url_rewritetable

THE WAYS 24 / 25

The end

THE WAYS 25 / 25

</THANKS>