An error on attrep_changes CDC table appears in the log

[desmondchew]

We have setup several tables to replicate from Oracle to MySQL. It has completed full loading and currently on CDC. What bothers me is the logfile is consistently showing errors.

Is there a way to resolve this by increasing the bulk settings? Do I need to worry about this error?

RetCode: SQL_ERROR SqlState: HY000 NativeError: 1118 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.21]Row size too large. The maximum row size.

Error forwarding bulk
Failed to create net changes table for bulk apply
Execute create net changes table statement failed. CREATE TEMPORARY TABLE `ABCdev-db`.`attrep_changesC036C4B87242D0AD` ( `seq` INTEGER NOT NULL, `col1` VARCHAR(4000), `col2` VARCHAR(200), `col3` VARCHAR(100), `col4` VARCHAR(100), `col5` VARCHAR(100), `col6` VARCHAR(100), `col7` VARCHAR(100), `col8`
..
VARCHAR(100), `col9` VARCHAR(100), `col10` VARCHAR(100), `col11` VARCHAR(100), `col12` VARCHAR(100), `col13` VARCHAR(100), `col14` VARCHAR(100), `col15` VARCHAR(100), `col16` VARCHAR(100), `col17` VARCHAR(100), `col18` VARCHAR(100
...
VARCHAR(20), `seg1` VARCHAR(41), `seg2` VARCHAR(80), `seg3` VARCHAR(41) ) CHARSET=utf8mb4
RetCode: SQL_ERROR SqlState: HY000 NativeError: 1118 Message: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.21]Row size too large. The maximum row size

Thank you.

[john_wang]

Hello @desmondchew ,

The message "Row size too large" is common error in MySQL world, many factors may lead the error especially the default engine type is InnoDB. You may check MySQL Doc or MariaDB doc to see if the problem can be solved.

The error message is cut it's hard to tell the exact reason. You may try to execute the the same CREATE TABLE SQL to understand the reason. In Qlik Replicate perspective, we have several options:

1- If the total size exceed MySQL limitation

     Change the target table's some columns to TEXT or BLOBs

2- If too many columns in a single table

     Reduce the tables number in the task, or split the single task to multiple tasks

3- Change to Transactional Apply mode from Batch Apply mode

     However Batch Apply mode has better performance

4- Change the net changes table engine from InnoDB to MyISAM

     MyISAM does not have that "Row size too large" limitations in MySQL.

If you want to take the option (4) feel free to let me know, I will provide the detailed steps in a dedicated article.

Regards,

John.

[desmondchew]

Hi John,

Apologies for not making it clear. I am attaching the screenshot showing the beginning of the error. It seems that the error is related to a Qlik CDC's table called - "attrep_changesEE21D208566E9EB3". https://community.qlik.com/t5/Qlik-Replicate/Replicate-table-creation-quot-public-quot-quot-attrep/t...

Please see attached screenshot. I have removed the db name.  I am able to replicate the table in target MySQL DB but not sure why attrep_changes temp table is throwing out the max row size limit error.

Nevertheless, the few points that you shared are useful for me as I encountered errors with my other tables. For point no 2, if we split a single task into multiple tasks on a table, would consistency be an issue? Task one replicate column ABC, while task two replicate column XYZ, both tasks able to coordinate and keep track the changes?

Thank you.

Desmond

[john_wang · Accepted solution]

Hello @desmondchew ,

When running in batch optimized mode Replicate creates a temporary net changes table on the Target Endpoint database which contains columns to put the string representation of all the columns contained in tables in that batch (as well as counter and PK key columns). Because of the layout of the tables, the number of tables, and the fact that the Target Endpoint database is MySQL, Replicate cannot create this temp table.
(Note that depending on the actual tables contained in a batch the size of the temp table could conceivably be within the limits for some batches.)

For point 2, I'm meaning you reduce the tables number in a task instead of 'split' a single table to multiple tasks. Anyway, I've a better option to solve the error and I put it to a dedicated article Replicate - How to custom target endpoint syntax  . 

Hope this helps.

Regards,

John.

[desmondchew]

Hi John,

Would it cause any issues if I don't convert the Qlik CDC's table  "attrep_changesEE21D208566E9EB3" to MyISAM? I know that innodb has crash consistency proof but has the limitation on the row size.

If it's not impacting the job, I'd like to keep it as default.

Thank you.

Desmond

[desmondchew]

Hi,

May I know if the steps to convert to MyISAM will impact existing tasks that are currently on CDC? Will we require to restart Qlik services after change? Also, will this feature be available in new release of Qlik Replicate so that an underlying parameter change would activate the MyISAM table?

[john_wang]

Hello @desmondchew ,

The net changes table error may lead data truncation or other potential issues, we have to solve the error.

After you convert to MyISAM, the task need to be stop/resume, we need not to restart the Qlik Services. BTW, I do not think it will be added to new release.

Let me know if you need any additional assistance.

Regards,

John.

[desmondchew]

Hi John,

Stopping and resuming tasks won't cause a full reload of the data right? We have a UAT coming up just try to avoid doing it now until a better window.

Thank you.
Des

[john_wang]

Hello Desmond,

You are right. The net changes table will be dropped/re-created during the task stopping/resuming. No real full reload will be performed but a 'dummy full load' which be done which will not affect the existing target tables data.

I'd like to suggest you doing all acceptance tests on UAT/TEST env prior to implement it in PROD system.

Regards,

John.