Qlik Sense - Issue when retrieving data through MySQL, Amazon RDS

MatteoMonni — Wed, 22 Dec 2021 15:24:59 GMT

Hi all,

we're experiencing intermittent issues while retrieving data when connecting Qlik Sense (June 2019 Patch 2) to a MySQL Database hosted on Amazon RDS.

In short, the problem we're experiencing is as follows: Four times out of ten (on average) our extraction scripts terminates with errors due to a missing field: Field <field_name> not found.

Please also note that the database structure is stable as we're not working on a development environment so nobody is making unsolicited changes to the underlying database/schema/table structures.

A more detailed explanation is provided below:

Connection Drivers used:

- MySQL Database - Amazon RDS - QlikSense --> ODBC 5.3(w) Driver

Our extraction application works as follows:

We use an Excel file to store the names of tables and fields we need to load. This file is then processed by our script to generate a load statement that looks like this:

Table1: Load FIELD_ONE, FIELD_TWO, FIELD_THREE, FIELD_FOUR ; SQL SELECT FIELD_ONE as FIELD_ONE, FIELD_TWO as FIELD_TWO, cast(FIELD_THREE as char) as FIELD_THREE, FIELD_FOUR as FIELD_FOUR FROM Schema.Table1; STORE Table1 INTO [lib://Folder_Connection/Table1.qvd](qvd);

At this point, we may or may not run into the aforementioned error: "Field <field_name> not found" (e.g.:"Field FIELD_FOUR not found").

We've done some preliminary testing to better understand possible causes and:

It seems that field names, transformations and aliasing have nothing to do with the errors thrown as it can happen to any field regardless of the complexity of the name, transformation or whether we assigned a DB alias to it while querying the data source or not.
It seems that the error is caused by the preceding load as we never encounter errors while running the query directly or by using "Load *" while using the preceding load. Please also note that we'd rather avoid using Load * as we need to perform some operations right after data is loaded from the Database and we have to know the exact field names for further transformations in later stages.

I cannot stress enough that most of the time the load script works correctly and no errors are thrown. It looks like sometimes field names get misinterpreted more that anything else.

Have you ever experienced issues similar to this before? How did you solve them?

Any bit of help is truly appreciated.

Please, let me know if I can provide any further detail to better explain the issue.

Thank you.

Regards,

Matteo

topic Qlik Sense - Issue when retrieving data through MySQL, Amazon RDS in Connectivity & Data Prep

Qlik Sense - Issue when retrieving data through MySQL, Amazon RDS