SQL Statements for DB Fixes

Monrezz · #1 02-01-2004, 06:38 AM

Compiled a little SQL together containing a few useful SQL Statements people have come up with. Simply source this into your database and it will:

Give all mobs with instant respawn a respawn time of 1000;
Give most newbie mobs loot appropriate to their race (rats drop rat items, skeletons drop bone chips etc);
Fix the damage for all mobs (no more level 1 mobs hitting for 400 etc);
Fix the HP for all mobs (no more level 24 mobs with 2k HP etc);
Fix the aggro radius so lower levels have smaller radius;
Fix most classes (no more merchants where there should be a GM);
Fix all starting items;
Fix all zone points;

Note - Most queries will only affect mobs with either "a_" or "an_" in their name to prevent customs and boss mobs from being affected, however some custom data might still be changed. Better to create a backup so if this does mess things up you can restore.

You can download the file from:
www.arnold11.karoo.net/Fixes.zip
(Any future updates will be announced here but keep same URL)

If it messes everything up, don't blame me. Just trying to help.

Good luck, Monrezz.

Squiffy · #2 02-01-2004, 01:33 PM

Not sure on that damage fix...

I mean, I haven't tested the database fully, but I imagine at least some mobs are accurate, and to have all level 60 mobs hit for 200 is kinda iffy.

I went with a more selective fix. Namely, a ton of mobs had been set for maxdmg=400, regardless of level. These were the majority of off-balanced mobs. (PC recondites, some skeletons, etc)

So instead of:

Code:

UPDATE npc_types SET maxdmg=200 WHERE level=60;

use

Code:

UPDATE npc_types SET maxdmg=200 WHERE level=60 and maxdmg=400;

Check this thread: HERE (About halfway down on the first page)

Good compilation otherwise, though. Only thing I see is that it will unbalance any pre-PoP raid zone trash mobs (ie Temple of Veeshan, Sleeper's Tomb, Veeshan's Peak) which tend to hit harder than your average level 60ish mob.

Small steps though, I guess

Monrezz · #3 02-03-2004, 06:04 AM

Okay new version uploaded (same URL) and first post updated.

The new version will affect much less custom/boss mobs (most queries only affect mobs named either "a_" or "an_").

There is also a loot table for skeletons up to level 32, so they drop bonechips, a loottable added for bats up to level 16 so they drop batwings, a loottable added for snakes up to level 16 so they drop snake stuff, a loottable added for rats up to level 16 so they drop rat stuff, and a number of other small tweaks and additions to the queries. I removed the major loot additions as they were just too broad.

In the zip is an sql file, all you need to do is source it. And you can look it it and remove anything you don't want to be affected...

Hope this helps,
Monrezz

boredofthesane · #4 02-04-2004, 09:38 AM

I tried your sql update, it did not work... Perhaps there is an error?

Monrezz · #5 02-04-2004, 10:58 AM

What "didn't work"? Was it some of the fixes mentioned, or it wouldn't source?

I've tested with MW and it works fine for me, using the 5.3-DR3.

Monrezz

Monrezz · #6 02-10-2004, 06:38 AM

Just updated the damage fixes - it now uses values calculated by the formula hardcoded. See attack.cpp for the formulas. Here's the SQL to source:

UPDATE npc_types SET maxdmg=2, mindmg=1 WHERE level=1 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=4, mindmg=1 WHERE level=2 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=6, mindmg=1 WHERE level=3 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=8, mindmg=1 WHERE level=4 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=10, mindmg=1 WHERE level=5 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=12, mindmg=1 WHERE level=6 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=14, mindmg=1 WHERE level=7 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=16, mindmg=1 WHERE level=8 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=18, mindmg=1 WHERE level=9 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=20, mindmg=1 WHERE level=10 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=22, mindmg=1 WHERE level=11 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=24, mindmg=1 WHERE level=12 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=26, mindmg=1 WHERE level=13 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=28, mindmg=1 WHERE level=14 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=30, mindmg=1 WHERE level=15 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=32, mindmg=1 WHERE level=16 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=34, mindmg=1 WHERE level=17 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=36, mindmg=1 WHERE level=18 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=38, mindmg=1 WHERE level=19 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=40, mindmg=1 WHERE level=20 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=42, mindmg=1 WHERE level=21 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=44, mindmg=1 WHERE level=22 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=46, mindmg=1 WHERE level=23 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=48, mindmg=1 WHERE level=24 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=50, mindmg=1 WHERE level=25 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=52, mindmg=1 WHERE level=26 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=54, mindmg=1 WHERE level=27 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=58, mindmg=14 WHERE level=28 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=60, mindmg=15 WHERE level=29 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=62, mindmg=15 WHERE level=30 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=64, mindmg=16 WHERE level=31 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=66, mindmg=16 WHERE level=32 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=68, mindmg=17 WHERE level=33 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=70, mindmg=17 WHERE level=34 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=72, mindmg=18 WHERE level=35 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=74, mindmg=18 WHERE level=36 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=76, mindmg=19 WHERE level=37 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=78, mindmg=19 WHERE level=38 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=80, mindmg=20 WHERE level=39 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=120, mindmg=40 WHERE level=40 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=123, mindmg=41 WHERE level=41 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=126, mindmg=42 WHERE level=42 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=129, mindmg=43 WHERE level=43 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=132, mindmg=44 WHERE level=44 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=135, mindmg=45 WHERE level=45 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=138, mindmg=46 WHERE level=46 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=141, mindmg=47 WHERE level=47 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=144, mindmg=48 WHERE level=48 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=147, mindmg=49 WHERE level=49 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=150, mindmg=50 WHERE level=50 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=153, mindmg=68 WHERE level=51 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=156, mindmg=69 WHERE level=52 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=159, mindmg=71 WHERE level=53 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=162, mindmg=72 WHERE level=54 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=165, mindmg=73 WHERE level=55 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=168, mindmg=75 WHERE level=56 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=171, mindmg=76 WHERE level=57 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=174, mindmg=77 WHERE level=58 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=177, mindmg=78 WHERE level=59 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=180, mindmg=80 WHERE level=60 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=183, mindmg=81 WHERE level=61 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=186, mindmg=82 WHERE level=62 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=189, mindmg=83 WHERE level=63 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=192, mindmg=85 WHERE level=64 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=195, mindmg=86 WHERE level=65 AND ((name like 'a_%') OR (name like 'an_%'));
UPDATE npc_types SET maxdmg=495, mindmg=220 WHERE level=66;
UPDATE npc_types SET maxdmg=742, mindmg=220 WHERE level=67;
UPDATE npc_types SET maxdmg=990, mindmg=220 WHERE level=68;
UPDATE npc_types SET maxdmg=1237, mindmg=220 WHERE level=69;
UPDATE npc_types SET maxdmg=1485, mindmg=220 WHERE level=70;

Note: I set the damage for ALL mobs level 66-70, as almost all these are boss mobs and would therefore be hitting for about the damage supplied. I calculated each value for maxdmg/mindmg for each level using the hard-coded value that is set for any mob without a max/min damage in the database.

Hope this helps some people - certainly saves a lot of time!

Monrezz