Missing rows when comparing MySQL Database and QVD

[merlephil]

Hello, 

I have a script building QVDs From a MySQL Database. Quite Simple, effective, tested and approved.

NoConcatenate
databases:
load * Inline [
	Database,
    btercpg
    
];

for each base in FieldValueList('Database')
LIB CONNECT TO $(base);
[tables]:
SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'btercpg';
for each table in FieldValueList('TABLE_NAME')
Qualify*;
[$(table)]:
NoConcatenate
select * from $(table);
if $(store) = 1 then
	store $(table) into '$(connect($(base)))$(table).qvd' (qvd);
end if;
Unqualify*;
drop table $(table);
next table;
drop table tables;

next base;
drop table databases;

I work with a QS Server updated recently from May 2021 to May 2022 version.

I have been notified that one of my apps was partially running since this upgrade, it came out the fact table was missing one third of its data. 

I splitted my fact table into smaller elements in order to find out where was the problem, one table was loading roughly 1000 records instead of 3000.
I tried to load only rough data directly from the qvd of that table (without joining or reducing the table) , the same roughly 1000 records loaded.

I tried to generate a new QVD from the mysql connector, it still not have more than a thousand records.
However when I make à select * from table in SQL I have more than 3000 records.

Do you have any Idea ?

[marcus_sommer]

There are surely various possibilities. At first how did you check the number of records? Are it UI objects within QLIK? If so it could mean that all records are there but as they were displayed distinct any duplication wouldn't visible.

Another possibility might be that these records are blocked by any other access/process within the data-base and/or there are any restrictions to the user-rights. Also thinkable is that the used driver couldn't handle all data and skipped some and/or the load takes too long and any timeout ends the query. The last mentioned things cause often an error but this must not mandatory be.

I think I would start by adding recno() and rowno() to the (preceding) load to get any hints which records are there and which one are missing - always the same or is it randomly? Also measuring the load-times may helpful.

Further you may to consider to extend your logic by querying the number of records and maybe some other information, too from the data-base system-tables and comparing it directly against noofrows('YourTable') to repeat the load n times and/or to log the success/failures into a log.qvd or doing similar stuff.

- Marcus

[merlephil]

When querying the database with MySQL Connector I found out I had the correct number of rows, the difference I noticed was because I was restricting on logical delete on a column with a integer data type with value 0 or 1, eliminating 1 in the result data set. In SQL that column has a not null constraint but when loading the table with Qlik MySQL connector, It outputs NULL values on 60% of this column data. Tricky.