Core/SQL: fix a few minor mishaps with wrong keys not being updated in commit 5357b1ba77 - fixes import of base characters database (thanks click for pointing out). Closes #1231.

Also rename indexes in recently cleaned tables to have 'idx_' prefix.
Add queries to recreate indexes based on recently modified columns (just to make sure that all indexes are correct).

2 files changed, 35 insertions, 6 deletions

diff --git a/sql/base/characters_database.sql b/sql/base/characters_database.sql
index 3ac49e0b8b3..29fa888ed78 100644
--- a/sql/base/characters_database.sql
+++ b/sql/base/characters_database.sql
@@ -1212,11 +1212,11 @@ CREATE TABLE `corpse` (
   `time` int(10) unsigned NOT NULL DEFAULT '0',
   `corpseType` tinyint(3) unsigned NOT NULL DEFAULT '0',
   `instanceId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Instance Identifier',
-  PRIMARY KEY (`guid`),
+  PRIMARY KEY (`corpseGuid`),
   KEY `idx_type` (`corpseType`),
-  KEY `instance` (`instanceId`),
-  KEY `Idx_player` (`player`),
-  KEY `Idx_time` (`time`)
+  KEY `idx_instance` (`instanceId`),
+  KEY `idx_player` (`guid`),
+  KEY `idx_time` (`time`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Death System';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
@@ -1241,7 +1241,7 @@ CREATE TABLE `creature_respawn` (
   `respawnTime` int(10) unsigned NOT NULL DEFAULT '0',
   `instanceId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Instance Identifier',
   PRIMARY KEY (`guid`,`instanceId`),
-  KEY `instance` (`instanceId`)
+  KEY `idx_instance` (`instanceId`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Grid Loading System';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
@@ -1314,7 +1314,7 @@ CREATE TABLE `gameobject_respawn` (
   `respawnTime` int(10) unsigned NOT NULL DEFAULT '0',
   `instanceId` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Instance Identifier',
   PRIMARY KEY (`guid`,`instanceId`),
-  KEY `instance` (`instanceId`)
+  KEY `idx_instance` (`instanceId`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Grid Loading System';
 /*!40101 SET character_set_client = @saved_cs_client */;
 
diff --git a/sql/updates/auth_char/2011_04_14_00_characters.sql b/sql/updates/auth_char/2011_04_14_00_characters.sql
new file mode 100644
index 00000000000..ab9bd93419d
--- /dev/null
+++ b/sql/updates/auth_char/2011_04_14_00_characters.sql
@@ -0,0 +1,29 @@
+ALTER TABLE corpse
+    DROP PRIMARY KEY,
+    DROP KEY `idx_type`,
+    DROP KEY `instance`,
+    DROP KEY `Idx_player`,
+    DROP KEY `Idx_time`;
+
+ALTER TABLE corpse
+    ADD PRIMARY KEY (`corpseGuid`),
+    ADD KEY `idx_type`(`corpseType`),
+    ADD KEY `idx_instance`(`instanceId`),
+    ADD KEY `idx_player`(`guid`),
+    ADD KEY `idx_time`(`time`);
+
+ALTER TABLE creature_respawn
+    DROP PRIMARY KEY,
+    DROP KEY `instance`;
+
+ALTER TABLE creature_respawn
+    ADD PRIMARY KEY (`guid`, `instanceId`),
+    ADD KEY `idx_instance`(`instanceId`);
+
+ALTER TABLE gameobject_respawn
+    DROP PRIMARY KEY,
+    DROP KEY `instance`;
+
+ALTER TABLE gameobject_respawn
+    ADD PRIMARY KEY (`guid`, `instanceId`),
+    ADD KEY `idx_instance`(`instanceId`);