search

eclipse-vertx / vertx-sql-client

High performance reactive SQL Client written in Java
Apache License 2.0
891 stars 201 forks source link

utf8 string appears as question mark (?) #444

Closed badrazizi closed 4 years ago

badrazizi commented 4 years ago

I am using version 3.8.2 and having some issues with utf8 string appears as question mark (?) in the database when inserting new record, but it's display old record correctly (inserted using phpmyadmin).

this how it's appeared.

Summery Title Time ID Image Story Link
??? ?????? ???? ???? ?? ????? ?????? ?????? ????... ??? ??? ????: La Noria 2019-11-02T05:03:08.884396 718 /2019/10/La-Noria-310x165.jpg ???? ????? ????? ?? ????? ??... /?p=718

here how i init MySQLPool

fun init(vertx: Vertx): MySQLPool {
    val connectOptions = MySQLConnectOptions()
      .setPort(Config.mysqlPort)
      .setHost(Config.mysqlHost)
      .setDatabase(Config.mysqlDatabase)
      .setUser(Config.mysqlUsername)
      .setPassword(Config.mysqlPassword)
      .setCharset("utf8") // tried utf8mb4 also same result
      .setCollation("utf8_general_ci") // also tried utf8mb4_general_ci

    val poolOptions = PoolOptions().setMaxSize(Config.maxPoolSize)

    return MySQLPool.pool(vertx, connectOptions, poolOptions)
}

mysql variables

Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8mb4
character_set_system utf8

table news

TABLE_COLLATION TABLE_NAME
utf8_general_ci news

mysql create table

 CREATE TABLE `news` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Title` text NOT NULL,
  `Image` text NOT NULL,
  `Link` mediumtext NOT NULL,
  `Summery` mediumtext NOT NULL,
  `Story` mediumtext NOT NULL,
  `Time` timestamp(6) NOT NULL DEFAULT current_timestamp(6) ON UPDATE current_timestamp(6),
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

aslo changed the type from text to varchar with same result

from

Name Type
Title text

to

Name Type
Title varchar(2048)
BillyYccc commented 4 years ago

Thanks for reporting, I will take a look

BillyYccc commented 4 years ago

@badrazizi Have you checked your collation/charset in your visualized database client?

badrazizi commented 4 years ago

Have you checked your collation/charset in your visualized database client?

@BillyYccc sorry i really don't know, i use phpmyadmin that come with xampp.

Mysql configuration

[client]
default-character-set=utf8

[mysqld]
character-set-server = utf8
collation-server = utf8_general_ci

[mysql]
default-character-set=utf8
BillyYccc commented 4 years ago

Can you give some sample rows to reproduce? Are you using text query or preparedQuery? and can you have a try with JDBC MySQL to see if it works in the same way as this client so that we can confirm whether this is a problem

badrazizi commented 4 years ago

sample rows

Summery Title Time ID Image Story Link
اعلن مطوري فيجول نوفل World End Economica الإعلان عن مشروع World End Economica 2019-11-01 19:00:17.000000 650 /2019/10/World_End_Economica.jpg اعلن مطوري فيجول نوفل World End Economica /?p=650

yes i use preparedQuery

query = "INSERT INTO `news` (`Title`, `Image`, `Link`, `Summery`, `Story`) VALUES (?, ?, ?, ?, ?)"
params = ArrayTuple(5)
params.addString(obj.getString("title"))
params.addString(obj.getString("image"))
params.addString(obj.getString("link"))
params.addString(obj.getString("summery"))
params.addString(obj.getString("story"))
sqlEngine.preparedQuery(query, params) { saved ->
  if(!saved.succeeded()) {
    println("Failed to save ${obj.getString("title")}")
  } else {
    val lastInsertId = saved.result().property(MySQLClient.LAST_INSERTED_ID)
    println("Added news with id: $lastInsertId")
  }
}

sure i will try JDBC MySQL client

badrazizi commented 4 years ago

@BillyYccc yes using JDBC MySQL client worked without error and insert new record correctly

gradle

implementation "io.vertx:vertx-jdbc-client:3.8.2"

JDBC Config

private val jdbcClientConfig = JsonObject()
    .put("url", "jdbc:${Config.sqlEngine}://${Config.mysqlHost}:${Config.mysqlPort}/${Config.mysqlDatabase}?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC")
    .put("user", Config.mysqlUsername)
    .put("password", Config.mysqlPassword)
    .put("driver_class", "com.mysql.cj.jdbc.Driver")
    .put("max_pool_size", 30)

query

query = "INSERT INTO `news` (`Title`, `Image`, `Link`, `Summery`, `Story`) VALUES (?, ?, ?, ?, ?)"
val params2 = JsonArray()
params2.add(obj.getString("title"))
params2.add(obj.getString("image"))
params2.add( obj.getString("link"))
params2.add(obj.getString("summery"))
params2.add(story)
sqlClient.queryWithParams(query, params2) { ar ->
  if(!ar.succeeded()) {
    println("Failed to save ${obj.getString("title")}")
  }
}

result

Summery Title Time ID Image Story Link
اعلن مطوري فيجول نوفل World End Economica الإعلان عن مشروع World End Economica 2019-11-01 19:00:17.000000 650 /2019/10/World_End_Economica.jpg اعلن مطوري فيجول نوفل World End Economica /?p=650
badrazizi commented 4 years ago

@BillyYccc any progress, i have not heard from in almost 3 days.

BillyYccc commented 4 years ago

can you try with adding useServerPrepStmts=true to the connection properties of the JDBC url so that the jdbc client uses server-side prepared queries and check that again? The MySQL JDBC client will use a client-side prepared statement by default which is different from reactive mysql client so the comparison does not make sense.

badrazizi commented 4 years ago

yes it does insert new record correctly with useServerPrepStmts=true

jdbc config

private val jdbcClientConfig = JsonObject()
    .put("url", "jdbc:${Config.sqlEngine}://${Config.mysqlHost}:${Config.mysqlPort}/${Config.mysqlDatabase}?useUnicode=true&useServerPrepStmts=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC")
    .put("user", Config.mysqlUsername)
    .put("password", Config.mysqlPassword)
    .put("driver_class", "com.mysql.cj.jdbc.Driver")
    .put("max_pool_size", 30)
BillyYccc commented 4 years ago

I have given another try in https://github.com/BillyYccc/reactive-mysql-client-issue444 but still could not reproduce the problem. I debug the network packets for both JDBC and reactive MySQL client and what they send to server is identical.

I notice you did not configure the characterEncoding property in JDBC, can you try with my reproducer and try again?

badrazizi commented 4 years ago

with or without characterEncoding jdbc still insert new record correctly, i have created a reproducer please give it a test here

badrazizi commented 4 years ago

@BillyYccc please test the reproducer i post above.

BillyYccc commented 4 years ago

I did more experiements but I could not reproduce the problem with using MySQLWorkbench and other clients. I start MySQL using docker in my reproducer project and here is my server charset config SHOW VARIABLES LIKE '%character%'

Variable_name | Value
-- | --
character_set_client | utf8mb4
character_set_connection | utf8mb4
character_set_database | utf8
character_set_filesystem | binary
character_set_results | utf8mb4
character_set_server | utf8
character_set_system | utf8
character_sets_dir | /usr/share/mysql/charsets/

can you try retrieving those rows inserted by reactive-mysql-client via JDBC or tools other than phpmyadmin(like MySQLWorkbench) like what I do in my reproducer and compare the results?

badrazizi commented 4 years ago

MySQLWorkbench SHOW VARIABLES LIKE '%character%'

character_set_client utf8mb4
character_set_connection utf8mb4
character_set_database utf8
character_set_filesystem binary
character_set_results utf8mb4
character_set_server utf8
character_set_system utf8
character_sets_dir C:\xampp\mysql\share\charsets\

Result from MySQLWorkbench

mysql

result from JDBC

[{
        "Summery": "كشفت مجلة شونين جمب عن إعلان مهم يخص مانجا Jujutsu Kaisen للمانجاكا جيجي أكوتامي سيتم الكشف عنه في العدد القادم من المجلة، كما تم الكشف كذلك عن حجز نطاق خاص بعنوان jujutsukaisen.jp ما يزيد من احتمالية حصول المانجا على أنمي خصوصًا و أن المجلد السابع من المانجا قد تم إصداره …",
        "Title": "حجز نطاق وإعلان مهم قادم لمانجا Jujutsu Kaisen، هل ستحصل المانجا على أنمي؟",
        "Time": "2019-11-14 19:00:04.000000"
    }, {
        "Summery": "تم الكشف عن عرض تشويقي للأنمي المأخوذ من لعبة الهواتف الذكية I★CHU والقادم بعنوان I★CHU Étoile Stage وكذلك عن طاقم العمل بالإضافة إلى الصورة بالأعلى. طاقم العمل الاستديو: Lay-duce. المخرج: هيتوشي نانبا (Golden Kamuy). منسق القصة: يوشيمي ناريتا. مصمم الشخصيات: مينا أوساوا (Given,) قصة اللعبة تدور في أكادمية تسمى Etoile …",
        "Title": "عرض والكشف عن طاقم العمل لأنمي I★CHU",
        "Time": "2019-11-14 19:00:07.000000"
    }, {
        "Summery": "???? ???? ????? ??? ?? ????? ??? ??? ????? Jujutsu Kaisen ????????? ???? ??????? ???? ????? ??? ?? ????? ?????? ?? ??????? ??? ?? ????? ???? ?? ??? ???? ??? ?????? jujutsukaisen.jp ?? ???? ?? ???????? ???? ??????? ??? ???? ?????? ? ?? ?????? ?????? ?? ??????? ?? ?? ?????? ?",
        "Title": "??? ???? ?????? ??? ???? ?????? Jujutsu Kaisen? ?? ????? ??????? ??? ?????",
        "Time": "2019-11-14 17:08:19.371199"
    }, {
        "Summery": "??? ?????? ???? ????? ???? ??????? ?? ???? ?cole des Nouvelles Images. ????? ??? ??46 ????? ????? ????? ?? 150 ??????? ????? ???? ???? ??? ???? ???????? ?? ??? ?????? ????? ????????.",
        "Title": "??? ???? ????: Hors Piste",
        "Time": "2019-11-14 17:08:19.503090"
    }, {
        "Summery": "???? ???? ?????? ?? ????? 23 ??? ????????? ??? ????? (???? Boku dake ga Inai Machi) ????? ????? ????? ?????? Mizutamari ni Ukabu Shima ???????? ?? ????? 24 ?? 26 ??????. ????? ????? ?????? ?? 44 ???? ???????? ??? ???????? ????? ? ???? ??? ???? ??????. ???? ?????: ???? ?????? ?",
        "Title": "???? Boku dake ga Inai Machi ???? ????? ?????",
        "Time": "2019-11-14 17:08:19.530617"
    }, {
        "Summery": "??? ?????? ?????? ?? ??? ???? ????? Kishibe Rohan wa Ugokanai ??????? ????? ?? ??????? ????????? ???? ????? ?? 6 ??? ???????? ?? 8 ??????. ?????? ?? ??????? ?? ????? ??????? ? ?????? ?????? 16 ?????? At a Confessional (????????) ??????? 10 ?????? The Run (?????) ? ???? ??? ????? ?",
        "Title": "??? ???? ?????? ???? ????? ??????? ?????? ??????? ?? Kishibe Rohan wa Ugokanai ?? ???? 2020",
        "Time": "2019-11-14 17:08:19.607746"
    }, {
        "Summery": "?? ????? ?? ??? ?????? ?????? ??????? ?? ???? ??????? ?????? I?CHU ??????? ?????? I?CHU ?toile Stage ????? ?? ???? ????? ???????? ??? ?????? ???????. ???? ????? ????????: Lay-duce. ??????: ?????? ????? (Golden Kamuy). ???? ?????: ?????? ??????. ???? ????????: ???? ?????? (Given,) ??? ?????? ???? ?? ??????? ???? Etoile ?",
        "Title": "??? ?????? ?? ???? ????? ????? I?CHU",
        "Time": "2019-11-14 17:08:19.619506"
    }, {
        "Summery": "???? ?? ????? ????? ?????? ??? ?????? ?????? ?????? ?????? ??? ?????? ?????? ?????? ?? ?????? ??? ???? ?????? ???????? ????? ?? ??? Dragon Ball Super Broly ???? ?? ????? ???? ??? ??????? ?????? ???? ???? ??? ???? ????? ?? ?????? ??? ??? ?? ???????? ???? ???? ?????.",
        "Title": "????? ?????? ????? ???????? ????? (DBS) ????? Dragon Ball FighterZ",
        "Time": "2019-11-14 17:08:21.533535"
    }, {
        "Summery": "",
        "Title": "????: GIANTS ????? League of Legends",
        "Time": "2019-11-14 17:08:21.643565"
    }, {
        "Summery": "??? ?????? ?????? ????? Yahari Ore no Seishun ?? ????? ??????? ?????? ?????? ???????? ??? ?????? ??????? ????? ????? ???? ?? ????? 2020. ??? ?? ????? ?? ????? ?????? ?????? ???? Yahari Ore no Seishun Love Comedy wa Machigatteiru: Kan ?????? 12 ????. ??? ?? ??????? ??? ?? ?????? ????? ?",
        "Title": "???? ?????? ?? ????? ??????? ?????? ?????? ?? ???? Yahari Ore no Seishun ?????? ???? ????? ?? ?????",
        "Time": "2019-11-14 17:08:21.908703"
    }
]

please note that retrieving data with reactive-mysql-client is correct the data is displayed correctly the issue is in saving new record

BillyYccc commented 4 years ago

@badrazizi thanks for the clues I see where the problem locates now.

BillyYccc commented 4 years ago

can you by the way check what your default charset is by calling java.nio.charset.Charset.defaultCharset().displayName()

badrazizi commented 4 years ago

it print windows-1252

badrazizi commented 4 years ago

i just got it working thanks to your comment by setting jvm default charset to utf8

System.setProperty("file.encoding", "UTF-8")
val charset = Charset::class.java.getDeclaredField("defaultCharset")
charset.isAccessible = true
charset.set(null,null)

but actually does not solve the issue since MySQLPool#pool does not respect MySQLConnectOptions()#setCharset configuration, also JDBC is working because i think is set to utf8 by default, or am i misunderstanding the code.

BillyYccc commented 4 years ago

Yes setting a default charset to UTF-8 seems to be a work around for now.

the charset and collation options are configured at the connection level but not really have and effects on charset for encoding, currently the encoding value is encoded in the default charset and I believe we need to improve this, I have posted an issue in https://github.com/eclipse-vertx/vertx-sql-client/issues/447.