3.3.1 DB table structure question
 

Could someone plz tell me exactly which tables were added/removed from 3.3.1, I'm a bit mixed up as to what ones were canged from 3.1-3.3, and I would like to update my DB for use with 3.3.1, so if someone could post the changes here or point me in the direction of where to find this that would be great!

This isn't garanteed to work, but I think I got everything. Make sure you have a backup of your DB first.

Just source this in you 3.1 DB It will delete all characters, and redo the zone table.

else if you know what you do in MySQL just create a second database and source a 3.3.1 db in it then check what as changed in the important tables ( items spawn2 spawngroup npctypes character_ ) but I think that using lurker translating sql will save you more time ;)

K thx lurker, a file to do the work for me is about as good as i could have hoped for lol. this should avoid the errors with spawn groups and such correct?

keep a copy or a dump of your actual db , but it should work fine as it uses only some alter tables

just found something lurkers file misses-- it doesnt put in a new 3.3.1 variables table and world.exe gives this:
--------------------------------------------------------------------------
LoginServer.ini read.
Using database 'eq2' at localhost
Error in GetVariable query 'SELECT value FROM variables WHERE varname like 'hold
zones'' #1146: Table 'eq2.variables' doesn't exist
Loading zone names & items...done.
Loading guild ranks...done.
World server listening on: port 9000
Connected to LoginServer: eqlogin2.eqemu.net:5999
-----------------------------------------------------------------------------

going to copy the table from the relese DB from 3.3 to mine and see if that fixes it

Also, after using the version change sql file i ahve 27 tables but the relese db has 32 or something, which could be a problem, im not sure

... perhaps raw_zone... I would have to compare the default DB's again to find out. I though what I put in that sql file was all that changed... perhaps I am thinking of 3.2

sorry to tired to think any more tonight. just add any missing tables, and try it for now...

Went through and took tables from the db released with 3.3.1 and added to mine, going to test it now. Added the following tables that were skipped by lurkers file:
1. starting items
2. variables
3. trade skill recipie

may have been more, cant remeber its like 1 am and my memory is failing me so I will just post what my test result is...
-----------------------
EDIT:

1. Zoneserver_auth isnt in, otherwise world.exe and boot5zones would be error free.. adding that table now.
2. Everything was great until i tryed to log in.. apparently the zone_points table is not in so no zone will load. adding that table.
3. Again got to char select and made character without a problem, but i get kicked to char select with a message "zone is unavailible" and this in zone.exe file:

Using database 'eq2' at localhost
Loading zone names, items & NPCs...done.
Spells loaded from 'spdat.eff'.
Loading guild ranks...done.
Loading faction data...done.
Connected to worldserver: 127.0.0.1:9000
Entering sleep mode
Map: Maps\freportn.map not found.
Error2 in PopulateZoneLists query 'SELECT DISTINCT(spawngroupID), spawngroup.nam
e FROM spawn2,spawngroup WHERE spawn2.spawngroupID=spawngroup.ID and zone='frepo
rtn'' #1146: Table 'eq2.spawngroup' doesn't exist
Zone->Init failed

when I added the spawngroup table on my first attempt it gave me a list of errors because my spawngroup data isnt correct for the npc's. I am going to try and use the newest lurker data that is 3.3 compatible..

4. new errors:
I downloaded lurker's .39 data and used the spawngroup data from that to build the spawngroup table in my db. this is the result i got from that, is the same with blank spawngroup and with lurker .35 data. not too sure what to do on this one

Using database 'eq2' at localhost
Loading zone names, items & NPCs...done.
Spells loaded from 'spdat.eff'.
Loading guild ranks...done.
Loading faction data...done.
Connected to worldserver: 127.0.0.1:9000
Entering sleep mode
Map: Maps\freportn.map not found.
Error in SpawngroupID: 35417
Error in SpawngroupID: 35352
Error in SpawngroupID: 35373
Error in SpawngroupID: 35422
Error in SpawngroupID: 35394
Error in SpawngroupID: 35324
Error in SpawngroupID: 35416
Error in SpawngroupID: 35351
Error in SpawngroupID: 35372
Error in SpawngroupID: 35423
Error in SpawngroupID: 35393
Error in SpawngroupID: 35319
Error in SpawngroupID: 35415
Error in SpawngroupID: 35350
Error in SpawngroupID: 35371
Error in SpawngroupID: 35424
Error in SpawngroupID: 35392
Error in SpawngroupID: 35318
Error in SpawngroupID: 35414
Error in SpawngroupID: 35349
Error in SpawngroupID: 35370
Error in SpawngroupID: 35425
Error in SpawngroupID: 35391
Error in SpawngroupID: 35317
Error in SpawngroupID: 35413
Error in SpawngroupID: 35348
Error in SpawngroupID: 35369
Error in SpawngroupID: 35390
Error in SpawngroupID: 35315
Error in SpawngroupID: 35412
Error in SpawngroupID: 35347
Error in SpawngroupID: 35368
Error in SpawngroupID: 35389
Error in SpawngroupID: 35311
Error in SpawngroupID: 35411
Error in SpawngroupID: 35346
Error in SpawngroupID: 35367
Error in SpawngroupID: 35388
Error in SpawngroupID: 35308
Error in SpawngroupID: 35410
Error in SpawngroupID: 35345
Error in SpawngroupID: 35366
Error in SpawngroupID: 35387
Error in SpawngroupID: 35307
Error in SpawngroupID: 35409
Error in SpawngroupID: 35344
Error in SpawngroupID: 35365
Error in SpawngroupID: 35386
Error in SpawngroupID: 35306
Error in SpawngroupID: 35408
Error in SpawngroupID: 35343
Error in SpawngroupID: 35364
Error in SpawngroupID: 35385
Error in SpawngroupID: 35304
Error in SpawngroupID: 35407
Error in SpawngroupID: 35342
Error in SpawngroupID: 35363
Error in SpawngroupID: 35384
Error in SpawngroupID: 35303
Error in SpawngroupID: 35406
Error in SpawngroupID: 35341
Error in SpawngroupID: 35362
Error in SpawngroupID: 35383
Error in SpawngroupID: 35302
Error in SpawngroupID: 35405
Error in SpawngroupID: 35340
Error in SpawngroupID: 35361
Error in SpawngroupID: 35382
Error in SpawngroupID: 35301
Error in SpawngroupID: 35404
Error in SpawngroupID: 35339
Error in SpawngroupID: 35360
Error in SpawngroupID: 35381
Error in SpawngroupID: 35420
Error in SpawngroupID: 35403
Error in SpawngroupID: 35338
Error in SpawngroupID: 35359
Error in SpawngroupID: 35380
Error in SpawngroupID: 35421
Error in SpawngroupID: 35402
Error in SpawngroupID: 35335
Error in SpawngroupID: 35358
Error in SpawngroupID: 35379
Error in SpawngroupID: 35427
Error in SpawngroupID: 35401
Error in SpawngroupID: 35334
Error in SpawngroupID: 35357
Error in SpawngroupID: 35378
Error in SpawngroupID: 35310
Error in SpawngroupID: 35399
Error in SpawngroupID: 35333
Error in SpawngroupID: 35356
Error in SpawngroupID: 35377
Error in SpawngroupID: 35312
Error in SpawngroupID: 35398
Error in SpawngroupID: 35332
Error in SpawngroupID: 35355
Error in SpawngroupID: 35376
Error in SpawngroupID: 35314
Error in SpawngroupID: 35397
Error in SpawngroupID: 35330
Error in SpawngroupID: 35419
Error in SpawngroupID: 35354
Error in SpawngroupID: 35375
Error in SpawngroupID: 35336
Error in SpawngroupID: 35396
Error in SpawngroupID: 35326
Error in SpawngroupID: 35418
Error in SpawngroupID: 35353
Error in SpawngroupID: 35374
Error in SpawngroupID: 35337
Error in SpawngroupID: 35395
Error in SpawngroupID: 35325
ZoneID = 8
Reading zhdr file './cfg/freportn.cfg'
Using database for safe coords.
Zone safe coords are x = 211; y = -296; z= 0
-----------
Zone server 'freportn' listening on port:7999
-----------
48344 New client from ip: ***.***.***.*** port: 3471
Login packet 1 (OP_SetDataRate = 5)
Unexpected packet during CLIENT_CONNECTING2: OpCode: 0xe821, size: 4
0: 00 00 A0 40 | ...@
Login packet 2
Loaded playerprofile for Foleleb - size: 8244/8244
Error in GetPlayerAlternateAdv query 'SELECT alt_adv FROM character_ WHERE accou
nt_id=2 AND name='Foleleb'' #1054: Unknown column 'alt_adv' in 'field list'
Client dropped: !GetPlayerAlternateAdv, name=Foleleb
Dropping client: Process=false, ip=***.***.***.***, port=3471

Fixed the issues stated above, took my world data out manually to .txt files, then sourced and exported as a db.sql file. I would put it up for download to everyone but a)i have learned that tripod is not for hosting files :P b) have no web domain to host from c) file is too large to attach to post. So basicly i can email it to a few people, and if someone would spare me the 1.2mb to host it on a page for a week or two, well, that wouldnt be too bad either :)