MySQL connector for Ash?

Hi,

I work for a small MVNO and I’m very much interested to try using Ash in our internal devs. However I have a (probably) breaking requirement : I need to be able to connect it to our MySQL 8.0 (Actually Percona XtraDB) cluster. Is there something in the making ? I heard about that in the past, but did not found anything up to now.
I might be interested in having a try at making a MySQL connector myself, but I’m not sure where should I start ?

:wave: Work has been made in that direction, but MySQL itself has not been done. The best way to move forward with that would be to copy ash_sqlite into ash_mysql and start making modifications to get the tests passing. What will likely additionally need to happen is that the AshSql implementation will need to be expanded to account for whatever special thing isn’t supported by

Thanks for your answer. I started trying from a copy of ash_sqlite like you suggested and I’ve hit a problem that I’m not sure how to solve : 89 tests on 115 fail because :

* ** (ArgumentError) :returning is not supported in insert/insert_all by MySQL

What do you use it for ? Do you have an idea how may I work around that ?

You can try removing it, we use it to get the result of the insert to return. It could be replaced with reading the inserted record again? Does SQLite return that info automatically perhaps?

MySQL does not have a way to return the result of an insert. It returns only the number of inserted records.

If you rely on auto generated primary keys (AUTO_INCREMENT in MySQL terms, which is the default for most people), there is a way to get it back wit a call to the LAST_INSERT_ID() function; You could then load the record back. However that would work only for an insert of a single record. For bulk inserts, that function returns only the id of the first inserted record, and there is no reliable way to get the ids of the other records (they might be guessable in simple cases, but it would break in a lot of clustering configs).

The only way around that would be to generate the primary key on the app side and load the records afterward or to not rely on that ability…

1 Like

There is a fair amount of stuff in ash that depends on being able to get the results of a creation currently. So what we will have to do is add a capability for data layers, like return_from_create, and then if they can’t do that, we’ll have to disable certain capabilities, like after_action hooks.

For tables where the primary key is ecto generated (isn’t it the default in ash?), wouldn’t it be possible to “fake” the :returning option ?

Yes, we could definitely do that :slight_smile: They aren’t ecto generated, but they are generated in Ash by default. Basically requiring that all primary attributes have a default configured could get us started. Then we could support auto incrementing integers later, but require that you can’t do certain things in create actions.

Soooo… As you suggested, I copied ash_sqlite and had a first try at an ash_mysql datalayer. It is far from finished : i have 35 failures on the 115 tests that came from ash_sqlite, and several hard coded parts that need to be properly finished/rewritten.

It’s available here : GitHub - lejoko/ash_mysql

I’m having a bit of a hard time with it, since I’m an Ash rookie and in a lot of cases I’m not too sure of what would be the “right way” to do something. I’d be really happy if I could have some guiding to continue on that project because I really like Ash and would like to be able to use it at work!

1 Like

Nice! I will take a look in the next few days.

1 Like

Super!

1 Like

Warning: not ready for use! There’s still significant work to do. I hope I will be able to finish it.

1 Like

Hi, I opened a PR against ash_sql for a small non standard SQL problem, that MySQL does not support. Moreover, I’m not too sure where to talk about those things. Where would be the right place ?

Here or in the GitHub repo I imagine.

Hi again,

I’m almost done with making my ash_mysql module pass all tests that came from ash_sqlite. I’ve a few problems left :

  1. There are several expr operators (<>, || and &&) that would require MySQL specific changes in ash_sql in the do_dynamic_expr function that is in lib/expr.ex but that function is not redefinable in the behaviour module and it would seem strange to redefine all operators when only a few need redefinition
  2. I’m not sure how to proceed regarding upserts. MySQL can upsert, but it can’t return the results and I found no way to fake the reloading with upserts (as I did with inserts and updates). Maybe should I leave upserts out ?
  3. My modules fakes reloading after create or update in a way that can work only if the table has single column primary key that is generated by Ash. At the moment, that behaviour is hard coded. I’m not sure how to proceed regarding that.
  4. I think it would be great to add transactions and aggregates to ash_mysql. What would be the best way to proceed ?
1 Like

This is very exciting stuff!

  1. I think we probably do want to allow extensions to override “any” expression, and to do that what we need to do is to rename do_dynamic_expr to do_generic_dynamic_expr (only the function heads, not where we call it), and replace the do_dynamic_expr function with something like:
defp do_dynamic_expr(...) do
  if get_specific_expr(...) do
    {:ok, expr} -> expr
    {:error, error} -> {:error, error}
    :error -> do_generic_dynamic_expr(....)
  end
end

This allows for a SQL extension to “hijack” any expression that requires special processing.

  1. You can disable upserts for now by adjusting the can?/2 function. Then we can figure something out later :slight_smile:

  2. In the short term, I’d require that all primary keys have default values and are marked as generated?: true. In the long term, we need to work this capability into Ash core, with capabilities like return_after_create, return_after_update, etc. This will be work, and will disable various capabilities, but would make us more compatible with various things.

  3. Adding transactions should be relatively simple, but I’m not familiar with transaction semantics in MySql so it’s hard to say for sure.

aggregates however, are much more complicated :slight_smile: Does MySQL support lateral joins?

  1. If I understand you well, that would be a breaking change, requiring all adapters depending on ash_sql to be updated ? Are there other ones than ash_postgres and ahs_sqlite currently ?

  2. I’ll go that way for now

  3. When you says “require that all primary keys have default values and are marked as generated?: true”, you mean with no code to guarantee it ? like just explaining that it currently works that way it in the doc ?

  4. MySQL has no nested transactions but has savepoints. I far as I understand ash_postgres uses that ?
    But my question was more like : what needs to be done to support transactions ? Would git grepping ‘transaction’ in the tests directory of ash_postgres, copying them and making them pass be enough ? Can you foresee something that would make that approach insufficient ?

  5. regarding aggregates, MySQL 8 does supports lateral joins (since 8.0.14, I discovered them recently and they are great!). Currently my module is developped with MySQL 8.0 in mind anyway and has at least one place where it wouldn’t work with older versions without change.

  1. It shouldn’t be, because they should already explicitly match on certain expressions, returning :error if they don’t want to override them.

  2. Your data layer can add a verifier that enforces it, if it is a requirement. ash/lib/ash/resource/verifiers/validate_accept.ex at main · ash-project/ash · GitHub that is an example verifier. Then you’d add it in `use Spark.Dsl.Extension, verifiers: [YourVerifier]

  3. Yep, copying the transaction semantics from ash_postgres would make sense :+1: If MySQL has savepoints then it can also support the error expression potentially, which would make it the “second best” sql data layer in terms of support :slight_smile: as it would be able to support atomics & the like. Assuming there is a way to raise a custom error.

  4. thats awesome news! In that case, you could attempt to use the aggregates code contained in AshSql and “see what happens” :slight_smile: There will 100% be modifications required to make it work with MySQL. I’d suggest that we ship everything else before focusing on this.

This is awesome work. Its shaping up really well and if you can get it to support aggregates, transactions and errors, then this has two major benefits:

  1. AshSql fully supporting two distinct flavors of SQL, as right now it still has plenty of postgres specific stuff.
  2. A great new SQL data layer :partying_face:
1 Like