NULL values get converted to zero when replicating mysql table

[avokicchi]

I'm having an issue in which it appears that NULL values are not being copied properly when replicating a table. This is my replicadb.conf:

######################## ReplicadB General Options ########################
mode=complete
jobs=1
fetch.size=100
verbose=true
############################# Source Options ##############################
source.connect=jdbc:mysql://source.example.com:3306/test
source.user=
source.password=
source.table=nodes
source.columns=id,label,deleted_at,is_required
############################# Sink Options ################################
sink.connect=jdbc:mysql://sink.example.com:3306/test
sink.user=
sink.password=
sink.table=nodes
sink.columns=id,label,deleted_at,is_required

The source and sink are both MySQL 8 servers with identical database structure. Only the data differs. Perhaps of interest, sql_mode is set to "".

This is the database structure:

CREATE TABLE nodes (
  id int(10) UNSIGNED NOT NULL,
  label varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  deleted_at int(11) DEFAULT NULL,
  is_required tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE nodes
  ADD PRIMARY KEY (id),
  ADD KEY deleted_at (deleted_at);

ALTER TABLE nodes
  MODIFY id int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

This is the data present at the source table:

INSERT INTO nodes (id, label, deleted_at, is_required) VALUES
(1, 'Was the banana straight?', NULL, 0),
(2, 'Are we living in a simulation?', NULL, 1),
(3, 'Was the product there on entry?', NULL, 0),
(4, 'Was the clown wearing his pinstripe suit?', NULL, 0),
(5, 'Was the walmart greeter wearing a top hat?', 1, 0),
(6, 'Is this a test  (yes)?', 1, 1),
(7, 'Is everything awesome?', NULL, 0),
(8, 'Is the product there on entry?', 3, 0),
(9, 'Is the product a thing?', 3, 0),
(10, 'Is the price tag there?', 3, 0);

After replication, the data present at the sink table is as follows:

INSERT INTO nodes (id, label, deleted_at, is_required) VALUES
(1, 'Was the banana straight?', 0, 0),
(2, 'Are we living in a simulation?', 0, 1),
(3, 'Was the product there on entry?', 0, 0),
(4, 'Was the clown wearing his pinstripe suit?', 0, 0),
(5, 'Was the walmart greeter wearing a top hat?', 1, 0),
(6, 'Is this a test  (yes)?', 1, 1),
(7, 'Is everything awesome?', 0, 0),
(8, 'Is the product there on entry?', 3, 0),
(9, 'Is the product a thing?', 3, 0),
(10, 'Is the price tag there?', 3, 0);

As you can see, all NULL values are turned to zeros.

I have played around with various nullString and quoteMode values with no luck. Was wondering if I am simply doing something wrong, or that this is a known issue?

[osalvador]

Hi @avokicchi,

I'll review the issue and get back to you as soon as possible.

Thanks!

[gurujsprasad]

Hi @osalvador,

Firstly, I would like to thank you for such a simple to use great tool.

I had similar issues while migrating data from PostgreSQL to MySQL.

1. NULL from int in postgre will store as 0 in mysql
2. NULL from varchar in postgre as empty string in mysql
3. true or false from bool in postgre as 0 tinyint in mysql

Looks like the issue with NULL is fixed and closed. Will this also fix while migrating from postgresql to mysql? If yes, Can you please let me know when it will be released? Else, is it better to create a new issue.

Appreciate all the help!

[osalvador]

Hi @gurujsprasad and @avokicchi,

I've created a new release that fixes this issue. You can download on the releases section: https://github.com/osalvador/ReplicaDB/releases/tag/v0.11.1

This patch should fix all replications when the sink database is a MySQL, regardless of the source type.

Thanks!

[gurujsprasad]

Hi @osalvador,

I got the latest release and ran it on postgresql to mysql db migration, NULL issues are resolved.

But true or false from bool column in postgre is saved as value 0 tinyint in mysql. It should 0 for false and 1 for true.

Can you please check on this? Not sure if I am going wrong in any step.

Thank you.

[osalvador]

Hi @gurujsprasad,

You should cast your values if source and sink tables do not have the same structure.

You can create your own custom query to convert the Boolean column into your required data type.

Does this solve your requirements?

Regards!

[gurujsprasad]

Hi @osalvador,

Yep, this solves the requirement. I was following the same. But a confirmation from you is great!

Thank you