WowRoster 1.7.3 Upgrade and missing database indexes

Installation issues with WoWRoster

WowRoster 1.7.3 Upgrade and missing database indexes

Postby Fangorn » Sat Feb 24, 2007 12:24 am

If you've been upgrading your roster for a few releases like I have, you might be suffering from the same issue.

Roster 1.7.3 adds primary keys to a number of tables to improve performances, however it seems that in older releases of the roster, four of those tables contained duplicate entries.
This prevents the creation of the indexes in 1.7.3 since the index is unique, however the installation does not report it so you won't see a difference (except poor performances on the character page). You will need to check your database manually to make sure the indexes are there.

You should have PRIMARY indexes for the 4 following tables:

roster_spellbook
roster_spellbooktree
roster_talents
roster_talenttree

If you can't see your indexes, the quick and dirty fix is (replace roster_ with your roster prefix):

1/ delete everything in the tables:
delete from roster_spellbook;
delete from roster_spellbooktree;
delete from roster_talents;
delete from roster_talenttree;

2/ Create the indexes:
ALTER TABLE `roster_spellbook`
ADD PRIMARY KEY (`member_id`,`spell_name`,`spell_rank`);
ALTER TABLE `roster_spellbooktree`
ADD PRIMARY KEY (`member_id`,`spell_type`);
ALTER TABLE `roster_talents`
ADD PRIMARY KEY (`member_id`,`tree`,`row`,`column`);
ALTER TABLE `roster_talenttree`
ADD PRIMARY KEY (`member_id`,`tree`);


This won't break/empty your roster: the character pages just won't display any talents or spells, and the missing talents and spells will be reprovisioned when your guildmates upload their lua files.

The clean fix would be to write a script to delete the duplicate rows in the tables but I'm too lazy to write it :P



- Fang
ImageImageImage
User avatar
Fangorn
WR.net Apprentice
WR.net Apprentice
 
Posts: 35
Joined: Mon Jul 24, 2006 4:26 am
Location: Ashburn, Virginia, USA

Return to Installation

Who is online

Users browsing this forum: No registered users and 0 guests

cron