You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-dev@db.apache.org by "Vignesh Swaminathan (JIRA)" <ji...@apache.org> on 2006/02/15 13:27:08 UTC
[jira] Created: (DDLUTILS-71) Default value for time stamp in
generated db schema
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
[jira] Updated: (DDLUTILS-71) Default value for time stamp in
generated db schema
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ 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
[jira] Resolved: (DDLUTILS-71) Default value for time stamp in
generated db schema
Posted by "Thomas Dudziak (JIRA)" <ji...@apache.org>.
[ 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
[jira] Closed: (DDLUTILS-71) Default value for time stamp in
generated db schema
Posted by "Vignesh Swaminathan (JIRA)" <ji...@apache.org>.
[ 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