After much frustration I gave up on updating the source for a bit. Now trying to give it another shot, and this time with Trevius suggested code as well. As you can see it seem to run fine and no errors:
(On a brief side note, after last source update attempt, with help from Secrets and Akkadius, figured out the zone crashing and npc eating items handed in was due to some plugins, now trying to resolve final issue - loot).
Code:
alter table loottable_entries add `droplimit` tinyint(2) unsigned NOT NULL default 0;
alter table loottable_entries add `mindrop` tinyint(2) unsigned NOT NULL default 0;
alter table lootdrop_entries change `chance` `chance` float not null default 1;
alter table lootdrop_entries add `multiplier` tinyint(2) unsigned NOT NULL default 1;
CREATE TEMPORARY TABLE temp_table (
lootdrop_id INT(11) PRIMARY KEY,
chance INT(11) DEFAULT 0
);
INSERT INTO temp_table ( temp_table.lootdrop_id, temp_table.chance ) (SELECT lootdrop_entries.lootdrop_id, SUM(lootdrop_entries.chance) FROM lootdrop_entries GROUP BY lootdrop_entries.lootdrop_id);
UPDATE lootdrop_entries, temp_table SET lootdrop_entries.chance = (lootdrop_entries.chance * 100 / temp_table.chance) WHERE lootdrop_entries.lootdrop_id = temp_table.lootdrop_id;
DROP TEMPORARY TABLE temp_table;
update lootdrop_entries lde
inner join loottable_entries lte ON lte.lootdrop_id = lde.lootdrop_id
SET lde.chance = (lte.probability/100)*(lde.chance/100)*100;
update loottable_entries set mindrop = multiplier where probability = 100;
update loottable_entries set droplimit = multiplier where probability = 100;
update loottable_entries set multiplier = 1 where probability = 100;
update loottable_entries set droplimit = 1 where probability != 100;
update lootdrop_entries set chance = 100 where chance > 100;
alter table loottable_entries drop `probability`;
ALTER TABLE `loottable_entries` ADD `probability` FLOAT NOT NULL DEFAULT '100';
/* ************************************* */
[SQL] alter table loottable_entries add `droplimit` tinyint(2) unsigned NOT NULL default 0;
Affected rows: 23935
Time: 0.060ms
[SQL]
alter table loottable_entries add `mindrop` tinyint(2) unsigned NOT NULL default 0;
Affected rows: 23935
Time: 0.050ms
[SQL]
alter table lootdrop_entries change `chance` `chance` float not null default 1;
Affected rows: 87010
Time: 0.210ms
[SQL]
alter table lootdrop_entries add `multiplier` tinyint(2) unsigned NOT NULL default 1;
Affected rows: 87010
Time: 0.210ms
[SQL]
CREATE TEMPORARY TABLE temp_table (
lootdrop_id INT(11) PRIMARY KEY,
chance INT(11) DEFAULT 0
);
Affected rows: 0
Time: 0.050ms
[SQL]
INSERT INTO temp_table ( temp_table.lootdrop_id, temp_table.chance ) (SELECT lootdrop_entries.lootdrop_id, SUM(lootdrop_entries.chance) FROM lootdrop_entries GROUP BY lootdrop_entries.lootdrop_id);
Affected rows: 23170
Time: 0.220ms
[SQL]
UPDATE lootdrop_entries, temp_table SET lootdrop_entries.chance = (lootdrop_entries.chance * 100 / temp_table.chance) WHERE lootdrop_entries.lootdrop_id = temp_table.lootdrop_id;
Affected rows: 4470
Time: 0.340ms
[SQL]
DROP TEMPORARY TABLE temp_table;
Affected rows: 0
Time: 0.010ms
[SQL]
update lootdrop_entries lde
inner join loottable_entries lte ON lte.lootdrop_id = lde.lootdrop_id
SET lde.chance = (lte.probability/100)*(lde.chance/100)*100;
Affected rows: 80171
Time: 0.840ms
[SQL]
update loottable_entries set mindrop = multiplier where probability = 100;
Affected rows: 1790
Time: 0.010ms
[SQL]
update loottable_entries set droplimit = multiplier where probability = 100;
Affected rows: 1790
Time: 0.010ms
[SQL]
update loottable_entries set multiplier = 1 where probability = 100;
Affected rows: 318
Time: 0.000ms
[SQL]
update loottable_entries set droplimit = 1 where probability != 100;
Affected rows: 22145
Time: 0.070ms
[SQL]
update lootdrop_entries set chance = 100 where chance > 100;
Affected rows: 0
Time: 0.000ms
[SQL]
alter table loottable_entries drop `probability`;
Affected rows: 23935
Time: 0.040ms
[SQL] ALTER TABLE `loottable_entries` ADD `probability` FLOAT NOT NULL DEFAULT '100';
Affected rows: 23935
Time: 0.050ms
Now I'm trying to compare the results of the new system to the old system, and maybe I don't understand the new system, but seems that some stuff didn't convert.
I'm not going to argue that the new loot system is broken. I'm sure it is working as intended. Its just the 5-6 years of custom content we have is not converting over properly, or at least it doesn't appear to.
The screenshot of the tables below is an example from our hohonora zone where players farm tokens to be turned in later for boss spawns. Originally each trash would have an 8 percent chance to drop 1 of 8 different tokens.
Now I understand the new loot system did away with the 'list' of items, but the new results now after conversion show the loot would drop either 0 or 1 of those 8 tokens, but when you step trying 20% chance, if fail then 15% chance, all the way down the list, that adds up to 100% chance (average) that a player would get 1 token.
So now instead of getting a token 8% of the time, its almost 100%, but more realistically players were reporting token drops about 8 of every 10 mobs (80% chance average getting tokens).
If my perception is wrong, then please correct me based on the screenshot provided below. I'm sure the new loot system is working as intended, and working better than the old system. I'm sure the conversion query is working as intended as well. Nothing seemed to break, but our loot tables don't seem to have drop rates as intended anymore.
Trying to give this update / loot system another shot, and not trying to stir up any pointless debates about 'fixing' the new loot system. Just want to be able to move forward with our custom loot drops as intended.
Linear Mode
