generateChangeLog generates incorrect sql using mysql

[Jensomato]

Hi all, I tried to generated changelogs for a existing db using liquibase 1.7 and mysql 5.0.51. generateChangeLog command produced

    <changeSet author="jd (generated)" id="1215778170739-2">
        <createTable tableName="alternate_geoname">
            <column name="alternatename_id" type="INT">
                <constraints nullable="false" primaryKey="true" primaryKeyName="PRIMARY" />
            </column>
            <column name="geoname_id" type="INT">

The attribute "primaryKeyName" in the constraints tag led to the following error in generated sql

Migration Failed: Error executing SQL CREATE TABLE `alternate_geoname` (`alternatename_id` INT NOT NULL, `geoname_id` IN
T NOT NULL, `iso_language` VARCHAR(7), `alternate_name` VARCHAR(200), `is_preferred_name` CHAR(1) DEFAULT 'N', `is_short
_name` CHAR(1) DEFAULT 'N', CONSTRAINT PRIMARY PRIMARY KEY (`alternatename_id`))

I think "PRIMARY" is a keyword in mysql and threrfor can not be used as name of constraint. Regards Jens

[Alexandr Popov]

On Friday 11 July 2008 15:44:38 Jensomato wrote:

> Hi all,
> I tried to generated changelogs for a existing db using liquibase 1.7 and
> mysql 5.0.51.
> generateChangeLog command produced
>
><changeSet author="jd (generated)" id="1215778170739-2">
>        <createTable tableName="alternate_geoname">
>            <column name="alternatename_id" type="INT">
>                <constraints nullable="false" primaryKey="true" primaryKeyName="PRIMARY" />
>            </column>
>            <column name="geoname_id" type="INT">
>
> The attribute "primaryKeyName" in the constraints tag led to the following
> error in generated sql

Value of primary key is leading to error. Are you shure that is result of generateChangeLog ??
Can you show definition of alternate_geoname table extracted from mysql.


> I think "PRIMARY" is a keyword in mysql and threrfor can not be used as
> name of constraint.

You are right it should be escaped if some one is willing to use it in any case

--
Alexandr Popov,
Nexui-SW
Senior Java Developer
E-mail: alexandr.popov(at)nexui.com
Homepage : http://www.nexui.com

-------------------------------------------------------------------------
Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW!
Studies have shown that voting for your favorite open source project,
along with a healthy diet, reduces your potential for chronic lameness
and boredom. Vote Now at http://www.sourceforge.net/community/cca08
_______________________________________________
Liquibase-user mailing list
Liquibase-user@...
https://lists.sourceforge.net/lists/listinfo/liquibase-user

[Voxland, Nathan]

MySQL uses a primary key name of 'PRIMARY' if one is not specified.  Since the genenrated sql doesn't currently escape the PK name, it will be easiest to remove it.  Just do a search and replace in the generated file for primaryKeyName="PRIMARY" to remove it and you should be good.

Nathan

-----Original Message-----
From: liquibase-user-bounces@... [mailto:liquibase-user-bounces@...] On Behalf Of Alexandr Popov
Sent: Friday, July 11, 2008 8:29 AM
To: liquibase-user@...
Subject: Re: [Liquibase-user] generateChangeLog generates incorrect sqlusing mysql

On Friday 11 July 2008 15:44:38 Jensomato wrote:

> Hi all,
> I tried to generated changelogs for a existing db using liquibase 1.7 and
> mysql 5.0.51.
> generateChangeLog command produced
>
><changeSet author="jd (generated)" id="1215778170739-2">
>        <createTable tableName="alternate_geoname">
>            <column name="alternatename_id" type="INT">
>                <constraints nullable="false" primaryKey="true" primaryKeyName="PRIMARY" />
>            </column>
>            <column name="geoname_id" type="INT">
>
> The attribute "primaryKeyName" in the constraints tag led to the following
> error in generated sql

Value of primary key is leading to error. Are you shure that is result of generateChangeLog ??
Can you show definition of alternate_geoname table extracted from mysql.


> I think "PRIMARY" is a keyword in mysql and threrfor can not be used as
> name of constraint.

You are right it should be escaped if some one is willing to use it in any case

--
Alexandr Popov,
Nexui-SW
Senior Java Developer
E-mail: alexandr.popov(at)nexui.com
Homepage : http://www.nexui.com

-------------------------------------------------------------------------
Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW!
Studies have shown that voting for your favorite open source project,
along with a healthy diet, reduces your potential for chronic lameness
and boredom. Vote Now at http://www.sourceforge.net/community/cca08
_______________________________________________
Liquibase-user mailing list
Liquibase-user@...
https://lists.sourceforge.net/lists/listinfo/liquibase-user
-------------------------------------------------------------------------
Sponsored by: SourceForge.net Community Choice Awards: VOTE NOW!
Studies have shown that voting for your favorite open source project,
along with a healthy diet, reduces your potential for chronic lameness
and boredom. Vote Now at http://www.sourceforge.net/community/cca08
_______________________________________________
Liquibase-user mailing list
Liquibase-user@...
https://lists.sourceforge.net/lists/listinfo/liquibase-user