MW_052_053_alpha2 (released 12-21-03)

[bud9weiser]

This release includes so many updates and fixes that i cant even begin to remember what all was intaled. Check Changelog for notes. Thanks to the MW team.
You will however notice that most zones are at leased populated, we will now begin work on faction and loot for the added spawns, and many other bug fixes.

Note: This release is intended for 5.2 and 5.3dr1 (5.3dr2 works but you will loose caracter information) dont say i didnt warn you.

http://osdn.dl.sourceforge.net/sourceforge/projecteq/mw_052_053_alpha2.zip Complete
http://osdn.dl.sourceforge.net/sourceforge/projecteq/mw_052_053_alpha2_upgrade.zip Upgrade (keeps your bugs petitions guilds caracters variables and some other things)
Reminder WE DO NOT TAKE RESPONSABILITY FOR DATA LOSS WITH 053dr2 for your caracters!

[Bearik]

Man, a EQlive compatible EQEmu and a new TCS release in one day. This kicks ass.

And to top it all off the seahawks won :) Woot

Edit: btw, is this the version that is merged with the "Re-invention of the wheel" DB?

[bud9weiser]

Yes its the merge, being so, alot of areas took a huge forward step, but i fear that some things may have taken a backwards step so please report bugs in this forum so we may get them fixed up asap

[rockocool]

This is tested in everfrost, low level mobs are still hiting for hundreds (incorrect maxdmg), and instant spawn.
Also rivervale halfing druid master now becomes BL master (incorrect class).
Havent tested anything else yet.

anytime you attack a high lvel mob or they attack you you get disconnected.
ie attacking a guard you go ld.

you pet can attack him after he kills your pet it disconnects you.

[vetoeq]

The problem with LD isn't happening to me. 5.3DR2 + This DB. I just can't #zone to sebilis or gukbottom (haven't tried others) it crashes the Zone.exe and I have to #movechar the char out.

--
Veto

[jardar]

i too am getting disconnected when fighting npcs, right before getting killed it just boots to character select. using the newly patched eq not sure if thats the problem or not. still have the eq right before the patch but thought i would try this out with newly patched eq to have the new spells affects :) wondering if yall are gonna work on fixing some of the aa's. im truly loving how fast you guys are getting these updates out to us. keep up the good work it's much appreciated.

[bud9weiser]

I have no reason to belive that this is a db issue at this time as this version of the db works fine with 5.3dr1 i do know their are some bugs in the newest release that works with eqlive, but have no confermation on what bugs where as i havent even attempted to run it yet

[bud9weiser]

I have no reason to belive that this is a db issue at this time as this version of the db works fine with 5.3dr1 i do know their are some bugs in the newest release that works with eqlive, but have no confermation on what bugs where as i havent even attempted to run it yet

[r2d2atemyhomework]

You are correct bud9weiser, however the frequency of the disconnects can be worked around by fixing bad data in the database. The client crashes in 5.3 DR2 appear to be happening whenever your character dies, so your assumption is correct, it's a code problem. However I was frequently getting killed in one hit by several low level mobs which then caused the disconnect. This is happening because an inordinate amount of mobs have their maxdmg set to 400:

mysql> select count(*) from npc_types where maxdmg = 400;
+----------+
| count(*) |
+----------+
| 44854 |
+----------+


Compare that to all the other rows with maxdmg values other than 400:

mysql> select count(*) from npc_types where maxdmg <> 400;
+----------+
| count(*) |
+----------+
| 26922 |
+----------+


Also see the levels which have maxdmg above 400:

mysql> select distinct level from npc_types where maxdmg > 400;
+-------+
| level |
+-------+
| 255 |
| 60 |
| 66 |
| 61 |
| 65 |
| 62 |
+-------+


My recommendation for the next release of the db is to take the average of all the distinct maxdmg values for each level and do a mass update per level with the averages. Of course if you are able to get the "legit" damage ranges in there that will be even better. :)

EDIT: I realized the above should read "take the average of all the distinct maxdmg values other than 400" :roll:

[Muuss]

since you know sql code, you could just make a short fix by yourself (at least for your own use if you need it), of the kind :

update npc_types set maxdmg=9 where level=1;
update npc_types set maxdmg=14 where level=2;

...

[r2d2atemyhomework]

since you know sql code, you could just make a short fix by yourself (at least for your own use if you need it), of the kind :

update npc_types set maxdmg=9 where level=1;
update npc_types set maxdmg=14 where level=2;

...

I actually already did and things are running great now. :D

In mysql you can't do a subselect in an update when the subselect queries the table you are updating, so I had to break it in to two queries. The first grabs the average and the second updates the maxdmg. It will only update rows with maxdmg equal to 400. Obviously you will have to do this manually for each level or write a script/app to do it for you. Here's the SELECT query which gets the average, in this case for level 1:


select ROUND(AVG(maxdmg)) from npc_types where level = 1 and maxdmg <> 400;


Then take the average returned by the query above and set maxdmg to it. In this update, the average was 4 for level 1:


update npc_types set maxdmg = 4 where level = 1 and maxdmg = 400;

[vetoeq]

The problem with LD isn't happening to me. 5.3DR2 + This DB. I just can't #zone to sebilis or gukbottom (haven't tried others) it crashes the Zone.exe and I have to #movechar the char out.


I removed my -old- maps/*.map files and I can now zone to Sebilis and such, so that was due to me having stuff laying around from 43 or 44.

By the way, I don't remember why I had the $EQEMU/Maps directory, what are those needed for? I see the zone loading the Maps/*.map file when it goes into a zone, however, it seems to work fine when I don't have them. What purpose do they serve?

--
Veto

[r2d2atemyhomework]

Also if you want to make sure you get loot (but no drops) off of every kill with this database, run the following in mysql:


update npc_types set loottable_id = 1 where level <= 10 and loottable_id = 0;
update npc_types set loottable_id = 2 where level > 10 and level <=20 and loottable_id = 0;
update npc_types set loottable_id = 3 where level > 20 and level <=30 and loottable_id = 0;
update npc_types set loottable_id = 4 where level > 30 and level <=40 and loottable_id = 0;
update npc_types set loottable_id = 5 where level > 40 and level <=50 and loottable_id = 0;
update npc_types set loottable_id = 6 where level > 50 and level <=60 and loottable_id = 0;
update npc_types set loottable_id = 7 where level > 60 and loottable_id = 0;


Again it's not accurate, but it's better than nothing. This query will maintain any existing loottable values NPCs already have.

[bud9weiser]

We will be fixing the issues with max and min damage as soon as we can, alot of this release is new data and still needs tweeking and adjusting. Their will be more updates to follow.

[DataMan4971]

Very Nice....Reloaded Server with it, seems to work nice, keep up the good work! :wink:

[maethwyn]

Awesome Job!

A few changes I made to make it more enjoyable for me:
01 - Stop those pesky insta-respawns:
UPDATE spawn2 SET respawntime=1000 WHERE respawntime=0;

02 - Create a new loottable (301 here) for NPCs with no drops at all (loottable_id=0):
UPDATE npc_types SET loottable_id=301 WHERE loottable_id=0;

03 - I created a small table of funky items (loottable_id=300) for all mobs lvl 1-10 for fun... it's kind of neat and made the low levels fun

04 - Some proposed maxdmg catch-alls (mainly to rid the 400[dmg] hitting noobie mobs):
http://www.everquestserver.com/forums/viewtopic.php?p=63156#63156

I'll post back if I have any more ideas :)

*EDIT* - And for the curious people who might not know how to do it... here is how to make a new loottable and have it implemented:

a) INSERT INTO loottable (id, name, mincash, maxcash, avgcoin) VALUES ('yourID', 'yourNAME', 25, 350, 2);
--> replace 'yourID' with and unused loottable ID number and use whatever text for 'yourNAME' to describe the entry
b) INSERT INTO loottable_entries (loottable_id, lootdrop_id, multiplier, probability) VALUES ('yourID', 'yourID', '#drops', '%#drops');
--> use the same 'yourID' number for sanity's sake; '#drops' is the max number of slots drops will occupy if '%drops' is @ '100'
c) INSERT INTO lootdrop (id, name) VALUES ('yourID', 'yourNAME2');
--> same 'yourID' number & don't use spaces in the 'yourNAME2' field (well, no one else did anyway... not sure if it's required)
d) INSERT INTO lootdrop_entries (lootdrop_id, item_id, item_charges, equip_item, chance) VALUES ('yourID', 'itemID', 1, 1, '%chance');
--> use same 'yourID' for ALL entries of individual items so they stay in your new table; 'itemID' is the "#summonitem" itemID number of the item you want to insert; just keep '%chance' the same with all for equal drop rates of all items in the table
e) UPDATE npc_types SET lootable_id='yourID' WHERE 'condition';
--> 'yourID' is your new loottable ID and 'condition' is the condition upon which you wish to apply the new table (i.e. - loottable_id=0)

Hope that helps :p

[maethwyn]

05 - Some level 24 mobs have WAY too many hps (i.e. - stonegrabbers in MSeru)
UPDATE npc_types SET hp=880 WHERE (level=24 AND hp>3000);

06 - Personal opinion, I guess, but skeletons seem to have an entirely too huge aggro range (e.g. - large chunks of kurns aggro at a time - even below/above current level)
UPDATE npc_types SET aggroradius=65 WHERE (name LIKE '%skeleton%' AND aggroradius=200);

[Kroeg]

Awesome recommendations... I wish everyone made them with sql statements! Thanks a lot for your time, I'll suggest these to get utilized.

[vetoeq]

I must agree with Kroeg that the inclusion of the SQL statements is a HUGE help with understanding how to get some things done.

/cheer

--
Veto

[Fleyhum]

i have a problem donno if its in DB or not but since its mob related i am assuming it is but when u attack a mob its health does not decrease it stays at full heath untill it dies using npcstats shows its hp droping but the target window stays at 100pct then it just dies

[Tertiary]

i have a problem donno if its in DB or not but since its mob related i am assuming it is but when u attack a mob its health does not decrease it stays at full heath untill it dies using npcstats shows its hp droping but the target window stays at 100pct then it just dies

http://www.everquestserver.com/forums/viewtopic.php?t=11967

[mollymillions]

Thanks for the DB, here are a few issues i have found.

Many of the class numbers for the NPC's are incorrect, many that are supposed to be GM's are set to merchants (41). There is no easy way to fix this other than referencing the class numbers from a db with correct data. I used this query to construct a query to perform the udates, where the EQ1 db is the current DB and the EQ db is an older version containing the correct class numbers:

SELECT CONCAT('UPDATE eq1.npc_types SET class = ', b.class, ' WHERE id = ', a.id, ';')
FROM eq1.npc_types a, eq.npc_types b
WHERE a.id = b.id
AND a.class <> b.class

(Here's (http://users.tpg.com.au/adslipjo/Files/ClassFix.sql) the resultant query)

Is there a reason why the start_zones table is not indexed? There are many redundant (replicated) records in this table.

Some of the start_zones.race, class, zone and deity records have no corresponding starting_items. race, class, zone and deity records (e.g. Any Freeport Cleric):

select start_zones.zone_id
, zone.short_name
, start_zones.player_race
, start_zones.player_choice
, start_zones.player_class
, class_skill.name
, start_zones.player_deity
, starting_items.id, items.name
from start_zones, zone, class_skill
left join starting_items
on start_zones.zone_id = starting_items.zoneid
and start_zones.player_race = starting_items.race
and start_zones.player_class = starting_items.class
and start_zones.player_deity = starting_items.deityid
left join items
on starting_items.itemid = items.id
where start_zones.zone_id = zone.zoneidnumber
and start_zones.player_class = class_skill.class
order by start_zones.player_race
, class_skill.name
, zone.short_name

This seems to be because of problems with the start_zones table, rather than the start_items table (some of the start_zone zone id's are incorrect). e.g. This update will fix the evil Erudite Clerics:

update start_zones
set zone_id = 75, start_zone = 75, x = 880, y = 456, z = -120.19
where start_zones.player_race = 3
and start_zones.player_class = 2
and start_zones.player_deity = 203

(I have referenced the x,y and z values from the Shodownights as I don

[cybermax]

Is there a relatively easy way of converting character data from 5.2 -> 5.3-DR2 format?

As you said.. i did loose all character data.. and if i tried to create a new character with the same name as the one i had (and prolly still is in the db someplace) i get a rejected name..

Any "hotfix" to this?

[Lurker_005]

Molly, keep putting out all that usefull info!

Nice SQL queries, more advanced that I have tried to go for yet.

[Tech_Gnome]

I've got a quick question about the database. It doesn't seem like anybody outside the dev team is interested in merchants. I've been looking through the database and noticed that some merchants are way off from what they should be, for example:

npc # 646 "a_clockwork_jeweller" in Ak'Anon is associated with fishing supplies
npc # 1344 "Felen_Razdal" in Butcherblock is associated with a random assortment of stuff, when he should be selling stuff like rations, lanterns, etc.

The code I used to come up with this is this:

SELECT items.name, items.id, merchantlist.item, merchantlist.merchantid, npc_types.merchant_id, npc_types.name, npc_types.id
FROM `merchantlist`, `items`, `npc_types` where items.id = merchantlist.item and merchantlist.merchantid = npc_types.merchant_id and npc_types.name like "%razdal%" order by npc_types.name, items.name

So my quick question becomes three. First off, what can I do to help set the merchants straight?

Secondly, is the merchantlist.merchantid an arbitrary value, or is the exact value important somewhere?

Lastly, did I just totally botch the SQL statement above(which isn't exactly pretty), and am getting randomly wrong results?

I've already started writing a SQL file out to help fix the problems, but I didn't want to start working on it and have someone say that I wasted my time because the merchantid's were important and I couldn't just choose arbitrary ones. If anyone can help answer my questions I'd be greatly appreciative.

Also, if I posted this in the wrong place.. smack me with a clue-by-four, and let me know where it needs to go.

[Scorpious2k]

Is there a relatively easy way of converting character data from 5.2 -> 5.3-DR2 format?

I wrote a conversion program for Scorpious2k Server :-) Interested?

I wasn't sure anyone but Conan at BrightBlade was looking for anything like this... maybe I should put a topic in 3rd PT...

[mollymillions]

Does anyone have a list of deityid's vs. names?

*edit* Scratch that, i worked it out.

[AK1122]

hey anyone notice that some of the spells in spells_us.txt were shifted around in the last patch? because of this some of the spell scrolls are bugged, and so are some of the weapon effects (eg. skin like wood).

The sollution to this i would persume is to make a program that compares the old and new spells_us.txt

first the spell structure must be loaded, then just keep reading from both files n build link lists (lots of memory required =S) then compare id's. Any changes are put into a query for SQL

UPDATE items SET spellid = (new spellid) where spellid = (old spellid) ;

should solve the problem... would fix it myself but i hate C++ and mysql =)

Thank you

[Shawn319]

hey anyone notice that some of the spells in spells_us.txt were shifted around in the last patch? because of this some of the spell scrolls are bugged, and so are some of the weapon effects (eg. skin like wood).

The sollution to this i would persume is to make a program that compares the old and new spells_us.txt

first the spell structure must be loaded, then just keep reading from both files n build link lists (lots of memory required =S) then compare id's. Any changes are put into a query for SQL

UPDATE items SET spellid = (new spellid) where spellid = (old spellid) ;

should solve the problem... would fix it myself but i hate C++ and mysql =)

Thank you

EQEmu was using the wrong format for spells_us.txt. This is fixed in 0.5.3-DR2 as of 1/2/03 and all spells should work as they should now.

[JerryChen]

I just update the sql files, but it has error 1017.

So i look the World.EXe status, i found a problem:Wrong name/pass: name='eqemu'

then i open mysql and run this command

INSERT INTO account SET name='eqemu', password='eqemu', status=200;


Now it's working

[cybermax]

I wrote a conversion program for Scorpious2k Server :-) Interested?

You could post one on the 3rd party forum indeed... I am in no rush tho, cos its just a private server for me and the kid i have like 6 characters, so i just remade them and summoned the gear again.. But i imagine it would be more peeps than me that would be interested tho :)

[rockocool]

Scorpious2K, could you please post this program? either here or in 3rd PT? thanks!

Is there a relatively easy way of converting character data from 5.2 -> 5.3-DR2 format?

I wrote a conversion program for Scorpious2k Server :-) Interested?

I wasn't sure anyone but Conan at BrightBlade was looking for anything like this... maybe I should put a topic in 3rd PT...

[Scorpious2k]

Posted:
http://www.eqemulator.net/forums/viewtopic.php?t=12165

[mollymillions]

There are a lot of duplicated spawns in the DB, they may be intentional but I wanted to get rid of them. e.g.

SELECT zone, x, y, z, left(npc_types.name,32) AS Name, count(spawn2.id) AS SpawnCount
FROM spawn2, spawnentry, npc_types
WHERE spawn2.spawngroupID = spawnentry.spawngroupID
AND spawnentry.npcID = npc_types.ID
AND spawn2.pathgrid = 0
GROUP by zone, x, y, z, npc_types.name
HAVING SpawnCount > 1
ORDER BY zone, x, y, z, npc_types.name

Here's a query to construct a query to delete the last spawn added for each replicated spawn (i didn

[Kroeg]

is this query built to delete anything other than the duplicate spawns? If so, hold off.. the My World team is tackling this problem (and has been for a few days) already.

When we merged data from another worldbuilder's sources, the transition wasn't entirely seemless =P but we're cleaning it up now. The id #s for the duplicate npcs are far too high, and contain strange lootdrop entries. Next DB release should contain a fix to this problem, but don't quote me. Sometimes we release the DBs before they're ready just to satisfy worldbuilders and serverops.. although I'm pretty sure this is the basis of our next release :D

Mollymillions -- We (MW DB Team) need you on our team.. check out #npcmovdb (and stay longer than 5 minutes, hehe.).

[grues0me]

I loaded this database up and have a problem:

One thing is that the guards don assist me when i flee to them from a fight. I realized the diconnects after dying too.

Second - are the trainers in? Do i have to source a different quest.sql in or are basic quests already in?

[farce]

Quests arent part of the db.

dunno about guards assisting.. they dont even attack a mob that is set to spawn 2 inches from them.

Disconnect on death is a known issue,and fixed for future releases.

[mattmeck]

One thing is that the guards don assist me when i flee to them from a fight.

thats done with factions in the DB

[grues0me]

I see. How about the trainers...i hail them and they wont reply...is this a prob with my setup or can someone confirm that its not working...

[mattmeck]

what happens when you right click on them? and are you running
the newest DB with the NPCID fixxes in it?

[Shawn319]

[quote=mollymillions]There are a lot of duplicated spawns in the DB, they may be intentional but I wanted to get rid of them. e.g.

SELECT zone, x, y, z, left(npc_types.name,32) AS Name, count(spawn2.id) AS SpawnCount
FROM spawn2, spawnentry, npc_types
WHERE spawn2.spawngroupID = spawnentry.spawngroupID
AND spawnentry.npcID = npc_types.ID
AND spawn2.pathgrid = 0
GROUP by zone, x, y, z, npc_types.name
HAVING SpawnCount > 1
ORDER BY zone, x, y, z, npc_types.name

Here's a query to construct a query to delete the last spawn added for each replicated spawn (i didn

[grues0me]

what happens when you right click on them? and are you running
the newest DB with the NPCID fixxes in it?

I am using live eq with Emu5.3Dr2 and mw_052_053_alpha2.

Right clicking opens a merchant window with no items in it.

The fix...i am not sure which you mean, but if you meant the classfix.sql...i gonna try it now.

[mattmeck]

I "think" thats what you need, with the last SOE patch they changed the class ID #'s, so it through everything off.

[grues0me]

Thanks that fix did it. My only problems now are now:

1. Health bars of the mob never decrease. I copied the fix i found on the board into the mob.cpp, no help.

2. The crash-on-dying bug. I realize it must have to do with the 400 damage some mob do, but i didnt found a fix on the board, except the global change of the max damage via sql commands.

[farce]

both of those issues are fixable.. LD on death is a one liner, mob HP stuff is a couple lines... the fixes work fine.

[grues0me]

For the Health bar:

void Mob::CreateHPPacket(APPLAYER* app)
{
this->IsFullHP=(cur_hp>=max_hp);

app->opcode = OP_HPUpdate;
app->size = sizeof(SpawnHPUpdate_Struct);
app->pBuffer = new uchar[app->size];
memset(app->pBuffer, 0, sizeof(SpawnHPUpdate_Struct));
SpawnHPUpdate_Struct* ds = (SpawnHPUpdate_Struct*)app->pBuffer;
ds->spawn_id = GetID();

if(IsClient())
{
ds->max_hp=max_hp;
ds->cur_hp=GetHP()-itembonuses->HP;
ds->cur_hp=ds->cur_hp>30000?30000:ds->cur_hp;
}
else
{
ds->cur_hp=IsNPC()?(sint32)GetHPRatio():cur_hp;
ds->max_hp=100;
}
}

Replacing the former lines in the mob.cpp doesnt work for me.


For the LD fix on player death i would like to see the one-liner that fixes the problem. Thanks

[Badil]

For the LD fix on player death i would like to see the one-liner that fixes the problem. Thanks

I think the fix in the second post in this thread is what is being referred to.

http://www.eqemulator.net/forums/viewtopic.php?t=11922

[grues0me]

Thanks! Gonna try that immediately.

Edit: I must be doing something wrong. Wether pasting the LD fix to the attack.cpp nor pasting the health bar fix to mob.cpp doesnt change ANYTHING. Is there anything else to be done after editing the files? What can be done wrong???

[Badil]

Edit: I must be doing something wrong. Wether pasting the LD fix to the attack.cpp nor pasting the health bar fix to mob.cpp doesnt change ANYTHING. Is there anything else to be done after editing the files? What can be done wrong???

You need to recompile the source code with either VS .NET/VC++ under Windows, or with GCC under Linux ...

[Shawn319]

Thanks that fix did it. My only problems now are now:

1. Health bars of the mob never decrease. I copied the fix i found on the board into the mob.cpp, no help.

2. The crash-on-dying bug. I realize it must have to do with the 400 damage some mob do, but i didnt found a fix on the board, except the global change of the max damage via sql commands.

Both these problems are fixed in 0.5.3-DR2(3)+ (the new ones).

you can get a pre-release version from http://Shawn319.eqemulator.net/cvs

[grues0me]

Thanks!

[MacLeod]

Where is this classfix.sql? It's been a while since I visited the warrior trainers in Halas, but I think the ones I'm r-clicking on are supposed to be trainers...

[Lurker_005]

Second page of this thread. http://www.eqemulator.net/forums/viewtopic.php?t=11889&postdays=0&postorder=asc&sta rt=22 for the exact post.

[MacLeod]

Don't know how I missed it, I've read through this whole thread 3-4 times. I gotta get these reading skills up. :lol: Thank you.

[Lurker_005]

Takes a good 5 times thru this thread to get one reading skill up! Dang slow skill improvments! ;)

[MacLeod]

Is there a fix yet for more than one mob spawning on one spot? The second mob is generally the wrong type as well.

Like in EverFrost, where there should be one Ice Giant, there are 2 Ice Giants except one has the Cyclops model. It is like this at all three Ice Giant spawn points in Everfrost.

And then in Vox's chamber, there are more Cyclops hanging out sharing a spawn point with other Giants.

And in Blackburrow, there are splitpaw gnolls running around but with Blackburrow gnoll names.

I tried the duplicate spawn removal and that didn't fix this. Is there any easy way to fix this?

*EDIT*
Forgot to mention, it looked like there were 2 groups of spawns in the DB so I tried deleting the spawns from spawn2 where their ids were very high since there seemed to be a good dividing line but it also got rid of some of the spawns that were supposed to be there.

Just looking at this it would be possible to order by x, y, and z coordinates and just delete one of the spawns if it resides on approximately the same point although that would take a while. I don't know sql that well, so if anyone has any suggestions. :)

[a_Guest03]

These are database problems and not code problems. If it's wrong, nobody has cleaned up that section of the database in the copy that you have.

[MacLeod]

Yeah, I know they are database problems. But I have the version linked on the first post of this thread, which should be the newest.

Just wondering if anyone else has these problems, and if they found an easier way to fix it than going through and manually deleting any mobs that were in almost the same x,y,z coordinates.

[j_illmatik]

is this query built to delete anything other than the duplicate spawns? If so, hold off.. the My World team is tackling this problem (and has been for a few days) already.

When we merged data from another worldbuilder's sources, the transition wasn't entirely seemless =P but we're cleaning it up now. The id #s for the duplicate npcs are far too high, and contain strange lootdrop entries. Next DB release should contain a fix to this problem, but don't quote me. Sometimes we release the DBs before they're ready just to satisfy worldbuilders and serverops.. although I'm pretty sure this is the basis of our next release :D


Looks like they are aware and working on this one already.

[masticism]

I did this mainly to get rid of the duplicate NPC's in paineel (they were starting to annoy me). They may be there for a reason but until the "proper" update gets finished, this will at least get rid of them. No gaurantees though. There is most likely intentional duplicates that are being removed as well...


Adapted from molly's SQL:

1) First, verify that there are duplicates that we want removed:

SELECT
`spawn2`.`x`,
`spawn2`.`y`,
`spawn2`.`z`,
`spawn2`.`zone`,
`npc_types`.`name`,
count(`spawn2`.`id`) AS `SpawnCount`
FROM
`npc_types`
INNER JOIN `spawnentry` ON (`npc_types`.`id` = `spawnentry`.`npcID`)
INNER JOIN `spawn2` ON (`spawnentry`.`spawngroupID` = `spawn2`.`spawngroupID`)
WHERE
spawn2.pathgrid = 0
GROUP BY
`spawn2`.`x`,
`spawn2`.`y`,
`spawn2`.`z`,
`spawn2`.`zone`
HAVING
(`SpawnCount` = 2)
ORDER BY
`spawn2`.`zone`,
`spawn2`.`x`,
`spawn2`.`y`,
`spawn2`.`z`
2) Next, for the deletion, we just reuse pieces from above:

SELECT CONCAT('delete from spawn2 WHERE id = ', max(spawn2.id),';DELETE FROM spawnentry WHERE spawngroupID = ', max(spawn2.spawngroupID),';') Query
FROM
`npc_types`
INNER JOIN `spawnentry` ON (`npc_types`.`id` = `spawnentry`.`npcID`)
INNER JOIN `spawn2` ON (`spawnentry`.`spawngroupID` = `spawn2`.`spawngroupID`)
WHERE
spawn2.pathgrid = 0
GROUP BY
`spawn2`.`x`,
`spawn2`.`y`,
`spawn2`.`z`,
`spawn2`.`zone`
HAVING
COUNT(spawn2.id) =2
ORDER BY
`spawn2`.`zone`,
`spawn2`.`x`,
`spawn2`.`y`,
`spawn2`.`z`
3) This will give several SQL statements that will delete the highest ID of the duplicate entry.

Again no gaurantees on this, use at your own risk etc.

Thanks to molly for the great inspiration for this!!!

[MacLeod]

Does this actually delete entries? It didn't say any rows were affected. I'd figure it out but my knowledge of SQL is limited, so the code is a bit too complicated.

Also, part of the problem is that many of these double spawns are offset in either of the x,y, or z planes by a couple units instead of on the same spot exactly.

[Kroeg]

There's nothing to figure out... it's the product of 2 DB's sloppily merged together, nothing more.. nothing deeper.

The npcs with the higher range are incorrect (duped) and the ones with the lower range and no lootdrop are the ones that were originally there (and thusly correspond to quest files).

[masticism]

The first step I listed above just generates the results of duplicate entries according to zone, x, y, z entries with the count of duplicates equal to 2. I skipped over all entries that have more than 2 duplicates because I figured these spawns were intentionally there as part of a group. There are other duplicates that are offset by small amounts of x, y or z values. This SQL statement will not handle these.

The second step creates a list of short DELELTE sql queries. The second query when run will not effect any rows directly. You need to take the resulting list of individual queries and run them to actually delete the rows.

I didn't test anything about Loot Drops for the spawns that are left after running these queries. As I said, I was mainly targeting duplicate merchants, gaurds etc. in paineel and this is only meant as something temporary until the real fix comes.

[MacLeod]

Well, it shouldn't affect drops because only the lower numbers will have any drops.

Thanks for the temp fix in any case.

[Monrezz]

SELECT CONCAT('delete from spawn2 WHERE id = ', max(spawn2.id),'; DELETE FROM spawnentry WHERE spawngroupID = ', max(spawn2.spawngroupID),';') Query
FROM spawn2, spawnentry, npc_types
WHERE spawn2.spawngroupID = spawnentry.spawngroupID
AND spawnentry.npcID = npc_types.ID
AND spawn2.pathgrid = 0
GROUP by zone, x, y, z, npc_types.name
HAVING COUNT(spawn2.id) > 1
ORDER BY zone, x, y, z, npc_types.name

The above gives a huge list of deletes...but it doens't do them. Do you have to manually type them into mySQL?

If so, can't you dump the results into an sql file that you could source? There are 1200 results from that query :/

Thanks,

Monrezz

[farce]

bah didnt work...

[mollymillions]

If so, can't you dump the results into an sql file that you could source?
Yes that's the idea, redirect the output of the query to a file and then source it. Be warned that i am not an expert at setting up spawns and there may be fundamental flaws with this query? Also its only designed to delete spawns that have the same name and and spawn in the same zone at exactly the same location. A lot of the replicated spawns have '_00' suffixed to the NPC name - the query is not matching these (but it could i.e. GROUP by zone, x, y, z, REPLACE(npc_types.name,'_00','') or similar).

[Monrezz]

How do you redirect the output of the query?

I'm not worried about having a few spawns removed by it, if it means it gets rid of most dupe spawns. If it removed anyone too important I can add them in by hand later. Simply changing to:

SELECT CONCAT('delete from spawn2 WHERE id = ', max(spawn2.id),'; DELETE FROM spawnentry WHERE spawngroupID = ', max(spawn2.spawngroupID),';') Query
FROM spawn2, spawnentry, npc_types
WHERE spawn2.spawngroupID = spawnentry.spawngroupID
AND spawnentry.npcID = npc_types.ID
AND spawn2.pathgrid = 0
GROUP by zone, x, y, z, REPLACE(npc_types.name,'_00','')
HAVING COUNT(spawn2.id) > 1
ORDER BY zone, x, y, z, npc_types.name

...will do it (after sourcing)?

Thanks,

Monrezz

[mollymillions]

Regarding removing all the duplicated spawns, this depends on how you define a duplicated spawn. If you define a duplicated spawn as any that spawn within close vicinity of another then it won't (this is possible but probably not desirable). It only removes spawns that have the same name and spawn in the same zone at exactly the same location.

That query will probably run as is, you will have to test it (i am not very familiar with how MYSQL will handle this, my experience is really limited to Oracle and MSSQL DBMS's). I may even be wrong about the '00' NPC's altogether, i hav'nt really looked that closely, so you should check this first (also including the underscore was misinformation, just use '00').

Check the help that comes with MySQL or the tool you are using to find out how to dump query output to a file (I have been payed out for giving half-assed support in the past, so i will leave it to the offical helpers to give you the low down).

The world building team had asked that we (me?) hold off on posting these types of mods, personally I would just wait for the next DB release. Good luck anyway.

[Monrezz]

I'm not worried about it removing things it shouldn't, I can put all the important ones in by hand.

Could you please tell me how to get it to dump the query into an sql file though, please? had a look on www.mysql.com can't find out how...

Thanks,

Monrezz

[masticism]

Because this groups by only zone, x, y and z (i.e. not name) it should handle any duplicates that have different trailing names. For example, bat00 and bat01 if at the same zone, x, y and z position will have bat01 deleted (assuming it has the higher id number).

I've run this query and deleted a large portion of the duplicates. As I said in the original post, I only targeted duplicates that are equal to 2. I found alot of instances where there were large groups (> 2) in the same location and I figured these were there for a reason.

There are still other dupes that weren't caught by this however mainly because of the fact (as mollymillions mentioned) that there are duplicated entries with small offsets in the x, y and z directions.

I did this as a temporary fiix until the update comes out. At that time I plan to dump my entire database and source in the new one. So if this deletes anything extra I don't mind at this point.

As far as getting the results of a query to dump to a file, it all depends on which mysql client you are using. I'm using a commercial one that shows me the results in its own window and lets me run the query directly from there.

[MacLeod]

If you use Mysql-Front, once the results come up right click on them and click copy as CSV data and then paste them into the query and run it.