View Issue Details

IDProjectCategoryView StatusLast Update
0003522SymmetricDSBugpublic2018-10-29 12:55
Reporterjubi74Assigned To 
Prioritynormal 
Status confirmedResolutionopen 
Product Version3.8.26 
Target Version3.10.0Fixed in Version 
Summary0003522: Table creation Error while initial Load into oracle DB (initial.load.create.first=true) when using Default value SYS_GUID()
DescriptionWhen creating table through SymmetricDS with column
ID RAW(20) Default SYS_GUID() NOT NULL
the Default value is quoted 'SYS_GUID()'.
Need to remove the quotes for valid table creation with oracle database 11g.

Sample Log:
2018-04-18 15:19:00,043 INFO [mpos] [DefaultDatabaseWriter] [mpos-dataloader-2] 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" schema="DB">
    <table name="CASHMANVOUCHER">
        <column name="ID" primaryKey="true" required="true" type="VARBINARY" size="20" default="SYS_GUID()">
            <platform-column name="oracle" type="RAW" size="20" default="SYS_GUID()"/>
        </column>
        <column name="CSHORTTEXT" type="VARCHAR" size="42">
            <platform-column name="oracle" type="VARCHAR2" size="42"/>
        </column>
        <column name="LVOUCHER" type="DECIMAL" size="11">
            <platform-column name="oracle" type="NUMBER" size="11"/>
        </column>
    </table>
</database>
2018-04-18 15:19:00,043 INFO [mpos] [OracleDatabasePlatform] [mpos-dataloader-2] Running alter sql:
CREATE TABLE "DB"."CASHMANVOUCHER"(
    "ID" RAW(20) DEFAULT 'SYS_GUID()' NOT NULL,
    "CSHORTTEXT" VARCHAR2(42),
    "LVOUCHER" NUMBER(11)
);
ALTER TABLE "DB"."CASHMANVOUCHER"
    ADD CONSTRAINT "CASHMANVOUCHER_PK" PRIMARY KEY ("ID");

2018-04-18 15:19:00,058 WARN [mpos] [JdbcSqlTemplate] [mpos-dataloader-2] ORA-01465: invalid hex number
. Failed to execute: CREATE TABLE "DB"."CASHMANVOUCHER"(
    "ID" RAW(20) DEFAULT 'SYS_GUID()' NOT NULL,
    "CSHORTTEXT" VARCHAR2(42),
    "LVOUCHER" NUMBER(11)
)
2018-04-18 15:19:00,058 ERROR [mpos] [DefaultDatabaseWriter] [mpos-dataloader-2] Failed to alter table using the following xml: <?xml version="1.0"?>

Steps To Reproduceinitial load with create first
using any table including column with Default value SYS_GUID()
Additional Informationworks with change in
org.jumpmind.db.platform.AbstractDdlBuilder.java

...
boolean shouldUseQuotes = !isNull && !TypeMap.isNumericType(typeCode) &&
                !(TypeMap.isDateTimeType(typeCode)
                        && (defaultValueStr.toUpperCase().startsWith("TO_DATE(")
                                || defaultValueStr.toUpperCase().startsWith("SYSDATE")
                                || defaultValueStr.toUpperCase().startsWith("SYSTIMESTAMP")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT_TIMESTAMP")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT_TIME")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT_DATE")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT TIMESTAMP")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT TIME")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT DATE")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT_USER")
                                || defaultValueStr.toUpperCase().startsWith("CURRENT USER")
                                || defaultValueStr.toUpperCase().startsWith("USER")
                                || defaultValueStr.toUpperCase().startsWith("SYSTEM_USER")
                                || defaultValueStr.toUpperCase().startsWith("SESSION_USER")
                                || defaultValueStr.toUpperCase().startsWith("DATE '")
                                || defaultValueStr.toUpperCase().startsWith("TIME '")
                                || defaultValueStr.toUpperCase().startsWith("TIMESTAMP '")
                                || defaultValueStr.toUpperCase().startsWith("INTERVAL '")
                                )) &&
                !(defaultValueStr.toUpperCase().startsWith("N'") && defaultValueStr.endsWith("'")) &&
                !(defaultValueStr.toUpperCase().startsWith("SYS_GUID()"));

Activities

hanes

2018-10-17 09:46

developer   ~0001275

Issue still exists in 3.9.14. The issue seems to be that SymmetricDS assumes the default value is a constant. In the case of functions, that's not the case.

Issue History

Date Modified Username Field Change
2018-04-19 11:25 jubi74 New Issue
2018-10-17 09:46 hanes Assigned To => hanes
2018-10-17 09:46 hanes Status new => confirmed
2018-10-17 09:46 hanes Note Added: 0001275
2018-10-17 09:46 hanes Assigned To hanes =>
2018-10-17 14:24 hanes Priority normal => low
2018-10-17 14:24 hanes Target Version => 3.10.0
2018-10-29 12:55 hanes Priority low => normal