View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006721 | SymmetricDS | Bug | public | 2025-02-06 16:27 | 2025-02-06 16:27 |
| Reporter | elong | Assigned To | elong | ||
| Priority | normal | ||||
| Status | assigned | Resolution | open | ||
| Product Version | 3.15.0 | ||||
| Target Version | 3.15.13 | ||||
| Summary | 0006721: Data loader incorrect SQL error when no columns on target table match | ||||
| Description | If source and target table names match but the column names do not, the dataloader will execute invalid SQL that causes a syntax error. Instead, we should log an illegal state exception explaining that no column names matched. This happened to a user whose intention was to transform column names, but the transform was not setup correctly, and the resulting error message was confusing. INFO [client-1] [DefaultDatabaseWriter] [client-1-dataloader-13] Failed to process insert event in batch server-478 on channel 'default'. Failed sql was: insert into "elong2"."dbo"."mytest" () values () Failed sql parameters: [] Failed sql parameters types: [] Failed sql state and code: S0001 (102) Failed row data was: "1","eric","2025-02-06 15:40:04.387" org.jumpmind.db.sql.SqlException: Incorrect syntax near ')'. at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297) at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:443) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:1218) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.insert(DefaultDatabaseWriter.java:285) at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:185) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62) at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:98) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62) at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:187) at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:194) at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:160) at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:108) at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1076) at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:840) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:261) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1752) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:686) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:605) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7748) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4410) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:293) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:263) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:548) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) at org.jumpmind.db.sql.JdbcSqlTransaction.executePreparedUpdate(JdbcSqlTransaction.java:462) at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:440) ... 16 more 2025-02-06 10:40:09,537 WARN [client-1] [CsvData] [client-1-dataloader-13] There are more keys than values 2025-02-06 10:40:09,537 WARN [client-1] [CsvData] [client-1-dataloader-13] There are more keys than values 2025-02-06 10:40:09,543 ERROR [client-1] [ManageIncomingBatchListener] [client-1-dataloader-13] Failed to load batch server-478 org.jumpmind.db.sql.SqlException: Incorrect syntax near ')'. at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308) at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:297) at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:443) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:1218) at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.insert(DefaultDatabaseWriter.java:285) at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:185) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62) at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:98) at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62) at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:187) at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:194) at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:160) at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:108) at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1076) at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1) at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:840) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:261) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1752) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:686) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:605) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7748) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:4410) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:293) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:263) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:548) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:136) at org.jumpmind.db.sql.JdbcSqlTransaction.executePreparedUpdate(JdbcSqlTransaction.java:462) at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:440) ... 16 more | ||||
| Steps To Reproduce | On server: create table mytest (id integer, name varchar(100), created_time datetime); insert into mytest values (1, 'eric', current_timestamp); update mytest set name = 'server', created_time = current_timestamp where id = 1; delete from mytest where id = 1; On client: create table mytest (my_id integer, my_name varchar(100), created_time my_datetime); | ||||
| Tags | data sync | ||||