Jump to content
TrinityCore

Could not prepare statements of the Character database


shadedmagus
 Share

Recommended Posts

Hello,

I am rebuilding my TrinityCore 3.3.5 project from scratch on Fedora Server 28. I have followed the Linux installation guide on the wiki and have reached the point where it is time to start the binaries. Authserver starts with no problems; but when I try to run worldserver, after all the databases are updated, I receive SQL errors.

In mysql_stmt_prepare() id: 61, sql: "SELECT guildid, rank FROM guild_member WHERE guid = ?"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM guild_member WHERE guid = ?' at line 1
In mysql_stmt_prepare() id: 119, sql: "INSERT INTO guild_member (guildid, guid, rank, pnote, offnote) VALUES (?, ?, ?, ?, ?)"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank, pnote, offnote) VALUES (?, ?, ?, ?, ?)' at line 1
In mysql_stmt_prepare() id: 142, sql: "UPDATE guild_member SET rank = ? WHERE guid = ?"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank = ? WHERE guid = ?' at line 1
In mysql_stmt_prepare() id: 190, sql: "UPDATE arena_team SET rating = ?, weekGames = ?, weekWins = ?, seasonGames = ?, seasonWins = ?, rank = ? WHERE arenaTeamId = ?"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank = ? WHERE arenaTeamId = ?' at line 1
In mysql_stmt_prepare() id: 242, sql: "INSERT INTO groups (guid, leaderGuid, lootMethod, looterGuid, lootThreshold, icon1, icon2, icon3, icon4, icon5, icon6, icon7, icon8, groupType, difficulty, raidDifficulty, masterLooterGuid) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups (guid, leaderGuid, lootMethod, looterGuid, lootThreshold, icon1, icon2, i' at line 1
In mysql_stmt_prepare() id: 246, sql: "UPDATE groups SET leaderGuid = ? WHERE guid = ?"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups SET leaderGuid = ? WHERE guid = ?' at line 1
In mysql_stmt_prepare() id: 247, sql: "UPDATE groups SET groupType = ? WHERE guid = ?"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups SET groupType = ? WHERE guid = ?' at line 1
In mysql_stmt_prepare() id: 250, sql: "UPDATE groups SET difficulty = ? WHERE guid = ?"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups SET difficulty = ? WHERE guid = ?' at line 1
In mysql_stmt_prepare() id: 251, sql: "UPDATE groups SET raiddifficulty = ? WHERE guid = ?"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups SET raiddifficulty = ? WHERE guid = ?' at line 1
In mysql_stmt_prepare() id: 327, sql: "DELETE FROM groups WHERE guid = ?"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups WHERE guid = ?' at line 1
In mysql_stmt_prepare() id: 414, sql: "REPLACE INTO calendar_invites (id, event, invitee, sender, status, statustime, rank, text) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank, text) VALUES (?, ?, ?, ?, ?, ?, ?, ?)' at line 1
Could not prepare statements of the Character database, see log for details.

The is after importing the "characters_database.sql" file in TrinityCore/sql/base.

I created the databases as per "create_sql.sql" suggested, using my sql-user instead of the default "trinity" account as the only deviation. I can access the databases remotely with HeidiSQL.

I'm not sure what the problem might be at this point.

Link to comment
Share on other sites

A quick inspection in the characters DB reveals that the guild_member table has a field  named rank, with a record that cannot be prepared for insertion. I'm not sure what updates must be applied, but I reckon applying all updates from 'sql/old/3.3.5a/characters' folder will solve the problem. I've got the same issue at the moment, will post an update shortly.

Edited by vladex
Link to comment
Share on other sites

I've just tested the new build .

Getting this now :

In mysql_stmt_prepare() id: 55, sql: "SELECT entry, 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, npcflag, speed_walk, speed_run, scale, rank, dmgschool, BaseAttackTime, RangeAttackTime, BaseVariance, RangeVariance, unit_class, unit_flags, unit_flags2, dynamicflags, family, trainer_type, trainer_spell, trainer_class, trainer_race, 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, ctm.Ground, ctm.Swim, ctm.Flight, ctm.Rooted, HoverHeight, HealthModifier, ManaModifier, ArmorModifier, DamageModifier, ExperienceModifier, RacialLeader, movementId, RegenHealth, mechanic_immune_mask, spell_school_immune_mask, flags_extra, ScriptName FROM creature_template ct LEFT JOIN creature_template_movement ctm ON ct.entry = ctm.CreatureId WHERE entry = ?"

Link to comment
Share on other sites

Managed to get past the problem by escaping MySQL reserved words .

 

PrepareStatement(WORLD_SEL_CREATURE_TEMPLATE, "SELECT entry, 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, npcflag, speed_walk, speed_run, scale, 'rank', dmgschool, BaseAttackTime, RangeAttackTime, BaseVariance, RangeVariance, unit_class, unit_flags, unit_flags2, dynamicflags, family, trainer_type, trainer_spell, trainer_class, trainer_race, '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, ctm.Ground, ctm.Swim, ctm.Flight, ctm.Rooted, HoverHeight, HealthModifier, ManaModifier, ArmorModifier, DamageModifier, ExperienceModifier, RacialLeader, movementId, RegenHealth, mechanic_immune_mask, spell_school_immune_mask, flags_extra, ScriptName FROM creature_template ct LEFT JOIN creature_template_movement ctm ON ct.entry = ctm.CreatureId WHERE entry = ?", CONNECTION_SYNCH);

 

Not sure this is good enough.  I am now getting :

[1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups' at line 1 .
 

 

Link to comment
Share on other sites

Tested new build. Still getting

[1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups' at line 1
Error while parsing SQL. Core fix required.

I've no idea where is this coming from.

Link to comment
Share on other sites

That's interesting, because I'm getting compile errors after pulling the latest commit just now:

[ 14%] Building CXX precompiled header src/server/database/cotire/database_CXX_prefix.hxx.pch
In file included from /home/trinity-lk/TrinityCore/build/src/server/database/cotire/database_CXX_prefix.hxx:4:
In file included from /home/trinity-lk/TrinityCore/build/src/server/database/cotire/database_CXX_prefix.cxx:4:
In file included from /home/trinity-lk/TrinityCore/src/server/database/PrecompiledHeaders/databasePCH.h:30:
In file included from /usr/include/mysql/mysql.h:84:
/usr/include/mysql/mysql/client_plugin.h:108:8: fatal error: definition of type 'MYSQL' conflicts with typedef of the same name
struct MYSQL;
       ^
/home/trinity-lk/TrinityCore/src/server/database/Database/DatabaseEnvFwd.h:48:25: note: 'MYSQL' declared here
typedef struct st_mysql MYSQL;
                        ^
1 error generated.
CMake Error at /home/trinity-lk/TrinityCore/dep/cotire/CMake/cotire.cmake:1885 (message):
  cotire: error 1 precompiling
  /home/trinity-lk/TrinityCore/build/src/server/database/cotire/database_CXX_prefix.hxx.
Call Stack (most recent call first):
  /home/trinity-lk/TrinityCore/dep/cotire/CMake/cotire.cmake:3558 (cotire_precompile_prefix_header)

make[2]: *** [src/server/database/CMakeFiles/database.dir/build.make:64: src/server/database/cotire/database_CXX_prefix.hxx.pch] Error 1
make[1]: *** [CMakeFiles/Makefile2:1124: src/server/database/CMakeFiles/database.dir/all] Error 2
make: *** [Makefile:130: all] Error 2

Link to comment
Share on other sites

Since it seems you are compiling it on linux, I am not sure this solution will work. I usually got this error on windows when using the portable mysql version. I fixed by installing the .msi installer version of mysql 5.6. You should  do the same by using the .deb installer version or whatever package installer your linux distro uses.

Link to comment
Share on other sites

54 minutes ago, vladex said:

Since it seems you are compiling it on linux, I am not sure this solution will work. I usually got this error on windows when using the portable mysql version. I fixed by installing the .msi installer version of mysql 5.6. You should  do the same by using the .deb installer version or whatever package installer your linux distro uses.

But the only thing I changed from when I first reported the error statements in my original post was doing a new git pull. When I made my post, it had compiled with no issues, those errors were when I started the worldserver binary.

Edited by shadedmagus
Clarity
Link to comment
Share on other sites

Like I said, the error you had I usually got from compiling with a mysql version that is not installed via the installer. I do not comprehend why it doesn't work, but I'm assuming that the installer version has extra libraries for debugging/development etc. It is also the only solution that worked for me so far, I've been gone from trinity for ~ 2-3 years so I decided to check it out and see what's new, I pulled the  source yesturday and got stuck at exactly the same problem (but on windows instead) . I might try to do it on my linux VM soon to see if I also get it .

Link to comment
Share on other sites

19 hours ago, shadedmagus said:

But the only thing I changed from when I first reported the error statements in my original post was doing a new git pull. When I made my post, it had compiled with no issues, those errors were when I started the worldserver binary.

I've just tested the new build on a fresh debian 9 VM (no other packages installed besides system utilities and gnome-core) , no errors , the only thing I've installed is the following packages:

apt-get install git clang cmake make gcc g++ libmariadbclient-dev libssl1.0-dev libbz2-dev libreadline-dev libncurses-dev libboost-all-dev mysql-server p7zip
 
 
After installing the packages I've created a build directory in the trinityCore solution folder:
 
mkdir build
cd build

cmake ../ -DCMAKE_INSTALL_PREFIX=/home/[your_username_here]/server_path_folder  -DTOOLS=0

make

make -j 8         // using  this since I've only got 8 cores on this machine

make install

That's it.

 

With that being said.

I will test the new build to see if I still get the same runtime errors.

 

 

Link to comment
Share on other sites

Tested new build. Getting

[1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Error while parsing SQL. Core fix required.

 

When loading Spell Rank Data. Will attempt to see if I can fix.

 

Link to comment
Share on other sites

Sorry for being such a bother, but the problem with the SQL parsing still exists, on a different table this time it seems, I've applied all world updates , still getting when running worldserver.

  ou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1.

Link to comment
Share on other sites

On 5/23/2018 at 8:02 AM, Shauren said:

You do not need to switch distros, the reason for this is mysql version 8 (and newer)

Thank you for posting, Shauren.

Since my build compiled and the binary couldn't open due to the reserved word "rank," and all I did was do a pull and now I can't compile, what should be my next step? Was there a regression in the

database_CXX_prefix

files that is keeping the compile from happening, when nothing in my build environment changed between one attempt and the next?

Please advise.

Link to comment
Share on other sites

I have done this, several times.

  1.  rm -rf /home/<user>/TrinityCore/build
  2. cd ~/TrinityCore
  3. mkdir build
  4. cd build
  5. cmake ../ -DCMAKE_INSTALL_PREFIX=/home/<user>/server -DTOOLS=1 -DWITH_WARNINGS=1
  6. make

At 14%, on the database_CXX_prefix files, it throws the errors I quoted earlier in the thread.

This is exactly the process I have followed since before the switch from mariadb; I do not modify any of the build files before I run cmake. I have not modified the build environment since I first built my Fedora Server 28 install and installed the required packages as per the install instructions on the wiki. I did one "dnf update" after the server was online, and that was before my first build attempt, which compiled but threw the initial mysql_stmt_prepare errors in my OP.

 

Edited by shadedmagus
I forgot to add one of the steps I take
Link to comment
Share on other sites

On 5/22/2018 at 12:59 PM, vladex said:

Tested new build. Getting

[1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Error while parsing SQL. Core fix required.

 

When loading Spell Rank Data. Will attempt to see if I can fix.

 

Try after https://git.io/vhIhw

Link to comment
Share on other sites

Issue still persists after commit 17539f1cb319f0d8d089038a4b10924d145ee248  . Seems to be somewhere else. Getting the same error when trying to load Spell Rank Data.

Loading Game Object Templates...
>> Loaded 21567 game object templates in 616 ms
Loading Game Object template addons...
>> Loaded 21500 game object template addons in 201 ms
Loading Transport templates...
>> Loaded 30 transport templates in 13 ms
Loading Transport animations and rotations...
Loading Spell Rank Data...

[1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
Error while parsing SQL. Core fix required.

 

Link to comment
Share on other sites

 Share

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...