topic Re: Why does a Qlik Sense Load Script with MySQL query hang when a BIT data type column is added? in Connectivity & Data Prep

Why does a Qlik Sense Load Script with MySQL query hang when a BIT data type column is added?

johnnyirldev — Fri, 16 Sep 2022 01:11:51 GMT

I'm running a very simple Qlik Sense script which connects to MySQL and returns a resultset. Everything runs OK until a bit datatype column is added. Then the Load Script just keeps on 'loading'. It will stay like that for hours on end. When I remove the BIT column from the column list in the SELECT statement, the script runs in a few seconds. Here is the script that works:

LIB CONNECT TO 'MySQL_.......';

First 5
LOAD col1,
     col2,
     col3;

[my_table]:
SELECT col1,
       col2,
       col3;
FROM `db_name`.`table_name`;

And, after adding the column of BIT data type, the script hangs:

LIB CONNECT TO 'MySQL_.......';

First 5
LOAD col1,
     col2,
     col3,
     bitCol;

[my_table]:
SELECT col1,
       col2,
       col3,
       bitCol;
FROM `db_name`.`table_name`;

Note: object names changed for privacy.

I looked up documentation on Qlik and it there is nothing specifically that says anything about BIT data type nuances. I'm baffled and any direction appreciated. Thanks!

Re: Why does a Qlik Sense Load Script with MySQL query hang when a BIT data type column is added?

marcus_sommer — Fri, 16 Sep 2022 13:30:34 GMT

Either the data-base didn't return these values - restricted to any setting or maybe just a lock - or the used driver couldn't handle this field respectively data-type. But they don't return any error and therefore Qlik will further wait to get this data-set (by a local load maybe forever and by a qmc-load until any timeout is touched).

- Marcus

Re: Why does a Qlik Sense Load Script with MySQL query hang when a BIT data type column is added?

johnnyirl — Fri, 16 Sep 2022 13:50:32 GMT

Thanks Marcus, for your feedback. It's probably worth mentioning that I've been able to preview the data from the MySQL data connection on the right-hand panel in the Data Load Editor. Also, this issue occurs for any column of bit data type. And another thing which could be useful is that querying the data from MySQL workbench works with no issues. I've also tried running the query on a read-only instance of the database and the problem persists. Would any of this indicate something more specific that I could look at? Thanks again.

Re: Why does a Qlik Sense Load Script with MySQL query hang when a BIT data type column is added?

marcus_sommer — Mon, 19 Sep 2022 06:18:02 GMT

I could say that Qlik didn't cause the issue because Qlik just transferred the query-string to the driver which communicates with the data-base and on the same way back comes the result-set and Qlik takes everything regardless from the data-types (Qlik just interprets in numeric and strings which works usually quite well and even if not it won't break the load in any way).

Therefore I think the driver might not be able to handle this data-type properly. Before looking for any different driver or any possible configurations for them you may just try to cast this field to a numeric or string to see if the load then worked. If yes you might transform the values on the Qlik side again.

- Marcus

Re: Why does a Qlik Sense Load Script with MySQL query hang when a BIT data type column is added?

johnnyirl — Thu, 22 Sep 2022 18:27:53 GMT

Hi Marcus,

You were correct. The Qlik MySQL connector was causing the issue. I setup a system ODBC data source to the MySQL instance and used a Qlik ODBC connection instead and it worked right away. Thanks for pointing me in the right direction. Jonathan