StaleEntryError when trying to insert into an empty table using tds_ecto 2.0.3

[hjalet]

Hi,

I am trying to get my existing project to work on SQL Server using tdc_ecto. The project already runs and works on MySQL including the below failing code.

I am using tds_ecto version 2.0.3.

My seeds.exs script is trying to insert some data and it fails on the first insert. I have replicated the error in iex, and the iex output is shown below:

INSERT INTO [roles] ([is_builtin_role], [name], [inserted_at], [updated_at]) OUTPUT INSERTED.[id] VALUES (@1, @2, @3, @4) [true, "foobar", {{2017, 10, 6}, {6, 51, 23, 0}}, {{2017, 10, 6}, {6, 51, 23, 0}}] [debug] QUERY OK db=1.8ms rollback [] ** (Ecto.StaleEntryError) attempted to insert a stale struct:

%IdentityProvider.Repo.Role{meta: #Ecto.Schema.Metadata<:built, "roles">, id: nil, inserted_at: nil, is_builtin_role: true, name: "foobar", permissions: #Ecto.Association.NotLoaded, updated_at: nil}

(ecto) lib/ecto/repo/schema.ex:473: Ecto.Repo.Schema.apply/4
(ecto) lib/ecto/repo/schema.ex:205: anonymous fn/13 in Ecto.Repo.Schema.do_insert/4
(ecto) lib/ecto/repo/schema.ex:684: anonymous fn/3 in Ecto.Repo.Schema.wrap_in_transaction/6
(ecto) lib/ecto/adapters/sql.ex:620: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
(db_connection) lib/db_connection.ex:1275: DBConnection.transaction_run/4
(db_connection) lib/db_connection.ex:1199: DBConnection.run_begin/3
(db_connection) lib/db_connection.ex:790: DBConnection.transaction/3
(ecto) lib/ecto/repo/schema.ex:131: Ecto.Repo.Schema.insert!/4

Below I perform a select on the table to make sure it is in fact empty.

iex(5)> Repo.all(from r in Role) {"[roles]", "r0", IdentityProvider.Repo.Role} [debug] QUERY OK source="roles" db=6.2ms SELECT r0.[id], r0.[name], r0.[is_builtin_role], r0.[inserted_at], r0.[updated_at] FROM [roles] AS r0 [] []

The DB structure is a classic roles->roles_permissions->permission structure where the Role struct has a many-to-many relation with the Permission struct.

Any ideas as to what goes wrong?

[hjalet]

Just an addition to the above.

I also happens using a simple struct with no "foreign key" relations, only values.

[mjaric]

I will try to recreate scenario.

[mjaric]

How did you map relationship between Permissions in Role model. Did you used through: :role_permissions?

[mjaric]

Also, did you you use binary_id or auto increment integer for primary key?

[mjaric]

Never mind, I found that this is issue when primary key is set to @primary_key [:id, :id, auto_generate: true].

What happens is that this is tds lib issue. It fails to count number of rows in result. I will fix this in tds project, and let you know.

Thanks for reporting this issue.

[hjalet]

Thanks for the quick reply. Let me know if you anything further from me. I will be happy to try and test it when you have a fix.

[mjaric]

It is fixed now in https://hexdocs.pm/tds/1.0.3 try to update deps in your project mix deps.update tds

[hjalet]

Thanks, I have tried it and it works.