Jump to content
TrinityCore

SQL Errors On AuthServer Launch (Attempting To Migrate Data To Ubuntu From Windows)


Recommended Posts

Hi all,

Sorry ahead of time if this is a noob-ish question as I'm fairly new to dealing with SQL. I've exported all the data from my Windows SQL server that functions normally and attempted to import to ubuntu after all previous setup. I've ensure I'm operating off the 5.7 revision of MySQL already just to make sure.

When I try to kick up the Auth server I'm getting a few errors with the database and I've referred to the old sql database to ensure they match 1 for 1 with the Windows version so I can confirm that the import was at the very least successful in gather all the needed data. This is what I'm currently seeing upon attempting to launch the Auth server and was hoping to get some assistance with this. I can tell it's not liking perhaps some of the format that the db is in or something of that sort but any guidance is helpful. Thank you ahead of time.

---

  1. Using configuration file /home/trinitycore/server/etc/authserver.conf.
  2. Using SSL version: OpenSSL 1.1.1f 31 Mar 2020 (library: OpenSSL 1.1.1f 31 Mar 2020)
  3. Using Boost version: 1.71.0
  4. Automatic database updates are disabled for all databases!
  5. Opening DatabasePool 'auth'. Asynchronous connections: 1, synchronous connections: 1.
  6. MySQL client library: 10.3.25
  7. MySQL server ver: 5.7.34
  8. Connected to MySQL database at 127.0.0.1
  9. MySQL client library: 10.3.25
  10. MySQL server ver: 5.7.34
  11. Connected to MySQL database at 127.0.0.1
  12. DatabasePool 'auth' opened successfully. 2 total connections running.
  13. In mysql_stmt_prepare() id: 10, sql: "UPDATE account SET salt = ?, verifier = ? WHERE id = ?"
  14. Unknown column 'salt' in 'field list'
  15. In mysql_stmt_prepare() id: 12, sql: "SELECT a.id, a.username, a.locked, a.lock_country, a.last_ip, a.failed_logins, ab.unbandate > UNIX_TIMESTAMP() OR ab.unbandate = ab.bandate, ab.unbandate = ab.bandate, aa.SecurityLevel, a.totp_secret, a.salt, a.verifier FROM account a LEFT JOIN account_access aa ON a.id = aa.AccountID LEFT JOIN account_banned ab ON ab.id = a.id AND ab.active = 1 WHERE a.username = ?"
  16. Unknown column 'aa.SecurityLevel' in 'field list'
  17. In mysql_stmt_prepare() id: 13, sql: "SELECT a.id, UPPER(a.username), a.locked, a.lock_country, a.last_ip, a.failed_logins, ab.unbandate > UNIX_TIMESTAMP() OR ab.unbandate = ab.bandate, ab.unbandate = ab.bandate, aa.SecurityLevel, a.session_key_auth FROM account a LEFT JOIN account_access aa ON a.id = aa.AccountID LEFT JOIN account_banned ab ON ab.id = a.id AND ab.active = 1 WHERE a.username = ? AND a.session_key_auth IS NOT NULL"
  18. Unknown column 'aa.SecurityLevel' in 'field list'
  19. In mysql_stmt_prepare() id: 17, sql: "SELECT a.id, a.session_key_auth, a.last_ip, a.locked, a.lock_country, a.expansion, a.mutetime, a.locale, a.recruiter, a.os, aa.SecurityLevel, ab.unbandate > UNIX_TIMESTAMP() OR ab.unbandate = ab.bandate, r.id FROM account a LEFT JOIN account_access aa ON a.id = aa.AccountID AND aa.RealmID IN (-1, ?) LEFT JOIN account_banned ab ON a.id = ab.id AND ab.active = 1 LEFT JOIN account r ON a.id = r.recruiter WHERE a.username = ? AND a.session_key_auth IS NOT NULL ORDER BY aa.RealmID DESC LIMIT 1"
  20. Unknown column 'a.session_key_auth' in 'field list'
  21. In mysql_stmt_prepare() id: 47, sql: "DELETE FROM account_access WHERE AccountID = ?"
  22. Unknown column 'AccountID' in 'where clause'
  23. In mysql_stmt_prepare() id: 48, sql: "DELETE FROM account_access WHERE AccountID = ? AND (RealmID = ? OR RealmID = -1)"
  24. Unknown column 'AccountID' in 'where clause'
  25. In mysql_stmt_prepare() id: 49, sql: "INSERT INTO account_access (AccountID, SecurityLevel, RealmID) VALUES (?, ?, ?)"
  26. Unknown column 'AccountID' in 'field list'
  27. In mysql_stmt_prepare() id: 51, sql: "SELECT SecurityLevel FROM account_access WHERE AccountID = ? AND (RealmID = ? OR RealmID = -1) ORDER BY RealmID DESC"
  28. Unknown column 'SecurityLevel' in 'field list'
  29. In mysql_stmt_prepare() id: 68, sql: "INSERT INTO logs_ip_actions (account_id, character_guid, realm_id, type, ip, systemnote, unixtime, time) VALUES (?, ?, ?, ?, (SELECT last_ip FROM account WHERE id = ?), ?, unix_timestamp(NOW()), NOW())"
  30. Unknown column 'realm_id' in 'field list'
  31. In mysql_stmt_prepare() id: 69, sql: "INSERT INTO logs_ip_actions (account_id, character_guid, realm_id, type, ip, systemnote, unixtime, time) VALUES (?, ?, ?, ?, (SELECT last_attempt_ip FROM account WHERE id = ?), ?, unix_timestamp(NOW()), NOW())"
  32. Unknown column 'realm_id' in 'field list'
  33. In mysql_stmt_prepare() id: 70, sql: "INSERT INTO logs_ip_actions (account_id, character_guid, realm_id, type, ip, systemnote, unixtime, time) VALUES (?, ?, ?, ?, ?, ?, unix_timestamp(NOW()), NOW())"
  34. Unknown column 'realm_id' in 'field list'
  35. In mysql_stmt_prepare() id: 71, sql: "INSERT INTO logs_ip_actions (account_id, character_guid, realm_id, type, ip, systemnote, unixtime, time) VALUES (?, 0, 0, 1, ?, ?, unix_timestamp(NOW()), NOW())"
  36. Unknown column 'realm_id' in 'field list'
  37. In mysql_stmt_prepare() id: 80, sql: "INSERT INTO secret_digest (id, digest) VALUES (?,?)"
  38. Table 'auth.secret_digest' doesn't exist
  39. In mysql_stmt_prepare() id: 81, sql: "DELETE FROM secret_digest WHERE id = ?"
  40. Table 'auth.secret_digest' doesn't exist
  41. In mysql_stmt_prepare() id: 83, sql: "UPDATE account SET totp_secret = ? WHERE id = ?"
  42. Unknown column 'totp_secret' in 'field list'
  43. Closing down DatabasePool 'auth'.
  44. Asynchronous connections on DatabasePool 'auth' terminated. Proceeding with synchronous connections.
  45. All connections on DatabasePool 'auth' closed.
  46. Could not prepare statements of the Login database, see log for details.
  47. Closing down DatabasePool 'auth'.
  48. Asynchronous connections on DatabasePool 'auth' terminated. Proceeding with synchronous connections.
  49. All connections on DatabasePool 'auth' closed.
  50. ---

 

Link to comment
Share on other sites

This is because mysql stores table and column names as lowercase on windows (accountid, securitylevel) but on linux they are case sensitive (AccountID, SecurityLevel)

Make sure that your exported windows database file has correct names in them (use mysqldump, not any other 3rd party export tool)

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By jaydee2k
      Hello,
      I compiled the latest source of the 3.3.5 core on Windows 10 - 64bit. On my desktop machine everything is running fine, but
      when i copy my server folder to my laptop both server exes are not even opening. When i double click on em the blue circle rotates next to my mouse and 
      thats it. The mysql server is running fine on my laptop too. The laptop has Windows 10 - 64bit, the needed vcredist installed and the processor has SSE2.
      I'm not even getting an error log because of this which makes it really hard to figure out what the problem is....
      Thanks for your help and patience! 
       
      EDIT: I need Boost, git, openssl, cmake and so on only to compile right? Not to run the compiled exe files or am I wrong?
      I don't want to install all these things on my laptop, he is slow enough. ^^
    • By bbgun21
      Hey everyone.
      I've got a problem concerning my compiled executable files not running properly.
      These are:
      -Authserver.exe
      -Worldserver.exe
      -mapextractor.exe
      -mmaps_generator.exe
      -vmap4extractor.exe
      -vmap4assembler.exe
      After running any of these files none of them open properly.
      What I mean by this is that when I double-click on a file, nothing happens, there is no window popup or anything for me to see.
      What I do see however is in my Task Manager processes of these files being there (2 or 3 from just one opening) that can't be terminated and these processes now block me from trying again launching the applications without restarting my PC.
      I've noticed that after I recompile the my things again through Visual Studio that the .exe files work once or twice (I tested only Authserver and Worldserver and they opened normally and were running normally), but after a few time same problem is there (the above mentioned one).
      I don't know is this because I'm trying to compile it in x64 bit, because when I try x32 bit, errors appear because MySQL is x64 bit.
      Thanks in forward.
       
      EDIT: I followed everything by the letter in the guide(s) here:
      https://trinitycore.atlassian.net/wiki/display/tc/Requirements
       
      EDIT 2:
      My PC:
      CPU: i5 2500K @ 4.5Ghz
      GPU: GTX 980 Ti
      SSD: Samsung 850 EVO
      RAM: Patriot 8GB (2x4GB) 1600 MHz
    • By Kr4ll
      Hey, first of all, sorry if this is the wrong place for my question, i didn't know where should i try to get help..
      I've made a new NPC on my server (I'm using Jeutie's Repack), i've made a new equipment in creature_equip_template, and when i'm trying to write my equipment id under the column "equipment_id" it always says:
      SQL Error (1264): Out of range value for column 'equipment_id' at row 1
      I tried to figure out what's the max range value, and it looks like it's 127. If i write 128  or more the error appears.
      Any idea how can i fix it, or how can i change the max value?
      I had already used Jeutie's Repack - Trinitycore before a few years ago, and as I remember there was no problem like this at all..

×
×
  • Create New...