Ecto, myxql and zero-datetime

Questions / Help
, , ,
1

Hi everyone,

I have a table in a MySQL database with a ‘timestamp’ column called ‘created’ which may contain zero datetimes, i.e. ‘0000-00-00 00:00:00’.
My schema definition looks as follows:

defmodule MySchema do
  use Ecto.Schema

  schema "my_schema" do    
    field :created, :naive_datetime    
  end

end

I use the following dependencies (mix.exs) to access the database:

defp deps do
  [      
    {:ecto_sql, "~> 3.0"},
    {:myxql, git: "https://github.com/elixir-ecto/myxql.git", ref: "e01ebc7", override: true},
  ]
end

I use the e01ebc7 commit for myxql since it converts a zero datetime into an atom called ‘:zero_datetime’.

But when I try to query data using Repo.all(MySchema), I get the following error:

 (ArgumentError) cannot load `:zero_datetime` as type :naive_datetime for field :created in %MySchema{__meta__: #Ecto.Schema.Metadata<:loaded, "my_schema">, created: nil, id: nil }
    (ecto) lib/ecto/repo/queryable.ex:345: Ecto.Repo.Queryable.struct_load!/6
    (ecto) lib/ecto/repo/queryable.ex:201: anonymous fn/5 in Ecto.Repo.Queryable.preprocessor/3
    (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2
    (elixir) lib/enum.ex:1336: Enum."-map/2-lists^map/1-0-"/2
    (ecto) lib/ecto/repo/queryable.ex:158: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3
    (my_app) lib/my_app/application.ex:23: MyApp.Application.example/0

Is there a way to convert data with :zero_datetime to MySchema?

Currently, I have to drop rows with zero-datetimes like this:

min_date = ~N[0000-01-01 00:00:00]
query = from m in MySchema,
        where: m.created >= ^min_date,
        select: m
Repo.all(query)

I’m looking forward for answers :slight_smile:

2

You can create a custom Ecto.Type implemetation, which knows how to handle zero’d dates, because 0000-00-00 00:00:00 is not a valid iso8601 datetime. There’s no month or day 0.

3 Likes
3

Yes, custom type is the way to go. I would however take a step back and ask what we are representing by this zero datetime? MySQL uses it to represent invalid values, eg result of some arithmetic, and only when appropriate sql mode is set, by default on newer versions you would get an error instead. This also brings the question that even if we add a custom type what would we cast/load/dump this value to - keep it as :zero_datetime? Then any time we work with this value we would have to check if its non-zero. Without knowing about the usecase I’d either use a NULL or smallest valid date instead of the zero one.

2 Likes
4

I already read about Ecto’s custom types but I wasn’t completely sure if this is the way to go.
Thanks very much for your answers!

5

Just stumbled upon this thread.

@wojtekmach I can’t question anything in this DB design because it’s a legacy MySQL instance that my Elixir app absolutely can’t modify at all; not schema, not data, anything. I wish Ecto had an enforceable read-only mode!

I suppose everyone who stumbles upon this problem will have to make their own Ecto.Type.

6

For future reference and for whomever might need this, here’s what worked for me:

defmodule YourApp.Types.ZeroableDateTime do
  use Ecto.Type

  def type, do: :naive_datetime

  def cast(data), do: {:ok, data}

  # Just return `nil` datetime instead of crashing.
  def load(:zero_datetime), do: {:ok, nil}
  def load(%NaiveDateTime{} = data), do: {:ok, data}

  def dump(%NaiveDateTime{} = data), do: {:ok, data}
  def dump(_), do: :error
end
8 Likes
7

This doesn’t work for me. Where do you put this file? I put mine in /lib/myapp/zeroabledatetime.ex and restarted “mix phx.server”.

I’m testing the basic login page for phx.gen.auth and I still get this error:

##### ArgumentError <small>at POST</small> <small>/users/log_in</small>

# cannot load `:zero_datetime` as type :naive_datetime for field :confirmed_at in #Exchat.Accounts.User<__meta__: #Ecto.Schema.Metadata<:loaded, "users">, confirmed_at: nil, email: nil, hashed_password: nil, id: nil, inserted_at: nil, updated_at: nil, ...>

lib/ecto/repo/queryable.ex

      :error ->
        field = field && " for field #{inspect(field)}"
        struct = struct && " in #{inspect(struct)}"
        raise ArgumentError,
              "cannot load `#{inspect(value)}` as type #{inspect(type)}#{field}#{struct}"
    end
  end
  defp to_map(nil, _fields) do

I’m using phoenix version 1.5.12, so maybe the ZeroableDateTime no longer works?

8

It doesn’t matter where you put the file, but you do have to use the module as a type for the field inside your Ecto.Schema module. Can you show some code on how do you use it?

9

Sorry, I’m a bit new. I assumed your module was just a plug-and-play, compile it and it would work automatically.

Over the past few minutes or so, I’ve been trying to do some quick coding, but can’t get it to work.

defmodule Exchat.Accounts.User do
  use Exchat.Type.ZeroableDateTime
  use Ecto.Schema
  import Ecto.Changeset

  @derive {Inspect, except: [:password]}
  schema "users" do
    field :email, :string
    field :password, :string, virtual: true
    field :hashed_password, :string
    field :confirmed_at, :zero_datetime   // changed from :naive_datetime

The trouble is happening with the :confirmed_at variable. I’m assuming that if I use :zero_datetime as the field type, it’s supposed to default to :naive_datetime, and then if the timestamp is “0” it will just return nil. (as per your module)

(I installed module in deps directory, put it in my lib directory instead and now compiles)

Let me know if I’m on the right track or not, I’d greatly appreciate your advice.

Thanks

UPDATE:

I’m getting this error instead:

** (UndefinedFunctionError) function Exchat.Type.ZeroableDateTime.__using__/1 is undefined or private

10

Ok, I think cleared up and learned some things, but I’m still having trouble. In the mind of a noob, I assumed when you said “to use the module as a type for the field inside your Ecto.Schema”, I was monkeying around with “/deps/ecto/lib/ecto/schema.ex”, which is the actual Ecto.Schema module, and trying to use this guide (Ecto.Type — Ecto v3.7.1) to add some configuration to that file lol.

So now I’m up to this point:

defmodule Exchat.Accounts.User do
  use Exchat.Type.ZeroableDateTime
  use Ecto.Schema
  import Ecto.Changeset

  @derive {Inspect, except: [:password]}
  schema "users" do
    field :email, :string
    field :password, :string, virtual: true
    field :hashed_password, :string
    field :confirmed_at, Exchat.Type.ZeroableDateTime

But when I run “mix phx.server”, I am still getting this error:

== Compilation error in file lib/exchat/accounts/user.ex ==
** (UndefinedFunctionError) function Exchat.Type.ZeroableDateTime.__using__/1 is undefined or private
    Exchat.Type.ZeroableDateTime.__using__([])
    lib/exchat/accounts/user.ex:2: (module)
    (stdlib 3.15.2) erl_eval.erl:685: :erl_eval.do_apply/6
    (elixir 1.12.2) lib/kernel/parallel_compiler.ex:319: anonymous fn/4 in Kernel.ParallelCompiler.spawn_workers/7
11

I figured it out, I had to remove the “use Exchat.Type.ZeroableDateTime” and it all compiled and worked:

defmodule Exchat.Accounts.User do
  use Ecto.Schema
  import Ecto.Changeset

  @derive {Inspect, except: [:password]}
  schema "users" do
    field :email, :string
    field :password, :string, virtual: true
    field :hashed_password, :string
    field :confirmed_at, Exchat.Type.ZeroableDateTime

Hopefully this helps another noob in the future. Thanks @dimitarvp

12

Yep, the final code is exactly how it should be. You do NOT use the module, you either alias it or you use the fully-qualified name (as you did).