MyXQL not working fine with AWS aurora mysql 3 with write forwarding

[teoortuno]

I'm trying to connect to a global database in AWS RDS, whose type is Aurora MySQL 3, where one write cluster is in one region and one reader cluster is in another region. This means that every writing query is executed in the writer node, the data is replicated almost instantly in all reader nodes.

Then, in the reader node we have enabled write forwarding. This way, the writing queries are forwarded to the write node, and after it's executed in the writer node, it's replicated to all reader nodes.

I have tested this in several mysql clients and it works fine (including mysql-client, dbeaver, and some python drivers). But when I try to execute writing queries in the reader node with MyXQL, this is what happens:

1. the query is forwarded from the reader node to the writer node
2. after that, the query is executed in the writer node and replicated in the reader node
3. then, the RDS instance in the reader node attempts to restart the database, and then it restarts in mode read-only, and doesn't forward writes anymore until we reboot the instance.

This is my configuration:

mix.exs

      {:myxql, "~> 0.7"},

config.exs

config :project, Project.Repo, telemetry_prefix: [:project, :repo]

prod.exs

config :project, Project.Repo,
  username: "dbuser",
  password: "password",
  database: "db",
  hostname: "host",
  pool_size: 20,
  queue_target: 1000

runtime.exs

  if region != "eu-west-1" do
    # this is so the read replica in the US region can foward writes to EU region
    # check https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-global-database-write-forwarding-ams.html#aurora-global-database-write-forwarding-isolation-ams
    config :project, Project.Repo,
      after_connect: {MyXQL, :query!, ["SET aurora_replica_read_consistency = 'session'", []]}
  end

Any ideas?

[josevalim]

Most MySQL libraries are wrappers around the C package but our library implements the wire protocol. Probably the best thing to do is to use Wireshark and compare how writing to a write-replica and read-replica is different over the wire (using any library), and then make sure we deal with it in MyXQL. It may be that there is something different which we are not expecting.