View Issue Details

IDProjectCategoryView StatusLast Update
0003962SymmetricDSBugpublic2019-05-17 19:52
ReporterhanesAssigned To 
Prioritynormal 
Status confirmedResolutionopen 
Product Version3.10.2 
Target VersionFixed in Version 
Summary0003962: Oracle's TIMESTAMP with Time Zone doesn't map to MariaDB
Description Source table:

         create table test23 (
            mytimestamp6 timestamp(6),
            mytimestamp6wtz timestamp(6) with time zone,
    myId varchar(256), primary key (myId));

fails to create a target table on MariaDB due to invalid type of "TIMESTAMPTZ".


[store-001] - DefaultDatabaseWriter - About to create table using the following definition: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="TEST23">
        <column name="MYID" primaryKey="true" required="true" type="VARCHAR" size="256">
            <platform-column name="oracle" type="VARCHAR2" size="256"/>
        </column>
        <column name="MYTIMESTAMP6" type="TIMESTAMP" size="11,6">
            <platform-column name="oracle" type="TIMESTAMP(6)" size="11" decimalDigits="6"/>
        </column>
        <column name="MYTIMESTAMP6WTZ" type="TIMESTAMPTZ" size="13,6">
            <platform-column name="oracle" type="TIMESTAMP(6) WITH TIME ZONE" size="13" decimalDigits="6"/>
        </column>
    </table>
</database>
[store-001] - MariaDBDatabasePlatform - Running alter sql:
CREATE TABLE `test23`(
    `myid` VARCHAR(256) NOT NULL,
    `mytimestamp6` DATETIME(6),
    `mytimestamp6wtz` TIMESTAMPTZ,
    PRIMARY KEY (`myid`)
);

[store-001] - JdbcSqlTemplate - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TIMESTAMPTZ,
    PRIMARY KEY (`myid`)
)' at line 4. Failed to execute: CREATE TABLE `test23`(
    `myid` VARCHAR(256) NOT NULL,
    `mytimestamp6` DATETIME(6),
    `mytimestamp6wtz` TIMESTAMPTZ,
    PRIMARY KEY (`myid`)
)
[store-001] - DefaultDatabaseWriter - Failed to alter table using the following xml: <?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database">
<database name="dataextractor">
    <table name="TEST23">
        <column name="MYID" primaryKey="true" required="true" type="VARCHAR" size="256">
            <platform-column name="oracle" type="VARCHAR2" size="256"/>
        </column>
        <column name="MYTIMESTAMP6" type="TIMESTAMP" size="11,6">
            <platform-column name="oracle" type="TIMESTAMP(6)" size="11" decimalDigits="6"/>
        </column>
        <column name="MYTIMESTAMP6WTZ" type="TIMESTAMPTZ" size="13,6">
            <platform-column name="oracle" type="TIMESTAMP(6) WITH TIME ZONE" size="13" decimalDigits="6"/>
        </column>
    </table>
</database>
org.jumpmind.db.sql.SqlException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TIMESTAMPTZ,
    PRIMARY KEY (`myid`)
)' at line 4
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:302)
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:291)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:513)
    at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:421)
    at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:115)
    at org.jumpmind.db.platform.AbstractDatabasePlatform.alterTables(AbstractDatabasePlatform.java:229)
    at org.jumpmind.db.platform.AbstractDatabasePlatform.alterDatabase(AbstractDatabasePlatform.java:202)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.create(DefaultDatabaseWriter.java:557)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:204)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
    at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
    at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:201)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:211)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:178)
    at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:124)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1095)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1072)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    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:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TIMESTAMPTZ,
    PRIMARY KEY (`myid`)
)' at line 4
    at sun.reflect.GeneratedConstructorAccessor63.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2480)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2438)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:436)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:421)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:511)
    ... 19 more
[store-001] - ManageIncomingBatchListener - Failed to load batch 000-208
org.jumpmind.db.sql.SqlException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TIMESTAMPTZ,
    PRIMARY KEY (`myid`)
)' at line 4
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:302)
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:291)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:513)
    at org.jumpmind.db.sql.JdbcSqlTemplate.update(JdbcSqlTemplate.java:421)
    at org.jumpmind.db.sql.SqlScript.execute(SqlScript.java:115)
    at org.jumpmind.db.platform.AbstractDatabasePlatform.alterTables(AbstractDatabasePlatform.java:229)
    at org.jumpmind.db.platform.AbstractDatabasePlatform.alterDatabase(AbstractDatabasePlatform.java:202)
    at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.create(DefaultDatabaseWriter.java:557)
    at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:204)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
    at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
    at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:201)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:211)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:178)
    at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:124)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1095)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1072)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    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:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'TIMESTAMPTZ,
    PRIMARY KEY (`myid`)
)' at line 4
    at sun.reflect.GeneratedConstructorAccessor63.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.Util.getInstance(Util.java:408)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2480)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2438)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:436)
    at org.jumpmind.db.sql.JdbcSqlTemplate$6.execute(JdbcSqlTemplate.java:421)
    at org.jumpmind.db.sql.JdbcSqlTemplate.execute(JdbcSqlTemplate.java:511)
    ... 19 more
[store-001] - RouterService - Routed 3 data events in 11 ms
[store-001] - IncomingBatchService - Retrying batch 000-208
Tagsddl/schema, dialect: mysql/mariadb

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2019-05-15 14:45 hanes New Issue
2019-05-15 14:45 hanes Status new => confirmed
2019-05-17 19:52 elong Tag Attached: ddl/schema
2019-05-17 19:52 elong Tag Attached: dialect: mysql/mariadb