Problem/Motivation

The website I manage has copious alias duplicates. It would be very nice to have a safe way of getting rid of duplicates. I don't know how or when they get produced. It may be a fairly common occurrence.

Steps to reproduce

N/A

Proposed resolution

Under "Delete Aliases" in the Admin UI configuration page (admin/config/search/path/delete_bulk), add an option to get rid of only duplicates, leaving one remaining for each instance with duplicates.

Remaining tasks

User interface changes

Under Delete options, add a checkbox saying "Only duplicate aliases, leaving only one alias record for every instance of duplicate records", or something like this.

API changes

Data model changes

In SQL, a self-join might be used.

DELETE t1 FROM alias t1
INNER JOIN your_table t2 
WHERE t1.email = t2.email AND t1.id > t2.id;

Or a temporary table could be created

CREATE TABLE alias_duplicates LIKE alias;
INSERT INTO alias_duplicates (col1, col2, col3)
SELECT DISTINCT col1, col2, col3
FROM alias;
DROP TABLE alias;
RENAME TABLE alias_duplicates TO alias;
CommentFileSizeAuthor
#3 image (1).png48.75 KBmatthieu_collet

Comments

ronraney created an issue. See original summary.

mably’s picture

mably commented
Status: Active » Postponed (maintainer needs more info)

What is your definition of "duplicates" exactly?

And what should be the rules to remove them?

matthieu_collet’s picture

matthieu_collet commented
StatusFileSize
new image (1).png48.75 KB

Hello

here's a concrete example from a live site (screenshot attached) that illustrates the problem clearly.

For source /user/512, the path_alias table contains three separate records for the exact same alias /architects//485 in French (pids 2347, 1375, 3317) and three more for the same alias in English (pids 2348, 1376, 3318). Each has a distinct UUID, so they were created as genuinely separate entities, not just displayed redundantly, this isn't a rendering artifact.

Based on that, I'd propose defining a "duplicate" as:
Two or more path_alias records sharing the same path (source), the same alias, and the same langcode.

This is narrower than matching on alias alone, since the same alias legitimately exists once per language (as shown here: one for fr, one for en). It's also narrower than matching on alias+source without langcode, for the same reason.
As for the rule to decide which record survives, a reasonable default would be:

  • Keep the lowest pid (i.e., the oldest/first-created record), since it's the one most likely to be referenced elsewhere (caches, external links, etc.), and
  • Delete the others sharing the same (path, alias, langcode) tuple.

Optionally, this could be configurable (keep oldest vs. keep newest), but "keep oldest" seems like the safest default.

Happy to help test a patch against this dataset if that's useful, we have several hundred of these duplicate triplets on one site alone, so it's not an edge case.