2021-03-05 16:03:05,814 INFO (j6ufnqf3-dataloader-8) [org.jumpmind.symmetric.service.impl.IncomingBatchService] Retrying batch home-13404021 2021-03-05 16:03:05,826 INFO (j6ufnqf3-dataloader-8) [org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter] Failed to process delete event in batch home-13404021 on channel 'J6UFNQF3'. Failed sql was: delete from "J6UFNQF3"."SPIRO_TEST" where "ID" = '4ba60e51-317b-4a55-8cf6-9d067a3cd179/pst.3' and "PATIENT_ID" = '4ba60e51-317b-4a55-8cf6-9d067a3cd179' and "CREATETIMESTAMP" = {ts '1970-01-01 02:00:00.003'} and "LASTUPDATETIMESTAMP" is NULL and "CLINIC_ID" = '2021-02-15 15:27:04.723' and "TEST_TIME" = {ts '1970-01-01 02:00:06.145'} and "SOURCE" = false and "REFERENCE" = 'false' and "REPO_ID" is NULL and "OPERATOR" = '1.85639989' and "NOTES" = '1.90864296' and "QUALITY_GRADE" is NULL and "TEST_NUMBER" = '1.80199992' and "TEST_TYPE" = 1.90399996 and "PRED_FEF_25" = 2.34648001 and "PRED_FEF_2575" = 2.01311797 and "PRED_FEF_50" = 4.13242764 and "PRED_FEF_75" = 4.13242764 and "PRED_FEV1" = 2.15863974 and "PRED_FEV1_FEV6" = 2.18846551 and "PRED_FEV1_FVC" = 1.26013867 and "PRED_FEV3" = 1.55670356 and "PRED_FEV3_FVC" = 4.13242764 and "PRED_FEV6" = 2.41739988 and "PRED_FIVC" = 2.51654911 and "PRED_FVC" = and "PRED_PEF" = and "PREDICTED_TEXT" = and "SYNC" = and "INTERPRETATION_TEXT" = and "PRED_PIF" = and "LLN_FEF_25" = and "LLN_FEF_2575" = and "LLN_FEF_50" = and "LLN_FEF_75" = and "LLN_FEV1" = and "LLN_FEV1_FEV6" = and "LLN_FEV1_FVC" = and "LLN_FEV3" = and "LLN_FEV3_FVC" = and "LLN_FEV6" = and "LLN_FIVC" = and "LLN_FVC" = and "LLN_PEF" = and "LLN_PIF" = Failed raw sql was: delete from "J6UFNQF3"."SPIRO_TEST" where "ID" = ? and "PATIENT_ID" = ? and "CREATETIMESTAMP" = ? and "LASTUPDATETIMESTAMP" is NULL and "CLINIC_ID" = ? and "TEST_TIME" = ? and "SOURCE" = ? and "REFERENCE" = ? and "REPO_ID" is NULL and "OPERATOR" = ? and "NOTES" = ? and "QUALITY_GRADE" is NULL and "TEST_NUMBER" = ? and "TEST_TYPE" = ? and "PRED_FEF_25" = ? and "PRED_FEF_2575" = ? and "PRED_FEF_50" = ? and "PRED_FEF_75" = ? and "PRED_FEV1" = ? and "PRED_FEV1_FEV6" = ? and "PRED_FEV1_FVC" = ? and "PRED_FEV3" = ? and "PRED_FEV3_FVC" = ? and "PRED_FEV6" = ? and "PRED_FIVC" = ? and "PRED_FVC" = ? and "PRED_PEF" = ? and "PREDICTED_TEXT" = ? and "SYNC" = ? and "INTERPRETATION_TEXT" = ? and "PRED_PIF" = ? and "LLN_FEF_25" = ? and "LLN_FEF_2575" = ? and "LLN_FEF_50" = ? and "LLN_FEF_75" = ? and "LLN_FEV1" = ? and "LLN_FEV1_FEV6" = ? and "LLN_FEV1_FVC" = ? and "LLN_FEV3" = ? and "LLN_FEV3_FVC" = ? and "LLN_FEV6" = ? and "LLN_FIVC" = ? and "LLN_FVC" = ? and "LLN_PEF" = ? and "LLN_PIF" = ? Failed sql parameters: ['4ba60e51-317b-4a55-8cf6-9d067a3cd179/pst.3', '4ba60e51-317b-4a55-8cf6-9d067a3cd179', {ts '1970-01-01 02:00:00.003'}, '2021-02-15 15:27:04.723', {ts '1970-01-01 02:00:06.145'}, false, 'false', '1.85639989', '1.90864296', '1.80199992', 1.90399996, 2.34648001, 2.01311797, 4.13242764, 4.13242764, 2.15863974, 2.18846551, 1.26013867, 1.55670356, 4.13242764, 2.41739988, 2.51654911] Failed sql parameters types: [VARCHAR, VARCHAR, TIMESTAMP, VARCHAR, TIMESTAMP, INTEGER, VARCHAR, VARCHAR, VARCHAR, VARCHAR, INTEGER, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, VARCHAR, BOOLEAN, LONGVARCHAR, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL, DECIMAL] Failed sql state and code: 42818 (20000) Failed pk data was: "4ba60e51-317b-4a55-8cf6-9d067a3cd179" Failed old data was: "4ba60e51-317b-4a55-8cf6-9d067a3cd179","22fe7f51-cef9-4db3-a2dc-240dff242ebc","2021-02-15 15:41:42.700000",,"0ad906db-d3c9-4587-aa42-f9fb84efef24","2021-02-15 15:25:50.723000","0","",,"pinky.tolamo","",,"20210215154137","4","0","4.64187489","0","0","4.1381249","0.86300004","0.85700004","0","0","4.82249989","4.85124989","4.85124989","10.03312477","Nhanes III * 0.88","0","No interpretation, not enough acceptable maneuvers","0","0","2.77312493","0","0","3.26624992","0.76400003","0.75300003","0","0","3.85249991","3.86624991","3.86624991","7.25249983","0" org.jumpmind.db.sql.SqlException: Exception while preparing sql [delete from "J6UFNQF3"."SPIRO_TEST" where "ID" = ? and "PATIENT_ID" = ? and "CREATETIMESTAMP" = ? and "LASTUPDATETIMESTAMP" is NULL and "CLINIC_ID" = ? and "TEST_TIME" = ? and "SOURCE" = ? and "REFERENCE" = ? and "REPO_ID" is NULL and "OPERATOR" = ? and "NOTES" = ? and "QUALITY_GRADE" is NULL and "TEST_NUMBER" = ? and "TEST_TYPE" = ? and "PRED_FEF_25" = ? and "PRED_FEF_2575" = ? and "PRED_FEF_50" = ? and "PRED_FEF_75" = ? and "PRED_FEV1" = ? and "PRED_FEV1_FEV6" = ? and "PRED_FEV1_FVC" = ? and "PRED_FEV3" = ? and "PRED_FEV3_FVC" = ? and "PRED_FEV6" = ? and "PRED_FIVC" = ? and "PRED_FVC" = ? and "PRED_PEF" = ? and "PREDICTED_TEXT" = ? and "SYNC" = ? and "INTERPRETATION_TEXT" = ? and "PRED_PIF" = ? and "LLN_FEF_25" = ? and "LLN_FEF_2575" = ? and "LLN_FEF_50" = ? and "LLN_FEF_75" = ? and "LLN_FEV1" = ? and "LLN_FEV1_FEV6" = ? and "LLN_FEV1_FVC" = ? and "LLN_FEV3" = ? and "LLN_FEV3_FVC" = ? and "LLN_FEV6" = ? and "LLN_FIVC" = ? and "LLN_FVC" = ? and "LLN_PEF" = ? and "LLN_PIF" = ?] at org.jumpmind.db.sql.JdbcSqlTransaction.prepare(JdbcSqlTransaction.java:439) ~[symmetric-jdbc-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.prepare(DefaultDatabaseWriter.java:1027) [symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.delete(DefaultDatabaseWriter.java:388) [symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:197) [symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64) [symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84) [symmetric-core-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64) [symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:202) [symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:212) [symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:178) [symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:124) [symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1085) [symmetric-core-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1061) [symmetric-core-3.12.6.jar:3.12.6] at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_261] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_261] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_261] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_261] Caused by: java.sql.SQLSyntaxErrorException: Comparisons between 'LONG VARCHAR (UCS_BASIC)' and 'LONG VARCHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') at org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientConnection.prepareStatement(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) ~[commons-dbcp-1.4.jar:1.4] at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) ~[commons-dbcp-1.4.jar:1.4] at org.jumpmind.db.sql.JdbcSqlTransaction.prepare(JdbcSqlTransaction.java:436) ~[symmetric-jdbc-3.12.6.jar:3.12.6] ... 16 more Caused by: org.apache.derby.client.am.SqlException: Comparisons between 'LONG VARCHAR (UCS_BASIC)' and 'LONG VARCHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') at org.apache.derby.client.am.ClientStatement.completeSqlca(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientStatement.readPrepareDescribeOutput(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientPreparedStatement.readPrepareDescribeInputOutput(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientPreparedStatement.flowPrepareDescribeInputOutput(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientPreparedStatement.prepare(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientConnection.prepareStatementX(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientConnection.prepareStatement(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) ~[commons-dbcp-1.4.jar:1.4] at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) ~[commons-dbcp-1.4.jar:1.4] at org.jumpmind.db.sql.JdbcSqlTransaction.prepare(JdbcSqlTransaction.java:436) ~[symmetric-jdbc-3.12.6.jar:3.12.6] ... 16 more 2021-03-05 16:03:05,837 ERROR (j6ufnqf3-dataloader-8) [org.jumpmind.symmetric.service.impl.ManageIncomingBatchListener] Failed to load batch home-13404021 org.jumpmind.db.sql.SqlException: Exception while preparing sql [delete from "J6UFNQF3"."SPIRO_TEST" where "ID" = ? and "PATIENT_ID" = ? and "CREATETIMESTAMP" = ? and "LASTUPDATETIMESTAMP" is NULL and "CLINIC_ID" = ? and "TEST_TIME" = ? and "SOURCE" = ? and "REFERENCE" = ? and "REPO_ID" is NULL and "OPERATOR" = ? and "NOTES" = ? and "QUALITY_GRADE" is NULL and "TEST_NUMBER" = ? and "TEST_TYPE" = ? and "PRED_FEF_25" = ? and "PRED_FEF_2575" = ? and "PRED_FEF_50" = ? and "PRED_FEF_75" = ? and "PRED_FEV1" = ? and "PRED_FEV1_FEV6" = ? and "PRED_FEV1_FVC" = ? and "PRED_FEV3" = ? and "PRED_FEV3_FVC" = ? and "PRED_FEV6" = ? and "PRED_FIVC" = ? and "PRED_FVC" = ? and "PRED_PEF" = ? and "PREDICTED_TEXT" = ? and "SYNC" = ? and "INTERPRETATION_TEXT" = ? and "PRED_PIF" = ? and "LLN_FEF_25" = ? and "LLN_FEF_2575" = ? and "LLN_FEF_50" = ? and "LLN_FEF_75" = ? and "LLN_FEV1" = ? and "LLN_FEV1_FEV6" = ? and "LLN_FEV1_FVC" = ? and "LLN_FEV3" = ? and "LLN_FEV3_FVC" = ? and "LLN_FEV6" = ? and "LLN_FIVC" = ? and "LLN_FVC" = ? and "LLN_PEF" = ? and "LLN_PIF" = ?] at org.jumpmind.db.sql.JdbcSqlTransaction.prepare(JdbcSqlTransaction.java:439) ~[symmetric-jdbc-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.prepare(DefaultDatabaseWriter.java:1027) ~[symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.delete(DefaultDatabaseWriter.java:388) ~[symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:197) ~[symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64) ~[symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84) ~[symmetric-core-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64) ~[symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:202) ~[symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:212) ~[symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:178) ~[symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:124) [symmetric-io-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1085) [symmetric-core-3.12.6.jar:3.12.6] at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1061) [symmetric-core-3.12.6.jar:3.12.6] at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_261] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_261] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_261] at java.lang.Thread.run(Thread.java:748) [?:1.8.0_261] Caused by: java.sql.SQLSyntaxErrorException: Comparisons between 'LONG VARCHAR (UCS_BASIC)' and 'LONG VARCHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') at org.apache.derby.client.am.SQLExceptionFactory.getSQLException(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientConnection.prepareStatement(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) ~[commons-dbcp-1.4.jar:1.4] at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) ~[commons-dbcp-1.4.jar:1.4] at org.jumpmind.db.sql.JdbcSqlTransaction.prepare(JdbcSqlTransaction.java:436) ~[symmetric-jdbc-3.12.6.jar:3.12.6] ... 16 more Caused by: org.apache.derby.client.am.SqlException: Comparisons between 'LONG VARCHAR (UCS_BASIC)' and 'LONG VARCHAR (UCS_BASIC)' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') at org.apache.derby.client.am.ClientStatement.completeSqlca(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientStatement.readPrepareDescribeOutput(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientPreparedStatement.readPrepareDescribeInputOutput(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientPreparedStatement.flowPrepareDescribeInputOutput(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientPreparedStatement.prepare(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientConnection.prepareStatementX(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.derby.client.am.ClientConnection.prepareStatement(Unknown Source) ~[derbyclient-10.14.2.0.jar:?] at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281) ~[commons-dbcp-1.4.jar:1.4] at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313) ~[commons-dbcp-1.4.jar:1.4] at org.jumpmind.db.sql.JdbcSqlTransaction.prepare(JdbcSqlTransaction.java:436) ~[symmetric-jdbc-3.12.6.jar:3.12.6] ... 16 more