[Bug]: DuckDB - if a table output fails, the destination duckdb database file remains locked and can no longer be used.

[usbrandon]

Apache Hop version?

2.7

Java version?

17

Operating system

Windows

What happened?

The first run, say a date that it does not know how to handle. Followed by just trying to run again yields ----- Second run ---- 2023/12/21 16:02:57 - Table output.0 - Error connecting to database: (using class org.duckdb.DuckDBDriver) 2023/12/21 16:02:57 - Table output.0 - IO Error: Cannot open file "c:\opt\salesvista1.duckdb": The process cannot access the file because it is being used by another process.

----- end Second run ----

--- First Run --- 2023/12/21 15:59:03 - Table output.0 - ERROR: Because of an error, this transform can't continue: 2023/12/21 15:59:03 - Table output.0 - ERROR: org.apache.hop.core.exception.HopException: 2023/12/21 15:59:03 - Table output.0 - Error inserting row into table [salesvista-2023-q4] with values: [503A], [71133218], [1], [119.8], [2023-09-11 00:00:00.000], [2023], [2023 September], [202309], [253312], [82678], [200000903], [Semaglutide / Cyanocobalamin (1 Ml)], [Injectable], [EMPOWER], [Product], [5/0.5 MG/ML], [152422], [-1], [Corey Anderson], [Schertz], [TX], [Not Set], [United States Of America], [181653], [200153587], [Icryo - Mericle Cryo And Wellness], [Schertz], [TX], [Not Set], [Not Set], [25614527], [10209083], [LG180361], [206360265], [960875], [null], [NULL], [Icryo Mericle Cryo And Wellness], [Corey Anderson], [null], [NULL], [null], [NULL], [null], [NULL], [SYS20.206360265] 2023/12/21 15:59:03 - Table output.0 - 2023/12/21 15:59:03 - Table output.0 - offending row : [business_unit String(4)], [invoice_no Integer(15)], [quantity Number(15, 1)], [total_revenue Number(15, 1)], [ship_date Date], [ship_year Integer(15)], [ship_year_month String(14)], [ship_year_month_order Integer(15)], [product_technical_key Integer(15)], [formula_id Integer(15)], [product_mk Integer(15)], [product_name String(148)], [product_form String(12)], [product_manufacturer String(10)], [product_type String(8)], [product_strength String(49)], [provider_technical_key Integer(15)], [provider_mk Integer(15)], [provider_full_name String(28)], [provider_city String(24)], [provider_state String(2)], [provider_county String(13)], [provider_country String(24)], [practice_technical_key Integer(15)], [practice_mk Integer(15)], [practice_name String(63)], [practice_city String(24)], [practice_state String(7)], [practice_county String(19)], [practice_country String(24)], [rx_number Integer(15)], [fill_id Integer(15)], [lot_number String(10)], [doctor_id Integer(15)], [practice_id Integer(15)], [salesforce_contact_id String(18)], [salesforce_account_id String(18)], [practice_name_cln String(63)], [provider_name_cln String(27)], [termed_customer String(29)], [terms String(6)], [billing_state String(10)], [POD String(4)], [billing_address String(31)], [billing_city String(4)], [source_primary_key String(15)] 2023/12/21 15:59:03 - Table output.0 - 2023/12/21 15:59:03 - Table output.0 - Error setting value #5 [Date] on prepared statement 2023/12/21 15:59:03 - Table output.0 - setTimestamp 2023/12/21 15:59:03 - Table output.0 - 2023/12/21 15:59:03 - Table output.0 - 2023/12/21 15:59:03 - Table output.0 - 2023/12/21 15:59:03 - Table output.0 - at org.apache.hop.pipeline.transforms.tableoutput.TableOutput.writeToTable(TableOutput.java:383) 2023/12/21 15:59:03 - Table output.0 - at org.apache.hop.pipeline.transforms.tableoutput.TableOutput.processRow(TableOutput.java:117) 2023/12/21 15:59:03 - Table output.0 - at org.apache.hop.pipeline.transform.RunThread.run(RunThread.java:55) 2023/12/21 15:59:03 - Table output.0 - at java.base/java.lang.Thread.run(Thread.java:840) 2023/12/21 15:59:03 - Table output.0 - Caused by: org.apache.hop.core.exception.HopDatabaseException: 2023/12/21 15:59:03 - Table output.0 - offending row : [business_unit String(4)], [invoice_no Integer(15)], [quantity Number(15, 1)], [total_revenue Number(15, 1)], [ship_date Date], [ship_year Integer(15)], [ship_year_month String(14)], [ship_year_month_order Integer(15)], [product_technical_key Integer(15)], [formula_id Integer(15)], [product_mk Integer(15)], [product_name String(148)], [product_form String(12)], [product_manufacturer String(10)], [product_type String(8)], [product_strength String(49)], [provider_technical_key Integer(15)], [provider_mk Integer(15)], [provider_full_name String(28)], [provider_city String(24)], [provider_state String(2)], [provider_county String(13)], [provider_country String(24)], [practice_technical_key Integer(15)], [practice_mk Integer(15)], [practice_name String(63)], [practice_city String(24)], [practice_state String(7)], [practice_county String(19)], [practice_country String(24)], [rx_number Integer(15)], [fill_id Integer(15)], [lot_number String(10)], [doctor_id Integer(15)], [practice_id Integer(15)], [salesforce_contact_id String(18)], [salesforce_account_id String(18)], [practice_name_cln String(63)], [provider_name_cln String(27)], [termed_customer String(29)], [terms String(6)], [billing_state String(10)], [POD String(4)], [billing_address String(31)], [billing_city String(4)], [source_primary_key String(15)] 2023/12/21 15:59:03 - Table output.0 - 2023/12/21 15:59:03 - Table output.0 - Error setting value #5 [Date] on prepared statement 2023/12/21 15:59:03 - Table output.0 - setTimestamp 2023/12/21 15:59:03 - Table output.0 - 2023/12/21 15:59:03 - Table output.0 - 2023/12/21 15:59:03 - Table output.0 - at org.apache.hop.core.database.Database.setValues(Database.java:922) 2023/12/21 15:59:03 - Table output.0 - at org.apache.hop.pipeline.transforms.tableoutput.TableOutput.writeToTable(TableOutput.java:250) 2023/12/21 15:59:03 - Table output.0 - ... 3 more 2023/12/21 15:59:03 - Table output.0 - Caused by: org.apache.hop.core.exception.HopDatabaseException: 2023/12/21 15:59:03 - Table output.0 - Error setting value #5 [Date] on prepared statement 2023/12/21 15:59:03 - Table output.0 - setTimestamp 2023/12/21 15:59:03 - Table output.0 - 2023/12/21 15:59:03 - Table output.0 - at org.apache.hop.core.row.value.ValueMetaBase.setPreparedStatementValue(ValueMetaBase.java:5693) 2023/12/21 15:59:03 - Table output.0 - at org.apache.hop.core.database.Database.setValue(Database.java:905) 2023/12/21 15:59:03 - Table output.0 - at org.apache.hop.core.database.Database.setValues(Database.java:920) 2023/12/21 15:59:03 - Table output.0 - ... 4 more 2023/12/21 15:59:03 - Table output.0 - Caused by: java.sql.SQLFeatureNotSupportedException: setTimestamp 2023/12/21 15:59:03 - Table output.0 - at org.duckdb.DuckDBPreparedStatement.setTimestamp(DuckDBPreparedStatement.java:770) 2023/12/21 15:59:03 - Table output.0 - at org.apache.hop.core.row.value.ValueMetaBase.setPreparedStatementValue(ValueMetaBase.java:5644) 2023/12/21 15:59:03 - Table output.0 - ... 6 more 2023/12/21 15:59:03 - Text file input.0 - Finished processing (I=146, O=0, R=0, W=144, U=1, E=0) 2023/12/21 15:59:03 - Table output.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)

Issue Priority

Priority: 1

Issue Component

Component: Database