View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003107SymmetricDSBugpublic2017-05-15 04:322019-03-14 12:48
Reportermarc Assigned Topmarzullo  
Prioritylow 
Status closedResolutionfixed 
Product Version3.8.24 
Target Version3.10.0Fixed in Version3.10.0 
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
TagsNo tags attached.

Activities

hanes

2018-10-17 19: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.

pmarzullo

2019-01-21 20:30

developer   ~0001387

Last edited: 2019-01-21 20:30

View 2 revisions

 MySql to MySql will create column type TINYBLOB as TINYBLOB now.

Related Changesets

SymmetricDS: 3.10 4d734dbd

2019-01-21 15:18:56

philipmarzullo

Details Diff 		0003107: Create table statement syntax error for tinyblob field Affected Issues
0003107
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/mysql/MySqlDdlBuilder.java Diff File

Issue History

Date Modified Username Field Change
2017-05-15 04:32 marc New Issue
2018-10-17 19:51 hanes Status new => confirmed
2018-10-17 19:51 hanes Note Added: 0001288
2018-10-17 19:51 hanes Target Version => 3.10.0
2018-10-29 16:52 hanes Priority normal => low
2019-01-21 20:29 pmarzullo Assigned To => pmarzullo
2019-01-21 20:29 pmarzullo Status confirmed => assigned
2019-01-21 20:30 pmarzullo Status assigned => resolved
2019-01-21 20:30 pmarzullo Resolution open => fixed
2019-01-21 20:30 pmarzullo Fixed in Version => 3.10.0
2019-01-21 20:30 pmarzullo Note Added: 0001387
2019-01-21 20:30 pmarzullo Note Edited: 0001387 View Revisions
2019-01-21 21:00 Changeset attached => SymmetricDS 3.10 4d734dbd
2019-03-14 12:48 admin Status resolved => closed