Pgloader: Mysql: date out of range

[fernandoluizao]

Hi,

Looks like I'm having the same problem as described on https://github.com/dimitri/pgloader/issues/252
I'm trying to convert a mysql database, using no extra options:

pgloader mysql://user@localhost/foo postgresql:///foo

Gives me the following error:

Database error 22008: date/time field value out of range: "0000-00-00"
CONTEXT: COPY maquinas, line 124, column ano_de_fabricacao: "0000-00-00"

Table schema:

CREATE TABLE `maquinas` (
  `ANO_DE_FABRICACAO` date DEFAULT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

pgloader installed from APT repository, version 3.3.1.

I'm doing something wrong?

[dimitri]

Nothing wrong. The default casting rules for dates involves taking care of zero dates only when that's a default value, in order to avoid introducing a performances penalty of checking all the values in the database when it might not be needed.

Try doing:

pgloader --cast "cast date to date using zero-dates-to-null" mysql://... pgsql:///foo

[fernandoluizao]

Sorry about that. By the way, what works for me was:

pgloader --cast "type date to date using zero-dates-to-null" 'mysql://... postgresql://

Thanks for your time, great project!

[vdebuen]

Hi dimitri

I was getting the same error as fernandoluizao and I tried the solution proposed here:

pgloader -v --cast "cast date to date using zero-dates-to-null" mysql://... postgresql://...

However, what I get is a parsing error

2020-08-04T14:28:04.016000Z NOTICE Starting pgloader, log system is ready.
2020-08-04T14:28:04.027000Z ERROR Could not parse --cast ("cast date to date using zero-dates-to-null"): At

  cast date to date using 
  ^ (Line 1, Column 0, Position 0)

In context CAST-SOURCE:

While parsing CAST-SOURCE. Expected:

     the character Tab
  or the character Newline
  or the character Return
  or the character Space
  or the string "--"
  or the string "/*"
  or the string "column"
  or the string "type"

Could not parse the command line: see above.

I guess it's bullshit, but I can't figure out where the problem is.

Thanks in advance!

[dimitri]

Hi @vdebuen,

My proposal for a fix reads:

pgloader --cast "type date to date using zero-dates-to-null" 'mysql://... postgresql://

And your attempt reads:

pgloader -v --cast "cast date to date using zero-dates-to-null" mysql://... postgresql://...

Turns out it's not the same thing. Can you try the first variant, it should work, if I'm to trust what I copy/pasted last time...