revati

Odd binary id mismatch

Hello, i have fallowing code in my tests:

source = %MyModule{} |> Repo.insert!()
new = Repo.get!(MyModule, source.id)

assert new.id == source.id

but oddly enough it throws an error, as you can see ids are slightly different.

 Assertion with == failed
 code:  assert new.id() == source.id()
 left:  "3f3fd9a2-3f69-463e-3f3f-153f34003f3f"
 right: "85c9d9a2-a969-463e-a8d5-15f834009ebc"

MyModule schema uses following base schema

defmodule Stats.Schema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema
      @primary_key {:id, :binary_id, autogenerate: true}
      @foreign_key_type :binary_id
    end
  end
end

How come ecto can find the row in DB by id, but later when comparing those ids, they mismatch?
is it some binary encoding problem?

Im use ecto: 2.2.10, mariaex: 0.8.4, mysql: 5.7.20

Thanks

15 comments

#ecto #mysql

1 4863 15

2018-07-30 16:51:53 UTC

Most Liked

peerreynders

Just an aside; possibly relevant:

MySQL does not support UUID types. Ecto emulates them by using binary(16).

Because MySQL does not support RETURNING clauses in INSERT and UPDATE, it does not support the :read_after_writes option of Ecto.Schema.field/3.

3: TIL: Ecto reading after writes:

by default, Ecto doesn’t read data back from the database, after writing new or updated data.

5.7.1 was released 2013-04-23. According to GUID/UUID Performance Breakthrough

This blog is mostly eliminated in MySQL 8.0

i.e. pre-8.0 UUIDs could be problematic/high maintenance.

eljorge

We changed the collation set of our DB from latin1_swedish (defaulton mysql 5.7) to ut8 and it solved our problem

Post #16

peerreynders

revati:

defmodule Stats.Repo.Migrations.CreateSourcesTable do
  use Ecto.Migration

  def change do
    create table(:sources) do
      add :nth, :integer, null: false
      add :url, :string, null: false
      add :contents, :text
    end

    create index("sources", [:nth], unique: true)
    create index("sources", [:url], unique: true)
  end
end

I’m guessing that the above simply, implicitly adds a

field :id, :id, primary_key: true

In the SQL shell, do a DESCRIBE sources; and see what the type of the id column actually is. Given Ecto’s documentation I would expect a binary(16) for a UUID column - a standard :id will be some kind of integer.

If the id column is the wrong type, I’d start with

defmodule Stats.Repo.Migrations.CreateSourcesTable do
  use Ecto.Migration

  def change do
    create table(:sources, primary_key: false) do
      add :id, :binary_id, primary_key: true
      add :nth, :integer, null: false
      add :url, :string, null: false
      add :contents, :text
    end

    create index("sources", [:nth], unique: true)
    create index("sources", [:url], unique: true)
  end
end

and go from there - i.e. see if it works or if it simply changes the problem.

defmodule Stats.Schema do
  defmacro __using__(_) do
    quote do
      use Ecto.Schema
      @primary_key {:id, :binary_id, autogenerate: true}
      @foreign_key_type :binary_id
    end
  end
end

only has an effect on Schemas that use it - it doesn’t impact the migration at all - so there needs to be some kind of hint in the migration that the table is dealing with a :binary_id instead of a :id type.

Post #5