search

r-o-b-o-t-o / azerothcore-armory

A website to view your AzerothCore server's characters
MIT License
47 stars 23 forks source link

Docker configuration clarification, Auth database errors #36

Open meteokr opened 1 week ago

meteokr commented 1 week ago

Your question

I've got this servier running in Docker, however when I open the page I get this error: DataTables warning: table id=results - Ajax error. For more information about this error, please see http://datatables.net/tn/7

Logs from docker show:

[INFO]: Loading config...
[INFO]: Loading data files...
[INFO]: Connecting to databases...
[INFO]: Starting server...
[INFO]: Server is listening on 0.0.0.0:48733.
[HTTP]: GET / 200 - ID 1ba72625-e80a-4bd9-a754-b8edcb746d05 -
IP 10.88.0.1 - 24.532 ms
[HTTP]: GET /css/armory.css 200 - ID ddcc65dc-5630-4573-8f23-e
19b172b533c - IP 10.88.0.1 - 0.893 ms
[HTTP]: GET /css/bulma.min.css 200 - ID ed369447-b01f-4e2d-acb
1-0694622ee00f - IP 10.88.0.1 - 4.081 ms
[HTTP]: GET /css/index.css 304 - ID 7f5bbdd0-25ac-4e08-9295-a2
0325360335 - IP 10.88.0.1 - 0.271 ms
[HTTP]: GET /css/datatables.css 304 - ID 2154c835-1aee-49a5-92
6f-15f8cf4161be - IP 10.88.0.1 - 0.208 ms
[HTTP]: GET /favicon.ico 404 - ID 8b150911-12ca-4a7c-9468-b528
e5d51079 - IP 10.88.0.1 - 14.380 ms
[ERROR]: Error on request 2bc07a24-c9fa-42cd-8a94-797d1778910e. Error: SELECT command denied to user 'acore'@'10.88.0.1' for table `acore_auth`.`accou
nt_access`
    at PromisePool.query (/node_modules/mysql2/promise.js:341:22)
    at DataTablesSsp.<anonymous> (/src/armory/DataTablesSsp.ts:207:30)
    at step (/build/armory/DataTablesSsp.js:33:23)
    at Object.next (/build/armory/DataTablesSsp.js:14:53)
    at /build/armory/DataTablesSsp.js:8:71
    at new Promise (<anonymous>)
    at __awaiter (/build/armory/DataTablesSsp.js:4:12)
    at DataTablesSsp.run (/build/armory/DataTablesSsp.js:159:16)
    at IndexController.<anonymous> (/src/armory/controllers/IndexController.ts:58:67)
    at step (/build/armory/controllers/IndexController.js:44:23)
[2024-09-04 03:07:34:734] [HTTP]: GET /search?draw=1&columns%5B0%5D%5Bdata%5D=0&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=true&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=1&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=true&columns%5B1%5D%5Borderable%5D=true&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=2&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=true&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=3&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=false&columns%5B3%5D%5Borderable%5D=true&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=4&columns%5B4%5D%5Bname%5D=&columns%5B4%5D%5Bsearchable%5D=false&columns%5B4%5D%5Borderable%5D=true&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=5&columns%5B5%5D%5Bname%5D=&columns%5B5%5D%5Bsearchable%5D=false&columns%5B5%5D%5Borderable%5D=true&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&order%5B0%5D%5Bcolumn%5D=0&order%5B0%5D%5Bdir%5D=asc&start=0&length=10&search%5Bvalue%5D=&search%5Bregex%5D=false&_=1725419254293 500 - ID 2bc07a24-c9fa-42cd-8a94-797d1778910e - IP 10.88.0.1 - 24.365 ms

The Docker logs lead me to believe that there is a problem with the database, however, I can log into the database from within the container no problem using this command to verify the credientials are correct:

mysql -u $ACORE_ARMORY_REALMS__0__AUTH_DATABASE__USER -p$ACORE_ARMORY_REALMS__0__AUTH_DATABASE__PASSWORD -h $ACORE_ARMORY_REALMS__0__AUTH_DATABASE__HOST -P $ACORE_ARMORY_REALMS__0__AUTH_DATABASE__PORT $ACORE_ARMORY_REALMS__0__AUTH_DATABASE

My configs look like this, with basically all defaults to test that it works:

              ACORE_ARMORY_AOWOW_URL="https://wowgaming.altervista.org/aowow";
              ACORE_ARMORY_WEBSITE_URL="https://mywebsite.com";
              ACORE_ARMORY_WEBSITE_NAME="My Website";
              ACORE_ARMORY_WEBSITE_ROOT="";
              ACORE_ARMORY_IFRAME_MODE__ENABLED="0";
              ACORE_ARMORY_IFRAME_MODE__URL="https://mywebsite.com/armory";
              ACORE_ARMORY_LOAD_DBCS="1";
              ACORE_ARMORY_HIDE_GAME_MASTERS="1";
              ACORE_ARMORY_TRANSMOG_MODULE="0";
              ACORE_ARMORY_USE_ZAM_CDN="0";
              ACORE_ARMORY_REALMS__0__NAME="AzerothCore";
              ACORE_ARMORY_REALMS__0__REALM_ID="1";
              ACORE_ARMORY_REALMS__0__AUTH_DATABASE="acore_auth";
              ACORE_ARMORY_REALMS__0__AUTH_DATABASE__HOST="AUTHDBHOST";
              ACORE_ARMORY_REALMS__0__AUTH_DATABASE__PORT="3056";
              ACORE_ARMORY_REALMS__0__AUTH_DATABASE__USER="acore";
              ACORE_ARMORY_REALMS__0__AUTH_DATABASE__PASSWORD="XXXXXXXXXXXXXX";
              ACORE_ARMORY_REALMS__0__CHARACTERS_DATABASE__HOST="CHARDBHOST";
              ACORE_ARMORY_REALMS__0__CHARACTERS_DATABASE__PORT="3058";
              ACORE_ARMORY_REALMS__0__CHARACTERS_DATABASE__USER="acore";
              ACORE_ARMORY_REALMS__0__CHARACTERS_DATABASE__PASSWORD="XXXXXXXXX";
              ACORE_ARMORY_REALMS__0__CHARACTERS_DATABASE__DATABASE="acore_characters";
              ACORE_ARMORY_WORLD_DATABASE__HOST="WORLDBHOST"
              ACORE_ARMORY_WORLD_DATABASE__PORT="3059";
              ACORE_ARMORY_WORLD_DATABASE__USER="acore";
              ACORE_ARMORY_WORLD_DATABASE__PASSWORD="XXXXXXXXXXXXXXXX";
              ACORE_ARMORY_WORLD_DATABASE__DATABASE="acore_world";
              ACORE_ARMORY_DB_QUERY_TIMEOUT="10000";

There is only a setting: "ACORE_ARMORY_REALMS0AUTH_DATABASE" but the example env file doesn't have a place to put the host, port, user, pass so I added them in using the same schema as the other database configurations, but it seems that's not the case? Where am I supposed to put the password and other settings for the auth database? Or am I just totally misunderstanding the architecture of these settings?

I'm not at all familiar with TypeScript, but a basic look into the code @./src/armory/Armory.ts:74

        this.logger.info("Connecting to databases...");
        this.worldDb = createPool(this.config.worldDatabase);
        for (const realm of this.config.realms) {
            this.charsDbs[realm.name.toLowerCase()] = createPool(realm.charactersDatabase);
        }

This looks to only be actually connecting to the the Character and World databases. So this check isn't validating the Auth database anyway, if the server is supposed to also use the Auth database, shouldn't it be checked during this for loop?

Thank you for the assistance. Please let me know if any other logs would be useful, or if I'm missing some documentation somewhere.

r-o-b-o-t-o commented 1 week ago

I see your auth and char databases are on two separate hosts (AUTHDBHOST and CHARDBHOST) The auth database is accessed through a SQL JOIN which doesn't support querying across several hosts. Make sure both databases are on the same server and that your ACORE_ARMORY_REALMS__0__CHARACTERS_DATABASE__USER has read access on the auth db.

You can remove these, they won't have any effect:

ACORE_ARMORY_REALMS__0__AUTH_DATABASE__HOST="AUTHDBHOST";
ACORE_ARMORY_REALMS__0__AUTH_DATABASE__PORT="3056";
ACORE_ARMORY_REALMS__0__AUTH_DATABASE__USER="acore";
ACORE_ARMORY_REALMS__0__AUTH_DATABASE__PASSWORD="XXXXXXXXXXXXXX";
meteokr commented 1 week ago

Got it, so the intended use is one auth db per realm then? I am currently running a separate auth db because I have multiple realms on different hosts that the auth server points to. I expect that changing this to support a discrete auth server would be a breaking change then, as that would be changing every call to the auth db. I could attempt this myself, but would that be mergable functionality, or would that be an unsupported use case?

r-o-b-o-t-o commented 1 week ago

Ideally I should have split the queries into two to allow for different database hosts, but the JOIN is just so convenient 😛 The auth db is only used for the hideGameMasters feature, so the fix shouldn't be too huge (it's used in 3 places).

Also if you want a hackfix in the meantime you can just disable hideGameMasters and edit this query so that it doesn't use the auth db. It would look something like this:

const [rows] = await this.armory.getCharactersDb(realm.name).query({
    sql: `
        SELECT \`characters\`.\`guid\`, \`characters\`.\`name\`, \`race\`, \`class\`, \`gender\`, \`level\`, \`skin\`, \`face\`, \`hairStyle\`, \`hairColor\`, \`facialStyle\`, \`playerFlags\`, \`online\`, \`guild\`.\`name\` AS \`guild\`
        FROM \`characters\`
        LEFT JOIN \`guild_member\` ON \`guild_member\`.\`guid\` = \`characters\`.\`guid\`
        LEFT JOIN \`guild\` ON \`guild\`.\`guildid\` = \`guild_member\`.\`guildid\`
        WHERE ${where}
    `,
    values: [character],
    timeout: this.armory.config.dbQueryTimeout,
});
meteokr commented 1 week ago

Thank you so much for the guidance! I made these changes (sorry for the formatting):

diff --git a/src/armory/controllers/CharacterController.ts b/src/armory/controllers/CharacterController.ts
index cbef2b8..fa6ed64 100644
--- a/src/armory/controllers/CharacterController.ts
+++ b/src/armory/controllers/CharacterController.ts
@@ -342,20 +342,42 @@ export class CharacterController {

        private async getCharacterData(realm: IRealmConfig, character: string | number): Promise<ICharacterData> {
                const where = typeof character === "string" ? "LOWER(`characters`.`name`) = LOWER(?)" : "`characters`.`guid` = ?";
-               const [rows] = await this.armory.getCharactersDb(realm.name).query({
-                       sql: `
-                               SELECT \`characters\`.\`guid\`, \`characters\`.\`name\`, \`race\`, \`class\`, \`gender\`, \`level\`, \`skin\`, \`face\`, \`hairStyle\`, \`hairColor\`, \`facialStyle\`, \`playerFlags\`, \`online\`, \`guild\`.\`name\` AS \`guild\`
-                               FROM \`characters\`
-                               LEFT JOIN \`guild_member\` ON \`guild_member\`.\`guid\` = \`characters\`.\`guid\`
-                               LEFT JOIN \`guild\` ON \`guild\`.\`guildid\` = \`guild_member\`.\`guildid\`
-                               LEFT JOIN \`${realm.authDatabase}\`.\`account_access\` ON \`account_access\`.\`id\` = \`characters\`.\`account\` AND \`account_access\`.\`RealmID\` IN (-1, ${realm.realmId}) AND \`account_access\`.\`gmlevel\` > 0
-                               WHERE
-                                       ${where}
-                                       AND (\`account_access\`.\`id\` IS NULL OR ? = 0)
-                       `,
-                       values: [character, this.armory.config.hideGameMasters ? 1 : 0],
-                       timeout: this.armory.config.dbQueryTimeout,
-               });
+                const baseQuery = `
+                SELECT \`characters\`.\`guid\`, \`characters\`.\`name\`, \`race\`, \`class\`, \`gender\`, \`level\`, \`skin\`, \`face\`, \`hairStyle\`, \`hairColor\`, \`facialStyle\`, \`playerFlags\`, \`online\`, \`guild\`.\`name\` AS \`guild\`
+                        FROM \`characters\`
+                        LEFT JOIN \`guild_member\` ON \`guild_member\`.\`guid\` = \`characters\`.\`guid\`
+                        LEFT JOIN \`guild\` ON \`guild\`.\`guildid\` = \`guild_member\`.\`guildid\`
+                `;
+               let additionalSql = '';
+                const queryParams: any[] = [character];
+
+               if (this.armory.config.hideGameMasters) {
+                       additionalSql = `
+                           LEFT JOIN \`${realm.authDatabase}\`.\`account_access\` ON \`account_access\`.\`id\` = \`characters\`.\`account\` AND \`account_access\`.\`RealmID\` IN (-1, ${realm.realmId}) AND \`account_access\`.\`gmlevel\` > 0
+                           WHERE
+                               ${typeof character === "string"
+                                   ? "LOWER(`characters`.`name`) = LOWER(?)"
+                                   : "`characters`.`guid` = ?"
+                               }
+                               AND (\`account_access\`.\`id\` IS NULL OR ? = 0)
+                       `;
+                       queryParams.push(1); // Hide GMs
+                   } else {
+                       additionalSql = `
+                           WHERE
+                               ${typeof character === "string"
+                    ? "LOWER(`characters`.`name`) = LOWER(?)"
+                                : "`characters`.`guid` = ?"
+                               }
+                       `;
+                         queryParams.push(0); // No GM filter
+                }
+                const sql = baseQuery + additionalSql;
+                const [rows] = await this.armory.getCharactersDb(realm.name).query({
+                       sql,
+                       values: queryParams,
+                       timeout: this.armory.config.dbQueryTimeout,
+               });

                if ((rows as RowDataPacket[]).length === 0) {
                        return null;

Essentially I just split the query into a base + additional query. Then perform the query itself after its constructed. This way when hideGameMasters is false, the authdb is never queried in the first place, which should have a performance benefit in addition to fixing this issue. Though to witness this benefit would be pretty rare, as you would need a server with a ton of characters, and also not be filtering GMs. This /should/ be a non-breaking change as the code path for the GM filter enabled should be exactly the same. The server now works perfectly, with this change and the setting change. I would still like the ability to filter GMs, but that will require more fiddling as you said, to actually make a separate db connection and handling the config changes.

Would it be possible for you to add this change?

EDIT: I've made a pull request https://github.com/r-o-b-o-t-o/azerothcore-armory/pull/37 if this is the recommended way to push a change.