Repo.get not reliably working for binary keys with Ecto/Mariaex

A little while ago I already described this issue in the Elixir IRC channel. It went away after I switched from MySQL to MariaDB but now I’m encountering it again on a server where I’m deploying my code and have no say in the exact version of MariaDB/MySQL being employed.

The heart of the issue is that, presumably with certain versions of MariaDB/MySQL, Repo.get/2 doesn’t work with binary keys.

Here is how I would normally try to retrieve a DB record:

uuid = "44337bf3-51c2-41d5-b7ac-31eabd5c43da"
MyApp.Repo.get(MyApp.User, uuid)

This works just fine on my local test machine.

On the server, however, I get this:

uuid = "44337bf3-51c2-41d5-b7ac-31eabd5c43da"
NowDoApi.Repo.get(NowDoApi.User, uuid)

#19:53:50.236 [debug] QUERY OK source="users" db=2.0ms
#SELECT u0.`uuid` […] FROM `users` AS u0 WHERE (u0.`uuid` = ?) [<<68, 51, 123, 243, 81, 194, 65, 213, 183, 172, 49, 234, 189, 92, 67, 218>>]
#=> nil

The database connection is established just fine and the UUID is also correct as evident by the following test:

user = MyApp.Repo.all(MyApp.User) |> Enum.find(fn user -> user.uuid == uuid end)
#=> %MyApp.User{[…], uuid:  "44337bf3-51c2-41d5-b7ac-31eabd5c43da"}

To make sure there wasn’t any encoding problem with my string, let’s try again with the uuid that was retrieved by the previous query:

MyApp.User |> where(uuid: ^uuid) |> MyApp.Repo.all()
#19:57:20.551 [debug] QUERY OK source="users" db=2.0ms
#SELECT u0.`uuid`, […] FROM `users` AS u0 WHERE (u0.`uuid` = ?) [<<68, 51, 123, 243, 81, 194, 65, 213, 183, 172, 49, 234, 189, 92, 67, 218>>]
#=> []

This I find rather astonishing.

Has anyone here encountered a similar problem or found a way around this?
I am using Elixir 1.4.0 with Mariaex 0.8.0 and Ecto from Git master. The DB server is MariaDB 5.5.42.

Edit: I just upgraded to Mariaex 0.8.1 and switched to Ecto 2.1.3, but that makes no difference.

Edit 2: A similar problem was reported by someone in the Ecto mailing list half a year ago but no solution was posted there either.

@jordan0day was so kind to point me to this open issue in mariaex. Apparently the character set and collation of the database are affecting how binary data is being handled by mariaex.

In case anyone else comes across this problem, altering the table to use utf8 everywhere and altering the charset for the database as well did the trick for me and now Ecto happily retrieves my User structs.

1 Like