View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0001282 | SymmetricDS | Bug | public | 2013-06-14 20:03 | 2022-09-01 16:00 |
| Reporter | abrougher | Assigned To | emiller | ||
| Priority | normal | ||||
| Status | closed | Resolution | fixed | ||
| Product Version | 3.5.0 | ||||
| Target Version | 3.14.1 | Fixed in Version | 3.14.1 | ||
| Summary | 0001282: MySQL outputs schema with DEFAULT '0000-00-00' that is not compatible | ||||
| Description | When 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 Reproduce | The 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") ). | ||||
| Tags | ddl/schema | ||||
|
|
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. |
|
SymmetricDS: 3.14 de963593 2022-08-17 13:08:09 evan-miller-jumpmind Details Diff |
0001282: Fixed '0000-00-00' default value for MySQL dates |
Affected Issues 0001282 |
|
| mod - symmetric-db/src/main/java/org/jumpmind/db/platform/mysql/MySqlDdlBuilder.java | Diff File | ||
| mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/mysql/MySqlDdlReader.java | Diff File | ||
|
SymmetricDS: 3.14 ec312222 2022-09-01 15:47:57 evan-miller-jumpmind Details Diff |
0001282: Made fix only apply to a default value of '0000-00-00' |
Affected Issues 0001282 |
|
| mod - symmetric-db/src/main/java/org/jumpmind/db/platform/mysql/MySqlDdlBuilder.java | Diff File | ||
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2013-06-14 20:03 | abrougher | New Issue | |
| 2013-06-14 20:05 | abrougher | Project | SymmetricDS Pro => SymmetricDS |
| 2014-03-03 14:49 | chenson | Assigned To | => gwilmer |
| 2014-03-03 14:49 | chenson | Status | new => assigned |
| 2014-04-18 16:54 | chenson | Assigned To | gwilmer => josh-a-hicks |
| 2014-05-13 13:33 | chenson | Target Version | 3.6.0 => 3.7.0 |
| 2014-12-10 14:56 | josh-a-hicks | Note Added: 0000633 | |
| 2014-12-30 19:25 | chenson | Target Version | 3.7.0 => |
| 2019-04-23 12:38 | elong | Tag Attached: ddl/schema | |
| 2019-05-13 20:14 | elong | Summary | MySQL to Postgres table auto creation failing. => MySQL outputs schema with DEFAULT '0000-00-00' that is not compatible |
| 2022-08-17 13:08 | emiller | Assigned To | josh-a-hicks => emiller |
| 2022-08-17 13:08 | emiller | Status | assigned => resolved |
| 2022-08-17 13:08 | emiller | Resolution | open => fixed |
| 2022-08-17 13:08 | emiller | Fixed in Version | => 3.14.1 |
| 2022-08-17 14:00 | Changeset attached | => SymmetricDS 3.14 de963593 | |
| 2022-08-30 13:04 | admin | Status | resolved => closed |
| 2022-09-01 16:00 | Changeset attached | => SymmetricDS 3.14 ec312222 |