View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0005544 | SymmetricDS | Bug | public | 2022-10-20 20:49 | 2022-11-30 19:49 |
| Reporter | elong | Assigned To | elong | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.6.0 | ||||
| Target Version | 3.14.3 | Fixed in Version | 3.14.3 | ||
| Summary | 0005544: Initial load error on MySQL with parameter db.treat.date.time.as.varchar.enabled=true | ||||
| Description | The 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 Reproduce | 1. 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 | ||||
| Tags | initial/partial load | ||||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2022-10-20 20:49 | elong | New Issue | |
| 2022-10-20 20:49 | elong | Status | new => assigned |
| 2022-10-20 20:49 | elong | Assigned To | => elong |
| 2022-10-20 20:49 | elong | Tag Attached: initial/partial load | |
| 2022-10-20 20:49 | elong | Issue generated from: 0005543 | |
| 2022-10-20 20:49 | elong | Relationship added | related to 0005543 |
| 2022-10-20 20:49 | elong | Status | assigned => resolved |
| 2022-10-20 20:49 | elong | Resolution | open => fixed |
| 2022-10-20 20:49 | elong | Fixed in Version | => 3.14.3 |
| 2022-11-30 19:49 | admin | Status | resolved => closed |