[DDLUTILS-71] Default value for time stamp in generated db schema

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Component/s: Core - MySql, Core - PostgreSql
• Environment: PostgreSQL 8.1, MySQL 5.1
• Assignee: Thomas Dudziak
• Reporter: Vignesh Swaminathan
• Created: 15/Feb/06 21:22
• Updated: 13/Jul/06 14:28
• Resolved: 18/Feb/06 05:46

Description

> My scenario is to take a db model (initially generated out of a MySQL
> 5.1 db) and use it to create/alter databases in PostgreSQL 8.1 and
> MySQL 5.1. I have attached the db model. During the test there were
> two errors that came up,
>
> First,
> Generated db model contained default value (removed in the attached
> file) for field of type timestamp. This default value is not valid for
> PostgreSQL 8.1 and throws an SQL error. See detail below,
>
> Database XML output using DatabaseIO class creates default value
> attribute as part of the output XML. The default value for TIMESTAMP
> data type is default="0000-00-00 00:00:00". This value is accepted by
> MySQL 5.1 but rejected by PostgreSQL 8.1 with following error
> statement,
>
> CREATE TABLE "cireport"
> (
> "id" INTEGER DEFAULT 0 NOT NULL,
> "startdate" TIMESTAMP DEFAULT '0000-00-00 00:00:00',
> "enddate" TIMESTAMP DEFAULT '0000-00-00 00:00:00',
> "employee" INTEGER DEFAULT 0,
> "lead" INTEGER DEFAULT 0,
> "rating" INTEGER DEFAULT 0,
> "type" VARCHAR(50),
> PRIMARY KEY ("id")
> ) failed with ERROR: date/time field value out of range: "0000-00-00
> 00:00:00" Feb 13, 2006 9:49:59 PM
> org.apache.ddlutils.platform.PlatformImplBase
> evaluateBatch
>
> The SQL fires well when the generated default values are removed from
> the input model file.

From what I could gather from the documentation of PostgreSQL and MySQL this is not a valid value for either of the two databases (in fact, it is not valid in the ISO date specification). The problem is that the values for month and day start at 1, not a 0. E.g. see here:

http://dev.mysql.com/doc/refman/5.0/en/datetime.html
http://www.postgresql.org/docs/8.1/interactive/datatype-datetime.html

The question now is: how is the column defined in the MySql database ? Could you provide the SQL for the table definition ?

Vignesh:
> The link given for MySQL says that
>
> "Illegal DATETIME, DATE, or TIMESTAMP values are converted to the
> "zero" value of the appropriate type ('0000-00-00 00:00:00' or
> '0000-00-00'). "

Tom:
That is unfortunate (because the value is invalid in ISO format). All DdlUtils could do here, is to convert this to a NULL value. Could you create an issue in JIRA for this ?