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

[Bross]

Thanks a lot for the help guys. really appreciate it. haha Winblows will be the end of me i swear

ok it seems to have installed properly this time. baby steps. thanks again for the help

[Tik]

nevermind, forgot to import the updates

[CDawg]

Thanks a lot for the help guys. really appreciate it. haha Winblows will be the end of me i swear

ok it seems to have installed properly this time. baby steps. thanks again for the help

no prob, if you still have mysql connection issues. (I did awhile back on my old XP machine) you can go into my.ini file, and add a 'bind-address 0.0.0.0' , restart mysql, and it clears right up.

mysql and windows can get buggy sometimes.

[CDawg]

The OP doesn't have mysql connecting properly, or even loaded.

Its unable to load the pool, clear indicator that mysql is missing from the agenda.

Your error loads mysql, however just not reading the columns correctly.

[dravce15a]

I have MySQL running it is visible in services tab in task manager, there are: MSSQL$SQLEXPRESS(running),MSSQLServerADHelper100(stoped),MySQL55(running).

This only happens with worldserver, auth server starts with no problems.

P.S, I have enable SQLDriverLogFile, and now I get the same error as Thatguy I think:

2012-05-13 09:41:31 Opening DatabasePool 'world'. Asynchronous connections: 1, synchronous connections: 1.

2012-05-13 09:41:31 MySQL client library: 5.5.9

2012-05-13 09:41:31 MySQL server ver: 5.5.24

2012-05-13 09:41:31 [WARNING] MySQL client/server version mismatch; may conflict with behaviour of prepared statements.

2012-05-13 09:41:31 MySQL client library: 5.5.9

2012-05-13 09:41:31 MySQL server ver: 5.5.24

2012-05-13 09:41:31 [WARNING] MySQL client/server version mismatch; may conflict with behaviour of prepared statements.

2012-05-13 09:41:31 [ERROR]: In mysql_stmt_prepare() id: 56, sql: "SELECT difficulty_entry_1, difficulty_entry_2, difficulty_entry_3, KillCredit1, KillCredit2, modelid1, modelid2, modelid3, modelid4, name, subname, IconName, gossip_menu_id, minlevel, maxlevel, exp, faction_A, faction_H, npcflag, speed_walk, speed_run, scale, rank, mindmg, maxdmg, dmgschool, attackpower, dmg_multiplier, baseattacktime, rangeattacktime, unit_class, unit_flags, dynamicflags, family, trainer_type, trainer_spell, trainer_class, trainer_race, minrangedmg, maxrangedmg, rangedattackpower, type, type_flags, lootid, pickpocketloot, skinloot, resistance1, resistance2, resistance3, resistance4, resistance5, resistance6, spell1, spell2, spell3, spell4, spell5, spell6, spell7, spell8, PetSpellDataId, VehicleId, mingold, maxgold, AIName, MovementType, InhabitType, HoverHeight, Health_mod, Mana_mod, Armor_mod, RacialLeader, questItem1, questItem2, questItem3, questItem4, questItem5, questItem6, movementId, RegenHealth, equipment_id, mechanic_immune_mask, flags_extra, ScriptName FROM creature_template WHERE entry = ?"

2012-05-13 09:41:31 [ERROR]: Unknown column 'HoverHeight' in 'field list'

2012-05-13 09:41:31 [ERROR]: In mysql_stmt_prepare() id: 58, sql: "SELECT c.country FROM ip2nationCountries c, ip2nation i WHERE i.ip < ? AND c.code = i.country ORDER BY i.ip DESC LIMIT 0,1"

2012-05-13 09:41:31 [ERROR]: Table 'world.ip2nationcountries' doesn't exist

EDIT: I have imported the updates and it gives me an diffrent error now, something about vmaps, so I am extracting them, and hopefully will work.

Thanks for the help guys!

Best regards

Edited May 13, 2012 by dravce15a

[Tik]

You forgot to run the updates, the big SQL file on the .zip file is not enough. you have to import all the updates to the world server, they can be found here : ...sqlupdatesworld

Instead of importing one by one which is really boring you can merge all into one file. Open ...sqlupdatesworld and then SHIFT+Right click -> Open command line here and then type on the command line: copy *.sql allupdates.sql And all you have to do is run the allupdates.sql

[dravce15a]

Thanks!

[Bross]

cool. thanks. i can connect to the database fine now. i got a different problem now though. when i import my databases from into the SQL database the auth and characters database is empty. tried refreshing. it doesnt even give me the Run file(s) directly option. importing the world database using the TDB full file works fine it seems. i followed the guide and used C:Trinitysqlbaseauth_database.sql and C:Trinitysqlbasecharacter_database.sql. but even after refreshing it seems both db's are empty and as a result when i run the authserver.exe and worldserver.exe they close a second after they open.

also one more thing in the guide it says scroll down to LoginDatabaseInfo = "127.0.0.1;3306;trinity;trinity;auth". when changing all these to match mysql databse login info i change the "trinity" "trinity" to what ever my sql root and password was correct? just want to make sure. sorry for asking stupid questions again

Bross

[Bross]

Hey guy i got a problem i wonder if someone can perhaps assist me? i will really appreciate it.

im using Win7 .64

HeidieSQL win32

mysql server win32 x86

the guide im using is http://www.trinitycore.info/How-to:Win to set up my server

when i import my databases intothe auth and characters database's they are still empty (attached pic). tried refreshing. it doesnt even give me the Run file(s) directly option. importing the world database using the TDB_full_335.11.46_2012_02_13.sql file it works fine it seems. i followed the guide and used C:\Trinity\sql\base\auth_database.sql and C:\Trinity\sql\base\character_database.sql. but even after refreshing it seems both db's are empty and as a result when i run the authserver.exe and worldserver.exe they close a second after they open.

also one more thing in the guide when setting up your server and opening the config files it says scroll down to LoginDatabaseInfo = "127.0.0.1;3306;trinity;trinity;auth". when changing all these to match mysql database login info i change the "trinity" "trinity" to what ever my sql root and password was correct? just want to make sure. sorry for asking stupid questions again

Regards

Bross

[Bross]

NVM it was my own stupid mistake. i didnt click run is all. everything seems fine now except my auth server.exe works fine but world does not for some reason. it just closes the moment it opens

[Polkic]

You must have done something wrong (missed one of SQL updates)?

[CDawg]

yes, change the conf files to your database settings. You can also create another username (if you are using root) and give less permissions, through the gui that you are using. Don't worry about changing root unless you are doing this locally and not planning to have people connect to your server.

Auth and Characters databases should be in TDB_Full* sql file. I just opened mine up and they were in there.

There is a '/base' directory where you will find auth_database.sql and characters_database.sql.

Auth as 9 tables and characters should have 78. If you are unsure that the tables are importing correctly there should be a 'drop_mysql.sql' you can run that to start over.

[rykuu]

it flashes on and then goes off

I looked at the log, and there are 2 errors

2012-05-18 15:06:35 ERROR: DatabasePool world NOT opened. There were errors opening the MySQL connections. Check your SQLDriverLogFile for specific errors.

2012-05-18 15:06:35 ERROR: Cannot connect to world database 127.0.0.1;3306;root;*********;world

I replaced the password with "*" for personal security, but I know that it is correct in the conf files. Any help is appriciated.

[Drek123]

I actually got the same problem right now :/

[Nay]

Go to worldserver.conf;

enable everything related to SQLDriver (Ctrl+F it);

start worldserver(.exe)

go to the logs folder, find SQLDriverLogFile.log and read what it says

[rykuu]

2012-05-18 17:12:22 Opening DatabasePool 'world'. Asynchronous connections: 1, synchronous connections: 1.

2012-05-18 17:12:22 MySQL client library: 5.5.24

2012-05-18 17:12:22 MySQL server ver: 5.5.24

2012-05-18 17:12:22 MySQL client library: 5.5.24

2012-05-18 17:12:22 MySQL server ver: 5.5.24

2012-05-18 17:12:22 [ERROR]: In mysql_stmt_prepare() id: 56, sql: "SELECT difficulty_entry_1, difficulty_entry_2, difficulty_entry_3, KillCredit1, KillCredit2, modelid1, modelid2, modelid3, modelid4, name, subname, IconName, gossip_menu_id, minlevel, maxlevel, exp, faction_A, faction_H, npcflag, speed_walk, speed_run, scale, rank, mindmg, maxdmg, dmgschool, attackpower, dmg_multiplier, baseattacktime, rangeattacktime, unit_class, unit_flags, dynamicflags, family, trainer_type, trainer_spell, trainer_class, trainer_race, minrangedmg, maxrangedmg, rangedattackpower, type, type_flags, lootid, pickpocketloot, skinloot, resistance1, resistance2, resistance3, resistance4, resistance5, resistance6, spell1, spell2, spell3, spell4, spell5, spell6, spell7, spell8, PetSpellDataId, VehicleId, mingold, maxgold, AIName, MovementType, InhabitType, HoverHeight, Health_mod, Mana_mod, Armor_mod, RacialLeader, questItem1, questItem2, questItem3, questItem4, questItem5, questItem6, movementId, RegenHealth, equipment_id, mechanic_immune_mask, flags_extra, ScriptName FROM creature_template WHERE entry = ?"

2012-05-18 17:12:22 [ERROR]: Unknown column 'HoverHeight' in 'field list'

2012-05-18 17:12:22 [ERROR]: In mysql_stmt_prepare() id: 58, sql: "SELECT c.country FROM ip2nationCountries c, ip2nation i WHERE i.ip < ? AND c.code = i.country ORDER BY i.ip DESC LIMIT 0,1"

2012-05-18 17:12:22 [ERROR]: Table 'world.ip2nationcountries' doesn't exist

[Nay]

You didn't apply SQL update files correctly (read the wiki?)

For the last time, this is what you need to do with the DBs:

• Drop world, characters, auth
  
• Create world, characters, auth
  
• Import /sql/base/auth_database.sql to auth database
  
• Import /sql/base/characters_database.sql to characters database
  
• Import https://github.com/d..._2012_02_13.rar toworld database
  
• Import ALL the sqls in /sql/updates/world to world database (see http://www.trinitycore.org/f/topic/43-windowslinux-combine-sql-updates/)
  

[Drek123]

Thank you Nay.

Edited May 18, 2012 by Drek123

[rykuu]

good lord, this would be easier if someone made a video. I will do it and come back if I cannot get it working after re-readong the wiki

Ok, a complete account of what I did....

1: open up sqlyog

2: log into the thing

3: click on the "auth" button found in the side box

4: clicked "database" drop box, then "import" then "execute script"

5: sleceted the "auth_database.sql" from c: rinitysqlbase

6: clicked execute button

7: clicked done

8: clicked "characters" button from the side box

9: repeat step 4

10: selected "characters_database.sql" from same place as in step 5

11: repeat steps 6 and 7

12: clicked "world" from the side box

13: repeat step 4

14: selected "TDB_full_335.11.46_2012_02_13.sql" from the folder on my desktop where it was extracted to from the dowload found at https://github.com/TrinityCore/TrinityCore/downloads

15: repeat steps 6 and 7

done...

still not working.

I have no real clue as to what OI am doing, so please correct em if I messed up in comprehending what I was told to do

Edited May 18, 2012 by rykuu

[Nay]

You missed the last action: "Import ALL the sqls in /sql/updates/world to world database (see http://www.trinityco...ne-sql-updates/)"

[rykuu]

Thank God, thank you!!!

now I just gotta expeirament some with it, and then I can allow my friends on. You don't know how much troubble you have saved me.

With my deepest thanks,

Rykuu Disaga

[Aokromes]

--- 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 ---

[Voltan]

Core rev. 69a4ac2209 (so the auth.log says. core was downloaded today @ 14:33)

TDB_full_335.11.47_2012_05_19

No custom scripts or SQLs included

I've got the same problem like there:

http://www.trinityco...ot-established/

Server.log

2012-05-22 22:29:16 ERROR: DatabasePool mainserver_trinity_world NOT opened. There were errors opening the MySQL connections. Check your SQLDriverLogFile for specific errors.

2012-05-22 22:29:16 ERROR: Cannot connect to world database localhost;3306;trinity;blablabla;trinity_world

SQLDriver.log

2012-05-22 22:29:16 [ERROR]: In mysql_stmt_prepare() id: 58, sql: "SELECT c.country FROM ip2nationCountries c, ip2nation i WHERE i.ip < ? AND c.code = i.country ORDER BY i.ip DESC LIMIT 0,1"

2012-05-22 22:29:16 [ERROR]: Table 'trinity_world.ip2nationCountries' doesn't exist

I've compiled the core today and created a new world-db with the full TDB and updates in the /sql/updates -folder (2012_05_19_03_world_version.sql __up to__ 2012_05_20_01_world_spell_bonus_data.sql).

I have no idea, what to do, cause i cannot miss any update. there are only six .sql's >.<

[Aokromes]

Redownload TDB 47, one issue was found when exporting DB with tables with capital leters from windows OS or rename the table ip2nationcountries to ip2nationCountries.

[Nay]

Run this query:

RENAME TABLE ip2nationcountries TO ip2nationCountries;

[/sql]