db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Dudziak (JIRA)" <j...@apache.org>
Subject [jira] Resolved: (DDLUTILS-71) Default value for time stamp in generated db schema
Date Fri, 17 Feb 2006 20:48:40 GMT
     [ http://issues.apache.org/jira/browse/DDLUTILS-71?page=all ]
     
Thomas Dudziak resolved DDLUTILS-71:
------------------------------------

    Resolution: Fixed

Since other databases cannot deal with such a value, the MySQL model reader will now replace
such an default value with NULL

> 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
>     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


Mime
View raw message