db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vignesh Swaminathan (JIRA)" <j...@apache.org>
Subject [jira] Created: (DDLUTILS-71) Default value for time stamp in generated db schema
Date Wed, 15 Feb 2006 12:27:08 GMT
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"
> (
>     "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:


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

> 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'). "

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:
For more information on JIRA, see:

View raw message