View Issue Details

IDProjectCategoryView StatusLast Update
0001282SymmetricDSBugpublic2019-05-13 16:14
ReporterabrougherAssigned Tojosh-a-hicks 
Prioritynormal 
Status assignedResolutionopen 
Product Version3.5.0 
Target VersionFixed in Version 
Summary0001282: MySQL outputs schema with DEFAULT '0000-00-00' that is not compatible
DescriptionWhen SymmetricDS is configured to auto create tables on load, tables are not created correctly on Postgres. Dates default to 0. Postgres cannot handle dates with a default value of 0.
Steps To ReproduceThe following is the originating table ddl on MySQL and the resulting Postgres SQL that will not run on Postgres. Dates cannot default to 0 on Postgres.

--Original MySQL DDL
> CREATE TABLE `Jobs2` (
> `JobID` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
> `JobTime` time NOT NULL DEFAULT '00:00:00',
> `JobHours` varchar(40) NOT NULL DEFAULT '',
> `JobDays` varchar(40) NOT NULL DEFAULT '',
> `JobComments` varchar(255) NOT NULL DEFAULT '',
> `SubmitDate` date NOT NULL DEFAULT '0000-00-00',
> `SubmitDueDate` date NOT NULL DEFAULT '0000-00-00',
> `JobStartDate` date NOT NULL DEFAULT '0000-00-00',
> `SectionalPointScores` varchar(255) NOT NULL DEFAULT '',
> `SubSectionalScores` varchar(255) NOT NULL DEFAULT '',
> `TopScore` smallint(4) unsigned DEFAULT NULL,
> `PointScore` smallint(4) DEFAULT NULL,
> `JobPay` decimal(6,2) unsigned NOT NULL DEFAULT '0.00',
> `JobExp` decimal(6,2) unsigned NOT NULL DEFAULT '0.00',
> `JobExpActual` decimal(6,2) unsigned NOT NULL DEFAULT '0.00',
> `SpExp` decimal(6,2) unsigned NOT NULL DEFAULT '0.00',
> `SpExpActual` decimal(6,2) unsigned NOT NULL DEFAULT '0.00',
> `SpExpDesc` varchar(254) NOT NULL DEFAULT '',
> `BonusPay` decimal(6,2) NOT NULL DEFAULT '0.00',
> `FeesPaidOn` date NOT NULL DEFAULT '0000-00-00',
> `JobGrade` tinyint(2) DEFAULT NULL,
> `ReviewedBy` varchar(7) NOT NULL DEFAULT '',
> `ReviewComments` varchar(255) NOT NULL DEFAULT '',
> `DistHist` tinyint(2) NOT NULL DEFAULT '0',
> `BillStatus` tinyint(1) unsigned NOT NULL DEFAULT '0',
> `Downloaded` tinyint(1) unsigned NOT NULL DEFAULT '0',
> `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
> CURRENT_TIMESTAMP, `extJobID` varchar(32) DEFAULT NULL,
> `JobCommentID` int(10) unsigned NOT NULL DEFAULT '0',
> PRIMARY KEY (`JobID`),
> KEY `BillStatus` (`BillStatus`),
> KEY `Downloaded` (`Downloaded`),
> KEY `SubmitDate` (`SubmitDate`)
> ) ENGINE=InnoDB AUTO_INCREMENT=3285 DEFAULT CHARSET=latin1



-- DDL that fails to run on Postgres
CREATE TABLE "Jobs2"
(
    "JobID" INTEGER NOT NULL DEFAULT nextval('"Jobs2_JobID_seq"'),
    "JobTime" TIME DEFAULT '00:00:00' NOT NULL,
    "JobHours" VARCHAR(40) NOT NULL,
    "JobDays" VARCHAR(40) NOT NULL,
    "JobComments" VARCHAR(255) NOT NULL,
    "SubmitDate" DATE DEFAULT '0000-00-00' NOT NULL,
    "SubmitDueDate" DATE DEFAULT '0000-00-00' NOT NULL,
    "JobStartDate" DATE DEFAULT '0000-00-00' NOT NULL,
    "SectionalPointScores" VARCHAR(255) NOT NULL,
    "SubSectionalScores" VARCHAR(255) NOT NULL,
    "TopScore" SMALLINT,
    "PointScore" SMALLINT,
    "JobPay" NUMERIC(6,2) DEFAULT 0.00 NOT NULL,
    "JobExp" NUMERIC(6,2) DEFAULT 0.00 NOT NULL,
    "JobExpActual" NUMERIC(6,2) DEFAULT 0.00 NOT NULL,
    "SpExp" NUMERIC(6,2) DEFAULT 0.00 NOT NULL,
    "SpExpActual" NUMERIC(6,2) DEFAULT 0.00 NOT NULL,
    "SpExpDesc" VARCHAR(254) NOT NULL,
    "BonusPay" NUMERIC(6,2) DEFAULT 0.00 NOT NULL,
    "FeesPaidOn" DATE DEFAULT '0000-00-00' NOT NULL,
    "JobGrade" SMALLINT,
    "ReviewedBy" VARCHAR(7) NOT NULL,
    "ReviewComments" VARCHAR(255) NOT NULL,
    "DistHist" SMALLINT DEFAULT 0 NOT NULL,
    "BillStatus" SMALLINT DEFAULT 0 NOT NULL,
    "Downloaded" SMALLINT DEFAULT 0 NOT NULL,
    "updated" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    "extJobID" VARCHAR(32),
    "JobCommentID" INTEGER DEFAULT 0 NOT NULL,
    PRIMARY KEY ("JobID")
).
Tagsddl/schema

Activities

josh-a-hicks

2014-12-10 09:56

developer   ~0000633

Read platform column for default value of 0 in dates and set column to remove default and ensure its nullable. Allowing null values to be set as the default date.

Issue History

Date Modified Username Field Change
2013-06-14 16:03 abrougher New Issue
2013-06-14 16:05 abrougher Project SymmetricDS Pro => SymmetricDS
2014-03-03 09:49 chenson Assigned To => gwilmer
2014-03-03 09:49 chenson Status new => assigned
2014-04-18 12:54 chenson Assigned To gwilmer => josh-a-hicks
2014-05-13 09:33 chenson Target Version 3.6.0 => 3.7.0
2014-12-10 09:56 josh-a-hicks Note Added: 0000633
2014-12-30 14:25 chenson Target Version 3.7.0 =>
2019-04-23 08:38 elong Tag Attached: ddl/schema
2019-05-13 16:14 elong Summary MySQL to Postgres table auto creation failing. => MySQL outputs schema with DEFAULT '0000-00-00' that is not compatible