[ http://issues.apache.org/jira/browse/DDLUTILS-71?page=all ]
Thomas Dudziak updated DDLUTILS-71:
-----------------------------------
Component: Core - MySql
Core - PostgreSql
> 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
> Components: Core - MySql, Core - PostgreSql
> Environment: PostgreSQL 8.1, MySQL 5.1
> Reporter: Vignesh Swaminathan
> Assignee: 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
