Cannot load JSON for certain field types using MariaDB

[odarriba]

Elixir version

v1.17.3

Database and Version

MariaDB v11.5.2

Ecto Versions

ecto 3.11.2, ecto_sql 3.11.2

Database Adapter and Versions (postgrex, myxql, etc)

myxql 0.6.4

Current behavior

In the error_tracker we use a field of type {:array, :string} for the list opdf breadcrumbs of an occurrence (source).

As we support all three major SQL databases (PostgreSQL, SQLite and MySQL/MariaDB), we need to implement the schema for all of them, so in the case of MySQL/MariaDB we implemented that field as a :json column (at the end, a list of strings is a valid JSON).

It work as expecrted for MySQL, which implements the JSON column type. However, for MariaDB it does nopt work because it implements JSON columns as longtext columns, and on runtime it breaks with:

** (ArgumentError) cannot load `"[]"` as type {:array, :string} for field :breadcrumbs in %ErrorTracker.Occurrence{__meta__: #Ecto.Schema.Metadata<:loaded, "error_tracker_occurrences">, id: nil, reason: nil, context: nil, breadcrumbs: nil, stacktrace: nil, error_id: nil, error: #Ecto.Association.NotLoaded<association :error is not loaded>, inserted_at: nil}

The original issue can be found here

Expected behavior

I would expect it to load the content using a JSON parser, the same way it does for a :map field.

[odarriba]

A solution I tried locally and worked was to add a line to this file

like this:

def loaders({:array, _}, type), do: [&json_decode/1, type]

but I'm not surte if this unsupported feature was on purpose, so I prefer to wait for your thoughts before opening a PR - I can do it myself, just wanted to be sure this is a bug and not a feature

[josevalim]

Generally speaking, we expect adapters to already return the data in the correct format. So if your type is {:array, :map}, we expect the adapter to return that, and we don't try to magically convert it (because it can lead to bugs, false positives, etc). For example, if we automatically did it, then perhaps someone would set their embeds column to a string in PostgreSQL, and end-up with a worse user experience without noticing it.

So if you can provide a way to make this feature opt-in, then we would gladly review it, but otherwise the underlying truth is that this type is simply not supported in their database of choice (and I can tell for certain that JSON operations such as p.meta.json_field won't work there, so they will get a limited experience anyway and other parts may fail.

[odarriba]

Thanks for so quick response!

I agree that the adapter should actually return the information in the expected. However I also see that on the file I linked there are already conversions for maps stored as JSON strings (maybe required for other scenarios as well?).

I don't know if storing an {:array, :string} as a :json column on the database is correct - maybe not, and doing that is the root cause of this problem - but if it is correct I am proposing trying to use the same decoding function that Ecto SQL is using for maps - which I suppose it is because of the same MySQL vs MariaDB difference 😅

I also agree that the developer experience using the wrong column type may be worse, so I completely understand your point. As for making the feature opt-in, I do not know how to manage that:
Maybe using a new option on the database config?
If so, should it apply to how maps are actually decoded too?

Thanks for your time 🤗

Edit: I see that Oban does not support MariaDB because of the lack of proper JSON support, so maybe it is better to keep it as it is instead of trying to patch the lack of support by that DB engine.

[josevalim]

You are right. Since we already handle :map and this would be specific to MyXQL, a PR adding the change you requested would be welcome!