Ecto (mysql) and UUID

Questions / Help
,
1

Hello,

I’m having a vexing issue using Ecto.UUIDs as my primary key in a table. When I create a record, I generate the UUID for the id, and pass that along with the other fields to insert. A record is created, and the UUID is shown. When I query the record, I get a different UUID back.

Here’s an example of what I’m seeing. Code for the schema and repo follow the example.

I believe I’m doing something dumb but, at least for me, the dumber it is the harder it is to find.

iex(5)> Repo.insert(%ReplicationType{id: Ecto.UUID.generate(), name: "test3"})
18:32:49.063 [debug] QUERY OK db=0.8ms queue=7.6ms
INSERT INTO `replication_types` (`id`,`name`,`inserted_at`,`updated_at`) VALUES (?,?,?,?) [<<71, 34, 96, 242, 205, 97, 72, 47, 171, 47, 132, 161, 18, 187, 187, 138>>, "test3", ~N[2019-04-19 22:32:49], ~N[2019-04-19 22:32:49]]
{:ok,
 %Pserver.Pserver.ReplicationType{
   __meta__: #Ecto.Schema.Metadata<:loaded, "replication_types">,
   id: "472260f2-cd61-482f-ab2f-84a112bbbb8a",
   inserted_at: ~N[2019-04-19 22:32:49],
   name: "test3",
   replication_tables: #Ecto.Association.NotLoaded<association :replication_tables is not loaded>,
   sites: #Ecto.Association.NotLoaded<association :sites is not loaded>,
   updated_at: ~N[2019-04-19 22:32:49]
 }}
iex(6)> Repo.get_by(ReplicationType, name: "test3")                           
18:33:11.506 [debug] QUERY OK source="replication_types" db=8.9ms
SELECT r0.`id`, r0.`name`, r0.`inserted_at`, r0.`updated_at` FROM `replication_types` AS r0 WHERE (r0.`name` = ?) ["test3"]
%Pserver.Pserver.ReplicationType{
  __meta__: #Ecto.Schema.Metadata<:loaded, "replication_types">,
  id: "4722603f-3f61-482f-3f2f-3f3f123f3f3f",
  inserted_at: ~N[2019-04-19 22:32:49],
  name: "test3",
  replication_tables: #Ecto.Association.NotLoaded<association :replication_tables is not loaded>,
  sites: #Ecto.Association.NotLoaded<association :sites is not loaded>,
  updated_at: ~N[2019-04-19 22:32:49]
}

the code for ReplicationType:

defmodule Pserver.Pserver.ReplicationType do
  use Ecto.Schema
  import Ecto.Changeset
  alias Pserver.Pserver.{ReplicationTable, Site}

  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id
  schema "replication_types" do
    # field :id, :binary_id
    field :name, :string
    
    has_many :sites, Site
    has_many :replication_tables, ReplicationTable
    timestamps()
  end

  @doc false
  def changeset(replication_type, attrs) do
    replication_type
    |> cast(attrs, [:name])
    |> validate_required([:name])
    |> unique_constraint(:name)
  end
end

And the Repo…

defmodule Pserver.Repo do
  use Ecto.Repo,
    otp_app: :pserver,
    adapter: Ecto.Adapters.MySQL
end

Thank you!

2

This won’t let id through, causing the built-in autogenerate to run instead.

1 Like
3

I don’t think that’s it since he’s passing the id as part of the struct itself %ReplicationType{id: Ecto.UUID.generate(), name: "test3"} (and he’s not going through the changeset at all)

@cboebel what version of mysql are you using and what is the exact column type of the id? Also you might want to checkout the MyXQL adapter: GitHub - elixir-ecto/myxql: MySQL 5.5+ driver for Elixir (I don’t see any UUID support mentioned but I would it expect it to work with direct binaries)

4

This is correct. The example I posted doesn’t use the changeset.

I’m using 5.7.25 MySQL Community Server (GPL).

The column type is binary(16)

5

I should also point out that this happens if I generate the UUID myself or let it happen as part of the autogenerate.

6

What column type in MySQL are you using?

7

binary(16)

8

Also tried this we MySQL 8 - same results.

9

I discovered this in the context of migrations so it might not make any difference here but when your define your primary key add read_after_writes: true.

@primary_key {:id, :binary_id, autogenerate: true, read_after_writes: true}

1 Like
10

MySQL does not like this:

For relational databases, this means the RETURNING option of those statements is used. For this reason, MySQL does not support this option and will raise an error if a schema is inserted/updated with read after writes fields.

1 Like
11

Can you double check that your character encoding and collation is ok? Ecto’s MySQL doesn’t support certain of MySQL encoding types, and I remember having major problems with uuids that drove me up the wall (but were solvable - except when I had to interface with other databases at work) until I switched to postgres.

I can’t be 100% sure but I recall values being trimmed at 3f as being diagnostic of the problem – you’ll have to use utf8- something other other and not Latin, which is the default in MySQL iirc.

1 Like
12

Good point, and one that is echoed here: Odd Binary ID Mismatch

I did find that the table was encoded with latin1, so I did this:

ALTER TABLE replication_types CONVERT TO CHARACTER SET utf8;

I tried again. Same results, unfortunately.

13

3f in ASCII is ?. Looks like MySQL will replace characters that aren’t representable in the column’s character set with ?:

1 Like
14

don’t forget to also change the collation, try running this:

ALTER TABLE `replication_types` CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci
1 Like
15

I tried this (thank you for the suggestion) and received the same results.

Here’s what I don’t understand about these suggestions: This is a binary field (binary(16)). Why would things like collation and character set have any bearing on what’s stored in a binary field? I should be able to store anything in there, right? An image, for example. (Not that I would).

16

I agree it looks fishy, but I don’t understand why it would replace characters in a binary field. I should be able to put anything in there, right?

17

Yeah, I would expect that as well, but that doesn’t necessarily mean it is actually true in reality.

Can you show the output of SHOW FULL COLUMNS FROM table_name;? Although I’m not sure if we’d learn anything else beyond binary(16). Or if you could create a sample project that reproduces the issue, that would be helpful in tracking it down.

18

I’m not 100% sure but it probably has to do with the ecto adapter. I think SQL’s put binary data into themselves using, say, stdin and if your adapter is sending non-texty data on stdin, strange things happen if the encodings don’t match. Quite a bit of an SQL query must be in text, so mix &match is bound to be hairy, e.g. how POST http queries can get strange esp. with multipart form encoding

19

Here’s the output from show full columns:

+-------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| Field       | Type         | Collation         | Null | Key | Default | Extra | Privileges                      | Comment |
+-------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+
| id          | binary(16)   | NULL              | NO   | PRI | NULL    |       | select,insert,update,references |         |
| name        | varchar(255) | latin1_swedish_ci | YES  | UNI | NULL    |       | select,insert,update,references |         |
| inserted_at | datetime     | NULL              | NO   |     | NULL    |       | select,insert,update,references |         |
| updated_at  | datetime     | NULL              | NO   |     | NULL    |       | select,insert,update,references |         |
+-------------+--------------+-------------------+------+-----+---------+-------+---------------------------------+---------+

I’ll do a small demonstration project later today and put it up somewhere.

Thank you for your help.

1 Like
20

Apparently you might want to try utf8mb4

https://mathiasbynens.be/notes/mysql-utf8mb4

Also try the settings in /etc/my.cnf, I suspect especially the client one might be important.