Hi all, There is a problem with the execution of onCreateDefault statements for non-mandatory columns in Oracle. The issue was introduced as part of the fix for this issue:
https://issues.openbravo.com/view.php?id=11886 Due to the issue 11886 (and other issues related to the same problem) we decided to initiate a refactoring of dbsourcemanager to make it independent from DAL, in order to prevent similar issues. This new dbsourcemanager which has been refactored already fixes the original issue that Rafa has now encountered. This new dbsourcemanager hasn't been merged yet to pi, but will be merged in the near future, and will be included as part of Openbravo ERP MP12 release. This issue can introduce some data inconsistencies in Oracle instances. Ismael will send an email to this list next week with tips to fix the potential problems. Regards, Antonio. Rafa Roda @ Openbravo wrote: > Some clues: > > 1. Please notice that you need to add an Attribute Set Value to any > product with an Attribute Set in order to make it fail: update > m_product set m_attributesetinstance_id = '1000000' where name > in ('Boots', 'Body') > 2. This new column is not mandatory: anyway we want to use the > onCreateDefault capability. > 3. In 2.40 and Oracle it does work perfectly. > 4. In pi and PostgreSQL it does work perfectly. > 5. In pi and Oracle the check constraint fails with error of > constraint violation (since the onCreateDefault has not been > applied properly). > > [java] Recreating Primary Keys > [java] Executed 61 SQL command(s) with 0 error(s) > [java] Executing update final script (NOT NULLs and dropping > temporary tables > [java] Executed 132 SQL command(s) with 0 error(s) > [java] Executed 57 SQL command(s) with 0 error(s) > [java] SQL Command failed with: ORA-02293: no se puede validar > (TADATTRI.M_PRODUCT_ATSETVALTYPE_CHK) - restricción de control violada > [java] -- END > [java] ALTER TABLE M_PRODUCT > [java] ADD CONSTRAINT M_PRODUCT_ATSETVALTYPE_CHK CHECK > ((M_ATTRIBUTESETINSTANCE_ID IS NULL) OR (ATTRSETVALUETYPE IS NOT NULL)) > [java] Executed 927 SQL command(s) with 1 error(s) > [java] Enabling Foreign Keys and Triggers > [java] Executing default postscript > [java] Executed 22 SQL command(s) with 0 error(s) > > database.postupdate.ORACLE: > [sql] Executing commands > [sql] 4 of 4 SQL statements executed successfully > > > Having a look at some other onCreateDefault for non-mandatory column, > the onCreateDefault is not executed. Remove FREIGHT_CURRENCY_ID column > of M_INOUT table and execute update.database: > <column name="FREIGHT_CURRENCY_ID" primaryKey="false" required="false" > type="VARCHAR" size="32" autoIncrement="false"> > <default/> > <onCreateDefault><![CDATA[SELECT C_ACCTSCHEMA.C_CURRENCY_ID > FROM C_ACCTSCHEMA, AD_CLIENTINFO, AD_CLIENT WHERE > AD_CLIENTINFO.C_ACCTSCHEMA1_ID = C_ACCTSCHEMA.C_ACCTSCHEMA_ID AND > AD_CLIENT.AD_CLIENT_ID = AD_CLIENTINFO.AD_CLIENT_ID AND > AD_CLIENT.AD_CLIENT_ID = M_INOUT.AD_CLIENT_ID]]></onCreateDefault> > </column> > > > 2010/2/3 Rafa Roda @ Openbravo <rafael.r...@openbravo.com > <mailto:rafael.r...@openbravo.com>> > > Hi Openbravo developers, > > I was just trying to add a new column named "ATTRSETVALUETYPE" to > the "M_PRODUCT" table with an onCreateDefault value and a check > constraint (see patch attached): > > * <column name="ATTRSETVALUETYPE" primaryKey="false" > required="false" type="VARCHAR" size="60" autoIncrement="false"> > <default/> > <onCreateDefault><![CDATA[CASE WHEN > M_ATTRIBUTESETINSTANCE_ID IS NOT NULL THEN 'D' ELSE NULL > END]]></onCreateDefault> > </column> > * <check > > name="M_PRODUCT_ATSETVALTYPE_CHK"><![CDATA[(M_ATTRIBUTESETINSTANCE_ID > IS NULL) OR (ATTRSETVALUETYPE IS NOT NULL)]]></check> > > The column is created but neither the onCreateDefault value nor > the check constraint are applied when I update.database. The check > constraint fails with error of constraint violation (since the > onCreateDefault has not been applied properly). > > Surprisingly, if I start from scratch again (removing > "ATTRSETVALUETYPE" column from the database), remove the check > constraint from the M_PRODUCT.XML table file and run > update.database, the onCreateDefault value is properly applied. I > add then the check constraint to the M_PRODUCT.XML table file and > run update.database again and the check constraint is now properly > applied. > > Can someone explain this behaviour of dbSourceManager? > > Thanks. > > > ------------------------------------------------------------------------ > > ------------------------------------------------------------------------------ > The Planet: dedicated and managed hosting, cloud storage, colocation > Stay online with enterprise data centers and the best network in the business > Choose flexible plans and management services without long-term contracts > Personal 24x7 support from experience hosting pros just a phone call away. > http://p.sf.net/sfu/theplanet-com > ------------------------------------------------------------------------ > > _______________________________________________ > Openbravo-development mailing list > Openbravo-development@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/openbravo-development > ------------------------------------------------------------------------------ The Planet: dedicated and managed hosting, cloud storage, colocation Stay online with enterprise data centers and the best network in the business Choose flexible plans and management services without long-term contracts Personal 24x7 support from experience hosting pros just a phone call away. http://p.sf.net/sfu/theplanet-com _______________________________________________ Openbravo-development mailing list Openbravo-development@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/openbravo-development
