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 MySQL/InnoDB build issues
Date Wed, 08 Jun 2005 16:07:39 GMT
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