Exception reading data row: java.lang.IllegalArgumentException: YEAR

I am transferring database from a server to another server with nodes as follows
MySQL Connection (Source) → DB Table Selector → DB Reader → DB Merge - > MySQL Connector (Target)

Tried the first two tables which worked fine. But on the third table, the node DB Reader did not execute because of this error 2 lines
ERROR DB Reader 0:22 Exception reading data row: java.lang.IllegalArgumentException: YEAR
ERROR DB Reader 0:22 Execute failed: java.lang.IllegalArgumentException: YEAR

  • The table do not have a column name YEAR
  • These are the JDBC Parameters used in the connections: allowLoadLocalInfile, serverTimezone, zeroDateTimeBehavior
  • No other configurations aside from the connections, db and table.

I have seen a topic here mostly similar in my error but still does not hold any answer that can help.
(DB Query Reader: IllegalArgumentException: HOUR_OF_DAY: 2 -> 3)

I am only a new user of knime, this is my first try.

Hi @meknime and welcome to the Knime Community.

As the message says, the column YEAR does not exist. If that is the case, then this is it. You can’t write to a column that does not exist. This does not work in Mysql, and Knime is just the messenger here.

I’m not sure how your workflow looks like, but based on what you described, I’m not sure it’s the right design.

The DB Merge is a node that will do an UPSERT (UPDATE if exist, else INSERT), and I imagine you want to do this on the target, not on the source.

Regardless, you can’t insert into column YEAR if that column does not exist. You either have to skip/remove that column before inserting, or create that column on the target system - only you can know if it’s needed or not.

Can you show us your workflow?

Generally speaking, a simplified version would look like this:
image

Optionally, you can also do DB operations on the target server with this added node:
image

The DB SQL Executor allows you to run SQL statement directly on the server. For example:

Technically, I am only trying to copy the table from one server to a different server so there is no need to alter the table. The error also says data row not column.

This is my workflow.

Nonetheless, I will try your suggestion to see if it’ll work. And will feedback again if doesn’t.

Thank you for this.

1 Like

@meknime if you want to transfer data between two databases you could try this approach with streaming:

1 Like

Hi @meknime , the error says that it had issues reading the row, because it’s getting an illegal argument YEAR. So while the error is happening at that row, it’s trying to read off the column YEAR or something. Or there could be value as “YEAR” that is illegal value for a particular column.

Your workflow looks simple enough. Some questions:

  1. Can you show the structure of the table you are selecting in the DB Table Selector?
  2. Can you show what you are doing in the DB Reader?
  3. Does the table already exist on the target server? (This is not related to the current issue as it does not even get there, but it could be an issue after the current issue is resolved - this is to assess if you should use a DB Merge or DB Writer/Stream Transfer)

Hello @meknime,

and welcome to KNIME Community!

Topic you linked (which, seems to me, explains reasons behind this error pretty well) leads to another topic where it was suggested to use version 5 of the MySQL driver which is also shipped with KNIME for this exact reason. Have you tried it?

MySQLConnDriverVer

Br,
Ivan

1 Like

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