View Issue Details

IDProjectCategoryView StatusLast Update
0003107SymmetricDSBugpublic2018-10-29 12:52
ReportermarcAssigned To 
Prioritylow 
Status confirmedResolutionopen 
Product Version3.8.24 
Target Version3.10.0Fixed in Version 
Summary0003107: Create table statement syntax error for tinyblob field
DescriptionHello,

I'm trying to integrate a mysql db with symmetricds and I'm having the following issue when running the initial load a the target node.

[store-001] - JdbcSqlTemplate - 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 '(255) NULL,
    PRIMARY KEY (`ID`)
)' at line 9. Failed to execute: CREATE TABLE `green_tpv_2`.`PEOPLE`(
    `ID` VARCHAR(255) NOT NULL,
    `NAME` VARCHAR(255) NOT NULL,
    `APPPASSWORD` VARCHAR(255) NULL,
    `CARD` VARCHAR(255) NULL,
    `ROLE` VARCHAR(255) NOT NULL,
    `VISIBLE` BIT NOT NULL,
    `IMAGE` MEDIUMBLOB NULL,
    `PREFERENCES` TINYBLOB(255) NULL,
    PRIMARY KEY (`ID`)
)
[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" catalog="green_tpv_2">
    <table name="PEOPLE">
        <column name="ID" primaryKey="true" required="true" type="VARCHAR" size="255">
            <platform-column name="mysql" type="VARCHAR" size="255"/>
        </column>
        <column name="NAME" required="true" type="VARCHAR" size="255">
            <platform-column name="mysql" type="VARCHAR" size="255"/>
        </column>
        <column name="APPPASSWORD" type="VARCHAR" size="255">
            <platform-column name="mysql" type="VARCHAR" size="255"/>
        </column>
        <column name="CARD" type="VARCHAR" size="255">
            <platform-column name="mysql" type="VARCHAR" size="255"/>
        </column>
        <column name="ROLE" required="true" type="VARCHAR" size="255">
            <platform-column name="mysql" type="VARCHAR" size="255"/>
        </column>
        <column name="VISIBLE" required="true" type="BIT" size="1">
            <platform-column name="mysql" type="BIT" size="1"/>
        </column>
        <column name="IMAGE" type="LONGVARBINARY" size="16777215">
            <platform-column name="mysql" type="MEDIUMBLOB" size="16777215"/>
        </column>
        <column name="PREFERENCES" type="BINARY" size="255">
            <platform-column name="mysql" type="TINYBLOB" size="255"/>
        </column>
        <unique name="PEOPLE_NAME_INX">
            <unique-column name="NAME"/>
        </unique>
        <index name="PEOPLE_CARD_INX">
            <index-column name="CARD"/>
        </index>
        <index name="PEOPLE_FK_1">
            <index-column name="ROLE"/>
        </index>
    </table>
</database>

It seems that the error is in the part : PREFERENCES TINYBLOB(255) NULL

The correct syntax should be without the field size : PREFERENCES TINYBLOB NULL

Note that it is working fine for the IMAGE field which is of type MEDIUMBLOB

Steps To ReproduceAttempt to do an initial load of a table containing tinyblob field, with mysql driver

Activities

hanes

2018-10-17 15:51

developer   ~0001288

Issue still exists in 3.9.14.

CREATE TABLE `people`(
    `ID` VARCHAR(255) NOT NULL,
    `NAME` VARCHAR(255) NOT NULL,
    `APPPASSWORD` VARCHAR(255) NULL,
    `CARD` VARCHAR(255) NULL,
    `ROLE` VARCHAR(255) NOT NULL,
    `VISIBLE` BIT NOT NULL,
    `IMAGE` MEDIUMBLOB NULL,
    `PREFERENCES` TINYBLOB NULL,
    PRIMARY KEY (`ID`)
);

when viewing source of this table you can see the (255) shows up as well.

Issue History

Date Modified Username Field Change
2017-05-15 00:32 marc New Issue
2018-10-17 15:51 hanes Status new => confirmed
2018-10-17 15:51 hanes Note Added: 0001288
2018-10-17 15:51 hanes Target Version => 3.10.0
2018-10-29 12:52 hanes Priority normal => low