From 3acb25c50cddb6f2b00bf8d8a48b5d47b1e7b4f7 Mon Sep 17 00:00:00 2001 From: Shauren Date: Thu, 3 Oct 2024 14:41:02 +0200 Subject: Core/Players: Prevent creation of characters with duplicate names Closes #30299 (cherry picked from commit 49ab2bbe3883749594fe92a752e1d618c625d1b6) --- .../characters/3.3.5/2024_10_03_00_characters.sql | 29 ++++++++++++++++++++++ 1 file changed, 29 insertions(+) create mode 100644 sql/updates/characters/3.3.5/2024_10_03_00_characters.sql (limited to 'sql/updates') diff --git a/sql/updates/characters/3.3.5/2024_10_03_00_characters.sql b/sql/updates/characters/3.3.5/2024_10_03_00_characters.sql new file mode 100644 index 00000000000..7b5a6fcf357 --- /dev/null +++ b/sql/updates/characters/3.3.5/2024_10_03_00_characters.sql @@ -0,0 +1,29 @@ +-- append guid as hex to characters who have duplicate names in database except the one with lowest guid (assumed to have been created first) +-- and flag them for rename +CREATE TEMPORARY TABLE `characters_to_rename` SELECT + c2.`guid` + FROM + ( + SELECT + c1.`name` AS `name`, + MIN(c1.`guid`) AS originalGuid + FROM + `characters` c1 + WHERE + LENGTH(c1.`name`) > 0 + GROUP BY + 1 + HAVING + COUNT(*) > 1 + ) c3 + INNER JOIN `characters` c2 ON c3.`name` = c2.`name` + WHERE + c2.guid <> c3.originalGuid; + +UPDATE `characters` SET `name` = CONCAT(SUBSTRING(`name` FROM 1 FOR 12 - LENGTH(CONV(`guid`, 10, 16))), CONV(`guid`, 10, 16)), `at_login` = `at_login` | 1 WHERE `guid` IN (SELECT `guid` FROM `characters_to_rename`); + +-- recreate name index with unique constraint +ALTER TABLE `characters` DROP INDEX `idx_name`; +ALTER TABLE `characters` MODIFY COLUMN `name` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL AFTER `account`; +UPDATE `characters` SET `name` = NULL WHERE `name` = ''; +ALTER TABLE `characters` ADD UNIQUE INDEX `idx_name` (`name` ASC); -- cgit v1.2.3