error with on_conflict in transaction

[atomkirk]

I'm doing this:

      ZB.Repo.insert(
        %ZB.IP{
          account_id: user.account_id,
          user_id: user.id,
          ip: ip,
          count: 1,
          date: Ecto.Date.utc,
        },
        on_conflict: [inc: [count: 1], set: [updated_at: NaiveDateTime.utc_now]]
      )

and if this runs outside a transaction it works great. If I run it in a transaction, like during tests, it gives me this error:

** (MatchError) no match of right hand side value: {:error, %ArgumentError{message: "query %Mariaex.Query{binary_as: nil, name: \"MARIAEX_ROLLBACK TO SAVEPOINT mariaex_savepoint\", num_params: nil, ref: nil, reserved?: false, statement: \"ROLLBACK TO SAVEPOINT mariaex_savepoint\", type: :text} uses reserved name"}, %Mariaex.Protocol{binary_as: :field_type_var_string, buffer: "", cache: #Reference<0.2367198246.2660630530.126223>, catch_eof: false, connection_id: #PID<0.3147.0>, cursors: %{}, deprecated_eof: true, lru_cache: {100, #Reference<0.2367198246.2660630530.126224>}, opts: [socket_options: [], sock_type: :tcp, cache_size: 100, port: 3306, pool: DBConnection.Poolboy, otp_app: :zipbooks, repo: ZB.Repo, adapter: Ecto.Adapters.MySQL, loggers: [], username: "root", password: "", database: "zipbooks_test_0", hostname: "localhost", pool_timeout: 5000, timeout: 15000, adapter: Ecto.Adapters.MySQL, loggers: [], username: "root", password: "", database: "zipbooks_test_0", hostname: "localhost"], seqnum: 0, sock: {Mariaex.Connection.Tcp, #Port<0.178420>}, ssl_conn_state: :not_used, state: :column_count, state_data: nil, timeout: 15000}}

Any ideas on this?

[atomkirk]

use ZB.ConnCase, async: true

actually its when tests are async. Any thoughts on how to fix?

[atomkirk]

I'm using mysql and if I remove the @reserved_prefix MARIAEX_ stuff, then I get this error:

     ** (Mariaex.Error) (1305): SAVEPOINT mariaex_savepoint does not exist
     code: |> ConnTest.dispatch(@endpoint, "POST", "v2/recommendations", %{
     stacktrace:
       (db_connection) lib/db_connection.ex:794: DBConnection.transaction/3

[atomkirk]

this is the full error with stack trace:

     ** (MatchError) no match of right hand side value: {:error, %ArgumentError{message: "query %Mariaex.Query{binary_as: nil, name: \"MARIAEX_ROLLBACK TO SAVEPOINT mariaex_savepoint\", num_params: nil, ref: nil, reserved?: false, statement: \"ROLLBACK TO SAVEPOINT mariaex_savepoint\", type: :text} uses reserved name"}, %Mariaex.Protocol{binary_as: :field_type_var_string, buffer: "", cache: #Reference<0.1445620248.3194355715.132991>, catch_eof: false, connection_id: #PID<0.747.0>, cursors: %{}, deprecated_eof: true, lru_cache: {100, #Reference<0.1445620248.3194355715.132992>}, opts: [socket_options: [], sock_type: :tcp, cache_size: 100, port: 3306, pool: DBConnection.Poolboy, otp_app: :zipbooks, repo: ZB.Repo, adapter: Ecto.Adapters.MySQL, loggers: [], username: "root", password: "", database: "zipbooks_test_0", hostname: "localhost", pool_timeout: 5000, timeout: 15000, adapter: Ecto.Adapters.MySQL, loggers: [], username: "root", password: "", database: "zipbooks_test_0", hostname: "localhost"], seqnum: 0, sock: {Mariaex.Connection.Tcp, #Port<0.64299>}, ssl_conn_state: :not_used, state: :column_count, state_data: nil, timeout: 15000}}
     code: conn = get conn, "/v2/label_kinds"
     stacktrace:
       (mariaex) lib/mariaex/protocol.ex:1140: Mariaex.Protocol.abort_statement/3
       (mariaex) lib/mariaex/protocol.ex:951: anonymous fn/3 in Mariaex.Protocol.handle_savepoint/4
       (elixir) lib/enum.ex:1826: Enum."-reduce/3-lists^foldl/2-0-"/3
       (ecto) lib/ecto/adapters/sql/sandbox.ex:359: Ecto.Adapters.SQL.Sandbox.Connection.proxy/3
       (db_connection) lib/db_connection.ex:1335: DBConnection.handle/5
       (db_connection) lib/db_connection.ex:1321: DBConnection.conclude_meter/6
       (db_connection) lib/db_connection.ex:1199: DBConnection.run_begin/3
       (db_connection) lib/db_connection.ex:790: DBConnection.transaction/3
       (zipbooks) lib/repo.ex:55: ZB.Repo.insert/2
       (zipbooks) lib/zipbooks_web/plugs/current_user.ex:10: ZB.Plug.CurrentUser.call/2
       (zipbooks) lib/zipbooks_web/controllers/label_controller.ex:1: ZB.LabelController.phoenix_controller_pipeline/2
       (zipbooks) lib/zipbooks_web/endpoint.ex:1: ZB.Endpoint.instrument/4
       (phoenix) lib/phoenix/router.ex:278: Phoenix.Router.__call__/1
       (zipbooks) lib/plug/error_handler.ex:64: ZB.Router.call/2
       (zipbooks) lib/zipbooks_web/endpoint.ex:1: ZB.Endpoint.plug_builder_call/2
       (zipbooks) lib/plug/debugger.ex:99: ZB.Endpoint."call (overridable 3)"/2
       (zipbooks) lib/zipbooks_web/endpoint.ex:1: ZB.Endpoint.call/2
       (phoenix) lib/phoenix/test/conn_test.ex:224: Phoenix.ConnTest.dispatch/5
       test/lib/zipbooks_web/controllers/label/index_test.exs:8: (test)

And again this only happens on tests with async: true. Any ideas what might be wrong?

[lucas-nelson]

@atomkirk we've just hit this same error and can't work out the why of it. Did you end up discovering a reason and/or a solution?

[atomkirk]

The ecto docs mention that using async: true in tests is unreliable with mysql. It advises removing async: true so thats what we do.

While both PostgreSQL and MySQL support SQL Sandbox, only PostgreSQL supports concurrent tests while running the SQL Sandbox. Therefore, do not run concurrent tests with MySQL as you may run into deadlocks due to its transaction implementation

https://hexdocs.pm/ecto/Ecto.Adapters.SQL.Sandbox.html