Setting collation_connection using libmysqlclient C API

Question

I am using libmysqlclient to write a C program to inserts data (surprisingly) into a database. Character set of the table that code inserts into and it's collation are:

CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

In my C program I used mysql_set_character_set() in order to change the character set; the problem is that this function only changes character set and not collation. If I query character set and collation using mysql_get_character_set_info the result is:

char-set=utf8mb4, collation=utf8mb4_general_ci

According to MySQL documentation:

SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES 'cp1251' tells the server, “future incoming messages from this client are in character set cp1251.” It also specifies the character set that the server should use for sending results back to the client.

So I decided to change connection collation using mysql_options and MYSQL_INIT_COMMAND:

SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';

The resulting function call looks like this (I called this before mysql_real_connect()):

if(mysql_options(conn, MYSQL_INIT_COMMAND, 
        "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_unicode_ci';")) {
    // log the errors and do proper actions
}

But it seems that this commands are not able to change character set and collation! Because now if I query character set using mysql_get_character_set_info() the result is:

char-set=latin1, collation=latin1_swedish_ci

The interesting thing here is that at the MySQL side if I enable logs and execute the program with MYSQL_INIT_COMMAND commands execute! But mysql_get_character_set_info() does not show this! Again according to libmysql's documentation:

int mysql_set_character_set(MYSQL *mysql, const char *csname)

This function is used to set the default character set for the current connection. The string csname specifies a valid character set name. The connection collation becomes the default collation of the character set. This function works like the SET NAMES statement, but also sets the value of mysql->charset, and thus affects the character set used by mysql_real_escape_string()

I want to change character set and collation in a way that mysql_real_escape_string() gets affected. What is the correct way to do that?

Answer 1

The init command is not executed by the root user. (More specifically, by any SUPER user.) Recommend you create a different user for your application.

Don't forget that your table columns should also be declared CHARACTER SET utf8mb4.