ERROR 1064 in 4_CLEANUP_AND_CONVERT_SPELLS.sql

[FullHavocJosh]

4_CLEANUP_AND_CONVERT_SPELLS.sql

ERROR 1064 (42000) at line 62: 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 ') WHERE cnt>2) x2) ON s.guid = x2.guid AND s.skill = x2.skill' at line 1

I apologize, my MySQL knowledge starts and ends at "google the error" so I don't really have anything constructive to add. I got this error when running 4_CLEANUP_AND_CONVERT_SPELLS.sql against azerothcore_characters DB, that DB contains a recent dump of my trinitycore characters DB.

I have a separate MySQL server from the AzerothCore and TrinityCore Servers, not sure if that is relevant. Let me know if I can provide anything more to assist in troubleshooting this error.

[WonkoMcSane]

::bump::

Same result and similar situation as OP.

Not sure if the problem is: a) my own lack of SQL experience b) a broken tool c) the method I tried using.

...more detail re: c) Using HeidiSQL I made dumps of my TCauth and TCchar DBs. Ran those against newly created AC databases (after dropping the original ac_auth and ac_characters databases that were created when I tested auth and worldserver function (felt I should test since I'm building off the OpenSSL3 PR). Previous steps of the conversion tool seem to have completed without error. Tried completing the conversion anyway, but step5 or step6 also errored. Authserver errored hard when I tried to run it.

[bigyong]

FIX

diff --git a/3_ALTER_TABLES.sql b/3_ALTER_TABLES.sql index 6860d01..9c43727 100644 --- a/3_ALTER_TABLES.sql +++ b/3_ALTER_TABLES.sql @@ -64,4 +64,6 @@ ALTER TABLE character_aura DROP COLUMN applyResilience; ALTER TABLE channels DROP COLUMN bannedList;

-- +ALTER TABLE corpse DROP PRIMARY KEY; ALTER TABLE corpse DROP COLUMN corpseGuid; +ALTER TABLE corpse ADD PRIMARY KEY (guid); diff --git a/4_CLEANUP_AND_CONVERT_SPELLS.sql b/4_CLEANUP_AND_CONVERT_SPELLS.sql index 8c89df1..95417a1 100644 --- a/4_CLEANUP_AND_CONVERT_SPELLS.sql +++ b/4_CLEANUP_AND_CONVERT_SPELLS.sql @@ -59,7 +59,7 @@ UPDATE character_skills SET VALUE=MAX WHERE VALUE>MAX; -- first delete skill SET @cnt := 0; SET @prevguid := 0; -DELETE s FROM character_skills s JOIN ((SELECT guid, skill FROM ((SELECT IF(@prevguid <> cs.guid, @cnt := 1, @cnt := @cnt+1) AS cnt, (@prevguid := guid) AS guid, cs.skill AS skill FROM character_skills cs JOIN __profession_skill t ON cs.skill = t.skill AND t.rank=6 ORDER BY cs.guid, cs.skill) X) WHERE cnt>2) x2) ON s.guid = x2.guid AND s.skill = x2.skill; +DELETE s FROM character_skills s JOIN ( SELECT x.guid, x.skill FROM (SELECT IF(@prevguid <> cs.guid, @cnt := 1, @cnt := @cnt+1) AS cnt, (@prevguid := guid) AS guid, cs.skill AS skill FROM character_skills cs JOIN profession_skill t ON cs.skill = t.skill AND t.rank=6 ORDER BY cs.guid, cs.skill) x WHERE cnt>2 ) x2 ON s.guid = x2.guid AND s.skill = x2.skill; -- now delete main profession spells if skill is missing DELETE s FROM character_spell s JOIN profession_skill t ON s.spell = t.spell LEFT JOIN character_skills cs ON s.guid = cs.guid AND t.skill = cs.skill WHERE cs.guid IS NULL;