Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0036226Openbravo ERPA. Platformpublic2017-06-12 13:172018-03-14 04:59
Reportershuehner 
Assigned ToAugustoMauch 
PriorityurgentSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR18Q2 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0036226: On first install.source don't show SQLException when 'drop role' tries to delete a role which does not exist
DescriptionIn short:
Some SQL commands at start of create.database are allowed to fail with the process continuing. However SQLException are shown which are confusing + annoying to log-checking and should be avoided.

First part of create.database (and indirectly install.source) is to delete + recreate the role + database by doing:

1.) drop database if not exists
2.) drop role

3.) create role
4.) create database

Config is that steps 1-3 can fail but step 4 must suceed.

Failure of 1+2 can be because first install and both user+role do not exist

Failure of 2+3 are possible (at least on postgres) by sharing a user for >=2 databases. Then while dropping db1 + then trying to drop the user this will fail as the 2nd database still exists. Consequently then the create role will also fail as the 'old' role still exists. The error messages shown in this case are acceptable.

However all that above are issues which are already 'correctly' ignored as if the main database in step 4 is created + owned by the role all is fine.

Steps To Reproducerun create.database with user+database name used first ever.
Observe SQLExceptions shown during:
- clean.database.*
- *.structure

with * being either POSTGRE or ORACLE:

Example error output:
a.) postgres
clean.database.POSTGRE:
      [sql] Executing commands
      [sql] 1 of 1 SQL statements executed successfully
      [sql] Executing commands
      [sql] Failed to execute: DROP ROLE tadpi
      [sql] org.postgresql.util.PSQLException: ERROR: role "tadpi" does not exist
      [sql] 0 of 1 SQL statements executed successfully

b.) oracle
clean.database.ORACLE:
      [sql] Executing commands
      [sql] Failed to execute: DROP USER tadpiora CASCADE
      [sql] java.sql.SQLSyntaxErrorException: ORA-01918: user 'TADPIORA' does not exist
      [sql] 0 of 1 SQL statements executed successfully
Proposed SolutionAvoid the exception by using 'not exists' form of drop commands which does not error out if the db object does not exist.
Additional Information
TagsNo tags attached.
Relationships
blocks design defect 0036162 acknowledged Triage Platform Base clean up openbravo.log 
Attached Filespatch 32514.patch (2,434) 2017-07-27 12:39
https://issues.openbravo.com/file_download.php?file_id=10936&type=bug
Issue History
Date ModifiedUsernameFieldChange
2017-06-12 13:17shuehnerNew Issue
2017-06-12 13:17shuehnerAssigned To => platform
2017-06-12 13:17shuehnerModules => Core
2017-06-12 13:17shuehnerTriggers an Emergency Pack => No
2017-06-12 13:33shuehnerSteps to Reproduce Updatedbug_revision_view_page.php?rev_id=15324#r15324
2017-06-16 11:26alostaleRelationship addedblocks 0036162
2017-06-16 11:26alostaleStatusnew => acknowledged
2017-06-28 07:30hgbotCheckin
2017-06-28 07:30hgbotNote Added: 0097728
2017-06-28 07:30hgbotStatusacknowledged => resolved
2017-06-28 07:30hgbotResolutionopen => fixed
2017-06-28 07:30hgbotFixed in SCM revision => http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/d39f80176463d1f9beeec3720e9d980121265d97 [^]
2017-06-28 13:48asiermartirenaStatusresolved => new
2017-06-28 13:48asiermartirenaResolutionfixed => open
2017-06-28 13:48asiermartirenaStatusnew => acknowledged
2017-06-28 13:49asiermartirenaNote Added: 0097738
2017-06-28 18:24asiermartirenaNote Edited: 0097738bug_revision_view_page.php?bugnote_id=0097738#r15438
2017-07-27 12:39staffrmFile Added: 32514.patch
2018-02-14 10:19alostalePrioritynormal => urgent
2018-02-14 10:38AugustoMauchNote Deleted: 0097728
2018-02-14 10:38AugustoMauchNote Deleted: 0097738
2018-02-14 12:00AugustoMauchDescription Updatedbug_revision_view_page.php?rev_id=16686#r16686
2018-02-14 13:36AugustoMauchReview Assigned To => caristu
2018-02-14 13:37hgbotCheckin
2018-02-14 13:37hgbotNote Added: 0102400
2018-02-14 13:37hgbotStatusacknowledged => resolved
2018-02-14 13:37hgbotResolutionopen => fixed
2018-02-14 13:37hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/d39f80176463d1f9beeec3720e9d980121265d97 [^] => http://code.openbravo.com/erp/devel/pi/rev/15559dddfaad50d6fe3ff863ee8df2a362f7fc02 [^]
2018-02-22 18:19hudsonbotCheckin
2018-02-22 18:19hudsonbotNote Added: 0102758
2018-02-28 16:23caristuAssigned Toplatform => AugustoMauch
2018-03-13 10:29hgbotCheckin
2018-03-13 10:29hgbotNote Added: 0103181
2018-03-13 10:59caristuNote Added: 0103182
2018-03-13 10:59caristuStatusresolved => closed
2018-03-13 10:59caristuFixed in Version => 3.0PR18Q2
2018-03-14 04:59hudsonbotCheckin
2018-03-14 04:59hudsonbotNote Added: 0103221

Notes
(0102400)
hgbot   
2018-02-14 13:37   
Repository: erp/devel/pi
Changeset: 15559dddfaad50d6fe3ff863ee8df2a362f7fc02
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Wed Feb 14 13:35:37 2018 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/15559dddfaad50d6fe3ff863ee8df2a362f7fc02 [^]

Fixes issue 36226: No error is shown on install.source if role does not exist

Now if the role/user does not exist in postgresql/oracle when invoking install.source, no error message will be shown:
- In postgresql, the role is only dropped if exists
- Oracle does not have the IF EXISTS clause, so the dba_users table is queried to check if a user with the given name exists.

---
M src-db/database/build-create.xml
---
(0102758)
hudsonbot   
2018-02-22 18:19   
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/980a6ad5bbf5 [^]
Maturity status: Test
(0103181)
hgbot   
2018-03-13 10:29   
Repository: erp/devel/pi
Changeset: a3ad3bc6723d0d1636f397a4dd30a92305615db4
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue Mar 13 10:28:31 2018 +0100
URL: http://code.openbravo.com/erp/devel/pi/rev/a3ad3bc6723d0d1636f397a4dd30a92305615db4 [^]

Related with issue 36226: Be coherent when querying users in dba_users

The query done to the dba_users table in clean.database.ORACLE is case insensitive, the query done to the
same table in ORACLE.structure should be case insensitive too.

---
M src-db/database/build-create.xml
---
(0103182)
caristu   
2018-03-13 10:59   
Code reviewed + tested OK both in PG and ORA.
(0103221)
hudsonbot   
2018-03-14 04:59   
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/b735f51dc9bc [^]
Maturity status: Test