I messed up my DB...

[bodi]

I accidentally deleted all the NPCs in qeynos from spawnentry, spawn2, and the other one. My server mobs are all very tailored at this point. How would I re-source the mobs/npcs in that zone only without it replacing all the rest of the world's mobs? Thanks.

[chrsschb]

If you have backups: Pull the data specific to qeynos from those tables and source it in.

[bodi]

how? I have a vanilla version of the database, what sql command would you use

[Uleat]

I don't have the exact expertise on how to do this, but there are a few ways depending on your knowledge of mysql and databases.

Before you start anything, make a backup of your original database in case something happens.

If it were me, I'd have another computer setup with mysql and then source in a new peq database.

In this new database, delete all other peq tables but the ones you need info from. Then, delete all other entries in those tables but the ones you need. This should leave you with only the information you're looking for.

I can't remember which programs let you create a source file, but you should be able to create a .sql file that can be used to update your original database.

-- \/ or \/ --

Try out-sourcing only the tables you need and then editing that file to remove everything but zone mobs/npcs that you need.

-- /\ /\ --

If you make it this far, on that second computer create a second database called test, or whatever you choose. Source in another new database and delete the information that was lost in the original one. This will give you a copy on which to test this source file.

'Use' the test database and source in this new file. Check to see if the source took properly without corrupting the other data.

If this worked, you might consider creating a temp eqemu server on this second computer and testing the database validity before trying it on your original.



I can't say if this will work, but these are the steps I would look at taking if I had this problem. Database versions could be a problem if you're using an older database.

Hopefully someone else can help to shed some light on this.

(Grabbing info out of the original peqdb.sql might work as well, but I don't have the patience to search through that much info.)

[bodi]

thanks for putting in the time to write that out. I appreciate it. I'll work on it thursday and let you know how it turned out.

[Uleat]

Ok..here's a better start.

I use HeidiSQL, but I imagine Navicat has a similar feature.

Open Notepad directly, don't create a new text file and open it.

Click save and name this something like MobFix.sql - anything is fine as long as you use the .sql extension

(I'll speak from using HeidiSQL since that's what I'm working from.)

Goto the first table in the backup database that you want info from. Select all of the affected rows that you want to copy. Usually click the first, then scrolling down to the last and then, holding <shift>, click again, will select the entire series between. You may have to sort by the 'zone' field so the data is continuous.

Richt-click somewhere inside the selected region and select -> Copy Data -> Copy selected rows as SQL.

This will copy all of the selected data to the clipboard parsed into an SQL format.

Now, paste this into the Notepad file that you created. Press <enter> twice to put a separator in.

Now, repeat this for the other tables that you want info from..don't forget the spacer lines between pastes.

Once you get all of the tables' information, save it, and make a backup copy because we're going to make some changes to it and the back-up will save us if we make a mistake that we can't fix.

This should be all of the needed info. Before you source this in, there are a lot of changes that need to be made.

If you notice, the beginning of each statement begins with 'INSERT.' If you insert this into your game database, you will either create an entire new row of duplicate entries (possibly messing up any key-references), or you will get an error saying that the row already exists.

We're going to have to change this for each row, and for each table. Don't fret though, Notepad is simple, but its replace function works just fine.

I have to log for the night..got a storm coming in... If anyone can help on changing this from an insert to an update, please feel free to do so. Here's an example of a 'Spawn2' entry:

INSERT INTO `spawn2` (`id`, `spawngroupID`, `zone`, `version`, `x`, `y`, `z`, `heading`, `respawntime`, `variance`, `pathgrid`, `_condition`, `cond_value`, `enabled`, `animation`) VALUES (1, 541, 'crushbone', 0, 387.000000, 3.000000, -13.750000, 158.000000, 640, 64, 0, 0, 1, 1, 0);

We need a replace 'this' with 'that' type fix so the entire file can be easily updated and used to fix a zone's mob/npc tables. (i.e., replace 'INSERT' with 'UPDATE', though that won't work.)

Thanks!