We’re about to migrate to a new DB, so I started testing KNIME (5.8.2) on it and at some point I realised query reader nodes hang indefinitely (well, they timeout after 10 minutes, but that’s probably just our configs) on SQL queries that should return no rows. This happens even when I run something as simple as:
select * from random_table where 1=0
A few things I’ve tested:
The same queries run fine on 4.7.4 and 4.4.2
No issues at all running the same queries on workbench.
Using DB connector instead of MySQL connector makes no difference
The DB is MariaDB 6.8.3, I tried on a different DB, same MariaDB version, and no issue at all
Also tried all 3 different drivers available on the connector (8.0.29, 8.0.11, 5.1.49), same issue
Looked around and tried different jdbc connector parameters like defaultFetchSize=1 and useInformationSchema=false, but no change
Any ideas on what could be happening or possible workarounds?
instead of query reader, try a DB Row Filter and attempt to filter on a specific column
computationally not that smart but worked well for me in the past: use a DB partitioner, set it to 100% and use the second output (the 0%) - this is generic and works with every table
db table selector with advanced setting/query and use WHERE 1=0 there
Hadn’t tested until now, but DB table selectors seem to hang no matter what I do (custom query or not - and pulling metadata works), so unless there’s another way, can’t try DB row filters. Tested table selectors/row filters on other DBs (I rarely use them), and they work fine, as expected
“Partition is not supported by this database” - across several tables, so probably a dead end.
if selecting the table (which doesnt even fire a statement) hangs, there must be something odd going on.
can you see columns in the select view? this sounds a bit like that you see tables but cannot retrieve the column definitions.
Yep, that’s what it looks like. I can always load metadata for schemas and tables, but every time I select a table in the db metadata browser, the node never shows anything beyond “Fetching columns…”
yeah, that’s all I had tried originally, it works fine when the query’s output returns at least one row, but when it’s a query that returns no rows, query reader hangs.
To clarify, if I run something like this:
select now() from random_non_empty_table where 1=1 limit 1
It returns a row, but if I run this instead:
select now() from random_non_empty_table where 1=0 limit 1
It hangs. Same behavior regardless of table/query, it seems to happen on all queries that should return no rows.
The other versions of KNIME I tried (listed on my original post) work fine.
Drivers seem to be the same for 5.8.2 and 4.7.4 on my machine (8.0.29, 8.0.11 (MySQL 8) and 5.1.49 (MySQL 5)). Had already tested them on all the different versions
defaultFetchSize=0 - no changes
useCursorFetch=false - yep, this produces an empty table. Removing the entry from the JDBC parameters has the same effect