[SOLVED] problem when inserting chinese characters into mysql via C++ ODB

problem when inserting chinese characters into mysql via C++ ODB

i am not a english speaker, pls be tolerant with my expression. When i insert some chinese characters into mysql 5.7 via odb, i got the error message "Incorrect string value: '\xD6\xD0\xB9\xFA' for column 'NATION' at row 1", i know it's the wrong setting of character set, i tried my efforts to fix the issue but all in vain.

the odb 2.4, mysql 5.7, mysql connector c6.1.11 and visual studio 2015 are used in my project, i already changed the mysql character set to utf8mb4. below is my mysql configuration file:

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect='SET NAMES utf8mb4'
skip-character-set-client-handshake = true
[mysql]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4

and it seems nothing wrong with the mysql server:

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_general_ci |
| collation_server         | utf8mb4_general_ci |
+--------------------------+--------------------+

mysql> show create table basininfo;
------------------------------------------------------------------------------------------------+

    | basininfo | CREATE TABLE `basininfo` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `BASIN_NAME` varchar(1000) DEFAULT NULL,
      `NATION` varchar(1000) DEFAULT NULL,  
      PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

i also tried change to mysql 8.0, hard coded the utf8mb4 character into the libodb-mysql libraray like below:

mysql_options(handle_, MYSQL_SET_CHARSET_NAME, "utf8mb4");
mysql_options(handle_, MYSQL_INIT_COMMAND, "SET NAMES utf8mb4");

but all of that cannot solve my problem.

the code piece induced the error is like below:

auto_ptr<odb::database> db(
    new odb::mysql::database(
    "news"     // database login name  
    , "news123" // database password  
    , "newsdb" // database name  
    , "localhost"
    , 3306
    , nullptr
    ,"utf8mb4"
)); 

transaction t(db->begin());         
BasinInfo binfo;
binfo.setNation("中国"); //chinese character insert into column Nation
db->persist(binfo);
t.commit();

the problem already spend my two days and make me desperate, pls help me to work out of that, really appreciate!

appendix information: if i query character set in mysql workbench, i got message different from the above, i dont know why, it's so weird.

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

Variable_name,Value
character_set_client,utf8
character_set_connection,utf8
character_set_database,utf8mb4
character_set_filesystem,binary
character_set_results,utf8
character_set_server,utf8mb4
character_set_system,utf8
collation_connection,utf8_general_ci
collation_database,utf8mb4_general_ci
collation_server,utf8mb4_general_ci

appendix infomation 2: i recreate the database with utf8 charset, and everything remains the same, how desperate!

Solution

i double checked the visual studio settings of utf8, and found the default setting is UTF-8 with signature, changed it to utf-8 without signature makes the problem gone!