View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003961SymmetricDSBugpublic2019-05-15 18:392022-08-30 13:04
Reporterhanes Assigned Toemiller  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.10.2 
Target Version3.14.1Fixed in Version3.14.1 
Summary0003961: Oracle XMLTYPE incorrectly mapped to invalid SQL Type SQLXML in MariaDB
DescriptionSource table:

create table test4 (myxml xmltype,
myId varchar(256), primary key (myId));

throws a SQL Exception when creating a table on the MariaDB target (and likely on MySQL, too):


[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="TEST4">
        <column name="MYID" primaryKey="true" required="true" type="VARCHAR" size="256">
            <platform-column name="oracle" type="VARCHAR2" size="256"/>
        </column>
        <column name="MYXML" type="SQLXML" size="2000">
            <platform-column name="oracle" type="XMLTYPE" size="2000"/>
        </column>
    </table>
</database>
[store-001] - MariaDBDatabasePlatform - Running alter sql:
CREATE TABLE `test4`(
    `myid` VARCHAR(256) NOT NULL,
    `myxml` SQLXML,
    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 'SQLXML,
    PRIMARY KEY (`myid`)
)' at line 3. Failed to execute: CREATE TABLE `test4`(
    `myid` VARCHAR(256) NOT NULL,
    `myxml` SQLXML,
    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="TEST4">
        <column name="MYID" primaryKey="true" required="true" type="VARCHAR" size="256">
            <platform-column name="oracle" type="VARCHAR2" size="256"/>
        </column>
        <column name="MYXML" type="SQLXML" size="2000">
            <platform-column name="oracle" type="XMLTYPE" size="2000"/>
        </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 'SQLXML,
    PRIMARY KEY (`myid`)
)' at line 3
    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 'SQLXML,
    PRIMARY KEY (`myid`)
)' at line 3
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    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-201
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 'SQLXML,
    PRIMARY KEY (`myid`)
)' at line 3
    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 'SQLXML,
    PRIMARY KEY (`myid`)
)' at line 3
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    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 6 ms
[store-001] - IncomingBatchService - Retrying batch 000-201
[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="TEST4">
        <column name="MYID" primaryKey="true" required="true" type="VARCHAR" size="256">
            <platform-column name="oracle" type="VARCHAR2" size="256"/>
        </column>
        <column name="MYXML" type="SQLXML" size="2000">
            <platform-column name="oracle" type="XMLTYPE" size="2000"/>
        </column>
    </table>
</database>
[store-001] - MariaDBDatabasePlatform - Running alter sql:
CREATE TABLE `test4`(
    `myid` VARCHAR(256) NOT NULL,
    `myxml` SQLXML,
    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 'SQLXML,
    PRIMARY KEY (`myid`)
)' at line 3. Failed to execute: CREATE TABLE `test4`(
    `myid` VARCHAR(256) NOT NULL,
    `myxml` SQLXML,
    PRIMARY KEY (`myid`)
)
Tagsddl/schema, dialect: mysql/mariadb

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.14 a2a5a26d

2022-08-16 17:06:25

evan-miller-jumpmind

Details Diff 		0003961: Mapped Oracle XMLTYPE type to MariaDB LONGTEXT type Affected Issues
0003961
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/mysql/MySqlDdlBuilder.java Diff File

Issue History

Date Modified Username Field Change
2019-05-15 18:39 hanes New Issue
2019-05-15 18:40 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-08-16 17:06 emiller Assigned To => emiller
2022-08-16 17:06 emiller Status confirmed => resolved
2022-08-16 17:06 emiller Resolution open => fixed
2022-08-16 17:06 emiller Fixed in Version => 3.14.1
2022-08-16 18:00 Changeset attached => SymmetricDS 3.14 a2a5a26d
2022-08-30 13:04 admin Status resolved => closed