elixir-ecto/myxql

Unique constraints do not detect the changeset unique_constraint/3

Allyedge opened this issue · 1 comments

Allyedge commented

So, I have a very simple unique constraint set up, but for some reason it keeps saying this:

If you would like to stop this constraint violation from raising an
exception and instead add it as an error to your changeset, please
call `unique_constraint/3` on your changeset with the constraint
`:name` as an option.

I am using PlanetScale, so perhaps that is the issue? I have no idea.

Versions
MySQL: 8.0.21-Vitess
Elixir: 1.14.2
MyXQL 0.6.3
db_connection: 2.4.3

This is my migration.

defmodule Server.Repo.Migrations.CreateConnections do
  use Ecto.Migration

  def change do
    create table(:connections) do
      add(:sender_id, :string)
      add(:receiver_id, :string)
      add(:status, :string)

      timestamps()
    end

    create(
      unique_index(:connections, [:sender_id, :receiver_id],
        name: :connections_sender_id_receiver_id_index
      )
    )
  end
end

And this is the schema.

defmodule Server.Connections.Connection do
  use Ecto.Schema
  import Ecto.Changeset

  schema "connections" do
    field(:receiver_id, :string)
    field(:sender_id, :string)
    field(:status, Ecto.Enum, values: [:pending, :accepted, :rejected], default: :pending)

    timestamps()
  end

  @doc false
  def changeset(connection, attrs) do
    connection
    |> cast(attrs, [:sender_id, :receiver_id, :status])
    |> validate_required([:sender_id, :receiver_id])
    |> validate_inclusion(:status, [:pending, :accepted, :rejected])
    |> unique_constraint([:sender_id, :receiver_id],
      name: :connections_sender_id_receiver_id_index
    )
  end
end

Am I doing something wrong here? Or is it perhaps another issue? Perhaps something related to Vitess?

This is the entire error.

[error] #PID<0.512.0> running Phoenix.Endpoint.SyncCodeReloadPlug (connection #PID<0.511.0>, stream id 1) terminated
Server: localhost:4000 (http)
Request: POST /api/connections
** (exit) an exception was raised:
    ** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * connections_sender_id_receiver_id_index' (errno 1062) (sqlstate 23000) (CallerID: l4g61f89hivug6cg702g): Sql: "insert into connections(receiver_id, sender_id, `status`, inserted_at, updated_at) values (:v1, :v2, :v3, :v4, :v5)", BindVars: {REDACTED (unique_constraint)

If you would like to stop this constraint violation from raising an
exception and instead add it as an error to your changeset, please
call `unique_constraint/3` on your changeset with the constraint
`:name` as an option.

The changeset defined the following constraints:

    * connections_sender_id_receiver_id_index (unique_constraint)

        (ecto 3.9.5) lib/ecto/repo/schema.ex:795: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
        (elixir 1.14.2) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
        (ecto 3.9.5) lib/ecto/repo/schema.ex:780: Ecto.Repo.Schema.constraints_to_errors/3
        (ecto 3.9.5) lib/ecto/repo/schema.ex:761: Ecto.Repo.Schema.apply/4
        (ecto 3.9.5) lib/ecto/repo/schema.ex:369: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
        (server 0.1.0) lib/server_web/controllers/connection_controller.ex:15: ServerWeb.ConnectionController.create/2
        (server 0.1.0) lib/server_web/controllers/connection_controller.ex:1: ServerWeb.ConnectionController.action/2
        (server 0.1.0) lib/server_web/controllers/connection_controller.ex:1: ServerWeb.ConnectionController.phoenix_controller_pipeline/2
        (phoenix 1.7.2) lib/phoenix/router.ex:430: Phoenix.Router.__call__/5
        (server 0.1.0) lib/server_web/endpoint.ex:1: ServerWeb.Endpoint.plug_builder_call/2
        (server 0.1.0) lib/plug/debugger.ex:136: ServerWeb.Endpoint."call (overridable 3)"/2
        (server 0.1.0) lib/server_web/endpoint.ex:1: ServerWeb.Endpoint.call/2
        (phoenix 1.7.2) lib/phoenix/endpoint/sync_code_reload_plug.ex:22: Phoenix.Endpoint.SyncCodeReloadPlug.do_call/4
        (plug_cowboy 2.6.1) lib/plug/cowboy/handler.ex:11: Plug.Cowboy.Handler.init/2
        (cowboy 2.9.0) /home/allyedge/code/reanvue/server/deps/cowboy/src/cowboy_handler.erl:37: :cowboy_handler.execute/2
        (cowboy 2.9.0) /home/allyedge/code/reanvue/server/deps/cowboy/src/cowboy_stream_h.erl:306: :cowboy_stream_h.execute/3
        (cowboy 2.9.0) /home/allyedge/code/reanvue/server/deps/cowboy/src/cowboy_stream_h.erl:295: :cowboy_stream_h.request_process/3
        (stdlib 4.1.1) proc_lib.erl:240: :proc_lib.init_p_do_apply/3

Any help is appreciated, thanks!

wojtekmach commented

thank you for the report!

connections_sender_id_receiver_id_index' (errno 1062)

the trailing ' in …index' looks pretty suspicious, maybe it prevents from matching the constraint name.

I believe it is not a myxql issue but an ecto_sql issue so let’s track it there. Can you reopen it in the latter? Before doing so if you could test your code (and/or come up with a minimal example, ideally a single file one like https://github.com/wojtekmach/mix_install_examples/blob/main/ecto_sql.exs) against stock MySQL server to see if it might be a planet scale related issue after all.

  • 👍1