IMPORTANT: Database changes coming!

[Astal]

All but character_house character_house_access character_house_deposit and character_house_spawn say InnoDB, those 4 say MyISAM as the type.

Those 3 tables should be dropped. Not sure if I've released those queries, but if those are the only ones with MyISAM, you're in good shape.


Now to brag just a little. I ran the Consistency Checker on the TessEQ2 DB and have 3 inconsistencies! THREE! I am happy.

tess-clean!.jpg

I've hit a lull in updating the constraints (listed above), but soon as I have a chance to talk with LE about the update process, I'll commit the rest and we can test it out. Meanwhile, just make sure your custom databases are nice and consistent.

Remember (as Zexis pointed out), if it is spawn_npc_equipment, spawn_npc_skills or spawn_npc_spells giving you issues, you have to create BOGUS records with *_list_id = 0 in order to get through this change. For those of you not wanting this headache, just wait til we release 0.7.0 + DB, and your worries will be solved.

All this nightmare is just for those silly people who are already building custom server content while the emulator is in Alpha

Oh really, ok leaving project until it is 100% complete all while complaining on the forums via proxy servers so i cant get banned

I noticed when i do use that tool, it erases all my npc spawns ect.... what good is that LOL

ok it removes the npc spawns, not the npcs themself in the database



Also im getting like 30 OP code errors when i run my server any idea why? It only happened when i updated to 271

[Astal]

Ok i fixed all the consistency errors.

Now im on to the next part and Im still getting errors in certain files, if you need i can write down each query that causes the problem and what it says. This is one of many

Query

Code: Select all

ALTER TABLE `character_language` ADD CONSTRAINT `FK_character_language` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

Error

Code: Select all

[SQL] 
ALTER TABLE `character_language` ADD CONSTRAINT `FK_character_language` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;
[Err] 1005 - Can't create table '.\eq2\#sql-774_e8.frm' (errno: 121)

[John Adams]

All this nightmare is just for those silly people who are already building custom server content while the emulator is in Alpha

Oh really, ok leaving project until it is 100% complete all while complaining on the forums via proxy servers so i cant get banned

K, here's the harsh reality of it. a) if you start being a jerk, you will not get any help from me or my team ever again. b) it has never been advised to build custom content while the database is still changing dramatically, since 1/2 the Emulator systems are not functioning or even designed yet. c) the effort I am extending on behalf of those 3-4 custom servers is gigantic. You think it isn't, try figuring this out yourself. If it were up to me, I'd blast out a completely new DB and let you figure out what parts changed to support it... however, the EQ2Emulator team has always tried to be considerate of it's admins in this respect. So I am trying, and definitely appreciate constructive feedback.

My comment about silly admins was in jest, because this really is stressful trying to get it right, and I have to laugh or I won't do it and you will suffer the consequences. Simple as that.

And you won't get banned for having an opinion. You'll get ass-raped in text by me for coming off like we owe you something


So as far as your character_language problem, there are a few updates that I found have to be made before the ALTER's occur. This is one of them.


Adds the constraint to table `characters`:

Code: Select all

ALTER TABLE `character_language` ADD CONSTRAINT `FK_character_language` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

FIXES an invalid ID data type (you cannot create a FK to different data types):

Code: Select all

ALTER TABLE `character_language` CHANGE `language_id` `language_id` INT(10) UNSIGNED DEFAULT '0' NOT NULL;

Creates an INDEX on the `languages` table to accept the incoming reference:

Code: Select all

ALTER TABLE `languages` ADD INDEX `LanguageIDX` (`language_id`);

and finally, the constraint that is causing you trouble should work:

Code: Select all

ALTER TABLE `character_language` ADD CONSTRAINT `FK_languages` FOREIGN KEY (`language_id`) REFERENCES `languages` (`language_id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

[Arremis]

What's an FK?

[John Adams]

FK = Foreign Key

Basically a key/index reference from outside the current table. Ie., characters.id is a FK of character_details.char_id.

[John Adams]

Ok so I have hacked my way through adding about 160 DB updates over the last few days, and I am ready for some poor fool to test it out.

Before we commit our version.h file (which is what makes this go out to **everyone**), I'd like to hear back from a few server admins who are willing to try the automated thing out. Here's what you have to do, with the current SVN code -

BACKUP YOUR DATABASE!
Please, for god sake... backup.

Run the DB Consistency Checker from Scatman, and CLEAN UP any inconsistencies.


Open ./common/version.h

Change the value

Code: Select all

#define CURRENT_DATABASE_MINORVERSION 138

to:

Code: Select all

#define CURRENT_DATABASE_MINORVERSION 226

This is what tells our database updater what modifications you want to receive. Right now, no one gets the ALTER's listed above, unless you applied them manually because these ALTERs came after version 138.


Compile your world (rebuild completely). Copy the new EQ2World.exe to your server dir and run it.


Here's how I tested it. I started an empty database, downloaded from the eq2emulator.net DB Service. I then wrote queries that copied every scrap of TessEQ2 data into this new, empty table (so there was data). Once that was done, I ran eq2world and received up to version 226 DB updates.

NOT ONE FAILURE out of all those updates!!!

Believe me, no one was more shocked than me.


So if you feel daring, give this a whirl and report back what you find. If you performed a backup BEFORE you ran this experiment, you should have no problems dropping your messed up DB and restoring your backup (psst, I recommend testing DB restore before you assume your backup is good enough...)

Once a few of you tell me it works famously, I'll commit the code and everyone else will get the updates normally.




Oh, and a second test would be to empty your DB completely (brand new) and download everything, including all the ALTER's, into a fresh new DB - make sure that works too, using your modified version.h/EQ2World.exe.

Good luck.

[John Adams]

No one brave enough eh?

I'd hate to have to commit these changes without someone testing them first.

Just sayin.

[Zcoretri]

This test was with a new empty DB...

Code: Select all

[20100115 21:01:14] [Error] Error in updating tables query '
update characters, zones set current_zone_id=zones.id where zones.name=current_z
one': #1146: Table 'eq2_dev.zones' doesn't exist
[20100115 21:01:14] [Error]     Attempting to update database table 'characters'

and the characters table engine came in as MyISAM.

Restarting world updated the table to InnoDB.

[John Adams]

As I explained in IRC, this is a known issue that dates back to 0.6.5 release. Due to some updates to characters that requires the `zones` table to already be in place, there is an error (always has been) updating characters on a fresh DB. LE posted originally that you have to run World twice to get around it.

I am trying to divine a fix for that, now that I understand the process better. But if that's the only error you got (using minor version 226), then we're in great shape.

[John Adams]

Alrighty then, changes are getting committed so the next Public SVN update you make will likely contain all these Constraint changes. The manual option is there for those who have issues.

Worst case, start a new DB and use INSERT INTO ... SELECT queries to move your custom data into the new DB.

The new table_versions values you need when this is all done are attached. Run this Query to get DB Updates to stop trying to push these ALTERs down.

[Zcoretri]

As I explained in IRC, this is a known issue that dates back to 0.6.5 release. Due to some updates to characters that requires the `zones` table to already be in place, there is an error (always has been) updating characters on a fresh DB. LE posted originally that you have to run World twice to get around it.

I am trying to divine a fix for that, now that I understand the process better. But if that's the only error you got (using minor version 226), then we're in great shape.

Cool beans...nice work there JA!

[Astal]

All this nightmare is just for those silly people who are already building custom server content while the emulator is in Alpha

Oh really, ok leaving project until it is 100% complete all while complaining on the forums via proxy servers so i cant get banned

K, here's the harsh reality of it. a) if you start being a jerk, you will not get any help from me or my team ever again. b) it has never been advised to build custom content while the database is still changing dramatically, since 1/2 the Emulator systems are not functioning or even designed yet. c) the effort I am extending on behalf of those 3-4 custom servers is gigantic. You think it isn't, try figuring this out yourself. If it were up to me, I'd blast out a completely new DB and let you figure out what parts changed to support it... however, the EQ2Emulator team has always tried to be considerate of it's admins in this respect. So I am trying, and definitely appreciate constructive feedback.

My comment about silly admins was in jest, because this really is stressful trying to get it right, and I have to laugh or I won't do it and you will suffer the consequences. Simple as that.

And you won't get banned for having an opinion. You'll get ass-raped in text by me for coming off like we owe you something


So as far as your character_language problem, there are a few updates that I found have to be made before the ALTER's occur. This is one of them.


Adds the constraint to table `characters`:

Code: Select all

ALTER TABLE `character_language` ADD CONSTRAINT `FK_character_language` FOREIGN KEY (`char_id`) REFERENCES `characters` (`id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

FIXES an invalid ID data type (you cannot create a FK to different data types):

Code: Select all

ALTER TABLE `character_language` CHANGE `language_id` `language_id` INT(10) UNSIGNED DEFAULT '0' NOT NULL;

Creates an INDEX on the `languages` table to accept the incoming reference:

Code: Select all

ALTER TABLE `languages` ADD INDEX `LanguageIDX` (`language_id`);

and finally, the constraint that is causing you trouble should work:

Code: Select all

ALTER TABLE `character_language` ADD CONSTRAINT `FK_languages` FOREIGN KEY (`language_id`) REFERENCES `languages` (`language_id`) ON DELETE CASCADE  ON UPDATE CASCADE ;

dude wow .... just wow, i was hoping the smiley face showed my sarcasm, i was joking in every respect, just as john wasnt directly insulting me or anything, i never have stated you guys owe anyone anything, i think its great your doing a thing like this and it is over all "YOUR" the dev team ect...'s project so plz dont be offended because my remark was intended to be in jest

[John Adams]

After playing with these DB changes in the "live" environments for a while, I feel some of the constraints may be a little pre-mature. I am taking a few of them out; namely the ones on spawn_npcs into the 5 fields representing spawn_npc_equipment, spawn_npc_skills, and spawn_npc_spells. While they will eventually be replaced, right now I see no need for them - and their current implementation causes possible data loss on the entire spawn_npcs table should any one of the other 3 tables lose a record. That is bad!

In an upcoming code update, expect a few small changes. Thanks, and sorry if anyone got hit by that bug.