Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0027378
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformminoralways2014-08-19 20:332015-01-27 09:59
ReporterjecharriView Statuspublic 
Assigned ToAugustoMauch 
PriorityhighResolutionfixedFixed in Version3.0PR15Q1
StatusclosedFix in branchFixed in SCM revisioncd8048353155
ProjectionnoneETAnoneTarget Version3.0PR15Q1
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionpiSCM revision 
Merge Request Status
Review Assigned Toalostale
OBNetwork customerOBPS
Web browserGoogle Chrome
ModulesCore
Support ticket29962
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0027378: 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
TagsNo tags attached.
Attached Files? file icon out.ogv [^] (3,989,627 bytes) 2014-09-03 11:02

- Relationships [ Relation Graph ] [ Dependency Graph ]
related to defect 0027379 closedshankarb Module management window should show more information in openbravo.log file at minimum 

-  Notes
(0069937)
shankarb (viewer)
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 (administrator)
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 (developer)
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 (developer)
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 (administrator)
2014-10-31 14:58

 Tested in postgresql and oracle
(0071315)
AugustoMauch (administrator)
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 (administrator)
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 (developer)
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 (developer)
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 (administrator)
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 (developer)
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 (viewer)
2014-11-10 08:57

 code reviewed.

Tested in PG and ORA. ORA with different values for NLS_SORT param
(0073008)
hudsonbot (viewer)
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 (viewer)
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 (viewer)
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 (developer)
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 (administrator)
2015-01-27 09:59

 This issue was automatically reopened again after pushing the changeset that fixed it to the dbsourcemanager repo

- Issue History
Date Modified Username Field Change
2014-08-19 20:33 jecharri New Issue
2014-08-19 20:33 jecharri Assigned To => AugustoMauch
2014-08-19 20:33 jecharri OBNetwork customer => Yes
2014-08-19 20:33 jecharri Web browser => Google Chrome
2014-08-19 20:33 jecharri Modules => Core
2014-08-19 20:33 jecharri Support ticket => 29962
2014-08-19 20:33 jecharri Resolution time => 1414796400
2014-08-19 20:33 jecharri Triggers an Emergency Pack => No
2014-08-19 20:37 jecharri Issue cloned 0027379
2014-08-19 20:37 jecharri Relationship added related to 0027379
2014-08-19 20:40 shuehner Issue Monitored: shuehner
2014-08-19 20:41 jecharri Web browser Google Chrome => Google Chrome
2014-08-19 20:41 jecharri Steps to Reproduce Updated View Revisions
2014-08-19 20:42 jecharri Web browser Google Chrome => Google Chrome
2014-08-19 20:42 jecharri Steps to Reproduce Updated View Revisions
2014-09-03 10:59 shankarb Note Added: 0069937
2014-09-03 11:02 shankarb File Added: out.ogv
2014-09-09 11:29 AugustoMauch Note Added: 0070045
2014-09-22 16:36 jonalegriaesarte Target Version => 3.0PR15Q1
2014-10-22 08:54 alostale Assigned To AugustoMauch => inigosanchez
2014-10-30 13:16 AugustoMauch Assigned To inigosanchez => AugustoMauch
2014-10-31 14:43 AugustoMauch Issue Monitored: alostale
2014-10-31 14:43 AugustoMauch Review Assigned To => alostale
2014-10-31 14:43 AugustoMauch Web browser Google Chrome => Google Chrome
2014-10-31 14:44 hgbot Checkin
2014-10-31 14:44 hgbot Note Added: 0071311
2014-10-31 14:44 hgbot Status new => resolved
2014-10-31 14:44 hgbot Resolution open => fixed
2014-10-31 14:44 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/32ef96c592a4573f46d5b9549de349733ccf4e6d [^]
2014-10-31 14:46 hgbot Checkin
2014-10-31 14:46 hgbot Note Added: 0071312
2014-10-31 14:46 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/pi/rev/32ef96c592a4573f46d5b9549de349733ccf4e6d [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/32ac51156b100be46b20a76f63953eb2800684e3 [^]
2014-10-31 14:58 AugustoMauch Note Added: 0071314
2014-10-31 15:02 AugustoMauch Note Added: 0071315
2014-11-03 13:29 AugustoMauch Note Added: 0071352
2014-11-03 13:29 AugustoMauch Status resolved => new
2014-11-03 13:29 AugustoMauch Resolution fixed => open
2014-11-03 16:44 hgbot Checkin
2014-11-03 16:44 hgbot Note Added: 0071372
2014-11-03 16:44 hgbot Status new => resolved
2014-11-03 16:44 hgbot Resolution open => fixed
2014-11-03 16:44 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/dbsm-main/rev/32ac51156b100be46b20a76f63953eb2800684e3 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/b55aa7117d468268b7a3b3b46a4e3b0eaaa086e6 [^]
2014-11-03 16:45 hgbot Checkin
2014-11-03 16:45 hgbot Note Added: 0071373
2014-11-03 16:45 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/dbsm-main/rev/b55aa7117d468268b7a3b3b46a4e3b0eaaa086e6 [^] => http://code.openbravo.com/erp/devel/pi/rev/a274c96e27ad7289c81e5a59508f7ec98224dd42 [^]
2014-11-07 11:23 AugustoMauch Note Added: 0071490
2014-11-07 11:23 AugustoMauch Status resolved => new
2014-11-07 11:23 AugustoMauch Resolution fixed => open
2014-11-07 11:40 hgbot Checkin
2014-11-07 11:40 hgbot Note Added: 0071492
2014-11-07 11:40 hgbot Status new => resolved
2014-11-07 11:40 hgbot Resolution open => fixed
2014-11-07 11:40 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/pi/rev/a274c96e27ad7289c81e5a59508f7ec98224dd42 [^] => http://code.openbravo.com/erp/devel/pi/rev/8c34cbb6b4adab030bd7e8c9cf7da00646b54ab2 [^]
2014-11-10 08:57 alostale Note Added: 0071511
2014-11-10 08:57 alostale Status resolved => closed
2014-11-10 08:57 alostale Fixed in Version => 3.0PR15Q1
2014-12-30 23:23 hudsonbot Checkin
2014-12-30 23:23 hudsonbot Note Added: 0073008
2014-12-30 23:24 hudsonbot Checkin
2014-12-30 23:24 hudsonbot Note Added: 0073012
2014-12-30 23:24 hudsonbot Checkin
2014-12-30 23:24 hudsonbot Note Added: 0073028
2015-01-26 16:26 hgbot Checkin
2015-01-26 16:26 hgbot Note Added: 0073783
2015-01-26 16:26 hgbot Status closed => resolved
2015-01-26 16:26 hgbot Fixed in SCM revision http://code.openbravo.com/erp/devel/pi/rev/8c34cbb6b4adab030bd7e8c9cf7da00646b54ab2 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/cd8048353155e9f34751f8783e0a4784e99018fb [^]
2015-01-27 09:59 AugustoMauch Note Added: 0073802
2015-01-27 09:59 AugustoMauch Status resolved => closed

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker