From graffito-dev-return-315-apmail-incubator-graffito-dev-archive=www.apache.org@incubator.apache.org Thu Jun 16 07:56:12 2005 Return-Path: Delivered-To: apmail-incubator-graffito-dev-archive@www.apache.org Received: (qmail 45767 invoked from network); 16 Jun 2005 07:56:12 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 16 Jun 2005 07:56:12 -0000 Received: (qmail 30752 invoked by uid 500); 16 Jun 2005 07:56:11 -0000 Mailing-List: contact graffito-dev-help@incubator.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: graffito-dev@incubator.apache.org Delivered-To: mailing list graffito-dev@incubator.apache.org Received: (qmail 30735 invoked by uid 99); 16 Jun 2005 07:56:10 -0000 X-ASF-Spam-Status: No, hits=0.2 required=10.0 tests=UPPERCASE_25_50 X-Spam-Check-By: apache.org Received: from ajax-1.apache.org (HELO ajax.apache.org) (192.87.106.226) by apache.org (qpsmtpd/0.28) with ESMTP; Thu, 16 Jun 2005 00:56:04 -0700 Received: from ajax.apache.org (ajax.apache.org [127.0.0.1]) by ajax.apache.org (Postfix) with ESMTP id 963DE109 for ; Thu, 16 Jun 2005 09:55:48 +0200 (CEST) Message-ID: <1458821809.1118908548440.JavaMail.jira@ajax.apache.org> Date: Thu, 16 Jun 2005 09:55:48 +0200 (CEST) From: "Martin Maisey (JIRA)" To: graffito-dev@incubator.apache.org Subject: [jira] Created: (GRFT-30) Build problems due to indexing on MySQL < 4.1.2 Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 7bit X-Virus-Checked: Checked X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N 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: 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 --- new--> new--> new-->
--- , 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