Just updated from KNIME 2.10 to 2.10.2 and now the Database Writer node will no longer append to an existing PostgreSQL table:
ERROR Database Writer Execute failed: ERROR: relation "myTable" already exists
Any ideas?
I've seen a smilar problem on here for KNIME 2.10.1, but that was a MySQL database. If I can't get it sorted I'll have to roll back to 2.10.0.
Hi,
sorry for the late reply. This is due to a problem introduced by a bug fix. We will partially revert the changes with the next release next week. The problem is due to the implicit lower case conversion of postgres. Until the release you can use the node with lower case table name e.g. mytest.
Sorry about that,
Tobias
1 Like
Hi Tobias,
Ah, when I referred to the table as "myTable" I was only using it as an example. The node is really trying to append to a table called "macromols.entries", i.e. "entries" table in "macromols" schema. Presumably this is caused by the same or similar bug fix?
Cheers,
Richard
Hi Richards,
yes the bug is also caused by schema names in the table name and will be fixed with the next release which we will publish this week.
Sorry about that,
Tobias
We are running 2.10.3 on a Teradata database and the error also occurs here..
ERROR Database Writer Execute failed: [Teradata Database] [TeraJDBC 14.10.00.17] [Error 3803] [SQLState 42S01] Table 'tst_gs1' already exists.
has this been fixed in 2.10.3?
Hi,
yes the problem should be fixed in 2.10.3. We have reverted most of the changes that caused the above mentioned problems.
Did it work befor you updated KNIME? Could you please send me the part of the KNIME log file that contains the error via private message. To open the KNIME logfile go to View -> Open KNIME log within KNIME.
Thanks for your help,
Tobias
Hi,
i'm using version 2.10.3 and it seems that a similar problem now does occure in my workflow. In my case it is a microsoft sql server database.
It worked fine in previous versions.
I found this in my error log:
2014-10-29 17:00:59,577 DEBUG KNIME-Worker-4 SQLServerUtility : Checking if table test exists
2014-10-29 17:00:59,578 DEBUG KNIME-Worker-4 SQLServerUtility : Execute query: SELECT * FROM (SELECT 1 FROM test) tempTable_8032347355211739216 WHERE (1 = 0)
2014-10-29 17:00:59,580 DEBUG KNIME-Worker-4 SQLServerUtility : Got exception while checking for existence of table 'test': Kein Spaltenname wurde für die Spalte 1 von 'tempTable_8032347355211739216' angegeben.
com.microsoft.sqlserver.jdbc.SQLServerException: Kein Spaltenname wurde für die Spalte 1 von 'tempTable_8032347355211739216' angegeben.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)
at org.knime.core.node.port.database.DatabaseUtility.tableExists(DatabaseUtility.java:168)
at org.knime.core.node.port.database.DatabaseWriterConnection.writeData(DatabaseWriterConnection.java:124)
at org.knime.base.node.io.database.DBWriterNodeModel.execute(DBWriterNodeModel.java:219)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:552)
at org.knime.core.node.Node.invokeNodeModelExecute(Node.java:1099)
at org.knime.core.node.Node.execute(Node.java:948)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:529)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:179)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:110)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:120)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:240)
2014-10-29 17:00:59,581 INFO KNIME-Worker-4 DatabaseWriterConnection : Table "test" does not exist in database, will create new table.
2014-10-29 17:00:59,582 DEBUG KNIME-Worker-4 DatabaseWriterConnection : Executing SQL statement as execute: CREATE TABLE test (column1 varchar(255), column2 varchar(255), column3 varchar(255), column4 varchar(255), column5 varchar(255))
2014-10-29 17:00:59,585 DEBUG KNIME-Worker-4 Database Writer : reset
2014-10-29 17:00:59,585 ERROR KNIME-Worker-4 Database Writer : Execute failed: In der Datenbank ist bereits ein Objekt mit dem Namen 'test' vorhanden.
2014-10-29 17:00:59,586 DEBUG KNIME-Worker-4 Database Writer : Execute failed: In der Datenbank ist bereits ein Objekt mit dem Namen 'test' vorhanden
Hi,
thanks for reporting the bug and the detailed logging information which helped us to reproduce the problem. It seems SQL Server has a problem with the query we use to check if the table exists. I have opened a bug for this issue and we will fix the problem with SQL Server with the next bug fix release or with the next KNIME release in December at the latest.
We are very sorry about these inconveniences.
Bye,
Tobias
Send my log file through private message for Teradata database connection
Hi,
I am seeing the same issue on KNIME 2.10.3 and trying to write tables to a postgreSQL database:
ERROR Database Writer Execute failed: ERROR: relation "screening_availability" already exists
I thought this issue was meant to be fixed in 2.10.3(?)
In case it is of use, I have pasted an excerpt of the KNIME log file below.
Kind regards
James
2014-11-01 13:21:45,000 DEBUG KNIME-Worker-143 NodeContainer : Database Writer 0:344:93 has new state: EXECUTING
2014-11-01 13:21:45,000 DEBUG KNIME-Worker-143 LocalNodeExecutionJob : Database Writer 0:344:93 Start execute
2014-11-01 13:21:45,000 DEBUG KNIME-Worker-143 WorkflowFileStoreHandlerRepository : Adding handler 02674734-6cec-4138-b5a6-889f0e9461e1 (Database Writer 0:344:93: <no directory>) - 191 in total
2014-11-01 13:21:45,010 DEBUG KNIME-Worker-143 DatabaseConnectionSettings : java.sql.Connection#isValid(1) throws error: Method org.postgresql.jdbc4.Jdbc4Connection.isValid(int) is not yet implemented.
2014-11-01 13:21:45,010 DEBUG KNIME-Worker-143 DatabaseWriterConnection : Executing SQL statement as execute: DROP TABLE screening_availability
2014-11-01 13:21:45,025 INFO KNIME-Worker-143 DatabaseWriterConnection : Can't drop table "screening_availability", will create new table.
2014-11-01 13:21:45,025 DEBUG KNIME-Worker-143 DatabaseWriterConnection : Executing SQL statement as execute: CREATE TABLE screening_availability (supplier_id integer, compound_id varchar(255), amount numeric(30,10), units varchar(255))
2014-11-01 13:21:45,025 DEBUG KNIME-Worker-143 Database Writer : reset
2014-11-01 13:21:45,025 ERROR KNIME-Worker-143 Database Writer : Execute failed: ERROR: relation "screening_availability" already exists
2014-11-01 13:21:45,025 DEBUG KNIME-Worker-143 Database Writer : Execute failed: ERROR: relation "screening_availability" already exists
org.postgresql.util.PSQLException: ERROR: relation "screening_availability" already exists
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:374)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:366)
at org.knime.core.node.port.database.DatabaseWriterConnection.writeData(DatabaseWriterConnection.java:286)
at org.knime.base.node.io.database.DBWriterNodeModel.execute(DBWriterNodeModel.java:219)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:552)
at org.knime.core.node.Node.invokeNodeModelExecute(Node.java:1099)
at org.knime.core.node.Node.execute(Node.java:948)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:529)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:179)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:110)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:120)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:240)
2014-11-01 13:21:45,025 DEBUG KNIME-Worker-143 WorkflowManager : Database Writer 0:344:93 doBeforePostExecution
2014-11-01 13:21:45,025 DEBUG KNIME-Worker-143 NodeContainer : Database Writer 0:344:93 has new state: POSTEXECUTE
2014-11-01 13:21:45,025 DEBUG KNIME-Worker-143 WorkflowManager : Database Writer 0:344:93 doAfterExecute - failure
2014-11-01 13:21:45,025 DEBUG KNIME-Worker-143 Database Writer : reset
2014-11-01 13:21:45,025 DEBUG KNIME-Worker-143 Database Writer : clean output ports.
2014-11-01 13:21:45,025 DEBUG KNIME-Worker-143 WorkflowFileStoreHandlerRepository : Removing handler 02674734-6cec-4138-b5a6-889f0e9461e1 (Database Writer 0:344:93: <no directory>) - 190 remaining
2014-11-01 13:21:45,025 DEBUG KNIME-Worker-143 NodeContainer : Database Writer 0:344:93 has new state: IDLE
2014-11-01 13:21:45,030 WARN KNIME-Worker-143 Database Writer : Existing table "screening_availability" will be dropped!
2014-11-01 13:21:45,030 DEBUG KNIME-Worker-143 Database Writer : Configure succeeded. (Database Writer)
2014-11-01 13:21:45,030 DEBUG KNIME-Worker-143 NodeContainer : Database Writer 0:344:93 has new state: CONFIGURED
2014-11-01 13:21:45,035 DEBUG KNIME-Worker-143 Database Reader : Configure succeeded. (Database Reader)
2014-11-01 13:21:45,035 DEBUG KNIME-Worker-143 NodeContainer : (Screening) Availability 0:344 has new state: IDLE
2014-11-01 13:21:53,535 DEBUG main NodeContainerEditPart : Database Writer 0:344:93 (CONFIGURED)
2014-11-01 13:21:55,456 DEBUG main VerifyingCompoundCommand : Executing <1> commands.
Sorry - I just realised what the issue is... I am trying to use the database writer to create a new table; and drop the existing one prior to this. However, I have created a view that depends on the table... This means that one cannot simply rely on the table writer node to execute a DROP. To get rid of dependent objects, DROP CASCADE would need to be used (at least in postgreSQL).
I guess the database writer node could be modified to do this, but I think that may be pretty dangerous(!) Instead perhaps it would be a good idea to propogate back the error information from the database to inform the node (and user) why the database writer is not able to drop the table(?)
Kind regards
James
Hi James,
thanks for the remark. We will change the error handling to surface these errors to the user. We will also release a new drop table node where we might add the cascade option to. This way the user can use the drop node prior the writer to ensure that the table is dropped.
Bye,
Tobias
Hello,
we have just release version 2.10.4 which should fix the problem with appending to existing tables. To update your installation open KNIME and go to Help->Check for updates. We are very sorry about the problems this bug has caused you.
Bye,
Tobias
@tobias.koetter I am using the latest version (4.4.2) and facing the same problem! It does not occur when I use MS SQL Server, but I would like to use Postgres. Any solutions?