hello all,
I recently came across a problem with a phplist install I maintain, which meant the “Update your preferences” and “Unsubscribe from our Newsletters” were not working for our users.
Basically I discovered there were many duplicate entries of the same value in the phplist_user_user.uniqid field for a significant number of users in the phplist_user_user table.
As I could find no other reference to these phplist_user_user.uniqid values amnywhere else I decided to write a script to replace all these duplicate values with unique ones.
This script is listed below for anyone’s future use.
<?php
// Database connection (adjust with your credentials)
$pdo = new PDO('mysql:host=localhost;dbname=<<DATABASE_NAME>>;charset=utf8mb4', '<<USERNAME>>', '<<PASSWORD>>');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Starting duplicate uniqid fix...\n";
// Fetch all duplicate uniqid groups, keeping the lowest id untouched
$stmt = $pdo->query("
SELECT
uniqid,
GROUP_CONCAT(id ORDER BY id) AS ids
FROM
phplist_user_user
WHERE
uniqid IS NOT NULL AND uniqid != ''
GROUP BY
uniqid
HAVING
COUNT(*) > 1
");
$updated = 0;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$ids = explode(',', $row['ids']);
$keep_id = array_shift($ids); // Keep the first (lowest) id unchanged
echo "Duplicate uniqid '{$row['uniqid']}' found in ids: " . $row['ids'] . " - Keeping id $keep_id, updating the rest.\n";
foreach ($ids as $id) {
$new_uniqid = bin2hex(random_bytes(16)); // 32-char hex string
// Optional: ensure no collision (extremely unlikely, but safe)
do {
$check = $pdo->prepare("SELECT 1 FROM phplist_user_user WHERE uniqid = ?");
$check->execute([$new_uniqid]);
if ($check->fetch()) {
echo "Rare collision detected for id $id, regenerating...\n";
$new_uniqid = bin2hex(random_bytes(16));
}
} while ($check->fetch()); // repeat only if collision (practically never happens)
$update = $pdo->prepare("UPDATE phplist_user_user SET uniqid = ? WHERE id = ?");
$update->execute([$new_uniqid, $id]);
echo "Updated id $id with new uniqid: $new_uniqid\n";
$updated++;
}
}
echo "Fix complete. Updated $updated rows.\n";
// Optional: verify no duplicates remain
$remaining = $pdo->query("
SELECT COUNT(*) FROM (
SELECT uniqid FROM phplist_user_user
WHERE uniqid IS NOT NULL AND uniqid != ''
GROUP BY uniqid HAVING COUNT(*) > 1
) AS dups
")->fetchColumn();
echo "Remaining duplicates: $remaining\n";