1

I use Mariaex lib to connect to mysql in an elixir project. But it does not handle local language correctly.

For example, When it save local text Dương Phi Phụng to mysql database, D??ng Phi Ph?ng was save in the db.

The default charset for Mariaex is utf8, but even when I set charset: "utf8" OR charset: "utf8mb4" in the config, it still did not work.

Can anyone help?

Mariaex version: 0.8.4 (cannot update to lastest version due to legacy issue)

db charset: utf8mb4

db collation: utf8mb4_unicode_ci

Khaino
  • 3,774
  • 1
  • 27
  • 36
  • in such cases you should open an issue on the github page – Daniel May 29 '20 at 07:53
  • I recommend you hack open Mariaex, stick in a few `IO.inspect` lines and see what's happening. Alternatively, the ecto/postgres is a very well tested route for db in elixir. – cjm2671 May 29 '20 at 18:59
  • Rather unbelievably, there's a question that looks like a similar issue, but relating to ecto/postgres: https://stackoverflow.com/questions/61946283/how-do-i-avoid-ecto-matching-incorrectly-when-comparing-binary-ids-with-in-a – cjm2671 May 29 '20 at 19:02
  • @cjm2671 I use `ecto prostgres` my main `postgres` database, but I use to connect a legacy `mysql` db. I do not have any issue with `ecto`. – Khaino May 31 '20 at 08:10
  • 1
    Can you verify that you are able to do a raw MySQL INSERT statement using the utf8 encoded text? I don't know if it applies to you, but I remember having encoding issues once with a MySQL database and the db charset/collation were correct, but I had to adjust the encodings for specific tables and columns (?) -- I don't remember now if that was a bug, but it might be worth verifying. – Everett Jun 01 '20 at 19:19
  • @Everett I tried raw INSERT statement, it did not work. ex. `INSERT INTO test (name) VALUES ('Dương Phi Phụng')`. Can you give me more details & example on how you adjust the encodings? – Khaino Jun 02 '20 at 02:21
  • That's progress! You've verified that this is a database issue (and not a language issue). I think this answer may be relevant: https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8 In my experience, the encoding defined at the db level didn't always get inherited by individual tables or columns, so you may need to modify the tables, e.g. `ALTER TABLE mytable CONVERT TO CHARACTER SET utf8;` (or use the `utf8mb4` encoding if available). – Everett Jun 02 '20 at 18:14
  • I'm pretty sure for MySQL you always want `utf8mb4` and never `utf8` – Jason Axelson Jun 03 '20 at 02:28
  • @Everett the `charcter_set_name` for the `col` is `utf8mb4`. The issue might not be database as it is correctly inserted when I use `myxql`. I think I should move to `ecto` with `Ecto.Adapters.MyXQL`. – Khaino Jun 05 '20 at 02:45

0 Answers0