EQEmulator Forums - New loot system

EQEmulator Forums (https://www.eqemulator.org/forums/index.php)

- General::General Discussion (https://www.eqemulator.org/forums/forumdisplay.php?f=586)

- - New loot system (https://www.eqemulator.org/forums/showthread.php?t=35770)

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.

http://i45.tinypic.com/vg34gn.png

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:

Quote:

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!

Restore your database from a backup (the old system) and run the following queries and ONLY the following queries on 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;

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.

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.

Quote:

Originally Posted by cavedude (Post 215260)

Restore your database from a backup (the old system) and run the following queries and ONLY the following queries on 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;

update loottable_entries set droplimit = 1;

On page 5 you posted this:

Code:

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.

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.

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

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.

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

The Magelo loot/merchant dump will be in the next PEQ database for OoW and after. GoD and before are already pretty good due to manual changes by the PEQ team.

Quote:

Originally Posted by cavedude (Post 215273)

The Magelo loot/merchant dump will be in the next PEQ database for OoW and after. GoD and before are already pretty good due to manual changes by the PEQ team.

Good stuff! I grabbed the database a while ago from the BETA backend download :)

After about 1/2 day of testing, players report loot is working 99.9% as before. Really hard to find anything 'not intended' drop rates now.

Thanks eqemu team!

The PEQ DB with Magelo's loot/merchants merged in for OoW to VoA (RoF is not on Magelo yet) is now up on PEQ SVN: https://code.google.com/p/projecteqdb/downloads/list

In addition, since Derision updated EQExtractor to work with the newest patch, Robregen got collects for all the RoF zones. They are in this DB as well. Of course, they cannot be played yet as we don't have a compatible client, but the f2p Steam client is being worked on currently :)

We are missing factions and the NPCs all need a mass stat update, but realistically there is nothing stopping folks from playing in the newer zones up to what UF supports. There isn't any quests or events of course, but you can certainly run around, kill things, and collect loot now.

awyeaaaah, christmas!

New PEQ 2 days before my birthday? You guys rock, keep up the incredible work!