Is there a utility to re-guid multiple tables (not creature spawns)?

[MrSmite]

Because of this issue, my item_instance table is currently at a GUID of 37410154. I know that isn’t a large number at the moment because an INT(10) can hold a value of 4294967295 but I wanted to reset the GUID to avoid problems in the future.

I’ve been trying to write SQL to do it but I keep getting “duplicate key” errors half way through (usually in character_inventory) even though it isn’t actually a duplicate key.

What I first do is the following:

[ul][li]Create a table with an auto-increment field new_guid where I copy the guid, itemEntry and owner_guid from item_instance.[/li][li]Run several SQL statements against all tables that refer to item_instance.guidto change their related fields to the value innew_guid[/li][/ul] At some point during the process, character_inventorywill get upset because of the indexguidon columnsguid,bag,slot`. The potential of collision exists because when I update an early row, it may duplicate a later row that hasn’t been updated yet.

I tried to copy character_inventory into a table with no constraints and the update works properly but when I attempt to add the constraints it fails saying it might cause duplicate entries.

[Nay]

http://www.trinitycore.org/f/topic/104-sql-authcharacters-database-cleanup/

http://www.trinitycore.org/f/topic/8867-guid-reorder

[Aokromes]

Wish was working on one more advanced version allowing to reguid also item_instance, idk the status of it.

[MrSmite]

@Nay

http://www.trinitycore.org/f/topic/104-sql-authcharacters-database-cleanup/

http://www.trinitycore.org/f/topic/8867-guid-reorder

Thanks but I saw those, they don’t do what I need. I’m not trying to delete old characters, I’m trying to reset GUIDs in item_instance to start at 1. That means also modifying every table that refers to item_instance.

@Aokromes

Wish was working on one more advanced version allowing to reguid also item_instance, idk the status of it.

I’m pretty sure the only table giving me trouble is character_inventory, I actually haven’t thought of this issue until recently. I noticed it took Visual Studio a long, long time to start the core in the debugger and I realized it was loading several million items for no reason.

[Exodius]

Maybe you can re-purpose this?

https://github.com/TrinityCore/TrinityCore_4.3.4_DB_Alpha/commit/55d0210d0291b0f8b40ddef5b59ee7798b5cef82

[MrSmite]

@Exodius

Maybe you can re-purpose this?

https://github.com/TrinityCore/TrinityCore_4.3.4_DB_Alpha/commit/55d0210d0291b0f8b40ddef5b59ee7798b5cef82

Thanks but that isn’t granular enough. The SQL I’m working on does the job for the most part, it’s just getting around these random duplicate key errors that is frustrating.

[Warpten]

disable the key; enter your stuff, make sure you dont mass overwrite; redo the key and set the increment.

[MrSmite]

@Warpten

disable the key; enter your stuff, make sure you dont mass overwrite; redo the key and set the increment.

Thanks, I was thinking of trying that next. I thought I could just delete all the rows and do an insert but it didn’t like that method.

[WishToDie]

Find below an almost full reguid script…only thing that it doesn’t reguid is “item sets” from chars…the script is tested on a 10GB database.So yeah…be a nice guy and post back the additional sql for item sets if you’re gonna make it…

Note:this script was used about 2-3 months ago, but i don’t think the structure has changed.

[SPOILER]-- item_instance and references reguid script

show warnings;

/* create a new table with autoincrement new guid */

DROP TABLE IF EXISTS item_instance_temp;

CREATE TABLE IF NOT EXISTS item_instance_temp (

guid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

itemEntry MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT ‘0’,

owner_guid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’,

creatorGuid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’,

giftCreatorGuid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’,

count INT(10) UNSIGNED NOT NULL DEFAULT ‘1’,

duration INT(10) NOT NULL DEFAULT ‘0’,

charges TINYTEXT NULL,

flags MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT ‘0’,

enchantments TEXT NOT NULL,

randomPropertyId SMALLINT(5) NOT NULL DEFAULT ‘0’,

durability SMALLINT(5) UNSIGNED NOT NULL DEFAULT ‘0’,

playedTime INT(10) UNSIGNED NOT NULL DEFAULT ‘0’,

text TEXT NULL,

old_guid int(10) unsigned NOT NULL DEFAULT ‘0’,

PRIMARY KEY (guid),

INDEX idx_owner_guid (owner_guid),

KEY idx_oldguid_tmp (old_guid)

) COMMENT=‘Item System’ COLLATE=‘utf8_general_ci’ ENGINE=InnoDB AUTO_INCREMENT=100;

/* add new guid fields to tables */

ALTER TABLE auctionhouse ADD COLUMN new_itemguid INT(10) UNSIGNED NOT NULL;

ALTER TABLE character_feed_log ADD COLUMN new_item_guid INT(10) UNSIGNED NOT NULL;

ALTER TABLE character_gifts ADD COLUMN new_item_guid INT(10) UNSIGNED NOT NULL;

ALTER TABLE character_inventory ADD COLUMN new_item_guid INT(10) NOT NULL DEFAULT ‘0’ AFTER item,

ADD COLUMN new_bag INT(10) NOT NULL DEFAULT ‘0’ AFTER new_item_guid;

ALTER TABLE guild_bank_item ADD COLUMN new_item_guid INT(10) UNSIGNED NOT NULL;

ALTER TABLE item_loot_items ADD COLUMN new_container_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE item_loot_money ADD COLUMN new_container_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE item_refund_instance ADD COLUMN new_item_guid INT(10) NOT NULL;

ALTER TABLE item_soulbound_trade_data ADD COLUMN new_itemGuid INT(10) NOT NULL;

ALTER TABLE mail_items ADD COLUMN new_item_guid INT(10) NOT NULL;

/* insert data into the new table */

INSERT INTO item_instance_temp (

itemEntry, owner_guid, creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, durability, playedTime, text, old_guid

) SELECT itemEntry, owner_guid, creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, durability, playedTime, text, guid

FROM item_instance ORDER BY itemEntry ASC;

/* delete entries that don’t have a reference in item_instance */

DELETE FROM auctionhouse WHERE itemguid NOT IN (SELECT guid FROM item_instance);

DELETE FROM character_gifts WHERE item_guid NOT IN (SELECT guid FROM item_instance);

DELETE FROM character_inventory WHERE item NOT IN (SELECT guid FROM item_instance);

DELETE FROM guild_bank_item WHERE item_guid NOT IN (SELECT guid FROM item_instance);

DELETE FROM item_loot_items WHERE container_id NOT IN (SELECT guid FROM item_instance);

DELETE FROM item_loot_money WHERE container_id NOT IN (SELECT guid FROM item_instance);

DELETE FROM item_refund_instance WHERE item_guid NOT IN (SELECT guid FROM item_instance);

DELETE FROM item_soulbound_trade_data WHERE itemGuid NOT IN (SELECT guid FROM item_instance);

DELETE FROM mail_items WHERE item_guid NOT IN (SELECT guid FROM item_instance);

/* delete entries from characher_inventory for players that don’t exist anymore*/

delete from character_inventory where guid not in (select guid from characters);

/* delete entries from characther_inventory with bags that don’t exist in item_instance */

delete from character_inventory where bag not in (select guid from item_instance) and bag>0;

/* delete entries from item_instance where itemEntry is not found in world.item_template */

delete from item_instance where itemEntry not in (select entry from world.item_template);

/* add new guid to tables */

UPDATE auctionhouse p

INNER JOIN item_instance_temp pp

ON p.itemguid = pp.old_guid

SET p.new_itemguid = pp.guid;

UPDATE character_feed_log p

INNER JOIN item_instance_temp pp

ON p.item_guid = pp.old_guid

SET p.new_item_guid = pp.guid;

UPDATE character_gifts p

INNER JOIN item_instance_temp pp

ON p.item_guid = pp.old_guid

SET p.new_item_guid = pp.guid;

UPDATE character_inventory p

INNER JOIN item_instance_temp pp

ON p.item = pp.old_guid

SET p.new_item_guid = pp.guid;

UPDATE character_inventory p

INNER JOIN item_instance_temp pp

ON p.bag = pp.old_guid

SET p.new_bag = pp.guid;

UPDATE guild_bank_item p

INNER JOIN item_instance_temp pp

ON p.item_guid = pp.old_guid

SET p.new_item_guid = pp.guid;

UPDATE item_loot_items p

INNER JOIN item_instance_temp pp

ON p.container_id = pp.old_guid

SET p.new_container_id = pp.guid;

UPDATE item_loot_money p

INNER JOIN item_instance_temp pp

ON p.container_id = pp.old_guid

SET p.new_container_id = pp.guid;

UPDATE item_refund_instance p

INNER JOIN item_instance_temp pp

ON p.item_guid = pp.old_guid

SET p.new_item_guid = pp.guid;

UPDATE item_soulbound_trade_data p

INNER JOIN item_instance_temp pp

ON p.itemGuid = pp.old_guid

SET p.new_itemGuid = pp.guid;

UPDATE mail_items p

INNER JOIN item_instance_temp pp

ON p.item_guid = pp.old_guid

SET p.new_item_guid = pp.guid;

/* drop old item_instance and rename item_instance_temp to item_instance */

DROP TABLE item_instance;

ALTER TABLE item_instance_temp DROP COLUMN old_guid;

RENAME TABLE item_instance_temp TO item_instance;

ALTER TABLE item_instance CHANGE COLUMN guid guid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ FIRST;

/* drop old guid field and rename new one to same name as the one dropped */

ALTER TABLE auctionhouse DROP COLUMN itemguid,

CHANGE COLUMN new_itemguid itemguid INT(10) UNSIGNED NOT NULL AFTER auctioneerguid,

ADD UNIQUE INDEX itemguid (itemguid);

ALTER TABLE character_feed_log DROP COLUMN item_guid,

CHANGE COLUMN new_item_guid item_guid INT(11) NOT NULL AFTER difficulty;

ALTER TABLE character_gifts DROP COLUMN item_guid,

CHANGE COLUMN new_item_guid item_guid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER guid,

ADD PRIMARY KEY (item_guid);

ALTER TABLE guild_bank_item DROP COLUMN item_guid,

CHANGE COLUMN new_item_guid item_guid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER SlotId,

ADD INDEX Idx_item_guid (item_guid);

ALTER TABLE item_loot_items DROP COLUMN container_id,

CHANGE COLUMN new_container_id container_id INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘guid of container (item_instance.guid)’ FIRST;

ALTER TABLE item_loot_money DROP COLUMN container_id,

CHANGE COLUMN new_container_id container_id INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘guid of container (item_instance.guid)’ FIRST;

ALTER TABLE item_refund_instance DROP COLUMN item_guid,

CHANGE COLUMN new_item_guid item_guid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘Item GUID’ FIRST,

DROP PRIMARY KEY,

ADD PRIMARY KEY (player_guid, item_guid);

ALTER TABLE item_soulbound_trade_data DROP COLUMN itemGuid,

CHANGE COLUMN new_itemGuid itemGuid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘Item GUID’ FIRST,

ADD PRIMARY KEY (itemGuid);

ALTER TABLE mail_items DROP COLUMN item_guid,

CHANGE COLUMN new_item_guid item_guid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER mail_id,

ADD PRIMARY KEY (item_guid);

ALTER TABLE character_inventory DROP INDEX guid,

DROP COLUMN item,

CHANGE COLUMN new_item_guid item INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘Item Global Unique Identifier’ AFTER slot,

ADD PRIMARY KEY (item),

DROP COLUMN bag,

CHANGE COLUMN new_bag bag INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER guid,

ADD UNIQUE INDEX guid (guid, bag, slot);

– item_instance and references reguid script

show warnings;

/* create a new table with autoincrement new guid */

DROP TABLE IF EXISTS item_instance_temp;

CREATE TABLE IF NOT EXISTS item_instance_temp (

guid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

itemEntry MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT ‘0’,

owner_guid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’,

creatorGuid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’,

giftCreatorGuid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’,

count INT(10) UNSIGNED NOT NULL DEFAULT ‘1’,

duration INT(10) NOT NULL DEFAULT ‘0’,

charges TINYTEXT NULL,

flags MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT ‘0’,

enchantments TEXT NOT NULL,

randomPropertyId SMALLINT(5) NOT NULL DEFAULT ‘0’,

durability SMALLINT(5) UNSIGNED NOT NULL DEFAULT ‘0’,

playedTime INT(10) UNSIGNED NOT NULL DEFAULT ‘0’,

text TEXT NULL,

old_guid int(10) unsigned NOT NULL DEFAULT ‘0’,

PRIMARY KEY (guid),

INDEX idx_owner_guid (owner_guid),

KEY idx_oldguid_tmp (old_guid)

) COMMENT=‘Item System’ COLLATE=‘utf8_general_ci’ ENGINE=InnoDB AUTO_INCREMENT=100;

/* add new guid fields to tables */

ALTER TABLE auctionhouse ADD COLUMN new_itemguid INT(10) UNSIGNED NOT NULL;

ALTER TABLE character_feed_log ADD COLUMN new_item_guid INT(10) UNSIGNED NOT NULL;

ALTER TABLE character_gifts ADD COLUMN new_item_guid INT(10) UNSIGNED NOT NULL;

ALTER TABLE character_inventory ADD COLUMN new_item_guid INT(10) NOT NULL DEFAULT ‘0’ AFTER item,

ADD COLUMN new_bag INT(10) NOT NULL DEFAULT ‘0’ AFTER new_item_guid;

ALTER TABLE guild_bank_item ADD COLUMN new_item_guid INT(10) UNSIGNED NOT NULL;

ALTER TABLE item_loot_items ADD COLUMN new_container_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE item_loot_money ADD COLUMN new_container_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE item_refund_instance ADD COLUMN new_item_guid INT(10) NOT NULL;

ALTER TABLE item_soulbound_trade_data ADD COLUMN new_itemGuid INT(10) NOT NULL;

ALTER TABLE mail_items ADD COLUMN new_item_guid INT(10) NOT NULL;

/* insert data into the new table */

INSERT INTO item_instance_temp (

itemEntry, owner_guid, creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, durability, playedTime, text, old_guid

) SELECT itemEntry, owner_guid, creatorGuid, giftCreatorGuid, count, duration, charges, flags, enchantments, randomPropertyId, durability, playedTime, text, guid

FROM item_instance ORDER BY itemEntry ASC;

/* delete entries that don’t have a reference in item_instance */

DELETE FROM auctionhouse WHERE itemguid NOT IN (SELECT guid FROM item_instance);

DELETE FROM character_gifts WHERE item_guid NOT IN (SELECT guid FROM item_instance);

DELETE FROM character_inventory WHERE item NOT IN (SELECT guid FROM item_instance);

DELETE FROM guild_bank_item WHERE item_guid NOT IN (SELECT guid FROM item_instance);

DELETE FROM item_loot_items WHERE container_id NOT IN (SELECT guid FROM item_instance);

DELETE FROM item_loot_money WHERE container_id NOT IN (SELECT guid FROM item_instance);

DELETE FROM item_refund_instance WHERE item_guid NOT IN (SELECT guid FROM item_instance);

DELETE FROM item_soulbound_trade_data WHERE itemGuid NOT IN (SELECT guid FROM item_instance);

DELETE FROM mail_items WHERE item_guid NOT IN (SELECT guid FROM item_instance);

/* delete entries from characher_inventory for players that don’t exist anymore*/

delete from character_inventory where guid not in (select guid from characters);

/* delete entries from characther_inventory with bags that don’t exist in item_instance */

delete from character_inventory where bag not in (select guid from item_instance) and bag>0;

/* delete entries from item_instance where itemEntry is not found in world.item_template */

delete from item_instance where itemEntry not in (select entry from world.item_template);

/* add new guid to tables */

UPDATE auctionhouse p

INNER JOIN item_instance_temp pp

ON p.itemguid = pp.old_guid

SET p.new_itemguid = pp.guid;

UPDATE character_feed_log p

INNER JOIN item_instance_temp pp

ON p.item_guid = pp.old_guid

SET p.new_item_guid = pp.guid;

UPDATE character_gifts p

INNER JOIN item_instance_temp pp

ON p.item_guid = pp.old_guid

SET p.new_item_guid = pp.guid;

UPDATE character_inventory p

INNER JOIN item_instance_temp pp

ON p.item = pp.old_guid

SET p.new_item_guid = pp.guid;

UPDATE character_inventory p

INNER JOIN item_instance_temp pp

ON p.bag = pp.old_guid

SET p.new_bag = pp.guid;

UPDATE guild_bank_item p

INNER JOIN item_instance_temp pp

ON p.item_guid = pp.old_guid

SET p.new_item_guid = pp.guid;

UPDATE item_loot_items p

INNER JOIN item_instance_temp pp

ON p.container_id = pp.old_guid

SET p.new_container_id = pp.guid;

UPDATE item_loot_money p

INNER JOIN item_instance_temp pp

ON p.container_id = pp.old_guid

SET p.new_container_id = pp.guid;

UPDATE item_refund_instance p

INNER JOIN item_instance_temp pp

ON p.item_guid = pp.old_guid

SET p.new_item_guid = pp.guid;

UPDATE item_soulbound_trade_data p

INNER JOIN item_instance_temp pp

ON p.itemGuid = pp.old_guid

SET p.new_itemGuid = pp.guid;

UPDATE mail_items p

INNER JOIN item_instance_temp pp

ON p.item_guid = pp.old_guid

SET p.new_item_guid = pp.guid;

/* drop old item_instance and rename item_instance_temp to item_instance */

DROP TABLE item_instance;

ALTER TABLE item_instance_temp DROP COLUMN old_guid;

RENAME TABLE item_instance_temp TO item_instance;

ALTER TABLE item_instance CHANGE COLUMN guid guid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ FIRST;

/* drop old guid field and rename new one to same name as the one dropped */

ALTER TABLE auctionhouse DROP COLUMN itemguid,

CHANGE COLUMN new_itemguid itemguid INT(10) UNSIGNED NOT NULL AFTER auctioneerguid,

ADD UNIQUE INDEX itemguid (itemguid);

ALTER TABLE character_feed_log DROP COLUMN item_guid,

CHANGE COLUMN new_item_guid item_guid INT(11) NOT NULL AFTER difficulty;

ALTER TABLE character_gifts DROP COLUMN item_guid,

CHANGE COLUMN new_item_guid item_guid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER guid,

ADD PRIMARY KEY (item_guid);

ALTER TABLE guild_bank_item DROP COLUMN item_guid,

CHANGE COLUMN new_item_guid item_guid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER SlotId,

ADD INDEX Idx_item_guid (item_guid);

ALTER TABLE item_loot_items DROP COLUMN container_id,

CHANGE COLUMN new_container_id container_id INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘guid of container (item_instance.guid)’ FIRST;

ALTER TABLE item_loot_money DROP COLUMN container_id,

CHANGE COLUMN new_container_id container_id INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘guid of container (item_instance.guid)’ FIRST;

ALTER TABLE item_refund_instance DROP COLUMN item_guid,

CHANGE COLUMN new_item_guid item_guid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘Item GUID’ FIRST,

DROP PRIMARY KEY,

ADD PRIMARY KEY (player_guid, item_guid);

ALTER TABLE item_soulbound_trade_data DROP COLUMN itemGuid,

CHANGE COLUMN new_itemGuid itemGuid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘Item GUID’ FIRST,

ADD PRIMARY KEY (itemGuid);

ALTER TABLE mail_items DROP COLUMN item_guid,

CHANGE COLUMN new_item_guid item_guid INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER mail_id,

ADD PRIMARY KEY (item_guid);

ALTER TABLE character_inventory DROP INDEX guid,

DROP COLUMN item,

CHANGE COLUMN new_item_guid item INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘Item Global Unique Identifier’ AFTER slot,

ADD PRIMARY KEY (item),

DROP COLUMN bag,

CHANGE COLUMN new_bag bag INT(10) UNSIGNED NOT NULL DEFAULT ‘0’ AFTER guid,

ADD UNIQUE INDEX guid (guid, bag, slot);

[/SPOILER]

[MrSmite]

+WishToDie:

That actually looks similar to the one I’ve been working on. Even the vernacular of the comments is how I write… up to the part where it says /* delete entries that don’t have a reference in item_instance */ is identical to what I’ve been working on.

I can’t find where I posted that before but it looks like someone has greatly expanded on what I was trying to do, I’ll have to give it a try sometime.

Thanks!

[WishToDie]

Well, i based the script on the function found in TC DB named “sp_dev_reguid” and adapted it to suit item guids…only thing needed is the item sets…at the time i used the script i forgot about those, and when i noticed it was already too late /emoticons/default_smile.png

[MrSmite]

@WishToDie

Well, i based the script on the function found in TC DB named “sp_dev_reguid” and adapted it to suit item guids…only thing needed is the item sets…at the time i used the script i forgot about those, and when i noticed it was already too late /emoticons/default_smile.png

Right now my main goal is to simply reguid the item_intsance table because of the leftover expired auctions. If I can cobble something together between what you posted and what I’ve already got, I’ll definitely post it.