Ecto: :on_conflict with where in queryable + mysql gives SQL syntax error

Created on 27 Feb 2018 · 2Comments · Source: elixir-ecto/ecto

Environment

Elixir version (elixir -v):
Erlang/OTP 20 [erts-9.2] [source] [64-bit] [smp:8:8] [ds:8:8:10] [async-threads:10] [hipe] [kernel-poll:false]

Elixir 1.6.0 (compiled with OTP 20)

Database and version (PostgreSQL 9.4, MongoDB 3.2, etc.): mysql 5.6
Ecto version (mix deps): 2.2.8
Database adapter and version (mix deps): mariaex 0.8.3
Operating system: ARCH Linux (but running inside Docker based on elixir:1.6)

Current behavior

I have some code that looks like this:

update_fallback =
  from(
    r in NrRequests,
    where: r.guid == ^request.request_id and r.last_seq < ^new_sequence_number,
    update: [set: [nr_status_id: ^status_id, last_seq: ^new_sequence_number]]
  )

NrRequests.changeset(%NrRequests{}, %{
  guid: request.request_id,
  nr_status_id: status_id,                                                     
  last_seq: new_sequence_number                                                
})                                                                             
|> Repo.insert(on_conflict: update_fallback)

This generates the following error (taken from the ecto debug logs):

13:30:45.851 [debug] QUERY ERROR db=0.1ms
INSERT INTO `nr_requests` (`guid`,`last_seq`,`nr_status_id`,`created_at`,`updated_at`) VALUES (?,?,?,?,?) ON DUPLICATE KEY UPDATE `nr_status_id` = ?, `last_seq` = ? WHERE ((n0.`guid` = ?) AND (n0.`last_seq` < ?)) ["101010", 1, 19, {{2018, 2, 27}, {13, 30, 45, 850780}}, {{2018, 2, 27}, {13, 30, 45, 850789}}, 19, 1, "101010", 1]

Here's a stacktrace:

     ** (Mariaex.Error) (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE ((n0.`guid` = ?) AND (n0.`last_seq` < ?))' at line 1
     code: PublicInterface.Logger.log_status(context.sample_request, "fooStatus", 2)
     stacktrace:
       (ecto) lib/ecto/adapters/mysql.ex:198: Ecto.Adapters.MySQL.insert/6
       (ecto) lib/ecto/repo/schema.ex:547: Ecto.Repo.Schema.apply/4
       (ecto) lib/ecto/repo/schema.ex:213: anonymous fn/14 in Ecto.Repo.Schema.do_insert/4
       (public_interface) lib/public_interface/logger.ex:27: PublicInterface.Logger.log_status/3
       test/public_interface/logger_test.exs:168: (test)

Expected behavior

After some googling I've come to realize that mysql doesn't support this kind of construction at all (on duplicate key update ... where). Still, it doesn't feel right that you can generate SQL syntax errors like this.

I could see two possible expected behaviours:

an error in the mysql adapter
implementing the "IF" solution

Or possibly the Ecto maintainers are OK with this kind of error coming through. Perhaps Ecto doesn't support every possible combination of inputs (I'd be OK with that outcome as well). It's hard for me to say as a relative newcomer.

I'd be happy to take a look at implementing either of the two possibilities above, but I'd need some guidance as to what would be accepted in Ecto before I invest a lot of time in a solution.

Source