Ecto database and tables set for utf8mb4, how do I get the connection to match?

I have a chat app that is giving an error message when a user posts 4 byte emojis. I verified that the database, table and column are all set to utf8mb4. I can tell that the character_set_connection is set to utf8 by default, so I want to override that. We are using MyXQL and I thought that I could add this to my config:

+config :myxql,
+  charset: "utf8mb4",
+  collation: "utf8mb4_unicode_ci"

It didn’t help. Are there any suggestions?

The actual error message is:

** (exit) an exception was raised:
    ** (MyXQL.Error) (1366) Incorrect string value: '\xF0\x9F\x8F\x86\x0A\x0A...' for column `my_db`.`messages`.`body` at row 1
        (ecto_sql 3.6.2) lib/ecto/adapters/myxql.ex:270: Ecto.Adapters.MyXQL.insert/6
        (ecto 3.7.1) lib/ecto/repo/schema.ex:744: Ecto.Repo.Schema.apply/4
        (ecto 3.7.1) lib/ecto/repo/schema.ex:367: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
        (ecto 3.7.1) lib/ecto/multi.ex:716: Ecto.Multi.apply_operation/5
        (elixir 1.13.3) lib/enum.ex:2396: Enum."-reduce/3-lists^foldl/2-0-"/3
        (ecto 3.7.1) lib/ecto/multi.ex:690: anonymous fn/5 in Ecto.Multi.apply_operations/5
        (ecto_sql 3.6.2) lib/ecto/adapters/sql.ex:1017: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
        (db_connection 2.4.0) lib/db_connection.ex:1512: DBConnection.run_transaction/4

What do I need to do to get Ecto/MyXQL to connect to the database with utf8mb4? Or is this error message indicating something else?


I added charset: "utf8mb4", collation: "utf8mb4_unicode_ci", to the configuration for my repository, and judging by the errors from myxql when I set the collation just to “utf8mb4”, I’m in the right place. So it appears that the connection should now SET NAMES "utf8mb4", collation "utf8mb4_unicode_ci" which seems like the ideal operation per the mysql docs, but I get the same error. What other setting could I need? And how do I change it?

Likely another collation. When I dealt with MySQL years ago we had to first use one non-obvious collation to even have our code detect the right Unicode characters and then after we had everything under control, we made a migration to change the collation to proper Unicode (and not a half-baked implementation like MySQL had a long time and many people grandfathered into their projects).

Sorry, I don’t remember the details, it was a while ago. Just giving you a vector to explore. MySQL did have problems with Unicode for a long time before utfmb4_unicode_ci was stabilized.