Default value for time stamp in generated db schema
---------------------------------------------------
Key: DDLUTILS-71
URL: http://issues.apache.org/jira/browse/DDLUTILS-71
Project: DdlUtils
Type: Bug
Environment: PostgreSQL 8.1, MySQL 5.1
Reporter: Vignesh Swaminathan
Assigned to: Thomas Dudziak
> 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 ?
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
