"unknown column" "missing table" "duplicate column" error / sql problems / SQLDriverLogFile < answer on 1st post of 1st page

[marshalpinokio]

Cannot connect to world database 127.0.0.1;3306;root;root;world

actually it does connect but when occured with errors shows this

hmmm i think Vincent is right

time to check it out

Edited June 15, 2013 by marshalpinokio

[Paradox]

doh!

[argoneus]

Hello,

 

I am using this core: https://github.com/TrinityCore/TrinityCore (commit 31b0b1afb8da2d72f197a61bf23787a7d783962a)

this TDB: http://www.trinitycore.org/f/files/file/9-tdb-full-434/

and all these updates: https://github.com/TrinityCore/TrinityCore_4.3.4_DB_Alpha/tree/master/updates (commit eafb5001e04b94775c2420d20c9c868fd8e95b85)

 

I compiled the core, added to the db the auth/characters from core, then added to world the full TDB, and then installed all those updates from the repo. Yet when I try to start worldserver, I get the error:

SQL and DBC format strings are not matching for table: 'spell_dbc'
[1146] Table 'world.spelleffect_dbc' doesn't exist
Your database structure is not up to date. Please make sure you've executed all queries in the sql/updates folders.

Are there any SQL updates I am missing? Thanks in advance!

 

 

 

[argoneus]

Alright, I found a way to fix it. It's apparently needed to apply some of the 3.3.5a SQL files.

[zeest]

Do you know which ones? I am also having this problem.

[kotori]

If you have your import history available, I would also like to know which 3.3.5a specific SQL files you imported to correct this error.

[Guybrush]

Do you know which ones? I am also having this problem.

 

They are marked with "434".

[dimenus]

Edit: Got it to run, but still getting many errors.

DB definitely needs cleanup at this point.

Edited June 26, 2013 by dimenus

[AlexDark]

Hi all,

I've been looking around for solutions (yes, i did use the Search button  ) but can't find a simple way to have this fixed:

I've recently imported the latest TDB release (TDB_full_335.51_2013_02_14.sql).  After that I merged all the updates into one single SQL file, but i get the error below:

 

Query:
UPDATE `creature_template` SET `equipment_id` = 0 WHERE `name` LIKE '%(1)' OR `name` LIKE '%(2)' OR `name` LIKE '%(3)' OR `name` LIKE '%(4)'

Error occured at:2013-06-29 23:37:05
Line no.:102
Error Code: 1054 - Unknown column 'equipment_id' in 'field list'



Query:
UPDATE `creature_template` SET `equipment_id` = 0 WHERE `name` LIKE '%(1)' OR `name` LIKE '%(2)' OR `name` LIKE '%(3)' OR `name` LIKE '%(4)'

Error occured at:2013-06-29 23:39:43
Line no.:14461
Error Code: 1054 - Unknown column 'equipment_id' in 'field list'



Query:
UPDATE `creature_template` SET `equipment_id` = 0 WHERE `name` LIKE '%(1)' OR `name` LIKE '%(2)' OR `name` LIKE '%(3)' OR `name` LIKE '%(4)'

Error occured at:2013-06-29 23:44:25
Line no.:14461
Error Code: 1054 - Unknown column 'equipment_id' in 'field list'



Query:
UPDATE `creature_template` SET `equipment_id` = 0 WHERE `name` LIKE '%(1)' OR `name` LIKE '%(2)' OR `name` LIKE '%(3)' OR `name` LIKE '%(4)'

Error occured at:2013-06-29 23:44:38
Line no.:102
Error Code: 1054 - Unknown column 'equipment_id' in 'field list'



Query:
UPDATE `creature_template` SET `equipment_id` = 0 WHERE `name` LIKE '%(1)' OR `name` LIKE '%(2)' OR `name` LIKE '%(3)' OR `name` LIKE '%(4)'

Error occured at:2013-06-29 23:44:53
Line no.:102
Error Code: 1054 - Unknown column 'equipment_id' in 'field list'



Query:
DELETE FROM `playercreateinfo_spell` WHERE `race`=10 AND `Spell`=61437

Error occured at:2013-06-29 23:46:17
Line no.:724
Error Code: 1054 - Unknown column 'race' in 'where clause'



Query:
UPDATE `creature_template` SET `equipment_id` = 0 WHERE `name` LIKE '%(1)' OR `name` LIKE '%(2)' OR `name` LIKE '%(3)' OR `name` LIKE '%(4)'

Error occured at:2013-06-30 12:54:04
Line no.:8035
Error Code: 1054 - Unknown column 'equipment_id' in 'field list'



Query:
INSERT INTO `playercreateinfo_spell` (`race`,`class`,`Spell`,`Note`) VALUES
(10,2,61437, 'Opening'),
(10,3,61437, 'Opening'),
(10,4,61437, 'Opening'),
(10,5,61437, 'Opening'),
(10,6,61437, 'Opening'),
(10,8,61437, 'Opening'),
(10,9,61437, 'Opening')

Error occured at:2013-06-30 12:57:12
Line no.:732
Error Code: 1054 - Unknown column 'class' in 'field list'



Query:
UPDATE `creature_template` SET `equipment_id` = 0 WHERE `name` LIKE '%(1)' OR `name` LIKE '%(2)' OR `name` LIKE '%(3)' OR `name` LIKE '%(4)'

Error occured at:2013-06-30 13:08:17
Line no.:102
Error Code: 1054 - Unknown column 'equipment_id' in 'field list'



Query:
UPDATE `creature_template` SET `equipment_id` = 0 WHERE `name` LIKE '%(1)' OR `name` LIKE '%(2)' OR `name` LIKE '%(3)' OR `name` LIKE '%(4)'

Error occured at:2013-06-30 13:18:07
Line no.:14461
Error Code: 1054 - Unknown column 'equipment_id' in 'field list'

It seems as one of the updates deletes the "equipment_id" column and after it tries to add some data again to the same.  

I know the solution for this is running the updates one by one and paying attention to the one that fails... but i'm wondering if there is any fix released for this already? 

 

 

Extra Info

OS:  Windows 7 Ultimate x64

RAM: 8GB
Processor: Quad Core Q8300 @ 2,5GHz

TrinityCore release: b3bdcc6 (updated today 30/06/2013)
TDB release: TDB_full_335.51_2013_02_14

MySQL Version: 5.5.
SQLyog v11.13

CMake 2.8

GitExtensions 2.44

Edited June 30, 2013 by AlexDark

[peycho]

I think you're missing some updates. Some table are changed.

 

column 'equipment_id' is obsolete.

[Guybrush]

2013_02_18_00_world_misc_equip <- did you run this sql in its entirety?

 

I just checked all of the updates following the above, and none update the equipment_id column in creature_template (all the way to today's commits). The only references made to this column are applied to the `creature` table (as they should).

 

You either missed the SQL I mentioned above, or your batch is screwy.

[AlexDark]

I checked the version table which says:  TrinityCore rev. 2013-01-14 12:00:00 -0000 (45363b8216f03f2) (Win64, Debug), meaning I have to import ALL the files in the update folder (all of them are older then 2013-01-14).

I merged them with the basic command (cmd > type *.sql > update.sql), so obviously i didn't missed the SQL you mentioned.  The files also have been merged in correct date order, so I'm not sure what else I have to do... 

Is there some batch there that I can use?  I haven't seen any batch in the source 

[Guybrush]

TrinityCore rev. 2013-01-14 12:00:00 -0000 (45363b8216f03f2) (Win64, Debug),

 

Interesting...I literally just checked out that exact same revision to restore a back up of characters I needed. As a matter of fact, I was doing that right before I came back to read your reply... What a coincidence?

 

Anyway, seeing as you are working with a core that is OLDER than the TDB (which was released on 2-14), you will also need to import the SQL queries found in the TDB 50 folder under sql/old/3.3.5a/TDB50_to_TDB51_updates/world, starting with the queries on the same day or immediately after your DB date found in `version`.

 

Since you did not do this, it may be difficult to do now that you've imported SQLs out of order. You may need to wipe the updates you've done and start over from the correct date.

[CDawg]

Interesting...I literally just checked out that exact same revision to restore a back up of characters I needed. As a matter of fact, I was doing that right before I came back to read your reply... What a coincidence?

 

I can't tell how many times I've worked on something, came back and seen another update added to TC, literally minutes later.

[Paradox]

Unless you have some really important custom changes, just drop the world DB and apply the latest TDB plus updates.

[baric]

Unless you have some really important custom changes, just drop the world DB and apply the latest TDB plus updates.

 

This.

 

In addition, every single change you make to the world DB should be saved as updates in an *.sql file, which you can then just re-run after you've reset the world DB.

[jadethread]

im having this same issue. please help with this issue i have no clue what to do ive applied the 4.3.4 updates and the "TrinityCore 4.3.4 DB Alpha Master" updates and i still have the same problem

[MrSmite]

Alright, I found a way to fix it. It's apparently needed to apply some of the 3.3.5a SQL files.

 

They are marked with "434".

 

Interesting. Both guides on the wiki (windows and linux) say not to install anything from the Updates folder and I was even accused of "doing it wrong" in a bug report when I mentioned that my character can't use any chat commands.

 

I found that I needed to copy the PlayerCreatInfo_* tables from my 3.3.5a database to 4.3.4 in order to be able to use gm commands, despite what the "experts" say.

[Guybrush]

Interesting. Both guides on the wiki (windows and linux) say not to install anything from the Updates folder and I was even accused of "doing it wrong" in a bug report when I mentioned that my character can't use any chat commands.

This is because *ideally* you shouldn't need to import anything from 335a -- changes should have already been merged with 434. But since this isn't the case...

Short story: 434 needs to be resynced with 335a.

[MrSmite]

This is because *ideally* you shouldn't need to import anything from 335a -- changes should have already been merged with 434. But since this isn't the case...

Short story: 434 needs to be resynced with 335a.

Indeed, I agree. Unfortunately the typical knee-jerk answer to these types of questions is "you did it wrong".

[ARCHFIENDONE]

Did anyone figure out which 3.3.5a files he imported to fix the issue? "They are marked with "434"" isn't clear enough.

[MrSmite]

Did anyone figure out which 3.3.5a files he imported to fix the issue? "They are marked with "434"" isn't clear enough.

 

Maybe the ones in the "srcsqlold4.3.4" folder although as I mentioned before, the "experts" say you're not supposed to be importing anything from there so it's up to you if you want to try it.

[ARCHFIENDONE]

Thanks,  I scrapped my world db, and started from scratch again using MySQL workbench. When I was using heidisql, the sql import would error out, and the worldserver error would remain. I'm not sure how to tell heidisql how to continue on error, so I used MySQL Workbench, imported the sql files and told it to continue on error, I did that with the TrinityCore_4.3.4_DB_Alphaupdates folder, then I made a batch file with "copy /a *.sql /b world_updates.sql" and stuck it in the TrinityCoresqlupdates folder, ran the batch file, and imported world_updates.sql. After doing that, I successfully created an account, logged in and it seems to be working. Thanks for the suggestion.

[CDawg]

@archifiendone, You can always import through command line, it's quicker than going through a GUI, and it wont timeout.

[Subv]

--- Canned message start ---

It appears the issue in the original post was solved, so this thread shall be closed.

Should you encounter any other difficulties, please open a new thread.

--- Canned message end ---