Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0033417Openbravo ERPA. Platformpublic2016-07-04 15:562016-12-16 18:37
ReporterSandrahuguet 
Assigned Tocaristu 
PriorityurgentSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS20OS VersionCommunity Appliance
Product Version 
Target Version3.0PR17Q1Fixed in Version3.0PR17Q1 
Merge Request Status
Review Assigned Toalostale
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0033417: Loading config script for a module template in install.source task fails in pgsql 9.3
DescriptionLoading config script for a module template in install.source task fails in pgsql 9.3.
The same config script can be load using update.database task in pgsql and oracle and using install.source task in oracle.

Steps To Reproduce-install the following module template:
https://code.openbravo.com/erp/pmods/org.openbravo.retail.storeserver.synchronization.template [^]
-ant install.source

Notice that the following error occurs in the execution of the task:

Loading config script for module Store Server Synchronized Tables for Retail Template. Path: /srv/ci/workspace/mod-simple-ci23.140/src-db/database/../../modules/org.openbravo.retail.storeserver.synchronization.template/src-db/database/configScript.xml
SQL Command failed with: ERROR: syntax error at or near "Y"
  Position: 135
-- END
UPDATE AD_TAB SET HQLWHERECLAUSE = 'e.obstsynMobserver.id = (select id from OBMOBC_SERVER_DEFINITION ms where ms.iscurrent=\'Y\')' WHERE AD_TAB_ID = '8E5972CF3664486D9D887BDEDA88627D'
SQL Command failed with: ERROR: syntax error at or near "Y"
  Position: 70
-- END
UPDATE AD_TAB SET HQLFILTERCLAUSE = 'e.securityBasedOnRole=\'Y\' or e.userContact.id=@user@\n' WHERE AD_TAB_ID = '8E5972CF3664486D9D887BDEDA88627D'
SQL Command failed with: ERROR: syntax error at or near "Y"
  Position: 182
-- END
UPDATE AD_TAB SET HQLWHERECLAUSE = 'e.processRequest.id = @ProcessRequest.id@ and e.obstsynMobserver.id = (select id from OBMOBC_SERVER_DEFINITION ms where ms.iscurrent=\'Y\')' WHERE AD_TAB_ID = 'A22C68A803DA43D3AE1FFD51BBA3234A'
Executed 5 SQL command(s) with 3 error(s)
Proposed SolutionUse the alterData method inside the applyConfigScript() method of the PlatformImplBase, this way a prepared statement with parameters will be used instead of make use of a query with scaped characters.
Additional Information
TagsNo tags attached.
Relationships
related to defect 0034022 acknowledged Triage Platform Base PostgreSqlBuilder configuration for escaped characters is not correct 
related to design defect 0034102 closed platform Update.database and Install.Source tasks should use the same flow to create the database and the db model 
Attached Filesdiff issue33417.diff (10,494) 2016-09-28 19:25
https://issues.openbravo.com/file_download.php?file_id=9870&type=bug
Issue History
Date ModifiedUsernameFieldChange
2016-07-04 15:56SandrahuguetNew Issue
2016-07-04 15:56SandrahuguetAssigned To => platform
2016-07-04 15:56SandrahuguetModules => Core
2016-07-04 15:56SandrahuguetTriggers an Emergency Pack => No
2016-07-04 15:58SandrahuguetSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=12604#r12604
2016-07-07 12:06AugustoMauchNote Added: 0088287
2016-07-14 12:19alostaleStatusnew => acknowledged
2016-07-26 07:56alostalePrioritynormal => urgent
2016-07-26 07:56alostaleTarget Version => 3.0PR16Q4
2016-07-27 16:05shuehnerIssue Monitored: shuehner
2016-07-27 16:06shuehnerNote Added: 0088680
2016-09-13 16:02caristuStatusacknowledged => scheduled
2016-09-13 16:02caristuAssigned Toplatform => caristu
2016-09-19 19:15caristuProposed Solution updated
2016-09-19 19:16caristuNote Added: 0090080
2016-09-19 19:24caristuRelationship addedrelated to 0034022
2016-09-27 16:50caristuRelationship addedrelated to 0034102
2016-09-28 14:50alostaleTarget Version3.0PR16Q4 => 3.0PR17Q1
2016-09-28 19:25caristuFile Added: issue33417.diff
2016-09-28 19:26caristuNote Added: 0090357
2016-10-17 13:46hgbotCheckin
2016-10-17 13:46hgbotNote Added: 0090638
2016-10-17 14:04hgbotCheckin
2016-10-17 14:04hgbotNote Added: 0090639
2016-10-17 14:04hgbotStatusscheduled => resolved
2016-10-17 14:04hgbotResolutionopen => fixed
2016-10-17 14:04hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/0f6f5ff03a21c796975c447b3e721494bc8d7ce5 [^]
2016-10-17 14:04caristuReview Assigned To => alostale
2016-10-17 14:05caristuIssue Monitored: alostale
2016-10-21 10:46hgbotCheckin
2016-10-21 10:46hgbotNote Added: 0090752
2016-10-28 09:20alostaleNote Added: 0090976
2016-10-28 09:20alostaleStatusresolved => closed
2016-10-28 09:20alostaleFixed in Version => 3.0PR17Q1
2016-10-28 15:13hgbotCheckin
2016-10-28 15:13hgbotNote Added: 0090998
2016-12-16 18:37hudsonbotCheckin
2016-12-16 18:37hudsonbotNote Added: 0092523

Notes
(0088287)
AugustoMauch   
2016-07-07 12:06   
Appying the configScript fails in the install.source (the install.source finishes with success, but the parts of the configScript that failed are not applied) but not whene the configScript is loaded in the update.database process.

This means that as a work around, an update.database can be run after the install.source, and the config script will be properly applied.
(0088680)
shuehner   
2016-07-27 16:06   
Can also be seen happening in job mod-but-BUT_QA (for pg)
(0090080)
caristu   
2016-09-19 19:16   
Fix sent to try
(0090357)
caristu   
2016-09-28 19:26   
Attached patch with the fix, that will be pushed for next 3.0PR17Q1
(0090638)
hgbot   
2016-10-17 13:46   
Repository: erp/devel/dbsm-main
Changeset: ee257b63e9179f570400adb3eef0af6574c080a3
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Mon Oct 17 13:43:31 2016 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/ee257b63e9179f570400adb3eef0af6574c080a3 [^]

related to issue 33417: ConfigScript with single quotes on install.source fails

When applying data changes of a Configuration Script on install.source the generated sql statement could contain unscaped single quotes, causing the failure in the statement execution. The same problem was reproducible with other special characters like breakline.

This problem has been solved using the same method which is being used on update.database: the alterData() method of PlatformImplBase eventually generates sql prepared statements with the special characters properly scaped (by using getUpdateSql() with the genPlaceholders parameter set as true)

---
M src-test/src/org/openbravo/dbsm/test/configscript/ConfigScriptBaseTest.java
M src-test/src/org/openbravo/dbsm/test/configscript/ConfigScriptColumnDataChange.java
M src/org/apache/ddlutils/platform/PlatformImplBase.java
M src/org/apache/ddlutils/platform/SqlBuilder.java
---
(0090639)
hgbot   
2016-10-17 14:04   
Repository: erp/devel/pi
Changeset: 0f6f5ff03a21c796975c447b3e721494bc8d7ce5
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Mon Oct 17 13:52:50 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/0f6f5ff03a21c796975c447b3e721494bc8d7ce5 [^]

fixes issue 33417: ConfigScript with single quotes on install.source fails

---
M src-db/database/lib/dbsourcemanager.jar
---
(0090752)
hgbot   
2016-10-21 10:46   
Repository: erp/devel/dbsm-main
Changeset: 123dc6f7d82fe1420e50f5a25052d2f30efe017c
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Fri Oct 21 10:45:56 2016 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/123dc6f7d82fe1420e50f5a25052d2f30efe017c [^]

related to issue 33417: fix test case in Oracle

Updated trigger definition to avoid error 'ORA-04098: trigger is invalid and failed re-validation' when executing isConfigurationScriptApplied() test in Oracle

---
M src-test/model/configScripts/BASE_MODEL.xml
---
(0090976)
alostale   
2016-10-28 09:20   
code reviewed + tested
(0090998)
hgbot   
2016-10-28 15:13   
Repository: erp/devel/dbsm-main
Changeset: 1f2c47f076e2412a86172f8344cb8a79483bf4e2
Author: Carlos Aristu <carlos.aristu <at> openbravo.com>
Date: Fri Oct 28 15:13:01 2016 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/1f2c47f076e2412a86172f8344cb8a79483bf4e2 [^]

related to issue 33417: improved log error messages

---
M src/org/apache/ddlutils/platform/PlatformImplBase.java
---
(0092523)
hudsonbot   
2016-12-16 18:37   
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/dc8bf00badd0 [^]
Maturity status: Test