You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ddlutils-user@db.apache.org by Satish Yellanki <sa...@ineoquest.com> on 2008/02/13 00:05:17 UTC
Problem with using DdlUtils with MySql
Hello,
I am trying to use DdlUtil with MySql database to upgrade from one
version to another. I create a model file from the latest schema and try
to alter an existing database to match that file. However, there is
problem with the default values.
Some of the columns have default values defined as "" (empty string),
while some don't have any default values. However, the DdlUtils package
out of the box is treating them the same... the xml output I create does
not have any default values in it.
When I add a new column to the database which is "NOT NULL" and has a
default value of "", the utility is trying to delete the existing table
and recreate it. I understand that this is because the utility assumes
there is no default value and since it cannot add a NOT NULL column
without a default, it is deleting and re-creating the table.
Is there a way I can make MySql handler treat the "" (Empty String) and
null values differently and also, save the "" default values in the
model file? This could potentially solve my problem.
Thanks,
Satish
Re: Problem with using DdlUtils with MySql
Posted by Thomas Dudziak <to...@gmail.com>.
> However, due to the fact that DdlUtils maps the jdbc tinyint type to
> MySql's SMALLINT datatype because MySql's TINYINT is too small (signed
> or unsigned).
Ups, I meant to write:
However, due to the fact that DdlUtils maps the jdbc tinyint type to
MySql's SMALLINT datatype because MySql's TINYINT is too small (signed
or unsigned), DdlUtils will still try to recreate the table.
Tom
Re: Problem with using DdlUtils with MySql
Posted by Thomas Dudziak <to...@gmail.com>.
On Feb 14, 2008 6:39 AM, Satish Yellanki <sa...@ineoquest.com> wrote:
> I am using MySql5.0 and ddlutils 1.0.
>
> Here is the create table statement for the existing table:
> CREATE TABLE mytable (
> column1 int(10) unsigned NOT NULL default '0',
> column2 varchar(45) NOT NULL default '',
> column3 varchar(7) NOT NULL default '',
> column4 tinyint(3) unsigned default NULL,
> column5 varchar(45) default NULL,
> PRIMARY KEY (column1)
> )
>
> The schema that is generated is:
> <table name="mytable">
> <column name="column1" primaryKey="true" required="true"
> type="INTEGER" size="10" default="0" autoIncrement="false"/>
> <column name="column2" primaryKey="false" required="true"
> type="VARCHAR" size="45" autoIncrement="false"/>
> <column name="column3" primaryKey="false" required="true"
> type="VARCHAR" size="7" autoIncrement="false"/>
> <column name="column4" primaryKey="false" required="false"
> type="TINYINT" size="3" autoIncrement="false"/>
> <column name="column5" primaryKey="false" required="false"
> type="VARCHAR" size="45" autoIncrement="false"/>
> </table>
>
> I would like to see the default values also generated for columns 2 and
> 3 (default=""). The problem with the current output is that when I add a
> new column "column6 varchar(10) not null default ''", the schema
> generated does not include the default value. Then, when I try to
> upgrade an existing database to this new model, it tries to delete and
> recreate this table which is not what we want.
> Also, are there any plans to support the "unsigned" types in ddlutils?
There was a bug with writing out the default value to XML that I fixed
as part of http://issues.apache.org/jira/browse/DDLUTILS-193 in trunk.
You can work around this by simply adding
default=""
to the generated XML.
However, due to the fact that DdlUtils maps the jdbc tinyint type to
MySql's SMALLINT datatype because MySql's TINYINT is too small (signed
or unsigned). This will not change until
http://issues.apache.org/jira/browse/DDLUTILS-86 is implemented which
will basically allow you to override the default type mapping in the
XML on a per-platform basis.
cheers,
Tom
RE: Problem with using DdlUtils with MySql
Posted by Satish Yellanki <sa...@ineoquest.com>.
Hi Tom,
I am using MySql5.0 and ddlutils 1.0.
Here is the create table statement for the existing table:
CREATE TABLE mytable (
column1 int(10) unsigned NOT NULL default '0',
column2 varchar(45) NOT NULL default '',
column3 varchar(7) NOT NULL default '',
column4 tinyint(3) unsigned default NULL,
column5 varchar(45) default NULL,
PRIMARY KEY (column1)
)
The schema that is generated is:
<table name="mytable">
<column name="column1" primaryKey="true" required="true"
type="INTEGER" size="10" default="0" autoIncrement="false"/>
<column name="column2" primaryKey="false" required="true"
type="VARCHAR" size="45" autoIncrement="false"/>
<column name="column3" primaryKey="false" required="true"
type="VARCHAR" size="7" autoIncrement="false"/>
<column name="column4" primaryKey="false" required="false"
type="TINYINT" size="3" autoIncrement="false"/>
<column name="column5" primaryKey="false" required="false"
type="VARCHAR" size="45" autoIncrement="false"/>
</table>
I would like to see the default values also generated for columns 2 and
3 (default=""). The problem with the current output is that when I add a
new column "column6 varchar(10) not null default ''", the schema
generated does not include the default value. Then, when I try to
upgrade an existing database to this new model, it tries to delete and
recreate this table which is not what we want.
Also, are there any plans to support the "unsigned" types in ddlutils?
Thanks for your quick response.
- Satish
-----Original Message-----
From: Thomas Dudziak [mailto:tomdzk@gmail.com]
Sent: Wednesday, February 13, 2008 10:19 PM
To: ddlutils-user@db.apache.org
Subject: Re: Problem with using DdlUtils with MySql
Hi Satish,
could you post the definition of the existing table (i.e. the CREATE
TABLE statement), and the part from the schema XML that defines how
this table should look like ? Also, which MySql version are you using
?
Tom
On Feb 12, 2008 3:05 PM, Satish Yellanki <sa...@ineoquest.com>
wrote:
> Hello,
>
> I am trying to use DdlUtil with MySql database to upgrade from one
> version to another. I create a model file from the latest schema and
try
> to alter an existing database to match that file. However, there is
> problem with the default values.
>
> Some of the columns have default values defined as "" (empty string),
> while some don't have any default values. However, the DdlUtils
package
> out of the box is treating them the same... the xml output I create
does
> not have any default values in it.
>
> When I add a new column to the database which is "NOT NULL" and has a
> default value of "", the utility is trying to delete the existing
table
> and recreate it. I understand that this is because the utility assumes
> there is no default value and since it cannot add a NOT NULL column
> without a default, it is deleting and re-creating the table.
>
> Is there a way I can make MySql handler treat the "" (Empty String)
and
> null values differently and also, save the "" default values in the
> model file? This could potentially solve my problem.
>
> Thanks,
>
> Satish
Re: Problem with using DdlUtils with MySql
Posted by Thomas Dudziak <to...@gmail.com>.
Hi Satish,
could you post the definition of the existing table (i.e. the CREATE
TABLE statement), and the part from the schema XML that defines how
this table should look like ? Also, which MySql version are you using
?
Tom
On Feb 12, 2008 3:05 PM, Satish Yellanki <sa...@ineoquest.com> wrote:
> Hello,
>
> I am trying to use DdlUtil with MySql database to upgrade from one
> version to another. I create a model file from the latest schema and try
> to alter an existing database to match that file. However, there is
> problem with the default values.
>
> Some of the columns have default values defined as "" (empty string),
> while some don't have any default values. However, the DdlUtils package
> out of the box is treating them the same... the xml output I create does
> not have any default values in it.
>
> When I add a new column to the database which is "NOT NULL" and has a
> default value of "", the utility is trying to delete the existing table
> and recreate it. I understand that this is because the utility assumes
> there is no default value and since it cannot add a NOT NULL column
> without a default, it is deleting and re-creating the table.
>
> Is there a way I can make MySql handler treat the "" (Empty String) and
> null values differently and also, save the "" default values in the
> model file? This could potentially solve my problem.
>
> Thanks,
>
> Satish