You are viewing a plain text version of this content. The canonical link for it is here.
Posted to jetspeed-dev@portals.apache.org by Randy Watler <rw...@finali.com> on 2004/12/17 01:39:57 UTC
M1 Security Schema MSSQL Constraints
David/Ate/Scott,
While executing the M1 scripts for DB configuration of MS SQL Server 2K,
I ended up with these errors:
db.execute:
[echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
[sql] Executing file:
/usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-schema.sql
[sql] Failed to execute: BEGIN ALTER TABLE
SSO_PRINCIPAL_TO_REMOTE ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_2
FOREIGN KEY (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL
(PRINCIPAL_ID) ON DELETE CASCADE END
BUILD FAILED
/usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The following
error occurred while executing this line:
/usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
java.sql.SQLException: Introducing FOREIGN KEY constraint
'SSO_PRINCIPAL_TO_REMOTE_FK_2' on table 'SSO_PRINCIPAL_TO_REMOTE' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
----------------------------------------------------------------------------------------------------------
db.execute:
[echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
[sql] Executing file:
/usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-schema.sql
[sql] Failed to execute: BEGIN ALTER TABLE SECURITY_USER_ROLE
ADD CONSTRAINT SECURITY_USER_ROLE_FK_2 FOREIGN KEY (USER_ID) REFERENCES
SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE END
BUILD FAILED
/usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The following
error occurred while executing this line:
/usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
java.sql.SQLException: Introducing FOREIGN KEY constraint
'SECURITY_USER_ROLE_FK_2' on table 'SECURITY_USER_ROLE' may cause cycles
or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO
ACTION, or modify other FOREIGN KEY constraints.
----------------------------------------------------------------------------------------------------------
db.execute:
[echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
[sql] Executing file:
/usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-schema.sql
[sql] Failed to execute: BEGIN ALTER TABLE SECURITY_USER_GROUP
ADD CONSTRAINT SECURITY_USER_GROUP_FK_2 FOREIGN KEY (USER_ID) REFERENCES
SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE END
BUILD FAILED
/usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The following
error occurred while executing this line:
/usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
java.sql.SQLException: Introducing FOREIGN KEY constraint
'SECURITY_USER_GROUP_FK_2' on table 'SECURITY_USER_GROUP' may cause
cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
----------------------------------------------------------------------------------------------------------
db.execute:
[echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
[sql] Executing file:
/usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-schema.sql
[sql] Failed to execute: BEGIN ALTER TABLE SECURITY_GROUP_ROLE
ADD CONSTRAINT SECURITY_GROUP_ROLE_FK_2 FOREIGN KEY (ROLE_ID) REFERENCES
SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE END
BUILD FAILED
/usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The following
error occurred while executing this line:
/usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
java.sql.SQLException: Introducing FOREIGN KEY constraint
'SECURITY_GROUP_ROLE_FK_2' on table 'SECURITY_GROUP_ROLE' may cause
cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
I worked around this by patching the constraints setting them to "DELETE
NO ACTION", but I have not attempted to diagnose or fix this issue for
real. Anyone else seen these errors?
Randy
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
Re: M1 Security Schema MSSQL Constraints
Posted by Randy Watler <rw...@finali.com>.
Gang,
Ok, I will look into extending the Torque macros to add support at the
DB level via triggers. I am not sure it is possible, (it will depend on
what cross table access to the schema definition is available from the
velocity templates). BTW, it does not appear that commons-sql supports
MS SQL Server triggers either.
Randy
David Sean Taylor wrote:
>
> On Dec 20, 2004, at 7:35 AM, Scott T. Weaver wrote:
>
>> I am getting the same thing now. We need to role back the SQL logic
>> to the point before the M1 release when this was not an issue.
>>
>
> If we role back the logic, then the cascading deletes will no longer
> work leaving orphaned records requiring us to programmatically
> implement cascading deletes
> Im -1 on that and much prefer the backend database to manage data
> integrity
>
> But i don't care about MSSQL. If you all need to remove the FK for
> MSSQL then thats fine by me.
> We could make it configurable in the build
>
>> Randy Watler wrote:
>>
>>> David/Ate/Scott,
>>>
>>> While executing the M1 scripts for DB configuration of MS SQL
>>> Server 2K, I ended up with these errors:
>>>
>>> db.execute:
>>> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
>>> [sql] Executing file:
>>> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-
>>> schema.sql
>>> [sql] Failed to execute: BEGIN ALTER TABLE
>>> SSO_PRINCIPAL_TO_REMOTE ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_2
>>> FOREIGN KEY (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL
>>> (PRINCIPAL_ID) ON DELETE CASCADE END
>>>
>>> BUILD FAILED
>>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
>>> following error occurred while executing this line:
>>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
>>> java.sql.SQLException: Introducing FOREIGN KEY constraint
>>> 'SSO_PRINCIPAL_TO_REMOTE_FK_2' on table 'SSO_PRINCIPAL_TO_REMOTE'
>>> may cause cycles or multiple cascade paths. Specify ON DELETE NO
>>> ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY
>>> constraints.
>>>
>>> ----------------------------------------------------------------------
>>> ------------------------------------
>>>
>>> db.execute:
>>> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
>>> [sql] Executing file:
>>> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-
>>> schema.sql
>>> [sql] Failed to execute: BEGIN ALTER TABLE
>>> SECURITY_USER_ROLE ADD CONSTRAINT SECURITY_USER_ROLE_FK_2 FOREIGN
>>> KEY (USER_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON
>>> DELETE CASCADE END
>>>
>>> BUILD FAILED
>>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
>>> following error occurred while executing this line:
>>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
>>> java.sql.SQLException: Introducing FOREIGN KEY constraint
>>> 'SECURITY_USER_ROLE_FK_2' on table 'SECURITY_USER_ROLE' may cause
>>> cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
>>> UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>>>
>>> ----------------------------------------------------------------------
>>> ------------------------------------
>>>
>>> db.execute:
>>> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
>>> [sql] Executing file:
>>> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-
>>> schema.sql
>>> [sql] Failed to execute: BEGIN ALTER TABLE
>>> SECURITY_USER_GROUP ADD CONSTRAINT SECURITY_USER_GROUP_FK_2 FOREIGN
>>> KEY (USER_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON
>>> DELETE CASCADE END
>>>
>>> BUILD FAILED
>>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
>>> following error occurred while executing this line:
>>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
>>> java.sql.SQLException: Introducing FOREIGN KEY constraint
>>> 'SECURITY_USER_GROUP_FK_2' on table 'SECURITY_USER_GROUP' may cause
>>> cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
>>> ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>>>
>>> ----------------------------------------------------------------------
>>> ------------------------------------
>>>
>>> db.execute:
>>> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
>>> [sql] Executing file:
>>> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-
>>> schema.sql
>>> [sql] Failed to execute: BEGIN ALTER TABLE
>>> SECURITY_GROUP_ROLE ADD CONSTRAINT SECURITY_GROUP_ROLE_FK_2 FOREIGN
>>> KEY (ROLE_ID) REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON
>>> DELETE CASCADE END
>>>
>>> BUILD FAILED
>>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
>>> following error occurred while executing this line:
>>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
>>> java.sql.SQLException: Introducing FOREIGN KEY constraint
>>> 'SECURITY_GROUP_ROLE_FK_2' on table 'SECURITY_GROUP_ROLE' may cause
>>> cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
>>> ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>>>
>>>
>>> I worked around this by patching the constraints setting them to
>>> "DELETE NO ACTION", but I have not attempted to diagnose or fix
>>> this issue for real. Anyone else seen these errors?
>>>
>>> Randy
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
>>> For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
>>>
>>>
>>>
>>
>>
>> --
>> "Great minds discuss ideas. Average minds discuss events. Small
>> minds discuss people." - Admiral Hyman Rickover
>>
>> *******************************************
>> * Scott T. Weaver *
>> * <we...@apache.org> *
>> * <http://www.einnovation.com> *
>> * -------------------------------------- *
>> * Apache Jetspeed Enterprise Portal *
>> * Apache Pluto Portlet Container *
>> * *
>> * OpenEdit, Website Content Management *
>> * <http://www.openedit.org> *
>> *******************************************
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
>>
>>
>>
>
> --
> David Sean Taylor
> Bluesunrise Software
> david@bluesunrise.com
> [office] +01 707 773-4646
> [mobile] +01 707 529 9194
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
Re: M1 Security Schema MSSQL Constraints
Posted by David Sean Taylor <da...@bluesunrise.com>.
On Dec 20, 2004, at 7:35 AM, Scott T. Weaver wrote:
> I am getting the same thing now. We need to role back the SQL logic
> to the point before the M1 release when this was not an issue.
>
If we role back the logic, then the cascading deletes will no longer
work leaving orphaned records requiring us to programmatically
implement cascading deletes
Im -1 on that and much prefer the backend database to manage data
integrity
But i don't care about MSSQL. If you all need to remove the FK for
MSSQL then thats fine by me.
We could make it configurable in the build
> Randy Watler wrote:
>
>> David/Ate/Scott,
>>
>> While executing the M1 scripts for DB configuration of MS SQL Server
>> 2K, I ended up with these errors:
>>
>> db.execute:
>> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
>> [sql] Executing file:
>> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-
>> schema.sql
>> [sql] Failed to execute: BEGIN ALTER TABLE
>> SSO_PRINCIPAL_TO_REMOTE ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_2
>> FOREIGN KEY (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL
>> (PRINCIPAL_ID) ON DELETE CASCADE END
>>
>> BUILD FAILED
>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
>> following error occurred while executing this line:
>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
>> java.sql.SQLException: Introducing FOREIGN KEY constraint
>> 'SSO_PRINCIPAL_TO_REMOTE_FK_2' on table 'SSO_PRINCIPAL_TO_REMOTE' may
>> cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION
>> or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>>
>> ----------------------------------------------------------------------
>> ------------------------------------
>>
>> db.execute:
>> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
>> [sql] Executing file:
>> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-
>> schema.sql
>> [sql] Failed to execute: BEGIN ALTER TABLE SECURITY_USER_ROLE
>> ADD CONSTRAINT SECURITY_USER_ROLE_FK_2 FOREIGN KEY (USER_ID)
>> REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE END
>>
>> BUILD FAILED
>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
>> following error occurred while executing this line:
>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
>> java.sql.SQLException: Introducing FOREIGN KEY constraint
>> 'SECURITY_USER_ROLE_FK_2' on table 'SECURITY_USER_ROLE' may cause
>> cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
>> UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>>
>> ----------------------------------------------------------------------
>> ------------------------------------
>>
>> db.execute:
>> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
>> [sql] Executing file:
>> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-
>> schema.sql
>> [sql] Failed to execute: BEGIN ALTER TABLE SECURITY_USER_GROUP
>> ADD CONSTRAINT SECURITY_USER_GROUP_FK_2 FOREIGN KEY (USER_ID)
>> REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE END
>>
>> BUILD FAILED
>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
>> following error occurred while executing this line:
>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
>> java.sql.SQLException: Introducing FOREIGN KEY constraint
>> 'SECURITY_USER_GROUP_FK_2' on table 'SECURITY_USER_GROUP' may cause
>> cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
>> ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>>
>> ----------------------------------------------------------------------
>> ------------------------------------
>>
>> db.execute:
>> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
>> [sql] Executing file:
>> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-
>> schema.sql
>> [sql] Failed to execute: BEGIN ALTER TABLE SECURITY_GROUP_ROLE
>> ADD CONSTRAINT SECURITY_GROUP_ROLE_FK_2 FOREIGN KEY (ROLE_ID)
>> REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE END
>>
>> BUILD FAILED
>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
>> following error occurred while executing this line:
>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
>> java.sql.SQLException: Introducing FOREIGN KEY constraint
>> 'SECURITY_GROUP_ROLE_FK_2' on table 'SECURITY_GROUP_ROLE' may cause
>> cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
>> ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>>
>>
>> I worked around this by patching the constraints setting them to
>> "DELETE NO ACTION", but I have not attempted to diagnose or fix this
>> issue for real. Anyone else seen these errors?
>>
>> Randy
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
>> For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
>>
>>
>>
>
>
> --
> "Great minds discuss ideas. Average minds discuss events. Small minds
> discuss people." - Admiral Hyman Rickover
>
> *******************************************
> * Scott T. Weaver *
> * <we...@apache.org> *
> * <http://www.einnovation.com> *
> * -------------------------------------- *
> * Apache Jetspeed Enterprise Portal *
> * Apache Pluto Portlet Container *
> * *
> * OpenEdit, Website Content Management *
> * <http://www.openedit.org> *
> *******************************************
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
>
>
>
--
David Sean Taylor
Bluesunrise Software
david@bluesunrise.com
[office] +01 707 773-4646
[mobile] +01 707 529 9194
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
Re: M1 Security Schema MSSQL Constraints
Posted by "Scott T. Weaver" <sc...@binary-designs.net>.
I am getting the same thing now. We need to role back the SQL logic to
the point before the M1 release when this was not an issue.
Randy Watler wrote:
> David/Ate/Scott,
>
> While executing the M1 scripts for DB configuration of MS SQL Server
> 2K, I ended up with these errors:
>
> db.execute:
> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
> [sql] Executing file:
> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-schema.sql
>
> [sql] Failed to execute: BEGIN ALTER TABLE
> SSO_PRINCIPAL_TO_REMOTE ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_2
> FOREIGN KEY (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL
> (PRINCIPAL_ID) ON DELETE CASCADE END
>
> BUILD FAILED
> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
> following error occurred while executing this line:
> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
> java.sql.SQLException: Introducing FOREIGN KEY constraint
> 'SSO_PRINCIPAL_TO_REMOTE_FK_2' on table 'SSO_PRINCIPAL_TO_REMOTE' may
> cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
> ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>
> ----------------------------------------------------------------------------------------------------------
>
>
> db.execute:
> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
> [sql] Executing file:
> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-schema.sql
>
> [sql] Failed to execute: BEGIN ALTER TABLE SECURITY_USER_ROLE
> ADD CONSTRAINT SECURITY_USER_ROLE_FK_2 FOREIGN KEY (USER_ID)
> REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE END
>
> BUILD FAILED
> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
> following error occurred while executing this line:
> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
> java.sql.SQLException: Introducing FOREIGN KEY constraint
> 'SECURITY_USER_ROLE_FK_2' on table 'SECURITY_USER_ROLE' may cause
> cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
> UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>
> ----------------------------------------------------------------------------------------------------------
>
>
> db.execute:
> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
> [sql] Executing file:
> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-schema.sql
>
> [sql] Failed to execute: BEGIN ALTER TABLE SECURITY_USER_GROUP
> ADD CONSTRAINT SECURITY_USER_GROUP_FK_2 FOREIGN KEY (USER_ID)
> REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE END
>
> BUILD FAILED
> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
> following error occurred while executing this line:
> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
> java.sql.SQLException: Introducing FOREIGN KEY constraint
> 'SECURITY_USER_GROUP_FK_2' on table 'SECURITY_USER_GROUP' may cause
> cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
> ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>
> ----------------------------------------------------------------------------------------------------------
>
>
> db.execute:
> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
> [sql] Executing file:
> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-schema.sql
>
> [sql] Failed to execute: BEGIN ALTER TABLE SECURITY_GROUP_ROLE
> ADD CONSTRAINT SECURITY_GROUP_ROLE_FK_2 FOREIGN KEY (ROLE_ID)
> REFERENCES SECURITY_PRINCIPAL (PRINCIPAL_ID) ON DELETE CASCADE END
>
> BUILD FAILED
> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
> following error occurred while executing this line:
> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
> java.sql.SQLException: Introducing FOREIGN KEY constraint
> 'SECURITY_GROUP_ROLE_FK_2' on table 'SECURITY_GROUP_ROLE' may cause
> cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
> ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>
>
> I worked around this by patching the constraints setting them to
> "DELETE NO ACTION", but I have not attempted to diagnose or fix this
> issue for real. Anyone else seen these errors?
>
> Randy
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
>
>
>
--
"Great minds discuss ideas. Average minds discuss events. Small minds discuss people." - Admiral Hyman Rickover
*******************************************
* Scott T. Weaver *
* <we...@apache.org> *
* <http://www.einnovation.com> *
* -------------------------------------- *
* Apache Jetspeed Enterprise Portal *
* Apache Pluto Portlet Container *
* *
* OpenEdit, Website Content Management *
* <http://www.openedit.org> *
*******************************************
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
Re: M1 Security Schema MSSQL Constraints
Posted by Randy Watler <rw...@finali.com>.
I believe that changes to security-schema.xml on 12/3 just before the M1
release that included adding "on delete cascade" to these foreign keys
is what broke the MSSQL configuration for me. While I believe these
constraints are appropriate schema wise, I am afraid they are hitting a
MS SQL Server 2000 limitation. Fixing this probably involves customizing
Torque... something Ate is pondering replacing... not sure it is worth
the trouble.
If we want to keep the cascading deletes, a Simpler fix might involve
splitting the usage of the SECURITY_PRINCIPAL into its separate usages,
(i.e. USER_PRINCIPAL, ROLE_PRINCIPAL, GROUP_PRINCIPAL, and
REMOTE_PRINCIPAL). This way, the cascading delete will not attempt to
visit the same table twice. Will this change cause problems in the admin
portlets?
Any thoughts on preserving the SECURITY_PRINCIPAL heterogeneous mapping
vs. cascading deletes? Does anyone want to even consider extending Torque?
Randy
Randy Watler wrote:
> Guys,
>
> Apparently, multiple foreign keys to a single related table with an ON
> DELETE CASCADE constraint is not supported in MS SQL Server by design.
> It requires use of yet another trigger to implement. Unfortunately,
> this is not the first time I have been told to use a trigger for basic
> constraints, (such as ON DELETE SET NULL), in MS SQL Server, >:-(.
>
> I am no SQL Server expert, so does anyone else care to attempt this
> before I do?
>
> Randy
>
> rwatler wrote:
>
>> David/Ate/Scott,
>>
>> While executing the M1 scripts for DB configuration of MS SQL Server
>> 2K, I ended up with these errors:
>>
>> db.execute:
>> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
>> [sql] Executing file:
>> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-schema.sql
>>
>> [sql] Failed to execute: BEGIN ALTER TABLE
>> SSO_PRINCIPAL_TO_REMOTE ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_2
>> FOREIGN KEY (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL
>> (PRINCIPAL_ID) ON DELETE CASCADE END
>>
>> BUILD FAILED
>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
>> following error occurred while executing this line:
>> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
>> java.sql.SQLException: Introducing FOREIGN KEY constraint
>> 'SSO_PRINCIPAL_TO_REMOTE_FK_2' on table 'SSO_PRINCIPAL_TO_REMOTE' may
>> cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION
>> or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>>
>> ...
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org
Re: M1 Security Schema MSSQL Constraints
Posted by Randy Watler <rw...@finali.com>.
Guys,
Apparently, multiple foreign keys to a single related table with an ON
DELETE CASCADE constraint is not supported in MS SQL Server by design.
It requires use of yet another trigger to implement. Unfortunately, this
is not the first time I have been told to use a trigger for basic
constraints, (such as ON DELETE SET NULL), in MS SQL Server, >:-(.
I am no SQL Server expert, so does anyone else care to attempt this
before I do?
Randy
rwatler wrote:
> David/Ate/Scott,
>
> While executing the M1 scripts for DB configuration of MS SQL Server
> 2K, I ended up with these errors:
>
> db.execute:
> [echo] Executing scripts for ./sql/DDL/mssql/security-schema.sql
> [sql] Executing file:
> /usr/share/tomcat5/jetspeed-database/scripts/sql/DDL/mssql/security-schema.sql
>
> [sql] Failed to execute: BEGIN ALTER TABLE
> SSO_PRINCIPAL_TO_REMOTE ADD CONSTRAINT SSO_PRINCIPAL_TO_REMOTE_FK_2
> FOREIGN KEY (REMOTE_PRINCIPAL_ID) REFERENCES SECURITY_PRINCIPAL
> (PRINCIPAL_ID) ON DELETE CASCADE END
>
> BUILD FAILED
> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:64: The
> following error occurred while executing this line:
> /usr/share/tomcat5/jetspeed-database/scripts/build.xml:38:
> java.sql.SQLException: Introducing FOREIGN KEY constraint
> 'SSO_PRINCIPAL_TO_REMOTE_FK_2' on table 'SSO_PRINCIPAL_TO_REMOTE' may
> cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or
> ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
>
> ...
---------------------------------------------------------------------
To unsubscribe, e-mail: jetspeed-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: jetspeed-dev-help@jakarta.apache.org