On 3/5/07, Paul Balomiri <[EMAIL PROTECTED]> 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
