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.
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:
Optionally, you can also do DB operations on the target server with this added node:
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.
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:
Can you show the structure of the table you are selecting in the DB Table Selector?
Can you show what you are doing in the DB Reader?
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)
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?
