Further DB Cleaning of Duplicates
 

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:

2) Next, for the deletion, we just reuse pieces from above:

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!!!

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.

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

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.

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

Thanks for the temp fix in any case.

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

bah didnt work...

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

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

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.

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

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.

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.