I am enjoying the latest release of KNIME (4.0) with the new DB nodes - thank you for the stable version.
Sometimes, I experience bugs in the new DB nodes, such as:
If I reset and restart a node (e.g. the Table Writer node) an error occurs: ERROR DB Connection Table Writer 2:1286:1260:326 Execute failed: Incorrect syntax near ‘.’.
If I restart the node, it works completely fine. I would also send you a log file, if you would provide me details which log you would need.
If the session to an SQL server “breaks” (e.g. if I kill a process on the server on purpose), I have to manually reset the whole SQL-workflow. It would be more convenient, if a check could be implemented, if the SQL session has to be re-initialised by the KNIME node. With the legacy nodes, I never experienced such issue (I assume, as there was only one connection to the server, it did not “break” as easy).
However: Do you want us to report bugs & errors in the Forum or would you prefer the bug reports in github / somewhere else?
…in addition, I find the “DB Table Selector” node very picky regarding the Database connection: Very often, it may (1) neither be executed via “F7”, nor (2) is it possible to configure the node (The dialog cannot be opened for the following reason: Database connection is not available) if an unconfigured/unexecuted “DB SQL Executor” or “DB Table Ceator” is connected before the DB Table Selector. If I manually start / execute the precedessor nodes, it works fine.
It might be nice, if the “DB Table Selector” node would be less strict regarding the unexectued predecessors so you can execute it right away.
Hello linkm,
thanks for all the valuable feedback. In general we prefer bug reports or general KNIME questions via the KNIME forum. So you have already found the right place
You can attache log files to your forum posts or also provide example workflow as described in the Bug Reporting Best Practices. However when working with databases you should make sure that they do not contain any sensitive information e.g. connection strings etc. That is why it is better if you only past the part of the log file which contains the error including the stacktrace. If you want to or need to submit sensitive information you can do so via personal messages in the forum. Also it would be great if you could create a separate forum post with good titles for each separate problem. This way it is easier for us to keep track of them and for other users to find existing entries.
Regarding your problems
Can you give me some more information about the DB Connection Table Writer node problems, e.g. which database you are connecting to and which driver you are using. The log file with the SQL statement would be also very helpful. To enable JDBC logging in KNIME open the node dialog of the DB connector node and go to the Advanced tab. In the tab select the JDBC logger option as shown below.
Regarding your connection problem, we didn’t support this in general since it might cause problems for example when working with session base objects e.g. temporary table that will no longer exist if the connection was recreated. Then executing all nodes after the node that creates the session based object will fail and might trigger other questions. However I see your point which might be pretty painful especially if you have a very large workflow with lots of nodes to executed. I will open up a feature request and we will rethink the implementation. Maybe we will automatically create a new connection and output a warning the in the KNIME log that a new connection was created.
I will also open up a feature request for the DB Table Selector node and maybe other nodes that have the same problem. The restriction was introduced with the Input Type Mapping tab which gets its information from the input connection. However we might be able to just leave it empty and still allow the user to open up the node to at least specify the schema and table name manually.
Thank you, Tobias, for your detailed explanations. I understand your idea behind the non-auto-renewal of the SQL connection - I think you are right: It could cause more problems than benefits. If you would include “less strict demands for Type Mapping in the nodes”, it would be less painfull to executive large workflows after a crash (which is only feasible of workflow-parts, today).
After having a trouble-free time with the new DB nodes, I am running into the same DB writer issue with MS SQL Server today:
ERROR DB Connection Table Writer 0:1286:1422 Execute failed: An invalid parameter or option was specified for procedure ‘sys.sp_rename’.
I enabled the JDBC logger in the DB Connector node, however, the log entry does not provide more detailed information than I provided above. If a seperated file is created that contains the logging data, I did not find it on the harddrive - do you have any hint for me, where to look for the extended log? I am happy to provide details.
Hi linkm,
is it possible that you use a temporary table name (a name that starts with #) in the DB Connection Table Writer?
It seems that SQL Server does not allow to use temporary table names in the rename object function (
‘sys.sp_rename’ as described here. But the DB Connection Table Writer uses this function to rename a temporary table if the target table already exists and the overwrite option is selected in the node dialog. In this case the node first creates a temporary table that is filled with the data from the source query. Only if this executes successfully the node drops the existing target table. Finally it renames the previously created temporary table to the target table. This fails if the target table is a temporary table e.g. start with #.
I will create a ticket for this problem. Until then I can only suggest to either use the DB Table Remover node to drop the temporary table before using the DB Connection Table Writer to create it again or to not use temporary table names in the DB Connection Table Writer.
Bye
Tobias
Hello @linkm,
the new database framework has now an option to automatic reconnect to the database if the connection has become invalid. For details see this post.
Bye
Tobias
