View Issue Details

IDProjectCategoryView StatusLast Update
0005543SymmetricDSBugpublic2022-11-30 19:40
Reporterelong Assigned Toelong  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.6.0 
Target Version3.13.9Fixed in Version3.13.9 
Summary0005543: Initial load error on MySQL with parameter db.treat.date.time.as.varchar.enabled=true
DescriptionThe MySQL JDBC driver cannot return invalid dates that contain a zero in the year, month, or day. (However, for a "zero date" with all components as zero, then the db.url parameter of zeroDateTimeBehavior=convertToNull will convert them to null.) Dates like 1973-00-00, 1973-01-00, and 0000-01-00 will get an error from JDBC when trying to extract them. The db.treat.date.time.as.varchar.enabled parameter will cast the date to a character string so it can be extracted. (It also modifies the trigger text to do the same for capture.) However, the SQL generated for initial/paritlal load extraction has the column aliased twice, resulting in a SQL syntax error.

ERROR [client-1] [NodeCommunicationService] [client-1-extract-default-1] Failed to execute EXTRACT for node server and channel default StackTraceKey.init [SqlException:4230537740] org.jumpmind.db.sql.SqlException: Failed to execute sql: select `id` as x__0,cast(
`create_time` as char) as
create_time as x__1 from `elong1`.`mydate` t where 1=1
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:308)
    at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(JdbcSqlReadCursor.java:101)
    at org.jumpmind.db.sql.JdbcSqlTemplate.queryForCursor(JdbcSqlTemplate.java:146)
    at org.jumpmind.db.sql.JdbcSqlTemplate.queryForCursor(JdbcSqlTemplate.java:139)
    at org.jumpmind.symmetric.extract.SelectFromTableSource.startNewCursor(SelectFromTableSource.java:277)
    at org.jumpmind.symmetric.extract.SelectFromTableSource.selectNext(SelectFromTableSource.java:191)
    at org.jumpmind.symmetric.extract.SelectFromTableSource.next(SelectFromTableSource.java:120)
    at org.jumpmind.symmetric.extract.SelectFromSymDataSource.processReloadEvent(SelectFromSymDataSource.java:208)
    at org.jumpmind.symmetric.extract.SelectFromSymDataSource.next(SelectFromSymDataSource.java:135)
    at org.jumpmind.symmetric.io.data.reader.ExtractDataReader.nextTable(ExtractDataReader.java:89)
    at com.jumpmind.symmetric.db.ProExtractDataReader.nextTable(ProExtractDataReader.java:71)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:151)
    at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:108)
    at org.jumpmind.symmetric.service.impl.DataExtractorService.extractOutgoingBatch(DataExtractorService.java:876)
    at org.jumpmind.symmetric.service.impl.DataExtractorService.execute(DataExtractorService.java:1797)
    at org.jumpmind.symmetric.service.impl.NodeCommunicationService$1.run(NodeCommunicationService.java:523)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:750)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as x__1 from `elong1`.`mydate` t where 1=1' at line 3
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1201)
    at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
    at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(DelegatingStatement.java:329)
    at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(JdbcSqlReadCursor.java:87)
    ... 17 more
Steps To Reproduce1. Set db.init.sql=set sql_mode=no_engine_substitution
2. Set db.treat.date.time.as.varchar.enabled=true
3. create table mydate (id integer primary key, create_time datetime);
4. insert into mydate values (1, '1973-00-00');
5. Send a load for the table
Tagsinitial/partial load

Relationships

related to 0005544 closedelong Initial load error on MySQL with parameter db.treat.date.time.as.varchar.enabled=true 

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.13 fa2c4da7

2022-10-20 20:46:20

admin

Details Diff
0005543: Initial load error on MySQL with parameter
db.treat.date.time.as.varchar.enabled=true
Affected Issues
0005543
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mysql/MySqlTriggerTemplate.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/nuodb/NuoDbTriggerTemplate.java Diff File

SymmetricDS: 3.14 ab45670b

2022-10-20 20:46:20

admin

Details Diff
0005543: Initial load error on MySQL with parameter
db.treat.date.time.as.varchar.enabled=true
Affected Issues
0005543
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/mysql/MySqlTriggerTemplate.java Diff File
mod - symmetric-client/src/main/java/org/jumpmind/symmetric/db/nuodb/NuoDbTriggerTemplate.java Diff File

Issue History

Date Modified Username Field Change
2022-10-20 20:42 elong New Issue
2022-10-20 20:42 elong Status new => assigned
2022-10-20 20:42 elong Assigned To => elong
2022-10-20 20:42 elong Tag Attached: initial/partial load
2022-10-20 20:42 elong Project SymmetricDS Pro => SymmetricDS
2022-10-20 20:48 elong Status assigned => resolved
2022-10-20 20:48 elong Resolution open => fixed
2022-10-20 20:48 elong Fixed in Version => 3.13.9
2022-10-20 20:49 elong Issue cloned: 0005544
2022-10-20 20:49 elong Relationship added related to 0005544
2022-10-20 21:00 admin Changeset attached => SymmetricDS 3.13 fa2c4da7
2022-10-20 21:00 admin Changeset attached => SymmetricDS 3.14 ab45670b
2022-11-30 19:40 admin Status resolved => closed