View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003844SymmetricDSBugpublic2019-01-03 16:472019-06-25 18:06
Reporterlukasz.krawczyk Assigned Topmarzullo  
Prioritynormal 
Status closedResolutionfixed 
Product Version3.9.16 
Target Version3.10.3Fixed in Version3.10.3 
Summary0003844: "Failed to execute: CREATE TABLE" in PostgreSQL 9.6 (BIT data type with default)
DescriptionTrying to replicate single database table from MSSQL Standard 2017 to PostgreSQL 9.6 with table definition creation in PostgreSQL.

MSSQL table definition:

CREATE TABLE [dbo].[vat_rate](
    [id] [int] NOT NULL,
    [name] [varchar](64) NOT NULL,
    [value] [decimal](5, 4) NULL,
    [VAT] [bit] NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [LAST_UPDATE_DATE_TIME] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_vat_rate_id] PRIMARY KEY CLUSTERED
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Selected master engine properties:
auto.reload=true
initial.load.create.first=true

The problem is with "[VAT] [bit] NOT NUL"L column conversion.

Exception:
2019-01-03 17:16:42,464 INFO [zapaonline-001] [DefaultDatabaseWriter] [zapaonline-001-dataloader-3] 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="vat_rate">
        <column name="id" primaryKey="true" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2008" type="int" size="10"/>
        </column>
        <column name="name" required="true" type="VARCHAR" size="64">
            <platform-column name="mssql2008" type="varchar" size="64"/>
        </column>
        <column name="value" type="DECIMAL" size="5,4">
            <platform-column name="mssql2008" type="decimal" size="5" decimalDigits="4"/>
        </column>
        <column name="VAT" required="true" type="BIT" size="1" default="1">
            <platform-column name="mssql2008" type="bit" size="1" default="1"/>
        </column>
        <column name="GUID" required="true" type="CHAR" size="36" default="newid()">
            <platform-column name="mssql2008" type="uniqueidentifier" default="newid()"/>
        </column>
        <column name="LAST_UPDATE_DATE_TIME" required="true" type="TIMESTAMP" size="27">
            <platform-column name="mssql2008" type="datetime2"/>
        </column>
    </table>
</database>
2019-01-03 17:16:42,503 INFO [zapaonline-001] [PostgreSqlDatabasePlatform] [zapaonline-001-dataloader-3] Running alter sql:
CREATE TABLE "vat_rate"(
    "id" INTEGER NOT NULL,
    "name" VARCHAR(64) NOT NULL,
    "value" NUMERIC(5,4),
    "vat" BOOLEAN DEFAULT 1 NOT NULL,
    "guid" CHAR(36) DEFAULT 'newid()' NOT NULL,
    "last_update_date_time" TIMESTAMP NOT NULL,
    PRIMARY KEY ("id")
);

2019-01-03 17:16:42,520 WARN [zapaonline-001] [JdbcSqlTemplate] [zapaonline-001-dataloader-3] BŁĄD: kolumna "vat" jest typu boolean ale domyślne wyrażenie jest typu integer
  Hint: Będziesz musiał przepisać lub rzutować wyrażenie.. Failed to execute: CREATE TABLE "vat_rate"(
    "id" INTEGER NOT NULL,
    "name" VARCHAR(64) NOT NULL,
    "value" NUMERIC(5,4),
    "vat" BOOLEAN DEFAULT 1 NOT NULL,
    "guid" CHAR(36) DEFAULT 'newid()' NOT NULL,
    "last_update_date_time" TIMESTAMP NOT NULL,
    PRIMARY KEY ("id")
)
2019-01-03 17:16:42,532 ERROR [zapaonline-001] [DefaultDatabaseWriter] [zapaonline-001-dataloader-3] 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="vat_rate">
        <column name="id" primaryKey="true" required="true" type="INTEGER" size="10">
            <platform-column name="mssql2008" type="int" size="10"/>
        </column>
        <column name="name" required="true" type="VARCHAR" size="64">
            <platform-column name="mssql2008" type="varchar" size="64"/>
        </column>
        <column name="value" type="DECIMAL" size="5,4">
            <platform-column name="mssql2008" type="decimal" size="5" decimalDigits="4"/>
        </column>
        <column name="VAT" required="true" type="BIT" size="1" default="1">
            <platform-column name="mssql2008" type="bit" size="1" default="1"/>
        </column>
        <column name="GUID" required="true" type="CHAR" size="36" default="newid()">
            <platform-column name="mssql2008" type="uniqueidentifier" default="newid()"/>
        </column>
        <column name="LAST_UPDATE_DATE_TIME" required="true" type="TIMESTAMP" size="27">
            <platform-column name="mssql2008" type="datetime2"/>
        </column>
    </table>
</database> StackTraceKey [SqlException:4238718066]
2019-01-03 17:16:42,658 ERROR [zapaonline-001] [ManageIncomingBatchListener] [zapaonline-001-dataloader-3] Failed to load batch 000-4 StackTraceKey [SqlException:4238718066]
2019-01-03 17:16:42,759 ERROR [zol-000] [AcknowledgeService] [qtp973576304-15] The outgoing batch 001-4 failed: BŁĄD: kolumna "vat" jest typu boolean ale domyślne wyrażenie jest typu integer
  Hint: Będziesz musiał przepisać lub rzutować wyrażenie.

Full log attached.
Tagsddl/schema, dialect: sql-server

Relationships

related to 0003954 closedpmarzullo BIT from SQLServer comes in as Integer, Postgres needs value converted to String (VARCHAR) to handle it correctly (3.9) 

Activities

lukasz.krawczyk

2019-01-03 16:47

reporter  

symmetric.log (193,339 bytes)

elong

2019-01-14 14:05

developer   ~0001376

When replicating a table definition, the SQL-Server "bit" type is converted to a Postgres "boolean", which is correct. The two problems I see here:

1 - It reads the default value from SQL-Server as "1" for the column, but no default value was defined.
2 - If a default value of "1" was actually defined on SQL-Server, then it needs to be translated to "true" when creating on Postgres.

Until this is fixed, the workaround is to set the parameter create.table.without.defaults=true to avoid sending default values.

pmarzullo

2019-06-14 18:29

developer   ~0001528

PostgresDdlBuilder class, printDefaultValue() method:
BOOLEAN logic applies to BIT logic as well, but BIT column coming in from source database has not been changed to BOOLEAN yet.
So apply the logic to both BOOLEAN and BIT column types, that is, quote the default value.

Related Changesets

SymmetricDS: 3.10 9bb56f08

2019-06-14 14:32:30

Philip Marzullo

Details Diff 		0003844: "Failed to execute: CREATE TABLE" in PostgreSQL 9.6 (BIT data
type with default)		 Affected Issues
0003844
mod - symmetric-db/src/main/java/org/jumpmind/db/platform/postgresql/PostgreSqlDdlBuilder.java Diff File

Issue History

Date Modified Username Field Change
2019-01-03 16:47 lukasz.krawczyk New Issue
2019-01-03 16:47 lukasz.krawczyk File Added: symmetric.log
2019-01-14 14:05 elong Status new => acknowledged
2019-01-14 14:05 elong Description Updated View Revisions
2019-01-14 14:05 elong Note Added: 0001376
2019-04-25 18:45 elong Tag Attached: ddl/schema
2019-04-25 18:45 elong Tag Attached: dialect: sql-server
2019-05-18 00:18 elong Relationship added related to 0003954
2019-05-18 00:20 elong Assigned To => pmarzullo
2019-05-18 00:20 elong Status acknowledged => assigned
2019-05-18 00:20 elong Summary "Failed to execute: CREATE TABLE" in PostgreSQL 9.6 => "Failed to execute: CREATE TABLE" in PostgreSQL 9.6 (BIT data type with default)
2019-06-14 18:29 pmarzullo Note Added: 0001528
2019-06-14 18:32 pmarzullo Status assigned => resolved
2019-06-14 18:32 pmarzullo Resolution open => fixed
2019-06-14 18:32 pmarzullo Fixed in Version => 3.10.3
2019-06-14 19:00 Changeset attached => SymmetricDS 3.10 9bb56f08
2019-06-25 18:06 admin Status resolved => closed