View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003962SymmetricDSBugpublic2019-05-15 18:452022-07-27 18:59
Reporterhanes Assigned Toelong  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.10.2 
Target Version3.14.0Fixed in Version3.14.0 
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

Relationships

related to 0005280 closedelong Table DDL with timestamp and fractional second precision 

Activities

There are no notes attached to this issue.

Issue History

Date Modified Username Field Change
2019-05-15 18:45 hanes New Issue
2019-05-15 18:45 hanes Status new => confirmed
2019-05-17 23:52 elong Tag Attached: ddl/schema
2019-05-17 23:52 elong Tag Attached: dialect: mysql/mariadb
2022-04-26 17:41 elong Relationship added related to 0005280
2022-04-26 17:43 elong Assigned To => elong
2022-04-26 17:43 elong Status confirmed => resolved
2022-04-26 17:43 elong Resolution open => fixed
2022-04-26 17:43 elong Fixed in Version => 3.14.0
2022-04-26 17:43 elong Target Version => 3.14.0
2022-07-27 18:59 admin Status resolved => closed