[jira] Created: (GRFT-30) Build problems due to indexing on MySQL < 4.1.2

["Martin Maisey (JIRA)" <ji...@apache.org>]

Build problems due to indexing on MySQL < 4.1.2
-----------------------------------------------

         Key: GRFT-30
         URL: http://issues.apache.org/jira/browse/GRFT-30
     Project: Graffito
        Type: Bug
  Components: OJB Store  
 Environment: MySQL 4.0.24
Windows XP Pro SP2
JDK 1.4.2
    Reporter: Martin Maisey


Building against MySQL 4.0.24 results in the following error:

---
db.create:
db.execute:
   [sql] Executing file: C:\cygwin\home\martin.maisey\graffito\components\targe
t\src\sql\mysql\security-schema.sql
   [sql] [ERROR] Failed to execute: CREATE TABLE PRINCIPAL_PERMISSION ( PRINCIP
AL_ID INTEGER NOT NULL, PERMISSION_ID INTEGER NOT NULL, PRIMARY KEY(PRINCIPAL_ID
,PERMISSION_ID), FOREIGN KEY (PERMISSION_ID) REFERENCES SECURITY_PERMISSION (PER
MISSION_ID) , FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPA
L_ID) )

BUILD FAILED
File...... C:\cygwin\home\martin.maisey\graffito\maven.xml
Element... maven:reactor
Line...... 77
Column.... 40
Unable to obtain goal [test:test] -- C:\cygwin\home\martin.maisey\graffito\compo
nents\maven.xml:97:33: <sql> java.sql.SQLException: Can't create table '.\graffi
to\principal_permission.frm' (errno: 150)
---

The reason for this is that the Torque-generated SQL in components/target/src/sql/mysql/security-schema.sql isn't generating enough indexes for the foreign keys to work - it generates (clean version of the above):

---
DROP TABLE PRINCIPAL_PERMISSION;
CREATE TABLE PRINCIPAL_PERMISSION (
   PRINCIPAL_ID INTEGER NOT NULL,
   PERMISSION_ID INTEGER NOT NULL,
   PRIMARY KEY(PRINCIPAL_ID,PERMISSION_ID),
   FOREIGN KEY (PERMISSION_ID) REFERENCES SECURITY_PERMISSION (PERMISSION_ID) ,
   FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) );
---

but the PERMISSION_ID foreign key declaration fails as there isn't an index with PRINCIPAL_ID as the first component, which MySQL requires until v4.1.2 (see http://bugs.mysql.com/bug.php?id=11188). The following SQL works:

It's possible to get around this by changing components/src/schema/security-schema.xml's definition of PRINCIPAL_PERMISSION to

---
   <table name="PRINCIPAL_PERMISSION">
       <column name="PRINCIPAL_ID" primaryKey="true" required="true" type="INTEGER"/>
       <column name="PERMISSION_ID" primaryKey="true" required="true" type="INTEGER"/>
new-->        <index name="PERMISSION_ID_IDX">
new-->               <index-column name="PERMISSION_ID"/>
new-->        </index>
       <foreign-key foreignTable="SECURITY_PERMISSION">
           <reference foreign="PERMISSION_ID" local="PERMISSION_ID"/>
       </foreign-key>
       <foreign-key foreignTable="SECURITY_PRINCIPAL">
           <reference foreign="PRINCIPAL_ID" local="PRINCIPAL_ID"/>
       </foreign-key>
   </table>
---

, although a similar issue then occurs with the SECURITY_CREDENTIAL table.

Although MySQL now automatically creates the required indexes, other databases (e.g. Oracle, MySQL) may not and will perform poorly, and it would be good to support older versions of MySQL. The schema should be corrected to ensure appropriate indexes are defined for foreign key constraints.

I will try to post a patch for this shortly.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira