0

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!

icewill
  • 23
  • 3
  • 1
    Unrelated but unless you are using a pre C++11 compiler, don't use `auto_ptr`, use `unique_ptr`. – Ted Lyngmo Jul 31 '19 at 04:25
  • @TedLyngmo thx for the first reply, the auto_ptr is used in the example of odb libray, i just copied from it! – icewill Jul 31 '19 at 04:39
  • I see. Just replace it with `unique_ptr` if you see it anywhere. [auto_ptr](https://en.cppreference.com/w/cpp/memory/auto_ptr) was deprecated in C++11 and completely removed in C++17. – Ted Lyngmo Jul 31 '19 at 04:42
  • Could it be a mismatch between client and server versions? The doc about [charset-connection](https://dev.mysql.com/doc/refman/5.5/en/charset-connection.html) seems to indicate that it could do a fallback to the default (utf8 with max 3 bytes) in such cases. – Ted Lyngmo Jul 31 '19 at 04:52
  • @TedLyngmo i replaced the connector with the library and headers shipped with mysql 5.7, and the problem remains, anyway thx for your kind help! – icewill Jul 31 '19 at 05:55
  • You're welcome. I'm only guessing but `character_set_connection` should probably be `utf8mb4` for it to work, so the solution is probably in that area somewhere. – Ted Lyngmo Jul 31 '19 at 05:59

1 Answers1

0

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!

icewill
  • 23
  • 3