View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003963SymmetricDSBugpublic2019-05-15 18:512022-07-27 18:59
Reporterhanes Assigned Toelong  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.10.2 
Target Version3.14.0Fixed in Version3.14.0 
Summary0003963: char(n) and nchar(n) where n>255 is valid in oracle but maps to invalid types in MariaDB
DescriptionSample tables:

    create table test24 (
            mybigchar char(300),
    myId varchar(256), primary key (myId));

    create table test25 (
            mybignchar nchar(300),
    myId varchar(256), primary key (myId));

In both cases, SymmetricDS should map over to varchar() (or nvarchar()) when n>255

nchar() case:



[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="TEST25">
        <column name="MYID" primaryKey="true" required="true" type="VARCHAR" size="256">
            <platform-column name="oracle" type="VARCHAR2" size="256"/>
        </column>
        <column name="MYBIGNCHAR" type="CHAR" size="300">
            <platform-column name="oracle" type="NCHAR" size="300"/>
        </column>
    </table>
</database>
[store-001] - MariaDBDatabasePlatform - Running alter sql:
CREATE TABLE `test25`(
    `myid` VARCHAR(256) NOT NULL,
    `mybignchar` CHAR(300) NULL,
    PRIMARY KEY (`myid`)
);

[store-001] - JdbcSqlTemplate - Column length too big for column 'mybignchar' (max = 255); use BLOB or TEXT instead. Failed to execute: CREATE TABLE `test25`(
    `myid` VARCHAR(256) NOT NULL,
    `mybignchar` CHAR(300) NULL,
    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="TEST25">
        <column name="MYID" primaryKey="true" required="true" type="VARCHAR" size="256">
            <platform-column name="oracle" type="VARCHAR2" size="256"/>
        </column>
        <column name="MYBIGNCHAR" type="CHAR" size="300">
            <platform-column name="oracle" type="NCHAR" size="300"/>
        </column>
    </table>
</database>
org.jumpmind.db.sql.SqlException: Column length too big for column 'mybignchar' (max = 255); use BLOB or TEXT instead
    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: Column length too big for column 'mybignchar' (max = 255); use BLOB or TEXT instead
    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-220
org.jumpmind.db.sql.SqlException: Column length too big for column 'mybignchar' (max = 255); use BLOB or TEXT instead
    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: Column length too big for column 'mybignchar' (max = 255); use BLOB or TEXT instead
    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 10 ms



char() case:
char() case:

[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="TEST24">
        <column name="MYID" primaryKey="true" required="true" type="VARCHAR" size="256">
            <platform-column name="oracle" type="VARCHAR2" size="256"/>
        </column>
        <column name="MYBIGCHAR" type="CHAR" size="300">
            <platform-column name="oracle" type="CHAR" size="300"/>
        </column>
    </table>
</database>
[store-001] - MariaDBDatabasePlatform - Running alter sql:
CREATE TABLE `test24`(
    `myid` VARCHAR(256) NOT NULL,
    `mybigchar` CHAR(300) NULL,
    PRIMARY KEY (`myid`)
);

[store-001] - JdbcSqlTemplate - Column length too big for column 'mybigchar' (max = 255); use BLOB or TEXT instead. Failed to execute: CREATE TABLE `test24`(
    `myid` VARCHAR(256) NOT NULL,
    `mybigchar` CHAR(300) NULL,
    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="TEST24">
        <column name="MYID" primaryKey="true" required="true" type="VARCHAR" size="256">
            <platform-column name="oracle" type="VARCHAR2" size="256"/>
        </column>
        <column name="MYBIGCHAR" type="CHAR" size="300">
            <platform-column name="oracle" type="CHAR" size="300"/>
        </column>
    </table>
</database>
org.jumpmind.db.sql.SqlException: Column length too big for column 'mybigchar' (max = 255); use BLOB or TEXT instead
    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: Column length too big for column 'mybigchar' (max = 255); use BLOB or TEXT instead
    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-216
org.jumpmind.db.sql.SqlException: Column length too big for column 'mybigchar' (max = 255); use BLOB or TEXT instead
    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: Column length too big for column 'mybigchar' (max = 255); use BLOB or TEXT instead
    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 8 ms


Tagsddl/schema, dialect: mysql/mariadb

Relationships

related to 0004298 closedelong Sycing schemas from MSSQL with column type CHAR(400) to MySQL fails 

Activities

There are no notes attached to this issue.

Related Changesets

SymmetricDS: 3.14 52ad8d41

2022-05-24 17:11:42

admin

Details Diff 		0003963: char > 255 should be varchar Affected Issues
0003963
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:51 hanes New Issue
2019-05-15 18:52 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-05-24 17:12 elong Assigned To => elong
2022-05-24 17:12 elong Status confirmed => resolved
2022-05-24 17:12 elong Resolution open => fixed
2022-05-24 17:12 elong Fixed in Version => 3.14.0
2022-05-24 17:12 elong Target Version => 3.14.0
2022-05-24 18:00 admin Changeset attached => SymmetricDS 3.14 52ad8d41
2022-05-27 16:18 elong Relationship added related to 0004298
2022-07-27 18:59 admin Status resolved => closed