View Issue Details

IDProjectCategoryView StatusLast Update
0005544SymmetricDSBugpublic2022-11-30 19:49
Reporterelong Assigned Toelong  
Status closedResolutionfixed 
Product Version3.6.0 
Target Version3.14.3Fixed in Version3.14.3 
Summary0005544: Initial load error on MySQL with parameter
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 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(
    at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(
    at org.jumpmind.db.sql.JdbcSqlTemplate.queryForCursor(
    at org.jumpmind.db.sql.JdbcSqlTemplate.queryForCursor(
    at org.jumpmind.symmetric.extract.SelectFromTableSource.startNewCursor(
    at org.jumpmind.symmetric.extract.SelectFromTableSource.selectNext(
    at org.jumpmind.symmetric.extract.SelectFromSymDataSource.processReloadEvent(
    at com.jumpmind.symmetric.db.ProExtractDataReader.nextTable(
    at org.jumpmind.symmetric.service.impl.DataExtractorService.extractOutgoingBatch(
    at org.jumpmind.symmetric.service.impl.DataExtractorService.execute(
    at org.jumpmind.symmetric.service.impl.NodeCommunicationService$
    at java.util.concurrent.ThreadPoolExecutor.runWorker(
    at java.util.concurrent.ThreadPoolExecutor$
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(
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(
    at com.mysql.cj.jdbc.StatementImpl.executeQuery(
    at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(
    at org.apache.commons.dbcp2.DelegatingStatement.executeQuery(
    at org.jumpmind.db.sql.JdbcSqlReadCursor.<init>(
    ... 17 more
Steps To Reproduce1. Set db.init.sql=set sql_mode=no_engine_substitution
2. Set
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


related to 0005543 closedelong Initial load error on MySQL with parameter 


There are no notes attached to this issue.

Issue History

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