[Building Sakai] Problem with Samigo and Oracle 11g Release 2

Adams, David da1 at vt.edu
Fri Nov 26 15:41:32 PST 2010 

While this is poor design on Oracle's part and should be fixed by them, I'd say the "bug" is in Samigo for assuming it knows what nextVal is going to return.

-dave
________________________________________
From: sakai-dev-bounces at collab.sakaiproject.org [sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Miguel Carro Pellicer [farreri.sakai at gmail.com]
Sent: Thursday, November 25, 2010 11:28 AM
To: Jaques Smith
Cc: sakai-dev
Subject: Re: [Building Sakai] Problem with Samigo and Oracle 11g Release 2

The bug is located in Oracle 11g and affects Samigo directly when starting a fresh installation.

I Found this reference that clarify the bug:
http://www.stefanocislaghi.eu/2010/06/10/sequence-bug-or-undocumented-feature-of-11gr2/

With this change....everything goes OK now!!!

I will try to explain the problem:

1-Samigo executes this query when auto.ddl=true

INSERT INTO SAM_ASSESSMENTBASE_T ("ID" ,"ISTEMPLATE" ,
    "PARENTID" ,"TITLE" ,"DESCRIPTION" ,"COMMENTS" ,
    "ASSESSMENTTEMPLATEID" ,"TYPEID" ,"INSTRUCTORNOTIFICATION" ,
    "TESTEENOTIFICATION" ,"MULTIPARTALLOWED" ,"STATUS" ,
    "CREATEDBY" ,"CREATEDDATE" ,"LASTMODIFIEDBY" ,
    "LASTMODIFIEDDATE" )
    VALUES (sam_assessmentBase_id_s.nextVal,1 ,0 ,'Default Assessment Type' ,'System Defined Assessment Type' ,'comments' , NULL
    ,'142' ,1 ,1 ,1 ,1 ,'admin' ,SYSDATE ,'admin' ,SYSDATE  );

2-Then....the row appears in DB...first ID value is 2, not 1!!!!!...the next query is:

INSERT INTO SAM_ASSESSEVALUATION_T ("ASSESSMENTID" ,
    "EVALUATIONCOMPONENTS" ,"SCORINGTYPE" ,"NUMERICMODELID" ,
    "FIXEDTOTALSCORE" ,"GRADEAVAILABLE" ,"ISSTUDENTIDPUBLIC" ,
    "ANONYMOUSGRADING" ,"AUTOSCORING" ,"TOGRADEBOOK" )
    VALUES (1 ,'' ,1 ,'' , NULL , NULL , NULL ,1 , NULL ,2  );

3-The result...INTEGRITY ERROR because ID=1 doesn't exists:

2010-11-25 16:58:07,017  WARN Thread-1 org.sakaiproject.db.impl.BasicSqlService - Sql.dbWrite(): error code: 2291 sql:  INSERT INTO SAM_ASSESSEVALUATION_T ("ASSESSMENTID" , "EVALUATIONCOMPONENTS" ,"SCORINGTYPE" ,"NUMERICMODELID" , "FIXEDTOTALSCORE" ,"GRADEAVAILABLE" ,"ISSTUDENTIDPUBLIC" , "ANONYMOUSGRADING" ,"AUTOSCORING" ,"TOGRADEBOOK" ) VALUES (1 ,'' ,1 ,'' , NULL , NULL , NULL ,1 , NULL ,2  ) binds:
java.sql.SQLException: ORA-02291: restricción de integridad (TESTING.FK6A6F29F5694216CC) violada - clave principal no encontrada

4-Tomcat Crashes
5-Next startup tomcat works but Samigo doesn't work good.

El 12/10/2010 8:54, Jaques Smith escribió:
Hi Miguel,

We had a problem where the indexes was created as unique. So check that the FK6A6F29F5694216CC index on sam_assessevaluation_t is NOT UNIQUE.

Cheers,
Jaques

From: Miguel Carro Pellicer [mailto:farreri.sakai at gmail.com]
Sent: 11 October 2010 12:11 PM
To: sakai-dev at collab.sakaiproject.org<mailto:sakai-dev at collab.sakaiproject.org>
Subject: [Building Sakai] Problem with Samigo and Oracle 11g Release 2

Hi subscribers.

Has anyone tested Samigo using Oracle 11g Release 2??

I installed a fresh Sakai configuring this database, when creating tables Samigo throws this error:

2010-10-11 12:05:11,328  WARN Thread-1 org.sakaiproject.db.impl.BasicSqlService - Sql.dbWrite(): error code: 2291 sql:  INSERT INTO SAM_ASSESSEVALUATION_T ("ASSESSMENTID" , "EVALUATIONCOMPONENTS" ,"SCORINGTYPE" ,"NUMERICMODELID" , "FIXEDTOTALSCORE" ,"GRADEAVAILABLE" ,"ISSTUDENTIDPUBLIC" , "ANONYMOUSGRADING" ,"AUTOSCORING" ,"TOGRADEBOOK" ) VALUES (1 ,'' ,1 ,'' , NULL , NULL , NULL ,1 , NULL ,2  ) binds:
java.sql.SQLIntegrityConstraintViolationException: ORA-02291: restricción de integridad (TESTING.FK6A6F29F5694216CC) violada - clave principal no encontrada

This ORA responds to a constraint violation, the same tomcat worked 100% in Oracle 10g and MySQL.

Thanks in advance, Miguel.

--
[cid:part1.07020008.00090006 at gmail.com]<http://www.samoo.es/>
 <http://www.samoo.es/>
Miguel Carro Pellicer
Technical Manager - Director técnico
miguel.carro at samoo.es<mailto:miguel.carro at samoo.es>
+34 - 96 393 74 33 ext. 32
www.samoo.es<http://www.samoo.es>

Este  correo  y  sus archivos asociados son privados y confidenciales y va dirigido  exclusivamente  a su destinatario. Si recibe este correo sin ser el  destinatario del mismo, le rogamos proceda a su eliminación y lo ponga en  conocimiento del emisor. La difusión por cualquier medio del contenido de  este  correo podría ser sancionada conforme a lo previsto en las leyes españolas, Ley Orgánica 15/1999 de Protección de Datos de Carácter Personal.  No  se autoriza la utilización con fines comerciales o para su incorporación a ficheros automatizados de las direcciones del emisor o del destinatario.”

This mail and its attached files are confidential and are only and exclusively intended to their addressee. In case you may receive this mail not being its addressee, we beg you to let us know the error by reply and to proceed to destroy it. The circulation by any mean of this mail could be penalised in accordance with the Spanish legislation. Is not allowed the use of both, the transmitter and the addressee’s, address with a commercial aim, or in order to be incorporated to automated data process or to any kind of files.

P Antes de imprimir este correo electrónico piense bien si es necesario hacerlo. El medioambiente es cosa de todos.



--
[cid:part2.07020308.05000006 at gmail.com]<http://www.samoo.es/>
 <http://www.samoo.es/>
Miguel Carro Pellicer
Technical Manager - Director técnico
miguel.carro at samoo.es<mailto:miguel.carro at samoo.es>
+34 - 96 393 69 15
www.samoo.es<http://www.samoo.es>

Este  correo  y  sus archivos asociados son privados y confidenciales y va dirigido  exclusivamente  a su destinatario. Si recibe este correo sin ser el  destinatario del mismo, le rogamos proceda a su eliminación y lo ponga en  conocimiento del emisor. La difusión por cualquier medio del contenido de  este  correo podría ser sancionada conforme a lo previsto en las leyes españolas, Ley Orgánica 15/1999 de Protección de Datos de Carácter Personal.  No  se autoriza la utilización con fines comerciales o para su incorporación a ficheros automatizados de las direcciones del emisor o del destinatario.”

This mail and its attached files are confidential and are only and exclusively intended to their addressee. In case you may receive this mail not being its addressee, we beg you to let us know the error by reply and to proceed to destroy it. The circulation by any mean of this mail could be penalised in accordance with the Spanish legislation. Is not allowed the use of both, the transmitter and the addressee’s, address with a commercial aim, or in order to be incorporated to automated data process or to any kind of files.

P Antes de imprimir este correo electrónico piense bien si es necesario hacerlo. El medioambiente es cosa de todos.

More information about the sakai-dev mailing list