New loot system

[jasmine88]

nice info keep it up..........

[thepoetwarrior]

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).

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.

[Secrets]

You are dropping probability and then re-adding it. Meaning you have a 100% chance to get every single loot drop.

Simply do not run the last two queries:

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

It's causing them to be fubared as a result.

edit: read cavedude's post. it will fix things!

[cavedude]

Restore your database from a backup (the old system) and run the following queries and ONLY the following queries on loot:

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;
update loottable_entries set droplimit = 1 where probability != 100;
update loottable_entries set mindrop = multiplier, droplimit = multiplier, multiplier = 1 where probability = 100;

This will convert your tables to the new system, while keeping the old functionality. The problem was when I removed probability the conversion SQL was needed. Since it's added back, the only change needed is to set droplimit to 1 so that the tables will only drop 1 item at a time like in the old system. Multiplier ignores droplimit so that will still work as intended. Your chance and probability do not need to change at all (which is your issue in the example - your probability changed from 8% to 100%, but you didn't change chance to compensate.)

I'm going to go ahead and change the SQL on SVN to reflect this.

[thepoetwarrior]

Originally Posted by cavedude

Restore your database from a backup (the old system) and run the following queries and ONLY the following queries on loot:

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;
update loottable_entries set droplimit = 1;

This will convert your tables to the new system, while keeping the old functionality. The problem was when I removed probability the conversion SQL was needed. Since it's added back, the only change needed is to set droplimit to 1 so that the tables will only drop 1 item at a time like in the old system. Multiplier ignores droplimit so that will still work as intended. Your chance and probability do not need to change at all (which is your issue in the example - your probability changed from 8% to 100%, but you didn't change chance to compensate.)

I'm going to go ahead and change the SQL on SVN to reflect this.

On page 5 you posted this:

update loottable_entries set mindrop = multiplier, droplimit = multiplier, multiplier = 1 where probability = 100;

Which would probably be good to run after your new 5 lines in the loot change query?

Before in the old system it had always 5 gems drop out of a list of 11 gems, but after running the 5 line query there would only be about 1-5 gems, not always 5. This seems that it might fix it. Players will be testing it soon to verify.

On a good note, loots are not flooding the economy like before. Thanks for allowing to some what use the old system in the new systems structure.

[cavedude]

Run that query last. Sorry about that I had forgotten about tables with a 100% probability and a multiplier set last night. I'll also add that query to SVN.

With probability back the new system can function exactly like the old if needed. But it is my hope that over time, server ops can incorporate the new features of loot into their server as it really is a more robust much better overall system.