New loot system

[ChaosSlayerZ]

thanks Cavedude

[ghanja]

I'm sorry for not following exactly in all this. Has the change been reverted entirely, or is there now chance and probability?

[Akkadius]

Originally Posted by ghanja

I'm sorry for not following exactly in all this. Has the change been reverted entirely, or is there now chance and probability?

Yes, there is now chance AND probability

[thepoetwarrior]

I know the new loot system is working on intended, just need to fix coverting properly the old loot to have loot drop rates as intended.

I was thinking that probability was the issue too.

Here is a quick sample:



I'm guessing if I copy the probability from the old loottable_entries to the new loottable_entries then I would see the desired drop rates we had before?

The problem with manually fixing all the drop rates below 1% is 6 years worth of custom content would be a nightmare to fix manually.

[cavedude]

If you intend on using probability, then both probability and chance would need to be changed to what you had in the old system. If you didn't change chance, then yes change probability back to what it was and you'll be good to go.

If you used the script on SVN to convert your tables over, then loot will drop at the same rate it did before. The only exception would be multiple lootdrops using the same chance but different probability would not have been converted properly. If you did convert, I'd suggest restoring from a backup and forget about the conversion process. That was an oversight on my part brought to my attention in this thread. Though, I am assuming you didn't convert, since your rates are off.

[thepoetwarrior]

I've restored from backup already and rolled back.

I think some of the loots got updated, and some didn't. Should be able to run probability on 100% for everything since chance should have been updated to usually a decimal number for rare loots.

Will try to re run the script again later.

[cavedude]

If you restored your database back to the way it was before the changes, run these queries to restore your functionality as well:

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

This will remove multiplier for tables that have a 100% probability (since mindrop/droplimit can now handle that, although you can still use multiplier if you wish using droplimit) and it forces all other lootdrops to only drop 1 item at a time max (unless a multiplier is set, of course) which duplicates the behavior of the old system. Chance and probability no longer needs to be changed since probability is back.

The difference is in the old system, a lootdrop could only drop a single item unless a multiplier was set, but that still was determined by probability. Now in the new system, we can control exactly how many items will or could drop per lootdrop and it can be as many or as few as you need.

[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]

Thanks you so much! Will give it a shot. Makes sense how the drop/add changed it all to 100%

As always, I back up the tables before doing any changes, so I can easily start over again to try.

[thepoetwarrior]

Yup, I ran 100% probability query last and so far the users are saying loot is back to normal.

I appreciate being able to keep old system drop rates that were intended, and I plan to use the new system the way it was meant to be used for future content.

I have an item with 1/666 chance for drop rate which required a chance of a chance, but now I can just make it 0.0015 chance. So I'll definitely be taking advantage of that float!

Thanks again

[cavedude]

Akkadius wrote up a kick-ass script to grab the loot data from Magelo and insert it into the DB. Many NPCs have a long list of < 1% drops. I've been testing out all those NPCs and the new system is handling it perfectly. Some of the NPCs have 100+ < 1% items and you'll be lucky if one of them drops on PEQ, which is in-tune with Live. So yeah, that's my favorite part of the new system as well.

[Gregk]

This new script and database update are still in the works, correct?