mysql.createPool charset doesn't work

[asasinmode]

I'm using node mysql to connect to database and I have the following createPool setup

const db = mysql.createPool({
    host: process.env.host,
    user: process.env.username,
    password: process.env.password,
    database: process.env.database,
    multipleStatements: true,
    charset: 'UTF8MB4_GENERAL_CI'
});

Now if I use `SHOW VARIABLES LIKE 'character_set%' in phpmyadmin database page I get

character_set_client	utf8mb4	
character_set_connection	utf8mb4	
character_set_database	utf8	
character_set_filesystem	binary	
character_set_results	utf8mb4	
character_set_server	latin2	
character_set_system	utf8	
character_sets_dir	/usr/share/mysql/charsets/	

On the other hand, if I use same query in node server db.query("SHOW VARIABLES LIKE 'character_set%'", ...) it returns

[
  RowDataPacket {
    Variable_name: 'character_set_client',
    Value: 'latin2'
  },
  RowDataPacket {
    Variable_name: 'character_set_connection',
    Value: 'latin2'
  },
  RowDataPacket {
    Variable_name: 'character_set_database',
    Value: 'utf8'
  },
  RowDataPacket {
    Variable_name: 'character_set_filesystem',
    Value: 'binary'
  },
  RowDataPacket {
    Variable_name: 'character_set_results',
    Value: 'latin2'
  },
  RowDataPacket {
    Variable_name: 'character_set_server',
    Value: 'latin2'
  },
  RowDataPacket {
    Variable_name: 'character_set_system',
    Value: 'utf8'
  },
  RowDataPacket {
    Variable_name: 'character_sets_dir',
    Value: '/usr/share/mysql/charsets/'
  }
]

And I'd expect them to be something related to charset I've set in createPool function. The issue here is that special characters returned by my database turn to � and I looked for solutions but none of them seem to work and I'm starting to think this is the issue.

Also if I use query db.query("SET character_set_client = 'utf8';" + "SET character_set_connection = 'utf8';" + "SET character_set_results = 'utf8';" + "SET character_set_server = 'utf8';" + "SHOW VARIABLES LIKE 'character_set%';", ...) it returns what I'd expect

[
    RowDataPacket {
      Variable_name: 'character_set_client',
      Value: 'utf8'
    },
    RowDataPacket {
      Variable_name: 'character_set_connection',
      Value: 'utf8'
    },
    RowDataPacket {
      Variable_name: 'character_set_database',
      Value: 'utf8'
    },
    RowDataPacket {
      Variable_name: 'character_set_filesystem',
      Value: 'binary'
    },
    RowDataPacket {
      Variable_name: 'character_set_results',
      Value: 'utf8'
    },
    RowDataPacket {
      Variable_name: 'character_set_server',
      Value: 'utf8'
    },
    RowDataPacket {
      Variable_name: 'character_set_system',
      Value: 'utf8'
    },
    RowDataPacket {
      Variable_name: 'character_sets_dir',
      Value: '/usr/share/mysql/charsets/'
    }
  ]

And also if used as multipleStatements in front of select query I'm using to get text from database, db.query("TONS OF SET VARIABLES HERE;" + "SELECT STUFF", ...) the special characters don't turn to � anymore and it works as I think it should.

What am I missing? Why does createPool charset not affect my character_set variables? I also tried utf8, UTF8, utf8mb4, UTF8MB4 as charset.

[dougwilson]

I tested it locally and the charsrt for the pool works fine when I try it. But there is a way for the server to behave as your described: if your server is configured to ignore the client charset in the handshake https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_character-set-client-handshake

[asasinmode]

I tested it locally and the charsrt for the pool works fine when I try it. But there is a way for the server to behave as your described: if your server is configured to ignore the client charset in the handshake https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_character-set-client-handshake

I'm not too familiar with SQL, could you maybe elaborate how can I access/change/fix that with phpmyadmin panel?

[dougwilson]

That setting is set on your server. It would be set in the same location that is setting your server to have a default of latin2, which is definitely custom, as no mysql released has that as the default. It is usually in the server's cnf file (https://dev.mysql.com/doc/refman/8.0/en/option-files.html)

You can read more about how to configure the server here: https://dev.mysql.com/doc/refman/8.0/en/server-configuration.html

[asasinmode]

I messaged my hosting about it because I don't think I have access to neither console nor config files, but I don't understand how is setting charset in createPool different from manually querying set character_something = 'utf8' since the manual one seems to work. I assume it's because setting charset doesn't insert set character at the beginning of all of my queries and does something different.

[dougwilson]

It is two separate methods to change the charset in mysql server: as part of the handshake in in a query after connecting. Both of which can be enabled or disabled by the server. The charart configuration in this module sets the value in the handshake, but it is clear the mysql server you are connecting to is configured to ignore that for whatever reason. You can always instead perform the query if that is what your hosted mysql server is configured to require you to do.

I would suggest running a mysql server locally to try out the charaet parameter to see that it does indeed work, just that for a reason I cannot explain the entity who has set up the mysql server you are using does not allow that.

[dougwilson]

You can use the connection pool event to run SET queries on all your pool connections: https://github.com/mysqljs/mysql#connection

[asasinmode]

Thanks a lot for detailed answer. It indeed looks like the issue is with my database hosting, so I'll go ahead and ask them about it.