db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Dudziak" <tom...@gmail.com>
Subject Re: Mysql PK Problems on table creation
Date Tue, 06 Mar 2007 17:27:22 GMT
On 3/5/07, Paul Balomiri <paulbalomiri@gmail.com> wrote:

> I encountered a problem using ddlutils with mysql.
> Starting point was an export done from a life table created with this Script:
> ------------------------------------
> CREATE TABLE A (a INTEGER(11) , b1 VARCHAR(255), b2 VARCHAR(255),
> PRIMARY KEY (a) ) ;
>
> -------------------------------------
>
> A flat xml export from the life database yielded this xml output for table A
>
> -----------------------------------------
>     <table name="A">
>       <column name="a" primaryKey="true" required="true"
> type="INTEGER" size="11" default="0" autoIncrement="false"/>
>       <column name="b1" primaryKey="false" required="false"
> type="VARCHAR" size="255" autoIncrement="false"/>
>       <column name="b2" primaryKey="false" required="false"
> type="VARCHAR" size="255" autoIncrement="false"/>
>       <index name="PRIMARY">
>         <index-column name="a"/>
>       </index>
>     </table>
> ----------------------------------------------
>
> The SQL generated by the SqlBuilder class contained an extra line
> CREATE INDEX PRIMARY on A(a); which was rejected by the Mysql Engine
> because PKs cannot be created using CREATE statements since mysql 4.1
> (see http://bugs.mysql.com/bug.php?id=6062 ).
>
> thus the problem is twohand:
>
> 1) When creating a Table the and using PRIMARY KEY(a) within the
> CREATE TABLE statement,
> the external statement CREATE INDEX PRIMARY... is invalid.
>
> 2) If a primary Index is to be added after table creation, one cannot
> use the CREATE INDEX PRIMARY ... statement. One must instead use the
> form ALTER TABLE  A ADD PRIMARY   KEY (a). PRIMARY, on the other hand
> is not a valid INDEX name, thus it is true that all indexes called
> PRIMARY must be PKs. There seems to be a difference in mysql between
> PK and indexes in general.
> Concluding from 2 I think it is safe to assume that all indexes called
> 'PRIMARY' from DdlUtils are in fact Primary Keys. Based on this
> assumption an overloaded version of
> writeExternalIndicesCreateStmt  replaces the SQL output for indexes
> called 'PRIMARY' with the Alter Table form.
>
> Even using this form the index creation is not valid, as the key is
> duplicate. So I also overloaded the createTable statement, to call a
> version of writeExternalIndicesCreateStmt which omits the Indexes
> called 'Index'

What is odd is that AFAICS the code does use an CREATE INDEX PRIMARY
statement at all, it uses a ALTER TABLE ADD CONSTRAINT statement. And
also, all unit tests pass on both MySQL 4.1 and 5.0.
Could you please check whether you run the latest version of the code
? If yes, then please create an issue in JIRA and attach the patch
there.

thanks,
Tom

Mime
View raw message