Writing Boolean to MySQL DB with DB loader leads to wrong bit-data

Hi,
I found an issue with the DB loader where Boolean Data (true/false) is wrongly uploaded to a MySQL DB. All Boolean values are stored as “true”. Doing the same thing with DB writer everything works fine, though I don’t think that it is a problem with MySQL-drivers.

Here is my test setup:

The Test-Table looks like:
image

The MySQL-Connector has no special settings (all vanilla):
image

To use the “DB Loader” Node, I have to create a table first. Here are the settings
image
Nothing changed here:
image

After uploading the Table I checked the MySQL-DB content.
With DB Query Reader I receive:
image

The “false” value in Row1 is changed to “true”!!! (I found this issue after uploading several tables with 200 Mio rows each :frowning: )

As I meantioned, doing the same with DB Writer Node works perfectly.

2 Likes

Hello ActionAndi,
we were able to reproduce the problem and are sorry about the inconveniences it caused you. I have created a bug report (internal number AP-16888) for this. We will fix this problem as soon as possible. I will keep you posted.
Bye
Tobias

5 Likes

Thank you Tobias.

Is it possible ask for a new feature or enhanced node behavior?

Usually I use an auto-incremented column as primary key, though I do not have to care about it by myself. Unfortunately the DB Loader Node complains as the number of columns does not match with the input table. Is there any chance to let the node check if a column is auto-incremented?

SELECT * FROM INFORMATION_SCHEMA.COLUMNS  WHERE TABLE_NAME = 'test_load'  AND EXTRA like '%auto_increment%'

Andreas

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

Hello ActionAndi,
the problem with the boolean values has been fixed with version 4.4 of the KNIME Analytics Platform which has just been released.
I have also opened another ticket for the auto-increment columns (AP-17127) and will keep you posted on this as well.
Bye
Tobias

3 Likes