View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005247 | SymmetricDS Pro | Bug | public | 2022-03-29 15:00 | 2022-07-27 20:54 |
| Reporter | mhelbach | Assigned To | elong | ||
| Priority | high | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.12.15 | ||||
| Target Version | 3.14.0 | Fixed in Version | 3.14.0 | ||
| Summary | 0005247: SQL Server Date columns are being converted to DateTime | ||||
| Description | Sql 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 Reproduce | Create 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 | ||||
| Tags | No tags attached. | ||||
|
|
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 |
|
|
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. |
|
|
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] |
|
|
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; |
|
|
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) |
|
|
Easy fix is to set trustServerCertificate=true in the db.url. |
|
|
That did it. I'm up on the microsoft driver and have started clearing batches that had this date issue. Thanks for your help. |
| 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 |