View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0005762SymmetricDSBugpublic2023-03-27 22:562023-03-31 19:44
Reporterenedil Assigned To 
Prioritynormal 
Status closedResolutionno change required 
Product Version3.14.6 
Summary0005762: `ORA-01843: not a valid month` while updating SYM_NODE_HOST
DescriptionI have two DBs, one Oracle and one mariadb. Full debug output below, I recommend to read it first (but pasted below to reduce clutter).

This error happens every time I run symmetricds with my configuration. To be fair I don't know which tables I should show. However, I strongly suspect that the core issue is that the default timestamp format is different on my Oracle installation. The oracle db server is not english-localized. When I try to repeat the query directly on the database, I get yet another error (which seems to indicate, that either symmetricds or my client does something peculiar to the connection):

```
ORA-01830: wzorzec formatu daty konczy sie przed konwersja calego napisu
```
which, translated means that the date format ends before convertion of whole string. However, if I do `set "HEARTBEAT_TIME" = timestamp '2023-03-28 00:42:10.025'` or `set "HEARTBEAT_TIME" = '2023-03-28 00:42:10'`, both update successfully.


```
2023-03-28 00:42:10,025 DEBUG [oracle] [TransformWriter] [oracle-dataloader-44] Data has been transformed to a UPDATE for the 0000001 transform. The mapped target columns are: {node_id,host_name,heartbeat_time,instance_id,ip_address,os_user,os_name,os_arch,os_version,available_processors,free_memory_bytes,total_memory_bytes,max_memory_bytes,java_version,java_vendor,jdbc_version,symmetric_version,timezone_offset,last_restart_time,create_time}. The mapped target values are: {usosweb,lisbook,2023-03-28 00:42:10.025,lisbook-f5a83b53-0c13-4d34-8f5c-e1d8735d0c85,192.168.1.103,root,Linux,amd64,6.1.13-200.fc37.x86_64,16,31683304,83886080,3625975808,17.0.5,Private Build,mysql-connector-java-8.0.28 (Revision: 7ff2161da3899f379fb3171b6538b191b1c5c7e2),3.14.x-SNAPSHOT,+02:00,2023-03-28 00:37:40,2023-03-28 00:37:42}
2023-03-28 00:42:10,026 DEBUG [oracle] [DefaultDatabaseWriter] [oracle-dataloader-44] Preparing dml: update "USOS_PROD_TAB"."SYM_NODE_HOST" set "HEARTBEAT_TIME" = ?, "INSTANCE_ID" = ?, "IP_ADDRESS" = ?, "OS_USER" = ?, "OS_NAME" = ?, "OS_ARCH" = ?, "OS_VERSION" = ?, "AVAILABLE_PROCESSORS" = ?, "FREE_MEMORY_BYTES" = ?, "TOTAL_MEMORY_BYTES" = ?, "MAX_MEMORY_BYTES" = ?, "JAVA_VERSION" = ?, "JAVA_VENDOR" = ?, "JDBC_VERSION" = ?, "SYMMETRIC_VERSION" = ?, "TIMEZONE_OFFSET" = ?, "LAST_RESTART_TIME" = ?, "CREATE_TIME" = ? where "NODE_ID" = ? and "HOST_NAME" = ?
2023-03-28 00:42:10,026 DEBUG [oracle] [DefaultDatabaseWriter] [oracle-dataloader-44] Submitting data ['2023-03-28 00:42:10.025', 'lisbook-f5a83b53-0c13-4d34-8f5c-e1d8735d0c85', '192.168.1.103', 'root', 'Linux', 'amd64', '6.1.13-200.fc37.x86_64', 16, 31683304, 83886080, 3625975808, '17.0.5', 'Private Build', 'mysql-connector-java-8.0.28 (Revision: 7ff2161da3899f379fb3171b6538b191b1c5c7e2)', '3.14.x-SNAPSHOT', '+02:00', '2023-03-28 00:37:40', '2023-03-28 00:37:42', 'usosweb', 'lisbook'] with types [VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, NUMERIC, NUMERIC, NUMERIC, NUMERIC, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR]
2023-03-28 00:42:10,049 INFO [oracle] [DefaultDatabaseWriter] [oracle-dataloader-44] Failed to process update event in batch usosweb-3 on channel 'heartbeat'.
Failed sql was: update "USOS_PROD_TAB"."SYM_NODE_HOST" set "HEARTBEAT_TIME" = '2023-03-28 00:42:10.025', "INSTANCE_ID" = 'lisbook-f5a83b53-0c13-4d34-8f5c-e1d8735d0c85', "IP_ADDRESS" = '192.168.1.103', "OS_USER" = 'root', "OS_NAME" = 'Linux', "OS_ARCH" = 'amd64', "OS_VERSION" = '6.1.13-200.fc37.x86_64', "AVAILABLE_PROCESSORS" = 16, "FREE_MEMORY_BYTES" = 31683304, "TOTAL_MEMORY_BYTES" = 83886080, "MAX_MEMORY_BYTES" = 3625975808, "JAVA_VERSION" = '17.0.5', "JAVA_VENDOR" = 'Private Build', "JDBC_VERSION" = 'mysql-connector-java-8.0.28 (Revision: 7ff2161da3899f379fb3171b6538b191b1c5c7e2)', "SYMMETRIC_VERSION" = '3.14.x-SNAPSHOT', "TIMEZONE_OFFSET" = '+02:00', "LAST_RESTART_TIME" = '2023-03-28 00:37:40', "CREATE_TIME" = '2023-03-28 00:37:42' where "NODE_ID" = 'usosweb' and "HOST_NAME" = 'lisbook'
Failed raw sql was: update "USOS_PROD_TAB"."SYM_NODE_HOST" set "HEARTBEAT_TIME" = ?, "INSTANCE_ID" = ?, "IP_ADDRESS" = ?, "OS_USER" = ?, "OS_NAME" = ?, "OS_ARCH" = ?, "OS_VERSION" = ?, "AVAILABLE_PROCESSORS" = ?, "FREE_MEMORY_BYTES" = ?, "TOTAL_MEMORY_BYTES" = ?, "MAX_MEMORY_BYTES" = ?, "JAVA_VERSION" = ?, "JAVA_VENDOR" = ?, "JDBC_VERSION" = ?, "SYMMETRIC_VERSION" = ?, "TIMEZONE_OFFSET" = ?, "LAST_RESTART_TIME" = ?, "CREATE_TIME" = ? where "NODE_ID" = ? and "HOST_NAME" = ?
Failed sql parameters: ['2023-03-28 00:42:10.025', 'lisbook-f5a83b53-0c13-4d34-8f5c-e1d8735d0c85', '192.168.1.103', 'root', 'Linux', 'amd64', '6.1.13-200.fc37.x86_64', 16, 31683304, 83886080, 3625975808, '17.0.5', 'Private Build', 'mysql-connector-java-8.0.28 (Revision: 7ff2161da3899f379fb3171b6538b191b1c5c7e2)', '3.14.x-SNAPSHOT', '+02:00', '2023-03-28 00:37:40', '2023-03-28 00:37:42', 'usosweb', 'lisbook']
Failed sql parameters types: [VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, NUMERIC, NUMERIC, NUMERIC, NUMERIC, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR, VARCHAR]
Failed sql state and code: 22008 (1843)
Failed pk data was: "usosweb","lisbook"
Failed row data was: "usosweb","lisbook","2023-03-28 00:42:10.025","lisbook-f5a83b53-0c13-4d34-8f5c-e1d8735d0c85","192.168.1.103","root","Linux","amd64","6.1.13-200.fc37.x86_64","16","31683304","83886080","3625975808","17.0.5","Private Build","mysql-connector-java-8.0.28 (Revision: 7ff2161da3899f379fb3171b6538b191b1c5c7e2)","3.14.x-SNAPSHOT","+02:00","2023-03-28 00:37:40","2023-03-28 00:37:42"
 StackTraceKey [SqlException:725613545]
2023-03-28 00:42:10,138 ERROR [oracle] [ManageIncomingBatchListener] [oracle-dataloader-44] Failed to load batch usosweb-3 StackTraceKey [SqlException:725613545]
2023-03-28 00:42:10,168 DEBUG [oracle] [NodeCommunicationService] [oracle-job-5] Found 1 node communication locks to push to in 0ms, communicationType=PUSH
2023-03-28 00:42:10,168 DEBUG [oracle] [NodeCommunicationService] [oracle-job-5] Found 1 nodes to push to in 0ms
2023-03-28 00:42:10,215 ERROR [oracle] [PushService] [oracle-job-5] Could not find my node security row, which is needed to authenticate as node oracle
2023-03-28 00:42:10,321 DEBUG [oracle] [DataLoaderService] [qtp1057307776-29] Failed to process batch org.jumpmind.db.sql.SqlException: ORA-01843: not a valid month

    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:1048)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.update(DefaultDatabaseWriter.java:565)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:182)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:62)
    at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84)
    at org.jumpmind.symmetric.io.data.writer.TransformWriter.callWriter(TransformWriter.java:207)
    at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:179)
    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:1048)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1024)
    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:833)
Caused by: java.sql.SQLDataException: ORA-01843: not a valid month

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1151)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:152)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1052)
    at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1531)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1311)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3746)
    at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3918)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3897)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:992)
    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
Caused by: Error : 1843, Position : 63, Sql = update "USOS_PROD_TAB"."SYM_NODE_HOST" set "HEARTBEAT_TIME" = :1 , "INSTANCE_ID" = :2 , "IP_ADDRESS" = :3 , "OS_USER" = :4 , "OS_NAME" = :5 , "OS_ARCH" = :6 , "OS_VERSION" = :7 , "AVAILABLE_PROCESSORS" = :8 , "FREE_MEMORY_BYTES" = :9 , "TOTAL_MEMORY_BYTES" = :10 , "MAX_MEMORY_BYTES" = :11 , "JAVA_VERSION" = :12 , "JAVA_VENDOR" = :13 , "JDBC_VERSION" = :14 , "SYMMETRIC_VERSION" = :15 , "TIMEZONE_OFFSET" = :16 , "LAST_RESTART_TIME" = :17 , "CREATE_TIME" = :18 where "NODE_ID" = :19 and "HOST_NAME" = :20 , OriginalSql = update "USOS_PROD_TAB"."SYM_NODE_HOST" set "HEARTBEAT_TIME" = ?, "INSTANCE_ID" = ?, "IP_ADDRESS" = ?, "OS_USER" = ?, "OS_NAME" = ?, "OS_ARCH" = ?, "OS_VERSION" = ?, "AVAILABLE_PROCESSORS" = ?, "FREE_MEMORY_BYTES" = ?, "TOTAL_MEMORY_BYTES" = ?, "MAX_MEMORY_BYTES" = ?, "JAVA_VERSION" = ?, "JAVA_VENDOR" = ?, "JDBC_VERSION" = ?, "SYMMETRIC_VERSION" = ?, "TIMEZONE_OFFSET" = ?, "LAST_RESTART_TIME" = ?, "CREATE_TIME" = ? where "NODE_ID" = ? and "HOST_NAME" = ?, Error Msg = ORA-01843: not a valid month

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:637)
    ... 33 more
```
Tagsdialect: oracle

Activities

emiller

2023-03-29 17:35

developer   ~0002302

Try setting the below parameter in your target node's engine file. Afterwards, restart the target node.

db.init.sql=alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';

enedil

2023-03-31 08:51

reporter   ~0002303

Thank you for input. In the end, I needed to do

db.init.sql=alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF'

and the error doesn't occur. The issue can be closed probably.

Issue History

Date Modified Username Field Change
2023-03-27 22:56 enedil New Issue
2023-03-27 22:56 enedil Tag Attached: dialect: oracle
2023-03-29 17:35 emiller Note Added: 0002302
2023-03-29 17:35 emiller Status new => feedback
2023-03-31 08:51 enedil Note Added: 0002303
2023-03-31 08:51 enedil Status feedback => new
2023-03-31 19:44 emiller Status new => closed
2023-03-31 19:44 emiller Resolution open => no change required