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

[Michael_Litz]

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. 

1. Go to the  Endpoint connection
2. Switch to the Advanced tab
3. Click Internal Parameters
4. Add unboundedVarcharMaxSize
   
   
   
   For more information about Internal Parameters, see Qlik Replicate: How to set Internal Parameters and what are they for?