4.3/4.4dr1 compatible DB addon (1.1 beta 5) - finally!
 

(edited to fix URLs and add items link)
http://www.edwardpinniger.bctalk.net...ta1.1beta5.zip
This version requires the 4.4dr1 binaries which you can download from http://www.edwardpinniger.bctalk.net/eq/44dr1.zip
(the zip includes an up-to-date db.sql)
also an updated items DB - http://www.edwardpinniger.bctalk.net/eq/itemsbooks.zip (edit: added books to zip file)
It'll probably work OK with 4.3 but tradeskills definitely won't work, as
the tradeskill table structure has been changed in 4.4, and new characters won't have any starting items.

Improvements in this version:
Compatible with 4.3 (new faction structure, NPC spell lists etc)
Fixed + added spawns for all Kunark outdoor zones
Working zonepoints and safepoints for most Norrath zones (no Luclin or PoP ATM)
More loot tables
More tradeskill recipes, including many created by Lurker_005 - all fletching and jewelcraft recipes, and most alchemy ones.
Merchants in Cabilis (and other Kunark zones) are now assigned the correct lists
Visible equipment for NPCs, guildmasters etc. (incomplete)
Included Lurker's up-to-date starting_items data (until I get round to creating my own)

Hopefully, the next version will be 1.1 final and will be as finished as possible - it should
include proper spawn locs and itemisation for most old-world and Kunark dungeons, finished tradeskills
(apart from PoP recipes), loot tables for all basic creature types, and finished merchant lists for tradeskill and
spell vendors. Also hopefully a full faction list, and the ability to raise factions by killing orcs and similar creaures.
So it might take me a while to finish, it'll be "done when it's done".
However if a future version of EQEmu "breaks" this DB addon version by changing the DB table structs, before I finish the
final version, I'll release another beta compatible with the new EQEmu version.

Thank you !
 

Let me be the first to say thank you ; )

Gonna check it out and give you some feedback later this evening.

Greetz DazL

wow this is indeed excellent! I will try this out as well tonite.

As a pre-caution to dissaster, what is the best recommended way to back up everything before attempting to source in new db's?

Id like to make sure all the accounts/characters of players are safe etc before doing this. Is there a sure-safe methode in mysql to save this information?

thx again for all the hard work!

Great work Drawde! I'll check this out ASAP.

BTW, you got your slashes mixed up in the links you gave above. ;) The corrected (clickable) links are:

http://www.edwardpinniger.bctalk.net...ta1.1beta5.zip
http://www.edwardpinniger.bctalk.net/eq/44dr1.zip

dangerdan30: To back up/restore your old database, do the following:

great! thanks alot for explaining how to make a back up of the files. Yes, my database is just named: eq

so by following your steps, this should go fine.

thanks again

Awesome job Drawde..

There are a couple of problems in faction_list and loot tables though.

use a mysql -f eq < Reload to source the database in, and it will move on other the bugs (dupe primary keys for the most part).

Hmm, the backup down't work for me.

My database name is: eq

So I type in "mysqldump eq > eqbackup.sql" (no "'s)...but then I get a new line with a "-->"

Then no commands work. (exit, use eq etc....)

And I have to exit...help please!

monrezz: Are you trying to run the command from mysql.exe? If so, there's your problem. As stated...

Don't start up mysql.exe. This uses a different command file (mysqldump.exe).

Oh yes - thank you very much! Didn't notice that :)

I am getting a ton of errors

Error 1136: Column count does not match value count at row 1.

This is when I am reloading over telmets database.

Same happening to me :(

Perhaps you should take to telmet... He is the one that made his database after all...

My guess is you havent updated your db.sql in awhile. I'd completely drop my database and source the db.sql, then load in drawdes addon. (you will still need an items addon).

A very newbie question, what are the steps to updating/loading the worlddata1.1beata5.zip files.

What happened to the items? there's no items.sql (or .txt) file in the database zip. Tried to source in someone elses but just got a bunch of errors

Item.sql
 

Heyas,

I like what Ive seen so far;) Gonna see some more tomorrow. Great job !

I had a problem sourcing in telmets items.sql.
Ik keep getting the following error. Tried telmets items.sql.

1136 Collum count doesnt match...at row one.

Anyone got any advise?

Greetz DazL

Not it makes any difference to use regular end users, but what I think the problem is is that since drawde uses his own db.sql it isn't compatible with others items.sql, or doesn't even have the columns for it. Because of this I can't load in any items. Would like to know what Trumpcard is doing because, according to the main page, he's using drawde's newest db

request
 

Maybe drawde can make a dump of his whole database and release it as db_drawde.sql
May resolve most issues plus would make sense to use the db that he uses for this addon.

- Also does anyone else get errors on loading the db.sql
and then errors after when loading drawdes?
I'm doing this on a brand new database with nothing in it.
Sourcing the db.sql from 4.4dr1 then adding drawdes.

You need to load your items, compare the db.sql you should be using with whats in the items database, and update the table to make items work with the new format.

the updated items field had a minstatus tinyint(3) unsigned field added between the last release and this one. You need to update your items to include this one...

alter table items add column minstatus tinyint(3) unsigned default '0' after raw_data;

Thanks for the tip Trump. Can you please confirm that Im doing this right?

CREATE TABLE items (
id int(11) NOT NULL default '0',
raw_data blob,
minstatus tinyint(3) unsigned default '0' after raw_data;
PRIMARY KEY (id)
) TYPE=MyISAM;

Thnks in advance

Hmm I put in the line:

minstatus tinyint(3) unsigned default '0' after raw_data;

into my db.sql, but Im still getting the error: 1136 Collum count doesnt match...at row one.

Im running 4.3 EQEmu with 4.4 DB.

I think what Trump means is that you need to use the old items table definition to load telmet's item.db first. Then run the alter table command. Your problem is that your item table's definition has the extra new column in it that your item.sql does not. Get it? It worked fine for me when I loaded the telmet's items.sql on the the old definition first and ran the alter table..

Hope it helps.

Ack i installed it now i dont have any spawns
 

i installed this add on and my spawns all went away :cry: what did i do? i got a back up of my db but would like to fix this one so that i can see all the neat new stuff done in the add on :D any ideas what i might have done wrong?

Sorry about the lack of items etc. The thing is that this is a DB ADDON, not a full DB, you have to source the basic db.sql, then items, doors, books etc. before loading my addon.

You can download an items DB compatible with the 4.4dr1 code (there was an extra column, minstatus, added to the items table in 4.4dr1) here:
http://www.edwardpinniger.bctalk.net/eq/items_44dr1.zip

About the errors in the faction table, I'll try and track those down ASAP.

Anyone know all that's missing? So far I've just noticed doors, books, and magelo. Also, I only know that magelo contains some items, no clue what it is other then that. But it won't source in properly anyway. (same reason items wouldn't i'm guessing)

mmm
 

You are doing a great job drawde...
but... let me give you an advice...
when you are about to release a db, plz... try to load db.sql into a new db, and then your addon...
there are so many errors in db.sql in your 4.4dr1, and a lot more in your addon (factions_list, spawn, and more)

if you fix them ONE time only, each of us will not have to fix them for EVERY release...

your db.sql simply don't load because of a MUCH different
'tradeskillrecipe' definition, from the insert statement.

and your addon has duplicate keys, wrong insert ( ,, in the statement, wrong numbers of columns...)
maybe you can simply do a dump of your running db and do a cut/paste in the txt files...

anyway, thanks for your work :)

I think the better solution is that next time the database be kept internally until the next emulator release, in which a new db.sql is supplied.

Drawde did this as a favor to get people up and running with the new faction templates. A little work on the part of the users might be required, especially since he was basing the database off CVS code that I sent him.

The updated CVS db.sql is updated frequently on sourceforges CVS, its just a matter of walking over there and downloading it, I personally dont feel Drawde needs to be responsible for testing his database against everyone elses goofy db design. He specificlly stated it had an updated trade skill struct.

I don't understand what can be wrong with his db.sql, it just sounds to me like you didnt know what to do with it. The db.sql does not go over the top of an existing database. If you didnt create a new database, and tried to lay in the new db.sql over the old one, no wonder you got a ton of errors. Thats a user error, not a problem in anything Drawde did. If you had dropped your original database, created a new one, source in the new db.sql, then applied the addon, you would have gotten nothing more than a couple of bugs in a couple of sql files (mostly dupe errors), which will be ignore with a mysql -f insert.


Just my 2 cents.

Great
 

Heyas,

Thanks for the items.sql. This database is very enjoyable to play. Im gonna play a lvl 20 char for some hours this evening and give you some feedback.

Great work drawde!

Greetz DazL

I'm inclined to agree with Trumpcard here. Drawde is putting his own time in on creating these database add-ons for the sake of the community, solely on a volunteer basis (as are all developers involved in EQEmu). Complaining to him to do more work for end-users may only serve to discourage him in making frequent updates in the future. Personally, I feel that a little footwork on the part of those who wish to use these files isn't too much to ask. There's plenty of documentation in both MySQL & EQEmu to go around, & the forums here where just about every zany question under the sun has been asked & answered at least once. Then of course there's plain old common sense. ;)

For the record, I encountered a ton of errors myself initially. Here's what I did to get it working properly:

• 1) Backed up my old database that was working (always a first step for any wise user)
  2) Dropped the current database
  3) Sourced the updated db.sql in
  4) Sourced the various add-ons from Telmet in
  5) Applied Trumpcard's fix (see above)
  6) Sourced in the items_44dr1.txt Drawde links to (above)
  7) Sourced in Drawde's add-ons

I still got some errors, but I haven't noticed any impact this has on anything in the game, so I'm not too worried about it. If I find something that isn't quite right, I'll look into fixing it myself.

You've done some great work Drawde! Thank you for your excellent add-ons & keep up the good work! :)

The latest db.sql (with the updated tradeskill table, etc.) is in the 4.4dr1 zip
which I posted a link to along with the link to the DB addon.
If you use this as a base for loading my DB addon, the only errors you should get are
genuine errors in my data - e.g some of the factions have the wrong number of columns (I'll
fix this when I can). Another thing that causes error messages is multiple tradeskill recipes
with the same product (e.g blocks of ore can be made either by melting down smaller ones or breaking
up larger ones with a chisel). This isn't supported in EQEmu at the minute (product is a primary
key in tradeskillrecipe) but I put the recipes in anyway, for when they are supported.

Another thing, the reason all the files are .txt rather than .sql is that the text editor I generally
use (EditPad) tends to lock up when loading very large .sql files (presumably it can't cope with all the
parsing and formatting), so I just make them text files to get around this.

adding items...To Mandulum
 

to add the items, download the items DB from Drawde's orig post. Copy the items_44dr1.txt file to mysql/bin. From MySql type source items_44dr1.txt. that's it...

Just finished loading it up, and what can I say....

Excellent work Drawde!!

Just wanted to let you know all your hard work is appreciated :D

Drawde addon
 

I know perfectly what to do with db.sql.. i created the db, i did a "mysql eq <db.sql" and i got errors (in tradeskillrecipes)

i fixed them, dropped created and imported again db.sql, and was ok.

But sourgin reloaddb.sql, i got MANY errors, most of them where duplicate keys (in many tables) that i double checked looking into the .txt files (many). And some others errors where of wrong column count in some insert statements (when i got an error of this type, i formatted the insert that was givin the error, and the insert before that like in a excell sheet... and i saw that the row giving error had some more or less columns than the one before).

I don't know if you guys are getting duplicate keys from the .txt files, but THEY ARE THERE...
do you want an example?

so...
from the db.sql in the 44dr1.zip


<quote>
CREATE TABLE tradeskillrecipe (
id int(11) NOT NULL auto_increment,
tradeskill smallint(6) NOT NULL default '0',
skillneeded smallint(6) NOT NULL default '0',
trivial smallint(6) NOT NULL default '0',
product smallint(6) NOT NULL default '0',
product2 smallint(6) NOT NULL default '0',
failproduct smallint(6) NOT NULL default '0',
productcount smallint(6) NOT NULL default '0',
i1 smallint(6) NOT NULL default '0',
i2 smallint(6) NOT NULL default '0',
i3 smallint(6) NOT NULL default '0',
i4 smallint(6) NOT NULL default '0',
i5 smallint(6) NOT NULL default '0',
i6 smallint(6) NOT NULL default '0',
i7 smallint(6) NOT NULL default '0',
i8 smallint(6) NOT NULL default '0',
i9 smallint(6) NOT NULL default '0',
i10 smallint(6) NOT NULL default '0',
notes text,
PRIMARY KEY (id),
UNIQUE KEY id (id),
UNIQUE KEY name (product)
) TYPE=MyISAM;

--
-- Dumping data for table 'tradeskillrecipe'
--


INSERT INTO tradeskillrecipe VALUES (1,68,14,16600,16500,10015,0,0,0,0,0,0,0,0);
INSERT INTO tradeskillrecipe VALUES (2,68,14,14600,16504,10015,0,0,0,0,0,0,0,0);
INSERT INTO tradeskillrecipe VALUES (3,68,30,14628,16504,10020,0,0,0,0,0,0,0,0);
INSERT INTO tradeskillrecipe VALUES (4,68,30,16656,16500,10020,0,0,0,0,0,0,0,0);
</quote>

do the insert have the same number of columns than the create table?

in the loot.txt, one example:
<quote>
INSERT INTO lootdrop VALUES (427,'InvisibleMan');
INSERT INTO lootdrop VALUES (427,'InvisibleManCaster');
</quote>

that is a duplicate entry... there are many...

same, for example, in merchantlist.txt
<quote>
INSERT INTO merchantlist VALUES (5750,12,11611); # warhammer of divine grace
INSERT INTO merchantlist VALUES (5750,13,3737); # breastplate of the holy rite
</quote>

then, a mismatch from db.sql and starting_items.txt

db.sql
<quote>
CREATE TABLE starting_items (
id int(11) unsigned NOT NULL auto_increment,
race int(11) NOT NULL default '0',
class int(11) NOT NULL default '0',
itemid int(11) NOT NULL default '0',
gm tinyint(1) NOT NULL default '0',
PRIMARY KEY (id,race)
) TYPE=MyISAM;
</quote>
and starting_items.txt
<quote>
INSERT IGNORE INTO starting_items (race, class, deityid, zoneid, itemid, item_charges) VALUES
</quote>

so, i'm not stating that drawde is doing a bad work... never want to say that..
i'm only saying that many of those errors where present a long time ago, and are still here.

if drawde try to source those files himself, he can find and fix those problems (duplicate keys and wrong column count) in less than a hour, so nobody (including himself) will have to think about that anymore.

If drawde want, i can make those fix myseld and pass the fixes to him... the only problem is that i can change keys, or add columns, but i'll be never sure if my fixes are correct (wrong values added?)

Sorry for my long posting and for my bad english.
And thanks to drawde for his work (even if i yet have to see his work running on my machine).

Dump of Drawde's DB - complete, just source and go.
 

I loaded up Drawde's DB ( excellent work IMHO ), then dumped it to a single sql file that I then zipped to attatch here.
Should be very simple, unzip into mysql bin folder, then...
create a fresh DB and source, includes everything , shouldn't be any errors.
Use with EqEmu 4.4DR1 for best results.

updated DB with missing items and NPC magery turned on and the EXE's I'm using with it here-
http://forums.eqemu.net/viewtopic.php?t=6717

Great work Dawde, I love your db. It's very complete and fun to play in. Please ignore the whiners, I agree with Trump, the users should do a little leg work, it doesn't hurt. Hey who knows, if all the users learned sql a little more, maybe they can use their skills to get a job later.. hmm? Plus, they should read all the threads fully before posting.

My only problem with the response is that it came across as 'why dont you check your work and make sure ALL errors are corrected before you post something'

Drawde specifically mentioned, as I did that there were a couple of bugs, but other than that it worked great....

The problem seems to be that the wrong db.sql is being used.
Its really not his job to go and put everything together for you and make a full release, if you want to use his BETA database, then you should go to CVS on sourceforge and download the latest db.sql file, rather than complaining about the one he provided. CVS is freely available to everyone, so use it. Otherwise, we'll just cut CVS updates off completely, and not make anything available until a stable version is released so we dont have people whining about not being able to attatch an updated part A to an updated part B because they expect everything to be done for them. When I set up the Sourceforge CVS updates, thats exactly what I was afraid of in the 1st place.

As Drawde notes in his version, it's beta, and no offense, but he's been releasing them for well over a year now, and has always done a smash up job, so the 'why dont you test it' tone kind of got under my skin'

He did offer to help fix the errors in a later post, which I think would have been more apprioate in the first post. Messages of this sort are appreciated..

'Hey, noticed a bug, you need to modify X to Y and that will correct it, in fact, I modified it and pasted it here !'

as opposed to

'Hey, noticed a bug.. We dont you fix these things before you release them?'

Like I said, I'm sure that's not what was meant, but the tone of the message came across as rude.

It wasn't my intention... my problem is that i don't know english enough to explain what i think, in the way i think it.

I was only stating that if he, or me, or someone else, is going to fix those duplicate keys one time, we will not get those errors in the future. At the moment, every person that want to use the db has to fix it himself.

And there are some ppl that have no sql know how.

Drawde said that some of the duplicate entries are there on purpose cuz he's waiting for the engine support that allows the same item to be created via different trade recipes. He also said that for the final release, he will make the db as complete and error free as possible. I also personally don't see how the duplicate entries really effect any of the end users. So take it easy, go load up the db, fire up the emu and enjoy the game, and wait for Drawde's final release..... I know I'm looking forwards to it. Peace...

Its not a problem, don't worry about it a bit. We're just a big, happy global community around here :-) Except for the french...
We hate the french... Paglioni sounds Italian to me...

Thanks
 

Thanks a lot drawde and all db builder :p nice work!

That's true.. i'm italian... and we too hate the french.. or at least I hate them... they tend to think the are better than anyone else... :)