View Issue Details

IDProjectCategoryView StatusLast Update
0005247SymmetricDS ProBugpublic2022-07-27 20:54
Reportermhelbach Assigned Toelong  
Priorityhigh 
Status closedResolutionfixed 
Product Version3.12.15 
Target Version3.14.0Fixed in Version3.14.0 
Summary0005247: SQL Server Date columns are being converted to DateTime
DescriptionSql server Date column with values of 1/1/1 in it thows the error Parameter arg '0001-01-01 00:00:00.0' type: TIMESTAMP caused exception: Only dates between January 1, 1753 and December 31, 9999 are accepted.

In sql server Date column 1/1/1 is a valid value however the lower limit for a date time is 1/1/1753

Based on the error message it looks like the system is trying to convert date values to datetime values
Steps To ReproduceCreate a sql server table with a date column
link to another sql server node
insert the value of 1/1/1 into the date column
sync the two nodes
TagsNo tags attached.

Activities

elong

2022-04-04 15:07

developer   ~0002067

This error comes from the JTDS JDBC driver. You can set db.treat.date.time.as.varchar.enabled=true to work around it. Or, switch to the Microsoft JDBC driver, which doesn't have a problem. Here is an example engine file that switches to Microsoft driver:

#db.driver=net.sourceforge.jtds.jdbc.Driver
db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
#db.url=jdbc:jtds\:sqlserver\://localhost/elong1
db.url=jdbc:sqlserver://localhost;databasename=elong1;sendTimeAsDateTime=false;lockTimeout=60000;socketTimeout=360000;applicationName=symds

elong

2022-04-05 17:28

developer   ~0002071

Thanks for the history on the error. In SymmetricDS 3.14, we are deprecating use of JTDS (because the project seems to have gone quiet) and preferring to use Microsoft driver. (Originally the JTDS driver had better support and performance, but now the Microsoft driver seems just as good.) So, using the Microsoft driver going forward should avoid this error.

mhelbach

2022-04-05 18:11

reporter   ~0002072

The microsoft driver(com.microsoft.sqlserver.jdbc.SQLServerDriver) was what we were using when we were on 3.11.12 when we upgraded to 3.12.14 we got the below error msg on server start up so switched to the source forge driver (net.sourceforge.jtds.jdbc.Driver)

2022-04-05 17:53:08,874 ERROR [DSFullV2] [AbstractSymmetricEngine] [symmetric-engine-startup-1] Could not get a connection to the database: Cannot create JDBC driver of class 'com.microsoft.sqlserver.jdbc.SQLServerDriver' for connect URL 'jdbc:jtds:sqlserver://Server.database.windows.net;databasename=DBName;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;'. Waiting for 10 seconds before trying to connect to the database again. StackTraceKey [SQLException:3637534823]

elong

2022-04-06 13:53

developer   ~0002073

You have db.driver set to Microsoft but the db.url is set to JTDS. That is a bad state, so it gives an error. In 3.11, it would accept the bad state and use JTDS based on db.url. In 3.12 and newer, it will not allow that bad state. Set both db.driver and db.url to use Microsoft:

db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
db.url=jdbc:sqlserver://Server.database.windows.net;databasename=DBName;sendTimeAsDateTime=false;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;

mhelbach

2022-04-06 14:16

reporter   ~0002074

That kicks back error
2022-04-06 14:14:43,135 ERROR [AbstractSymmetricEngine] [symmetric-engine-startup-1] Could not get a connection to the database: Cannot create PoolableConnectionFactory (The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:c6bd8f53-192b-4644-b2b1-3472dc54c09e). Waiting for 10 seconds before trying to connect to the database again.
StackTraceKey.init [SQLException:2338137088] java.sql.SQLException: Cannot create PoolableConnectionFactory (The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption.
Error: "sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:c6bd8f53-192b-4644-b2b1-3472dc54c09e)

elong

2022-04-06 14:35

developer   ~0002075

Easy fix is to set trustServerCertificate=true in the db.url.

mhelbach

2022-04-06 15:01

reporter   ~0002076

That did it. I'm up on the microsoft driver and have started clearing batches that had this date issue. Thanks for your help.

Issue History

Date Modified Username Field Change
2022-03-29 15:00 mhelbach New Issue
2022-04-04 15:07 elong Note Added: 0002067
2022-04-05 17:28 elong Note Added: 0002071
2022-04-05 17:29 elong Assigned To => elong
2022-04-05 17:29 elong Status new => resolved
2022-04-05 17:29 elong Resolution open => fixed
2022-04-05 17:29 elong Fixed in Version => 3.14.0
2022-04-05 17:29 elong Target Version => 3.14.0
2022-04-05 18:11 mhelbach Status resolved => feedback
2022-04-05 18:11 mhelbach Resolution fixed => reopened
2022-04-05 18:11 mhelbach Note Added: 0002072
2022-04-06 13:53 elong Note Added: 0002073
2022-04-06 14:16 mhelbach Note Added: 0002074
2022-04-06 14:16 mhelbach Status feedback => assigned
2022-04-06 14:35 elong Note Added: 0002075
2022-04-06 15:01 mhelbach Note Added: 0002076
2022-04-08 13:24 elong Status assigned => resolved
2022-04-08 13:24 elong Resolution reopened => fixed
2022-07-27 20:54 admin Status resolved => closed