Mysql PK Problems on table creation

[Paul Balomiri <pa...@gmail.com>]

Hi,

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'

The attached diff to MySqlBuilder.java (rev. 514702) attempts to
solve these problems.


-- 
paulbalomiri@gmail.com

Re: Mysql PK Problems on table creation

[Thomas Dudziak <to...@gmail.com>]

On 3/5/07, Paul Balomiri <pa...@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