[ http://issues.apache.org/jira/browse/DDLUTILS-71?page=all ] Vignesh Swaminathan closed DDLUTILS-71: ---------------------------------------
> 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
