topic Re: Replication tasks encountered the following error: Logging of database data is disabled in Qlik Replicate

Replication tasks encountered the following error: Logging of database data is disabled

desmondchew — Tue, 25 Jan 2022 01:55:08 GMT

We have several tasks that encountered licensing error and unable to run CRC properly. This happened after we tried to reload tables in the tasks.

Log file shows logging of database is disabled but we do have the valid license.

Please let me know if you need further information for troubleshooting or any assistance to resolve this is highly appreciated.

Re: Replication tasks encountered the following error: Logging of database data is disabled

john_wang — Tue, 25 Jan 2022 02:06:17 GMT

Hello @desmondchew ,

The information "Logging of database data is disabled" is irrelevant to your task failure. I've an article about the info Logging of database data is disabled . Hope it helps.

Regards,

John.

Re: Replication tasks encountered the following error: Logging of database data is disabled

desmondchew — Tue, 25 Jan 2022 02:38:42 GMT

Thanks for clarifying so that I am not looking into the wrong direction. I am seeing high latency in apply. Should I turn on verbose logging? I will share the logs here.

Re: Replication tasks encountered the following error: Logging of database data is disabled

john_wang — Tue, 25 Jan 2022 02:48:04 GMT

Hello @desmondchew ,

Thanks for the update.

Set logging component PERFORMANCE to Trace is a good start, keep it running more than half hour will be better, or keep it until you get the latency occurs the collect Diagnostics Packages.

Please do not share the logs here because of Security reason and it's impossible to download the attachment to our local machine, I'd like suggest you to open support case for the latency issue.

Regards,

John.

Re: Replication tasks encountered the following error: Logging of database data is disabled

desmondchew — Tue, 25 Jan 2022 14:50:20 GMT

Hi John,

Thank you for your reply. I have open a case but it seems not pick up yet. Case id: 00022189.

I have set the logging component to performance for the respective task. It's been running for a while. I would really like to work closely to resolve this issue.

Thank you.

Desmond

Re: Replication tasks encountered the following error: Logging of database data is disabled

Heinvandenheuvel — Tue, 25 Jan 2022 15:40:08 GMT

[updated post]

I'm confused - you write "tasks that encountered licensing error " as well as " we do have the valid license." which is it? Show (log output) about the errors and and indication as to why the license should be valid. (New Year?!)

You wrote "unable to run CRC properly. ". Do you perhaps refer to CDC?

You wrote "logging of database is disabled" I suspect with @john_wang that this is just a warning, based on a deliberately chosen task setting, but I am a little confused why that is marked with a RED (X).

The latency is almost certainly due to failure to connect to the target endpoint as indicated by the red (x) next to the endpoint. That may well be due to an invalid license so Focus on the license first. Replicate can(is) licensed for specific endpoint. Got to the SERVER section in the UI and select license. look for target type, expiration date and server name. See attached example.

The latency is just building a minute every minute as the Replicate apply engine gets a recoverable error when trying to connect in a loop. One a valid license is there it will move forward. It is likely confirmed in the logs already. Start at the end, look for target_apply backward. What are the messages? If not clear enough try increasing log level for target_apply to trace or verbose for a while.

Please also try improve your trouble shooting skills! Always add source DB type and version, target DB type and replicate version to a problem. Try to get actual TEXT in text (not picture) format. Why provide a picture with half a message if you can click on the message and copy the actual text. See second attachment. The text is probably also to be found in the <replicatedata>\logs\reptask__<taskname>.log file(s).

Good luck!

Hein

Re: Replication tasks encountered the following error: Logging of database data is disabled

desmondchew — Wed, 26 Jan 2022 03:17:07 GMT

Hi,

Please see attached source and target license. We do have a valid license.

Source Types	FileChannel,Oracle
Target Types	FileChannel,MySQL,Oracle
Usage
Version	6.5

Please see excerpt of the log file - reptask_NAVI_DEV4.log. I have replaced all the variables or values that could be sensitive. The error I spotted is on Illegal mix of collations.

00005012: 2022-01-25T21:49:44 [TARGET_APPLY ]E: Failed to get bulk update statement for table 'DB_DEV1'.'DB_TABLE_A' [1022502] (odbc_util.c:4204)
00005012: 2022-01-25T21:49:44 [TARGET_APPLY ]E: Failed to apply UPDATE (3) for table 9 [1022502] (bulk_apply.c:2983)
00005012: 2022-01-25T21:49:44 [TARGET_APPLY ]E: Error forwarding bulk [1022502] (bulk_apply.c:826)
00005012: 2022-01-25T21:49:44 [TARGET_APPLY ]I: Error executing data handler [1022502] (streamcomponent.c:1998)
00005012: 2022-01-25T21:49:44 [TASK_MANAGER ]I: Stream component failed at subtask 0, component st_0_MySQL_DEV [1022502] (subtask.c:1396)
00005012: 2022-01-25T21:49:44 [TARGET_APPLY ]I: Target component st_0_MySQL_DEV was detached because of recoverable error. Will try to reattach (subtask.c:1511)
00009084: 2022-01-25T21:49:44 [UTILITIES ]I: Mail "[servername1.com\Any errors] NAVI_DEV4:encountered errors" sent successfully (notification_manager.c:1916)
00005012: 2022-01-25T21:49:49 [STREAM_COMPONEN ]I: Target last committed record id from the previous run is '6329' (streamcomponent.c:1661)
00005012: 2022-01-25T21:49:49 [TARGET_APPLY ]I: Going to connect to ODBC connection string: DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=dev_db.com;PORT=3306;DATABASE=DB_DEV1;FOUND_ROWS=1;CHARSET=binary;NO_LOCALE=1;ENABLE_LOCAL_INFILE=1;UID=userid1; (odbc_endpoint_imp.c:2884)
00005012: 2022-01-25T21:49:49 [TARGET_APPLY ]I: ODBC driver version: '08.00.0024' (ar_odbc_conn.c:612)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]I: ODBC driver version: '08.00.0024' (ar_odbc_conn.c:612)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]I: Target database time zone is UTC+0 hours (odbc_endpoint_imp.c:4748)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]I: Target endpoint 'Mysqltarget' is using provider syntax 'MySQL' (provider_syntax_manager.c:879)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]I: Transaction size is 10000. Array size is 1000. (odbc_endpoint_imp.c:972)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]I: Loading tables in 2 parallel threads by default (odbc_endpoint_imp.c:1937)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]I: Restore bulk state. Last bulk last record id - '0', last applied record id - '0', target confirmed record id - '6329', sorter confirmed record id - '6329' (endpointshell.c:1918)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]I: Set Bulk Timeout = 30000 milliseconds (bulk_apply.c:562)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]I: Set Bulk Timeout Min = 1000 milliseconds (bulk_apply.c:563)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]I: Working in bulk apply mode (endpointshell.c:1926)
00005012: 2022-01-25T21:49:50 [ASSERTION ]W: Cannot get a 'max_string_size' parameter. Using STANDARD 'max_string_size' (oracle_endpoint_conn.c:577)
00005012: 2022-01-25T21:49:50 [SOURCE_CAPTURE ]I: Source endpoint 'Oracle' is using provider syntax 'Oracle' (provider_syntax_manager.c:873)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]I: Target components st_0_MySQL_DEV was reattached after 5 seconds. (subtask.c:1380)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]I: Start applying of 'UPDATE (3)' cached events for table 'DWDM'.'DB_TABLE_A' (9). (bulk_apply.c:2878)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]I: Error in bulk, bulk state: bulk confirmed record id - '0', bulk last record id - '0', confirmed record id - '6329', sorter confirmed record id - '6329' (bulk_apply.c:2464)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]E: RetCode: SQL_ERROR SqlState: HY000 NativeError: 1267 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.21]Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' [1022502] (ar_odbc_stmt.c:3974)
00005012: 2022-01-25T21:49:50 [TARGET_APPLY ]E: Failed to prepare statement 'UPDATE `DB_DEV1`.`DB_TABLE_A` INNER JOIN `DB_DEV1`.`attrep_changes61C12A0BEBFDC43C` ON ( `DB_DEV1`.`DB_TABLE_A`.`ID`=`seg1` AND `DB_DEV1`.`DB_TABLE_A`.`FR_N`=`seg2` AND `DB_DEV1`.`attrep_changes61C12A0BEBFDC43C`.`seq` >= ? and `DB_DEV1`.`attrep_changes61C12A0BEBFDC43C`.`seq` <= ? ) SET `DB_DEV1`.`DB_TABLE_A`.`FR_N`= CASE WHEN `DB_DEV1`.`attrep_changes61C12A0BEBFDC43C`.`col1` IS NULL THEN `DB_DEV1`.`DB_TABLE_A`.`FR_N` WHEN `DB_DEV1`.`attrep_changes61C12A0BEBFDC43C`.`col1` = '<att_null>' THEN NULL ELSE `col1` END ,`DB_DEV1`.`DB_TABLE_A`.`P_OF_LOAD_NM`= CASE WHEN `DB_DEV1`.`attrep_changes61C12A0BEBFDC43C`.`col2` IS NULL THEN `DB_DEV1`.`DB_TABLE_A`.`P_OF_LOAD_NM` WHEN `DB_DEV1`.`attrep_changes61C12A0BEBFDC43C`.`col2` = '<att_null>' THEN NULL ELSE `col2` END

Happy to work to resolve this issue.

Thanks
D

Re: Replication tasks encountered the following error: Logging of database data is disabled

Heinvandenheuvel — Wed, 26 Jan 2022 04:18:44 GMT

Good work @desmondchew !

Now we have something to work with. Licenses seem fine. Done.

The observed Latency is not a performance issue, but a side effect of a functionality/implementation issue. As I suggested earlier Replicate thinks it can clear an update error by simply reconnecting and retrying.

However, where several 'sqlState: HY000' situation may recover, the specific NativeError: 1267 will never clear. And the task is in a never ending loop (personally I see such repeats as a longstanding implementation deficiency. I feel they should only try to reconnect for errors know to possibly clear like say Db down, deadlock, network not reachable. I've seen too many of these futile retry attempts.

Anyway, you have found the core error yourself:

1267 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.21]Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '=' [1022502] (ar_odbc_stmt.c:3974)]

I'd say you have a legit support case here. Submit the log you created along with table definition on source and target for 'DWDM'.'DB_TABLE_A' and if you can grab a create statement for replicate internal net changes table `DB_DEV1`.`attrep_changes61C12A0BEBFDC43C`. Just 'kill' a tasks such that it has no change to clean it up or enable trace logging for TARGET_APPLY and catch the create there.

If you want to work this yourself some more, you need to figure out which column triggers the error, or maybe all string columns cause this because the source and target databases are too different - butthat's unlikely because the net changes table and target table in the update are on the same db.

Ask yourself how(which) columns do not have a default collation sequence or why.

You may want to manually try to execute the update statement in studio in the hope the error handling gives further details (which columns)

I would also recommend to try to run in transactional apply mode as a workaround and as a knowledge point in further discovery. I suspect it will work in non-batch mode but it may not work fast enough to be workable long term.

good luck!

Hein.

Re: Replication tasks encountered the following error: Logging of database data is disabled

desmondchew — Wed, 26 Jan 2022 05:36:01 GMT

I have set the table logging target_apply to trace for more information. I was able to get the create table SQL. I have opened a case 22189 for further troubleshooting. You mentioned on "kill" a task that has not change. What do you mean?

Meanwhile I have set the task to transactional mode and it seems to work as workaround.

Thanks
Des

Re: Replication tasks encountered the following error: Logging of database data is disabled

john_wang — Wed, 26 Jan 2022 08:24:00 GMT

Hello @desmondchew ,

"Kill" a task means you stop/kill the task process by task manager, or "kill -9" command to forcedly stop the process so far it have not the opportunity to "clean up" the net change table, the table will be left there for your further analysis, you can check the internal net change table columns collation information. I think this is @Heinvandenheuvel intention.

However I think the net change table columns default collation should be as same the MySQL Server level setting. you need to check why there are different collations. For example, you remove some columns to see which column lead the error.

Transactional mode is a WA however Batch apply mode has better performance.

BTW, the case will be picked up by region support team.

Regards,

John.

Re: Replication tasks encountered the following error: Logging of database data is disabled

desmondchew — Thu, 27 Jan 2022 02:14:52 GMT

Hi John/Hein,

Thank you for the pointers. I have found out that the change table- "attrep_apply_exceptions" is using utf8mb4_0900_ai_ci collation whereas all the other user tables are on "utf8mb4_general_ci". This change table is from Qlik to store changes. How can we resolve this by recreating the table or convert it to "utf8mb4_general_ci" collation?

Regards

Desmond

Re: Replication tasks encountered the following error: Logging of database data is disabled

john_wang — Thu, 27 Jan 2022 02:46:17 GMT

Hello Desmond,

May I know what's the default collation of the target database?

whereas all the other user tables are on "utf8mb4_general_ci"

How the other user tables being created? Manually, or automatically by Replicate?

Regards,

John.

Re: Replication tasks encountered the following error: Logging of database data is disabled

desmondchew — Thu, 27 Jan 2022 03:11:50 GMT

Hi John,

The default collation is utf8mb4_general_ci. All tables are replicated using Qlik except two tables are manually created. Manually created tables are using default collation.

Regards
Desmond

Re: Replication tasks encountered the following error: Logging of database data is disabled

Heinvandenheuvel — Thu, 27 Jan 2022 05:56:25 GMT

> I have found out that the change table- "attrep_apply_exceptions" is using utf8mb4_0900_ai_ci collation

That might be a typo, but the attrep_apply_exceptions is as the name implies a table to record apply exceptions.

The net changes tables are named something like: attrep_changes61C12A0BEBFDC43C

You indicated you captured the create statement for that - does it request a specific collation? Probably not. Perhaps share that create statement here? But ead on, I don't think that is it.

Was the server or database default collation perhaps altered at some point? Unlikely I know, but then again I noticed this: https://www.monolune.com/what-is-the-utf8mb4_0900_ai_ci-collation/ - "utf8mb4 has become the default character set, with utf8mb4_0900_ai_ci as the default collation in MySQL 8.0.1 and later. Previously, utf8mb4_general_ci was the default collation.

8.0.1 was released in 2017, but still, that would explain nicely what you see!

(btw .. see why for you always need to provide db and version numbers for questions ?!)

So maybe the database was upgrade and now new tables such as the attrep_changes tables are new created as utf8mb4_0900_ai_ci collation whereas the existing tables were/are utf8mb4_general_ci

Seems to me you may have to use something like "ALTER DATABASE database_name CHARACTER SET character_set_name COLLATE collation_name;". ( https://www.mysqltutorial.org/mysql-collation/ )

If you reload a table in Replicate, with drop, what collating sequence does it get? Reload all??

fwiw - The MySQL endpoint has an internal parameter CharsetMapping which you can set to strings like "latin1,1252,latin9,99999" to map charactersets to codepage but best I know there is no parameter explicit about collation.

hth,

Hein.

Re: Replication tasks encountered the following error: Logging of database data is disabled

john_wang — Thu, 27 Jan 2022 06:37:20 GMT

Hello @Heinvandenheuvel ,

Thank you so much for your excellent assistance. I had 2 meetings today and I'm sorry for my slow response @desmondchew . Let us know if need any additional assistance.

Regards,

John.

Re: Replication tasks encountered the following error: Logging of database data is disabled

desmondchew — Thu, 27 Jan 2022 10:35:24 GMT

No worries. Thanks John and Hein for your replies.

I will need to check with the architecture team why there is a difference in these tables collations. We are running on MySQL 8.0.21. As per your document link, the default MySQL DB is utf8mb4_0900_ai_ci . The MySQL schema was created as utf8mb4_0900_ai_ci but the user database (dev-db) was created as utf8mb4_general_ci . I shall follow up on our end on this to check.

Nevertheless, the user database is created using utf8mb4_general_ci , all the Qlik replicated tables are in utf8mb4_general_ci . But why is the table "attrep_apply_exceptions" created using utf8mb4_0900_ai_ci ? How can we rectify this? Is there an option in Qlik Replicate to overwrite when replicating?

By the way, I found there are two "attrep_apply_exceptions" tables. One is located in "attrep_control" schema and another in dev-db schema. (please see schemas collation.png). Attrep_control is created with utf8mb4_general_ci (attrep_applyl_exceptions.png).

Thank you.
Desmond

Re: Replication tasks encountered the following error: Logging of database data is disabled

john_wang — Thu, 27 Jan 2022 13:12:51 GMT

Hello @desmondchew ,

Replicate detects if "attrep_apply_exceptions" exists, if yes then use it. otherwise will create it.

So if it was created (by default the collation is same with the DB) and then the DB level collation changed, this table collation will be kept unless you removed it and let Replicate recreate it again. Another possibility is this control table was created in another DB (different DB with the user tables).

Anyway, personally I do not suggest to continue this article as it's way far away from the topic. it's confusion. you may open a new one with informative subject.

Regards,

John.

Re: Replication tasks encountered the following error: Logging of database data is disabled

Heinvandenheuvel — Fri, 28 Jan 2022 00:03:52 GMT

Please focus on the attrep_changes table. That's the one leading to "Illegal mix of collations" because columns for it are used in a join with a column with a different collation.

The attrep_exception table is only freshly create when it doe not exist, but the net-changes table is recreated every time a task is started and several times more when usertables with incompatible layout have changes.

It is deleted on clean task exit, which is why kill was suggested ( kill - 9 under Linux, Taskmanager or pskill for Windows)

I don't think you can control its collating sequence other then through the schema/db/server default.

Hein.

Re: Replication tasks encountered the following error: Logging of database data is disabled

desmondchew — Wed, 02 Feb 2022 13:08:56 GMT

Noted John. I have changed to transactional mode.

Thanks
Desmond