db-ddlutils-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Thomas Dudziak (JIRA)" <j...@apache.org>
Subject [jira] Resolved: (DDLUTILS-150) Trying to change a column makes ddlutils want to drop non-related foreign keys which fails with for example "Can't DROP 'text_docs_FK_meta_id_meta'; check that column/key exists"
Date Mon, 29 Jan 2007 03:25:49 GMT

     [ https://issues.apache.org/jira/browse/DDLUTILS-150?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

Thomas Dudziak resolved DDLUTILS-150.

       Resolution: Fixed
    Fix Version/s: 1.0

As per MySql documentation (http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html),
InnoDB creates an index for the foreign key fields if there is not already one. This index
has the same name as the foreign key, and DdlUtils tries to automatically drop this index
when the foreign key is dropped. However, if such an index already exists (e.g. if the local
foreign key column is the primary key column), then no such index is created and hence the
DROP statement fails.
I've changed this to only drop the index if - during reading in the model from the live db
- such an index for the foreign key was detected.

> Trying to change a column makes ddlutils want to drop non-related foreign keys which
fails with for example "Can't DROP 'text_docs_FK_meta_id_meta'; check that column/key exists"
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>                 Key: DDLUTILS-150
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-150
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core - MySql
>         Environment: MySQL 5.0.20-something, MySQL 5.0.32 on Linux, InnoDB tables.
>            Reporter: Christoffer Hammarström
>         Assigned To: Thomas Dudziak
>             Fix For: 1.0
>         Attachments: DDLUTILS-150.patch
> I'm trying variations on the following code:
>                org.apache.ddlutils.model.Database model = platform.readModelFromDatabase(null);
>                 Table table = model.findTable("text_docs");
>                 Column templateNameColumn = table.findColumn("template_id");
>                 templateNameColumn.setType("VARCHAR");
>                 platform.alterTables(model,false);
> Trying to change any column makes ddlutil try to drop unrelated foreign keys, which in
my case fails with the following stack-trace:
> org.apache.ddlutils.DatabaseOperationException: Error while executing SQL ALTER TABLE
>     DROP INDEX text_docs_FK_meta_id_meta
> 	at org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:331)
> 	at org.apache.ddlutils.platform.PlatformImplBase.alterTables(PlatformImplBase.java:573)
> 	at org.apache.ddlutils.platform.PlatformImplBase.alterTables(PlatformImplBase.java:507)
>         ... snipped my classes ...
> Caused by: java.sql.SQLException: Can't DROP 'text_docs_FK_meta_id_meta'; check that
column/key exists
> 	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2847)
> 	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
> 	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
> 	at com.mysql.jdbc.Connection.execSQL(Connection.java:2370)
> 	at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
> 	at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1289)
> 	at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
> 	at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:225)
> 	at org.apache.ddlutils.platform.PlatformImplBase.evaluateBatch(PlatformImplBase.java:309)
> 	... 33 more
> The ddl for the table looks like this:
>     <table name="text_docs">
>         <column name="meta_id" primaryKey="true" required="true" type="INTEGER" />
>         <column name="template_id" required="true" type="INTEGER" />
>         <column default="1" name="group_id" required="true" type="INTEGER" />
>         <column default="-1" name="default_template_1" required="true" type="INTEGER"
>         <column default="-1" name="default_template_2" required="true" type="INTEGER"
>         <column name="default_template" type="INTEGER" required="false" />
>         <foreign-key foreignTable="meta">
>             <reference foreign="meta_id" local="meta_id" />
>         </foreign-key>
>         <foreign-key foreignTable="templates">
>             <reference foreign="template_id" local="template_id" />
>         </foreign-key>
>         <foreign-key foreignTable="templates">
>             <reference foreign="template_id" local="default_template" />
>         </foreign-key>
>     </table>
> I'm not sure if ddlutils should be dropping the foreign keys, but it should at least
not fail with an error. Could this be because i'm using InnoDB tables?
> I've tried both some MySQL 5.0.20 version, and the latest 5.0.32.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message