NULL instead of DEFAULT value in migration

[Sahaquielxo]

Hi, I have a table in MySQL with:

`last_update` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

but when the default value is used in MySQL, pg_chameleon rewrite it as Null, so get an error:

2018-05-14 14:07:45 MainProcess ERROR pg_lib.py (3406): SQLCODE: 23502 SQLERROR: ERROR: null value in column "last_update" violates not-null constraint
DETAIL: Failing row contains (306238, http://example.com, 0, example, 3093, 3, PHY, 1, Test text, [email protected], , null, , null, 2002-01-17 18:47:39, 2002-01-17 18:47:39, null, null, 0, , , , 0, 1, 0, 0, 1, 0, null, 0, null, null).

The real data:

mysql> SELECT * FROM account WHERE id='306238'\G
*************************** 1. row ***************************
               id: 306238
              url: http://example.com
        parent_id: 0
           passwd: example
            flags: 3093
              gif: 3
          orgtype: PHY
              grp: 1
             name: Test text
             addr: [email protected]
        alt_title:
     announce_url: NULL
   announce_title:
         add_year: NULL
       start_time: 2002-01-17 18:47:39
       reset_time: 2002-01-17 18:47:39
      rateon_time: NULL
     rateoff_time: NULL
      start_count: 0
         keywords:
            owner:
          creator:
            alive: 0
     user_deleted: 1
          in_rate: 0
              rss: 0
          quality: 1
     announce_rss: 0
          favicon: NULL
     days_wo_stat: 0
dismiss_reason_id: NULL
      last_update: 0000-00-00 00:00:00
1 row in set (0.00 sec)

Can you help me with that? If there a way to fix an error?

[Sahaquielxo]

As I read here https://stackoverflow.com/questions/33344587/insert-0000-00-00-000000-datetime-from-mysql-to-postgresql Postgres can't work with 0 values.
So if there a way (via config file may be) to make correctly migration with these values?

[the4thdoctor]

Hi, is this happening during the init replica or during the replication process?

[Sahaquielxo]

It is init step

[the4thdoctor]

I had a similar issue fixed in a previous issue, the #16 .
unfortunately the replica library nullifies this specific date format and therefore I followed the same rule during the init replica. I'll try to find a way to skip the NOT NULL creation for specific tables/fields. But probably this enhancement will come out with the version 2.0.8 by the middle of June, hopefully.

[Sahaquielxo]

Thanks.
If it's quick to ask, can you say, what will happen if I'll ignore the errors?
I will have tables with null instead of 0, or the row with error just will be skipped?

[the4thdoctor]

the row with error should be skipped. if the entire init_replica is aborted then it is definitely a bug :)

[Sahaquielxo]

No, init_replica running well. Just wanted to insert incorrect data and fix it with sed or smth like that :( Nvm, waiting for 2.0.8 :)

[Sahaquielxo]

Once more, I've found that pg_chameleon in connection to the source DB and making SELECT queries like:

| 1577366 | pgrep          | 1.2.3.4:39718               | NULL   | Query   |       0 | Opening tables                   | SELECT
						column_name,
						data_type
					FROM
						information_schema.COLUMNS
					WHERE
							table_schema='test100'
						AND table_name='res_formats_positions_v2_2018022201'
					ORDER BY
						ordinal_position |

Can you explain me the reason for these selects? I've placed all tables expect 1 in skip_tables in configuration file, also table 'res_formats_positions_v2_2018022201' is in skip_tables.

P.S. Newbie in Github issues. If my questions are off-topic, let me pls know where can I find an answer on my question, all how can I write you in email/telegram/etc.

[the4thdoctor]

mmm interesting. is this happening at init_replica or during the replica?

In theory the having tables in skip tables should not generate any query.

[Sahaquielxo]

It is going on during the replica_start running.
I've marked all my tables expect 1 in skip field, but pg making selects anyway in my case.

[the4thdoctor]

I will have a look, thanks.

[the4thdoctor]

Hi, can you open a new ticket for the information_schema issue?
Can you please add your configuration file (please remove any password)?

Thanks

[the4thdoctor]

never mind the ticked.

The method mysql_engine.get_table_type map executes a query on all tables present in the replicated schemas ignoring the skip_tables. This happens every time the replica stream is initiated and is required for mapping the binary data which need to be hexified.

I agree that skip tables should be used and I can work on a fix if this is source of concern.

[the4thdoctor]

regarding the date, there is a PR on the mysql replica library, waiting since September 2017.
julien-duponchelle/python-mysql-replication#228

The moment this fix is available in the replica library I'll be able to sort out the problem properly.

[the4thdoctor]

I'm closing the ticket for now. I'll look after the problem when the PR will be merged in the mysql replica library.

[strafer]

@the4thdoctor Hi, can I initiate the reopening of this issue?

I am currently setting up MySQL → PostgreSQL replication and there are a lot of date type fields in the source database with the default value 0000-00-00 00:00:00 and, accordingly, a lot of records with this value. When initializing a replica, I get a lot of errors like those listed in the first post. Is it possible to fix it?

[the4thdoctor]

I'm reopening the issue and I'll have a look asap.