Invalid utf-8 byte sequences in varchar

[chulkilee]

I have an invalid utf-8 string in varchar, and mariaex returns it as binary, not string.

• mariaex: 0.9.1
• proxysql: 1.4.12 (severalnines/proxysql:1.4.12 docker image)
• table: InnoDB / CHARSET=utf8 COLLATE=utf8_unicode_ci
• column: varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci

Query:

query = "SELECT name, LENGTH(name), BIT_LENGTH(name) FROM my_table WHERE id = 1;"

When talking to ProxySQL:

{:ok, %Mariaex.Result{
    columns: ["name", "HEX(name)", "LENGTH(name)", "BIT_LENGTH(name)"],
    connection_id: #PID<0.458.0>,
    last_insert_id: nil,
    num_rows: 1,
    rows: [[<<199, 224, 226>>, "C387C3A0C3A2", 6, 48]]
  }
}

When talking directly to MySQL:

Mariaex.query(pid, query)

{:ok, %Mariaex.Result{
    columns: ["name", "HEX(name)", "LENGTH(name)", "BIT_LENGTH(name)"],
    connection_id: #PID<0.1256.0>,
    last_insert_id: nil,
    num_rows: 1,
    rows: [["Çàâ", "C387C3A0C3A2", 6, 48]]
  }
}

Examining the value:

str = "Çàâ"
raw = <<199, 224, 226>>

String.valid?(raw)
# => false

byte_size(str)
# => 6

byte_size(raw)
# => 3

to_charlist(str)
# => [199, 224, 226]

Base.decode16("C387C3A0C3A2")
# => {:ok, "Çàâ"}

[wojtekmach]

@chulkilee hello, could you provide a bit more context what the problem is? Perhaps you can reproduce this problem with just Mariaex function calls?

[chulkilee]

@wojtekmach updated the issue description with more tests. I found that it only happens when I talk to ProxySQL. If I run the query directly MySQL, it works. Interesting..

I don't know much details how ProxySQL handles it. Probably it's a ProxySQL bug?

[IanLuites]

We actually experienced the exact same issue.

UTF issues when Mariaex connects through ProxySQL.

Unable to read existing UTF fields and when writing writes garbled latin1.

[StanAnsems]

It looks like we have the same problem, for now we found a workaround that works for us. we added BINARY before the field we select, update or insert:

Mariaex.query("SELECT BINARY data FROM mytable WHERE id = ?", [id])
Mariaex.query("UPDATE mytable SET data = BINARY ?, [data])
Mariaex.query("INSERT INTO mytable (id, data) VALUES (?,BINARY ?), [id,data])

Maybe this helps for finding the issue or moving forward for now?