Insert with on_conflict : erratic behaviour

Questions / Help
,
1

Hi, I am confused. I need to update a lot of records, and am using the following procedure.

  1. select a batch (100_000 records) from the sessions table.
  2. insert the mac address into a seperate table.
  3. update the session with the id for this inserted record.

Because the mac addresses in the sessions table are not unique, i am using on_conflict, the insert looks like this:

{:ok, mac} =
          %MacAddress{}
          |> cast(
            %{
              mac: session.mac |> String.replace(":", "") |> Convertat.from_base(16),
              updated_at: session.created_at,
              inserted_at: session.created_at
            },
            [:mac, :updated_at, :inserted_at]
          )
          |> Repo.insert(on_conflict: [set: [updated_at: session.created_at]], returning: true)

when I inspect the returned mac: it gives me this:

07:30:59.958 [debug] QUERY OK db=0.3ms
INSERT INTO `mac_addresses` (`inserted_at`,`mac`,`updated_at`) VALUES (?,?,?) ON DUPLICATE KEY UPDATE `updated_at` = ? [{{2013, 3, 27}, {12, 52, 37, 0}}, 101538539605959, {{2013, 3, 27}, {12, 52, 37, 0}}, {{2013, 3, 27}, {12, 52, 37, 0}}]
%Gwapi.Hotspot.MacAddress{
  __meta__: #Ecto.Schema.Metadata<:loaded, "mac_addresses">,
  id: nil,
  inserted_at: ~N[2013-03-27 13:08:33.000000],
  mac: 181221090047854,
  updated_at: ~N[2013-03-27 13:08:33.000000]
}

The id is nil!
But when I do the same in the console:

iex(gwapi@127.0.0.1)6> %MacAddress{} |> cast(%{mac: 92524711229635}, [:mac]) |> Gwapi.Repo.insert(on_conflict: [set: [updated_at: DateTime.utc_now()]], returning: [:id])
07:40:08.040 [debug] QUERY OK db=110.0ms
INSERT INTO `mac_addresses` (`mac`,`inserted_at`,`updated_at`) VALUES (?,?,?) ON DUPLICATE KEY UPDATE `updated_at` = ? [92524711229635, {{2018, 4, 21}, {7, 40, 7, 930341}}, {{2018, 4, 21}, {7, 40, 7, 930364}}, {{2018, 4, 21}, {7, 40, 7, 930280}}]
{:ok,
 %Gwapi.Hotspot.MacAddress{
   __meta__: #Ecto.Schema.Metadata<:loaded, "mac_addresses">,
   id: 1168299652,
   inserted_at: ~N[2018-04-21 07:40:07.930341],
   mac: 92524711229635,
   updated_at: ~N[2018-04-21 07:40:07.930364]
 }}

The mac.id has the id.
I am using docker, and both commands are executed in the same console session.
I am using Percona Mysql, phoenix_ecto (3.3.0), mariaex (0.8.4)
And it does not matter what I put in returning.

1 Like
2

Try setting read_after_writes: true for the id field in the schema

1 Like
3

I was under the impression that This is the default for the ìdˋ column. So I need to define the I’d column as an ˋautogeneratedˋ field? i’ll Give it a try, but it still doesn’t explain why it works if I do it manually.

4

I assume the ID is a primary key? If so, there is nothing to do/we can do. But basically, databases are highly inconsistent on what they return once there is a conflict. The docs for insert/insert_all explain more under the upsert/on_conflict section.

5

Yes, the ID is the primary key.

I was reading the issue on GH, so I was aware of the difficulty, the only thing I found strange was that it would return the id when running the command by hand.

But anyway, I rewrote the code avoiding the upsert completely. I am using an ETS table to cache the information and then use an insert with a unique_validation. Works like a charm.

tx for the help