Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0027378Openbravo ERPA. Platformpublic2014-08-19 20:332015-01-27 09:59
Reporterjecharri 
Assigned ToAugustoMauch 
PriorityhighSeverityminorReproducibilityalways
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Versionpi 
Target Version3.0PR15Q1Fixed in Version3.0PR15Q1 
Merge Request Status
Review Assigned Toalostale
OBNetwork customer
Web browserGoogle Chrome
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0027378: Openbravo detects that there are database changes when it should not due to session configuration in ORACLE
DescriptionOpenbravo detects that there are database changes when it should not due to session configuration in ORACLE

Steps To Reproduce-execute function
select ad_db_modified('N') from dual; in database --> you will receive N

-login as system in the ERP and execute the same function --> you will receive 'Y'

It seems that problem is the database configuration and line
bbdd.sessionConfig=alter session set NLS_DATE_FORMAT='DD-MM-YYYY' NLS_SORT='BINARY' NLS_NUMERIC_CHARACTERS='.,' <- client configuration

in file Openbravo.properties.


If you execute statement
alter session set NLS_DATE_FORMAT='DD-MM-YYYY' NLS_SORT='BINARY' NLS_NUMERIC_CHARACTERS='.,'
in database, you will receive also a 'Y' value when you execute function "ad_db_modified".

Proposed SolutionDuring update.database and before calling function "ad_db_modified":
- create a new session
- execute "alter session....." statement with correct parameters
- close de session
Additional Information
TagsNo tags attached.
Relationships
related to defect 0027379 closed shankarb Module management window should show more information in openbravo.log file at minimum 
Attached Files? out.ogv (3,989,627) 2014-09-03 11:02
https://issues.openbravo.com/file_download.php?file_id=7196&type=bug
Issue History
Date ModifiedUsernameFieldChange
2014-08-19 20:33jecharriNew Issue
2014-08-19 20:33jecharriAssigned To => AugustoMauch
2014-08-19 20:33jecharriWeb browser => Google Chrome
2014-08-19 20:33jecharriModules => Core
2014-08-19 20:33jecharriResolution time => 1414796400
2014-08-19 20:33jecharriTriggers an Emergency Pack => No
2014-08-19 20:37jecharriIssue cloned0027379
2014-08-19 20:37jecharriRelationship addedrelated to 0027379
2014-08-19 20:40shuehnerIssue Monitored: shuehner
2014-08-19 20:41jecharriWeb browserGoogle Chrome => Google Chrome
2014-08-19 20:41jecharriSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=6366#r6366
2014-08-19 20:42jecharriWeb browserGoogle Chrome => Google Chrome
2014-08-19 20:42jecharriSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=6367#r6367
2014-09-03 10:59shankarbNote Added: 0069937
2014-09-03 11:02shankarbFile Added: out.ogv
2014-09-09 11:29AugustoMauchNote Added: 0070045
2014-09-22 16:36jonalegriaesarteTarget Version => 3.0PR15Q1
2014-10-22 08:54alostaleAssigned ToAugustoMauch => inigosanchez
2014-10-30 13:16AugustoMauchAssigned Toinigosanchez => AugustoMauch
2014-10-31 14:43AugustoMauchIssue Monitored: alostale
2014-10-31 14:43AugustoMauchReview Assigned To => alostale
2014-10-31 14:43AugustoMauchWeb browserGoogle Chrome => Google Chrome
2014-10-31 14:44hgbotCheckin
2014-10-31 14:44hgbotNote Added: 0071311
2014-10-31 14:44hgbotStatusnew => resolved
2014-10-31 14:44hgbotResolutionopen => fixed
2014-10-31 14:44hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/32ef96c592a4573f46d5b9549de349733ccf4e6d [^]
2014-10-31 14:46hgbotCheckin
2014-10-31 14:46hgbotNote Added: 0071312
2014-10-31 14:46hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/32ef96c592a4573f46d5b9549de349733ccf4e6d [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/32ac51156b100be46b20a76f63953eb2800684e3 [^]
2014-10-31 14:58AugustoMauchNote Added: 0071314
2014-10-31 15:02AugustoMauchNote Added: 0071315
2014-11-03 13:29AugustoMauchNote Added: 0071352
2014-11-03 13:29AugustoMauchStatusresolved => new
2014-11-03 13:29AugustoMauchResolutionfixed => open
2014-11-03 16:44hgbotCheckin
2014-11-03 16:44hgbotNote Added: 0071372
2014-11-03 16:44hgbotStatusnew => resolved
2014-11-03 16:44hgbotResolutionopen => fixed
2014-11-03 16:44hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/32ac51156b100be46b20a76f63953eb2800684e3 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/b55aa7117d468268b7a3b3b46a4e3b0eaaa086e6 [^]
2014-11-03 16:45hgbotCheckin
2014-11-03 16:45hgbotNote Added: 0071373
2014-11-03 16:45hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/b55aa7117d468268b7a3b3b46a4e3b0eaaa086e6 [^] => http://code.openbravo.com/erp/devel/pi/rev/a274c96e27ad7289c81e5a59508f7ec98224dd42 [^]
2014-11-07 11:23AugustoMauchNote Added: 0071490
2014-11-07 11:23AugustoMauchStatusresolved => new
2014-11-07 11:23AugustoMauchResolutionfixed => open
2014-11-07 11:40hgbotCheckin
2014-11-07 11:40hgbotNote Added: 0071492
2014-11-07 11:40hgbotStatusnew => resolved
2014-11-07 11:40hgbotResolutionopen => fixed
2014-11-07 11:40hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/a274c96e27ad7289c81e5a59508f7ec98224dd42 [^] => http://code.openbravo.com/erp/devel/pi/rev/8c34cbb6b4adab030bd7e8c9cf7da00646b54ab2 [^]
2014-11-10 08:57alostaleNote Added: 0071511
2014-11-10 08:57alostaleStatusresolved => closed
2014-11-10 08:57alostaleFixed in Version => 3.0PR15Q1
2014-12-30 23:23hudsonbotCheckin
2014-12-30 23:23hudsonbotNote Added: 0073008
2014-12-30 23:24hudsonbotCheckin
2014-12-30 23:24hudsonbotNote Added: 0073012
2014-12-30 23:24hudsonbotCheckin
2014-12-30 23:24hudsonbotNote Added: 0073028
2015-01-26 16:26hgbotCheckin
2015-01-26 16:26hgbotNote Added: 0073783
2015-01-26 16:26hgbotStatusclosed => resolved
2015-01-26 16:26hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/pi/rev/8c34cbb6b4adab030bd7e8c9cf7da00646b54ab2 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/cd8048353155e9f34751f8783e0a4784e99018fb [^]
2015-01-27 09:59AugustoMauchNote Added: 0073802
2015-01-27 09:59AugustoMauchStatusresolved => closed

Notes
(0069937)
shankarb   
2014-09-03 10:59   
Issue not reproducible in Oracle version 11.2.0.2.0 using Ubuntu. Attached a video of the same.
(0070045)
AugustoMauch   
2014-09-09 11:29   
It is not reproducible using the default bbdd.sessionConfig value (ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YYYY' NLS_NUMERIC_CHARACTERS='.,')
(0071311)
hgbot   
2014-10-31 14:44   
Repository: erp/devel/pi
Changeset: 32ef96c592a4573f46d5b9549de349733ccf4e6d
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Fri Oct 31 14:42:52 2014 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/32ef96c592a4573f46d5b9549de349733ccf4e6d [^]

Fixes issue 27378:sessionConfig query is executed before calling ad_db_modified

The problem was the following:
- Before dbsourcemanager executed ad_db_modified to calculate the db_checksum, it did not exec, the sessionConfig query defined in Openbravo.properties was not executed
- When ad_db_modified is executed from the Module Management Console flow, the sessionConfig query has already been executed

Executing the sessionConfig query can modify the db_checksum generated. For instance, adding NLS_SORT='BINARY' to the sessionConfig query will modify the sorting of tables/columns/etc, and the db_checksum
will be modified.

To avoid this, the sessionConfig query should either be always or never executed before calling ad_db_modified. It is not easy to avoid executing it in the module management console flow, because it is aut
omatically executed when the connection is retrieved. It has been decided then to execute it always. Now the query is executed before ad_db_modified is executed in the DBSMOUtil.hasBeenmodified method, in
the DBSMOUtil.updateCRC method and in the postgresql and oracle database.postcreate scripts.

---
M src-db/database/build-create.xml
M src-db/database/lib/dbsourcemanager.jar
---
(0071312)
hgbot   
2014-10-31 14:46   
Repository: erp/devel/dbsm-main
Changeset: 32ac51156b100be46b20a76f63953eb2800684e3
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Fri Oct 31 14:40:00 2014 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/32ac51156b100be46b20a76f63953eb2800684e3 [^]

Fixes issue 27378:sessionConfig query is executed before calling ad_db_modified

The problem was the following:
- Before dbsourcemanager executed ad_db_modified to calculate the db_checksum, it did not exec, the sessionConfig query defined in Openbravo.properties was not executed
- When ad_db_modified is executed from the Module Management Console flow, the sessionConfig query has already been executed

Executing the sessionConfig query can modify the db_checksum generated. For instance, adding NLS_SORT='BINARY' to the sessionConfig query will modify the sorting of tables/columns/etc, and the db_checksum will be modified.

To avoid this, the sessionConfig query should either be always or never executed before calling ad_db_modified. It is not easy to avoid executing it in the module management console flow, because it is automatically executed when the connection is retrieved. It has been decided then to execute it always. Now the query is executed before ad_db_modified is executed in the DBSMOUtil.hasBeenmodified method, in the DBSMOUtil.updateCRC method and in the postgresql and oracle database.postcreate scripts.

---
M src/org/openbravo/ddlutils/util/DBSMOBUtil.java
---
(0071314)
AugustoMauch   
2014-10-31 14:58   
Tested in postgresql and oracle
(0071315)
AugustoMauch   
2014-10-31 15:02   
Test plan:
1- Set this value in the bbdd.sessionConfig parameter: alter session set NLS_DATE_FORMAT='DD-MM-YYYY' NLS_SORT='BINARY' NLS_NUMERIC_CHARACTERS='.,'
2- Execute ant install.source
3- When it finishes, execute this on the database:
    * alter session set NLS_DATE_FORMAT='DD-MM-YYYY' NLS_SORT='BINARY' NLS_NUMERIC_CHARACTERS='.,'
    * select ad_db_modified('N') from dual;
(Those commands are executed in that order when adding a module using the Module Management Console) The response should be 'N'
4- Execute update.database. Repeat step 3, the result should be the same.
5- Try installing a module using the module management console (i.e. the Hello World module). Database local changes should not be detected.
(0071352)
AugustoMauch   
2014-11-03 13:29   
Reopened, because it is causing an exception when running export.database:

[exportdatabase] Error while executing the sessionConfig query
[exportdatabase] java.io.FileNotFoundException: /home/openbravo/workspaces/src/pi-bugfixing/../../config/Openbravo.properties (No such file or directory)
[exportdatabase] at java.io.FileInputStream.open(Native Method)
[exportdatabase] at java.io.FileInputStream.<init>(FileInputStream.java:140)
[exportdatabase] at java.io.FileInputStream.<init>(FileInputStream.java:96)
[exportdatabase] at org.openbravo.ddlutils.util.DBSMOBUtil.executeSessionConfigQuery(DBSMOBUtil.java:1168)
[exportdatabase] at org.openbravo.ddlutils.util.DBSMOBUtil.updateCRC(DBSMOBUtil.java:588)
[exportdatabase] at org.openbravo.ddlutils.task.ExportDatabase.execute(ExportDatabase.java:272)
[exportdatabase] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:292)
[exportdatabase] at sun.reflect.GeneratedMethodAccessor4.invoke(Unknown Source)
...
(0071372)
hgbot   
2014-11-03 16:44   
Repository: erp/devel/dbsm-main
Changeset: b55aa7117d468268b7a3b3b46a4e3b0eaaa086e6
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Mon Nov 03 16:42:26 2014 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/b55aa7117d468268b7a3b3b46a4e3b0eaaa086e6 [^]

Fixes issue 27378: Obtains properly path to Openbravo.properties

The Openbravo.properties file was not being properly obtained when executing the export.database task. When the update.database task was run the workingDir was openbravoRoot/src-db/database, but when export.database is run the workingDir is just openbravoRoot.

Now the executeSessionConfigQuery method checks for both paths in order to retrieve the Openbravo.properties file. This problem could have been solved by passing the sessionConfig paramater to the update.database and export.database tasks, but it has been preferred not to modify the build.xml file, as that would disallow using that file with a previous version of dbsourcemanager.

---
M src/org/openbravo/ddlutils/util/DBSMOBUtil.java
---
(0071373)
hgbot   
2014-11-03 16:45   
Repository: erp/devel/pi
Changeset: a274c96e27ad7289c81e5a59508f7ec98224dd42
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Mon Nov 03 16:44:16 2014 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/a274c96e27ad7289c81e5a59508f7ec98224dd42 [^]

Fixes issue 27378: Obtains properly path to Openbravo.properties

The Openbravo.properties file was not being properly obtained when executing the export.database task. When the update.database task was run the workingDir was openbravoRoot/src-db/database, but when expor
t.database is run the workingDir is just openbravoRoot.

Now the executeSessionConfigQuery method checks for both paths in order to retrieve the Openbravo.properties file. This problem could have been solved by passing the sessionConfig paramater to the update.d
atabase and export.database tasks, but it has been preferred not to modify the build.xml file, as that would disallow using that file with a previous version of dbsourcemanager.

---
M src-db/database/lib/dbsourcemanager.jar
---
(0071490)
AugustoMauch   
2014-11-07 11:23   
Reopened because the alter session statement is being executed on a pooled connection (dbcp is used to retrieve it).

It would be better to use an unpooled connection to ensure that the alter session query only affects the query to ad_db_modified.
(0071492)
hgbot   
2014-11-07 11:40   
Repository: erp/devel/pi
Changeset: 8c34cbb6b4adab030bd7e8c9cf7da00646b54ab2
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Fri Nov 07 11:37:03 2014 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/8c34cbb6b4adab030bd7e8c9cf7da00646b54ab2 [^]

Fixes issue 27378: Execute the alter session statement in unpooled connection

It is better to execute the alter session query in an unpooled connection, to ensure that the only query affected by it is the call to ad_db_modified. Now the connection used to execute ad_db_modified is retrieved through DriverManager.getConnection.

---
M src-db/database/lib/dbsourcemanager.jar
---
(0071511)
alostale   
2014-11-10 08:57   
code reviewed.

Tested in PG and ORA. ORA with different values for NLS_SORT param
(0073008)
hudsonbot   
2014-12-30 23:23   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/6525fe229e06 [^]
Maturity status: Test
(0073012)
hudsonbot   
2014-12-30 23:24   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/6525fe229e06 [^]
Maturity status: Test
(0073028)
hudsonbot   
2014-12-30 23:24   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/6525fe229e06 [^]
Maturity status: Test
(0073783)
hgbot   
2015-01-26 16:26   
Repository: erp/devel/dbsm-main
Changeset: cd8048353155e9f34751f8783e0a4784e99018fb
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Fri Nov 07 11:30:58 2014 +0100
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/cd8048353155e9f34751f8783e0a4784e99018fb [^]

Fixes issue 27378: Execute the alter session statement in unpooled connection

It is better to execute the alter session query in an unpooled connection, to ensure that the only query affected by it is the call to ad_db_modified. Now the connection used to execute ad_db_modified is retrieved through DriverManager.getConnection.

---
M src/org/openbravo/ddlutils/util/DBSMOBUtil.java
---
(0073802)
AugustoMauch   
2015-01-27 09:59   
This issue was automatically reopened again after pushing the changeset that fixed it to the dbsourcemanager repo