topic Re: Replication of large field gets replicated in consecutive rows in Qlik Replicate

Replication of large field gets replicated in consecutive rows

kritostar — Fri, 12 May 2023 20:25:38 GMT

Hello, so I have a replication that is kind of propagating a large field content from a row to the consecutive ones, and we don't knwo why. It i hapenning in more than one table

Any ideas?

Source:

Destination:

Source:

Source

Destination:

Destination

Re: Replication of large field gets replicated in consecutive rows

narendersarva — Fri, 12 May 2023 20:27:03 GMT

Hi @kritostar

Please check the target db large field data type is matching with the source db large field data type.

What is your replicate version?

Thanks
Naren

Re: Replication of large field gets replicated in consecutive rows

kritostar — Fri, 12 May 2023 20:30:09 GMT

Hi @narendersarva

On both DBs the field is text Charset latin1

The version is 2022.11.0.208

Re: Replication of large field gets replicated in consecutive rows

Dana_Baldwin — Fri, 12 May 2023 20:33:22 GMT

Hi @kritostar

What is the source and target endpoints of the task?

Assuming the target is a file, or at least uploads a file as an interim step - how is the field delimiter and record delimiter set?

Thanks,

Dana

Re: Replication of large field gets replicated in consecutive rows

kritostar — Fri, 12 May 2023 20:40:07 GMT

The source and target are tables on the DB, not a file.

Re: Replication of large field gets replicated in consecutive rows

Dana_Baldwin — Fri, 12 May 2023 20:47:22 GMT

Hi @kritostar

What databases are you using for the source and target endpoints?

Thanks,

Dana

Re: Replication of large field gets replicated in consecutive rows

kritostar — Fri, 12 May 2023 21:05:22 GMT

Both are MySQL databases. I think it has something to do with the delimiters, can it be? I have . - ( ) on those fields

Re: Replication of large field gets replicated in consecutive rows

Dana_Baldwin — Fri, 12 May 2023 21:38:46 GMT

Hi @kritostar

By default, Replicate will use CSV files to move data into a MySQL target database during the full load phase of the task. Do these fields contain any single quotes, double quotes, commas or "new line" characters?

I'm not sure at the moment how to redefine the delimiters if needed - but one way that might work around this issue is to disable the "load using CSV" option. The full load might run slower as a result.

To try this, go to your target endpoint, advanced tab, and click Internal Parameters. Enter an exclamation point and hit enter on your keyboard to show a listing. Scroll down to loadUsingCSV and hit enter on your keyboard to add it to the list of parameters. Uncheck the box in the value column. Click OK to close the window, then click save on the endpoint.

FYI, changes to endpoint settings do not take effect until the tasks that use them are stopped and resumed, as the settings are only read during task startup. Probably not relevant in this situation, but wanted you to know.

Thanks,

Dana

Re: Replication of large field gets replicated in consecutive rows

kritostar — Fri, 12 May 2023 21:47:38 GMT

Ok, I'll try this and come back. Thanks

Re: Replication of large field gets replicated in consecutive rows

Dana_Baldwin — Fri, 12 May 2023 22:00:08 GMT

Hi @kritostar

You can also replace or delete/remove the new line characters in task settings, Character Substitution. For the source character, enter an A. To delete it, use 0 as the substitute character (or choose a unicode value of your preference).

This might perform better than disabling the CSV load.

Thanks,

Dana

Re: Replication of large field gets replicated in consecutive rows

narendersarva — Mon, 15 May 2023 20:54:48 GMT

Hi @kritostar

Please open a support case, so that our technical support team can assist you with some working sessions if required.

Thanks
Naren

Re: Replication of large field gets replicated in consecutive rows

kritostar — Mon, 15 May 2023 21:38:13 GMT

So, none of the two options seemed to work. Put as much as special characters I found, they are getting replaced on the destination, but still long rows are been mixed on other rows

Re: Replication of large field gets replicated in consecutive rows

kritostar — Mon, 15 May 2023 21:57:30 GMT

Ok, thanks! I have just created one

Re: Replication of large field gets replicated in consecutive rows

shashi_holla — Mon, 15 May 2023 22:46:32 GMT

Hi @kritostar

Is the source MySQL with 8.0.30 ODBC driver?

Then it's bug on the driver and is documented:

Due to an issue with MySQL ODBC Driver 8.0.30, empty TEXT columns may not be replicated correctly during Full Load. For example, if one table row contains a TEXT column with a value and the same column in the next row contains an empty value (but not NULL), both rows will display the value of the first row on the target.

https://bugs.mysql.com/bug.php?id=108431

Workaround would be downgrade the driver to 8.0.26.

Thank you,

Re: Replication of large field gets replicated in consecutive rows

kritostar — Tue, 16 May 2023 17:57:28 GMT

Thanks! I am 99% sure this is the problem, I will ask to downgrade the drive and if it works, I'll accept the solution.

Re: Replication of large field gets replicated in consecutive rows

shashi_holla — Tue, 16 May 2023 18:04:27 GMT

Sounds good, please update how it goes.

Thank you,

Re: Replication of large field gets replicated in consecutive rows

kritostar — Thu, 18 May 2023 20:33:34 GMT

So, we cannot downgrade the driver because we fix another issue with an updated one.

How can I add a global rule to replace an empty text field with any other char like '-' ?

Re: Replication of large field gets replicated in consecutive rows

shashi_holla — Thu, 18 May 2023 21:28:33 GMT

@kritostar

Please add the table level transformation on the column and set it to

ifnull($ColumnName,'-')

CASE $ColumnName WHEN null Then '-' Else $ColumnName END

But I doubt if it will work or not depending on how driver treats the data but give it a try.

Thank you,

Re: Replication of large field gets replicated in consecutive rows

Heinvandenheuvel — Thu, 18 May 2023 21:28:59 GMT

>>> How can I add a global rule to replace an empty text field with any other char like '-' ?

It'll be too late for that. If I understand the driver issue correctly then is it the driver which adds the repeated text chunks so Replicate does not get an empty field to replace. Replicate does not have an option to 'remember' and detect 'same as last time'.

>> cannot downgrade the driver because we fix another issue with an updated one

Is that other issue visible with Replicate or elsewhere and if with Replicate in this task (or other tasks processing table wit this issue?) . Maybe you can add a specify a specific (older) MySQL driver Replicate or specific (newer) one to the other application? You could perhaps have multiple endpoints to the same DB, using different drivers through some connection setting possibly "additionalConnectionProperties" ? Not sure if/how this would work exactly for MySQL. If this is a Linux server then perhaps some magic in /etc/odbcinst.ini ?

Hein.