incubator-graffito-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Martin Maisey <graff...@maisey.co.uk>
Subject Re: MySQL/InnoDB build issues
Date Wed, 08 Jun 2005 19:13:34 GMT
A quick follow-up: BDB tables do work and I now have a clean build. This 
appears to be because they are transactional, so pass the unit test, but 
ignore the foreign key constraints allowing the tables to be created in 
the first place. It seems that BDB is the only MySQL table type that 
will work at the moment - or are people building successfully using 
other configurations?

As to the root cause of the issue, it's generally a good idea to have 
indexes in place for columns with foreign key constraints to stop table 
scanning during joins, unless I missed something about the usage 
characteristics of this schema that mean's its not required. If I 
produce a patch to do this and allow InnoDB to be used, is a committer 
prepared to pick up the patch?

In the longer, this might be something that's worth discussing with the 
Torque developers - given that it's invalid to create a MySQL constraint 
without the index in place, there's possibly an argument for having 
Torque implicitly create the index. This would be MySQL specific - other 
RDBMS do allow FK constraints to be created without an index, and there 
are a few edge cases where this might be valid. What are people's thoughts?

Cheers,

Martin

Martin Maisey wrote:

> Hi all,
>
> I'm trying to get up and running with Graffito, and having a few build 
> issues I was hoping someone could help me with...
>
> To save some other people some time, some history:
>
> Firstly, I tried HSQLDB and hit the same problems as Herman did - 
> unfortunately, I only just saw his posting last Saturday and the reply.
>
> I then tried MySQL (v4.0.24). I hit an issue with the 
> TestSpringEngine.testGraffitoEngine() unit test failing at the assert 
> on line 126. Had a quick look at the code - appeared to be that the 
> rollback wasn't working. Checked the MySQL table type and it was 
> MyISAM, so non-transactional - I assume this test needs transactional 
> capability on the database. Is this correct?
>
> Trying again with InnoDB as the default table type, I get a failure 
> earlier in the build during the database creation stage:
>
> ---
> 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)
> ---
>
> It looks to me like the problem 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. 
> The following SQL works:
>
> ---
> DROP TABLE PRINCIPAL_PERMISSION;
> CREATE TABLE PRINCIPAL_PERMISSION (
>    PRINCIPAL_ID INTEGER NOT NULL,
>    PERMISSION_ID INTEGER NOT NULL,
>    INDEX (PERMISSION_ID),
>    PRIMARY KEY(PRINCIPAL_ID,PERMISSION_ID),
>    FOREIGN KEY (PERMISSION_ID) REFERENCES SECURITY_PERMISSION 
> (PERMISSION_ID) ,
>    FOREIGN KEY (PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL 
> (PRINCIPAL_ID) );
> ---
>
> 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>
> ---
>
> But then I run straight into a similar problem with SECURITY_CREDENTIAL.
>
> So my question is - the response to Herman's posting suggested MySQL, 
> but what version/configuration of MySQL is a good one for Graffito?
>
> Cheers,
>
> Martin



Mime
View raw message