I want to turn off .html from the category paths. When I toggle the setting off in the admin panel I get a unique constraint error. We have hundreds of categories, pages and products so finding which one or more urls are conflicting is not feasible via the admin panel. As such, I'm looking in the url_rewrite table.
+------------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------+----------------+
| url_rewrite_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| entity_type | varchar(32) | NO | | NULL | |
| entity_id | int(10) unsigned | NO | MUL | NULL | |
| request_path | varchar(255) | YES | MUL | NULL | |
| target_path | varchar(255) | YES | MUL | NULL | |
| redirect_type | smallint(5) unsigned | NO | | 0 | |
| store_id | smallint(5) unsigned | NO | MUL | NULL | |
| description | varchar(255) | YES | | NULL | |
| is_autogenerated | smallint(5) unsigned | NO | | 0 | |
| metadata | varchar(255) | YES | | NULL | |
+------------------+----------------------+------+-----+---------+----------------+
I'm starting with a generic "find all duplicate rows" query and trying to build out to select request_paths if they contain .html or not, however I'm struggling with the query. Here's the generic sql:
select count(request_path), request_path, store_id from url_rewrite group by request_path, store_id having count(request_path) > 1;
I've tried variations of count(replace(request_path, '.html','')) but I'm not getting any results out (e.g. select count(replace(request_path, '.html','')), request_path, store_id from url_rewrite group by request_path, store_id having count(replace(request_path, '.html','')) > 1;).
If I had the urls:
my_slug.html
my_slug
what sql query do I need to select to get my_slug (and hopefully other row data?) from the table?