Support Ecto.Adapters.MyXQL for MySQL

[bsuprun]

Hello. I was playing with great feature Ecto Poll Queue. At some point, I notice that you are not supporting the MySQL database. Are there any plans for that? Thanks

[koudelka]

Hey there,

I don't have any plans to implement it, but I'd be happy to include it in the repository if someone were to write it (and test it).

[stephanos]

For what it's worth, a colleague and I took a stab at it and it seems hard to do right now.

Why? The library expects to be able to run an update and get the updated data back in one request (using the non-standard RETURNING that PG offers but not MySQL). Since the interface expects an SQL string, it doesn't seem to allow making two requests easily.

And that's where we gave up and moved on :-/

[koudelka]

I haven't looked into it at all, but maybe you could do an update and a select within a single transaction to mimic the RETURNING?

[stephanos]

I haven't looked any closer, but we thought that the driver implementation only defines queries as strings and so it would be hard to run two queries in a transaction. For example, from the Postgresql driver:

    def cancel(state) do
      "UPDATE #{state.table}
      SET #{state.lock_field} = NULL
      WHERE
        #{SQL.where_keys_fragment(state, 1)}
      RETURNING #{state.lock_field}"
    end

I might be totally missing something, not an Ecto expert :)

[koudelka]

Honeydew certainly wants strings for queries, it's calling Ecto.Adapters.SQL.query/4. So you should be able to provide any kind of free-form SQL you like in there, including a transaction, unless I'm totally missing something.

Happy to help give pointers if you guys want to take swing at it. :)

[stephanos]

🤦 I didn't realize you could just put multiple statements in that free-form SQL query including a transaction. Anyway, this was a week ago and since then we had to move on and so we wrote our own - very simplified - job queue. I don't think we'll have the time to develop this driver :-/ But I can ask my colleague to share our WiP.