Odd binary id mismatch

revati

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 5116 15

2018-07-30 16:51:53 UTC

Showing Posts 15 to 6

Show Best Posts
Show All (oldest first)
Show All (newest first)

eljorge

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

Post #15

revati

this isn’t exactly the same as this example. As shown in binary id odd bug · Issue #2602 · elixir-ecto/ecto · GitHub

# insert query
INSERT INTO `my_modules` (`id`) VALUES (?) [<<246, 200, 234, 122, 29, 144, 70, 72, 176, 30, 56, 120, 54, 0, 135, 84>>]

# select query
SELECT m0.`id` FROM `my_modules` AS m0 WHERE (m0.`id` = ?) [<<246, 200, 234, 122, 29, 144, 70, 72, 176, 30, 56, 120, 54, 0, 135, 84>>]

ecto doesn’t use MySQL functions to convert to binary and vice versa.

Post #14

eljorge

we have also seen success with using the utf8 character set

Post #13

eljorge

this link helps gives some explanation on how to insert an elixir UUID:

Post #12

revati

As you suggested, I created separate mix project with ecto and minimal migrations and model to replicate this issue: GitHub - revati/ecto_mysql_bug · GitHub

Post #11

peerreynders

please let us know the result of Repo.all

I’d go even further. Once you insert a record in iEX, use the MySQL shell to show you what value in the id column actually is. Something like:

SELECT HEX(id), nth, url, contents FROM sources;

That really is the only way to know what was actually written to the database as the UUID data can just as easily be compromised on the Repo.all return trip.

The table is empty before that and id column is a primary index (must be unique), so in either case, there couldn’t be duplicate ids in the table.

The fundamental issue here is that you have shown that in your environment the result of the query cannot necessarily be trusted. Given that there is only one record in the table, multiple distinct IDs seem to be returning the same record which can only have one ID. So there could be a whole set of ID values capable of selecting this one record.

You write the record with ID A and you retrieve with ID A something with ID B.

If the query works then ID A is in the database and ID B is a version of ID A that is somehow corrupted on the way back from the database.
If the query works - ID A could be corrupted before being written as ID B to the database. ID A could be corrupted in a similar fashion during the query becoming ID B and then retrieving the data of the ID B record.
If the query doesn’t work it simply returns the only row in the table. While ID B is different from ID A it’s not clear whether ID A was corrupted before writing it to the database or after reading it from the database.

So at this point superficially it looks like ID B is in the database but it could be corrupted on read, so ID A could still exist in the database.

Now you retrieve with ID B and get something with ID B.

If you believe the query works and take the previous observations into account then you have to believe the whatever corrupted ID A into ID B doesn’t change the value of ID B - allowing the query to actually seem to work the way it should if ID B is actually in the database.
If you are open to the possibility that the query simply returned the only record in the table then you can’t have any confidence in the ID value being retrieved as it is clearly being corrupted somewhere - before writing, after reading, both?

Given that it seems that the ID value is being corrupted somewhere, there is the possibility that sometimes ID related queries will not work (depending on the nature of the corruption).

To narrow this down you should create a minimal ecto-only project with a single table in a new database with an UUID column and a second (string) column for information that lets you uniquely identify each record created for inspection.

Set up a unit test similar to the one you have. You may have to run the test repeatedly until it fails (or some max is reached) - sometimes these type of defects are intermittent.

Once you have a setup that consistently fails in your environment, put the project up on GitHub and reference the repository in the issue. That way somebody else can easily try it in their (MySQL) environment to see if the problem behaviour persists.

Post #10

revati

https://github.com/elixir-ecto/ecto/issues/2602

Post #9

revati

I updated MySQL to 8.0.11 version, but there still is a problem

Post #8

revati

What is not clear is whether new actually originates from the same record that was created by the previous Repo.insert!. If there are other records in the table, new could be an entirely different record.

Didn’t quite catch. Its internal Ecto function Repo.get! which retrieves a new record by id. It isn’t in any way tied to the previous source record. The table is empty before that and id column is a primary index (must be unique), so in either case, there couldn’t be duplicate ids in the table.

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

assert new2.id == new.id

tried and this works fine

Post #7

peerreynders

Have your tried:

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

  assert new2.id == new.id

What is not clear is whether new actually originates from the same record that was created by the previous Repo.insert!. If there are other records in the table, new could be an entirely different record.

Some experiments with iEX and the SQL shell could shed some light on that:

after the insert - is there a record with the new UUID (e.g. 3f3fd9a2-3f69-463e-3f3f-153f34003f3f)
after the retrieve - is there a record with the different UUID (e.g. 85c9d9a2-a969-463e-a8d5-15f834009ebc)
does the remainder of the retrieved record match the former or the latter record content?

This was never resolved: