Qlik Replicate and Aurora Postgres: null values on varchar data type

Michael_Litz — Wed, 27 Sep 2023 11:27:35 GMT

Due to an issue with MySQL ODBC Driver 8.0.027 to 8.0.033, empty TEXT columns may not be replicated correctly during Full Load.

Running the full load on a table, it appears the data alignment is off because the target .csv file has null values when the source field is defined as not nullable.

Environment

Qlik Replicate
Aurora Postgres

Identifying the root cause:

Verify the source table DDL:

Examining the source table DDL will show a datatype with no precision (varchar NOT NULL,).

Example:

CREATE TABLE tran.category (
	id	integer NOT NULL,
	name	varchar NOT NULL,
	description	varchar NOT NULL,
	category	varchar NOT NULL,
	is_active	boolean NOT NULL DEFAULT true,
	create_employee	integer NOT NULL,
	create_datetime	timestamp NOT NULL,
	PRIMARY KEY(id)
)

Resolution

This is a documented limitation. See VARCHAR(n).

VARCHAR without a length (n) is not recognized as a valid data type by target endpoints. Consequently, if a source column data type is set to VARCHAR without an explicit length, Replicate will set a default length of 8000 bytes.

Workaround:

You can change the default by deploying an internal parameter, unboundedVarcharMaxSize, and setting a custom value.

Go to the Endpoint connection
Switch to the Advanced tab
Click Internal Parameters
Add unboundedVarcharMaxSize

For more information about Internal Parameters, see Qlik Replicate: How to set Internal Parameters and what are they for?

article Qlik Replicate and Aurora Postgres: null values on varchar data type in Official Support Articles

Qlik Replicate and Aurora Postgres: null values on varchar data type

Environment

Identifying the root cause:

Resolution

Workaround: