Note: This bug is displayed in read-only format because the product is no longer active in Red Hat Bugzilla.

Bug 1103621

Summary: Can't edit data provider query on some databases (false duplicate provider name detection)
Product: [Retired] JBoss BPMS Platform 6 Reporter: Jan Hrcek <jhrcek>
Component: BAMAssignee: Jan <jschatte>
Status: CLOSED EOL QA Contact: Gui Jospin <gjospin>
Severity: medium Docs Contact: Dawn Eisner <deisner>
Priority: medium    
Version: unspecifiedCC: kverlaen
Target Milestone: ER2   
Target Release: 6.1.0   
Hardware: Unspecified   
OS: Unspecified   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2020-03-27 20:11:50 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Attachments:
Description Flags
Error when trying to save edited data provider none

Description Jan Hrcek 2014-06-02 08:37:58 UTC 
Created attachment 901377 [details]
Error when trying to save edited data provider

Description of problem:
When you edit 'jBPM Task Summary' query and try to save the edited data provider, there is an error message "The data provider names (Chinese:????) already in use [...]" and so the edited data provider cannot be saved.

The root cause seems to be how the underlying database treats chinese characters - it converts them to '?' and as a result, the chinese Data provider names are considered identical:

jBPM 任务摘要 (jBPM Task Summary - converted by DB to jBPM ????)
jBPM 进程摘要 (jBPM Process Summary - converted by DB to jBPM ????)

The code for checking duplicated names is in Dashboard UI Core : DataProviderHandler # checkConfig()


Version-Release number of selected component (if applicable):
BPMS 6.0.2 ER3

How reproducible:
This problem occurs in 100% when using MS SQL 2012 or MySQL 5.5
I also saw it several times on Oracle 11gR2

Steps to Reproduce:
1. Deploy dashbuilder with MS SQL 2012 or MySQL 5.5 as underlying database.
2. Login with root
3. Navigate to Showcase > Administration > Data Providers
4. Edit jBPM Task Summary (for example remove a column from SQL query's select statement) and try to save it

Actual results:
The edited query cannot be saved, because dashbuilder detects duplicated data provider name.

Expected results:
It should be possible to edit data provider query on all databases.

Additional info:
MySQL manual states: "Characters outside the BMP compare as REPLACEMENT CHARACTER and convert to '?' when converted to a Unicode character set." (http://dev.mysql.com/doc/refman/5.5/en/charset-unicode.html)
Comment 1 Jan Hrcek 2014-08-25 11:30:55 UTC 
The same problem also occurs when using Oracle database (11gR2) as a data source - (checked with BPM 6.0.3 ER1)
Comment 2 Jan 2014-10-09 11:11:13 UTC 
I have found that, in MySQL, in order for chinese and japanese characters to appear correctly (and by extension for the application to work correctly by comparing etc), it is necessary to configure both the database as well as the database server in UTF8 encoding.

For the database: it should be created by putting "character set utf8" in the create clause. Although optional, it might be prudent to also specify the collation in the same sentence (by adding "collate utf8_general_ci").

For the server: the character set and collation can be specified at startup by adding the following to the server config file:

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

Additional information can be found at:
http://dev.mysql.com/doc/refman/5.1/en/faqs-cjk.html
http://dev.mysql.com/doc/refman/5.0/en/charset.html


It may be necessary to open a new documentation BZ in order to modify the installation guide according to these guidelines.
Comment 3 Jan Hrcek 2015-03-03 07:44:01 UTC 
@Vikram
We need to inform users to setup their databases to support UTF8 encoding if they want to use chinese/japanese etc. characters in dashbuilder. Could you please document this for BPM Suite 6.1?
Comment 4 Vikram Goyal 2015-03-03 08:07:28 UTC 
Jan - this was documented [1] for another bug (I can't find it now), but at that time I believe it was for only PostGres.

Is this enough or do you want it to be clearly set for all databases to be UTF8?

[1] https://documentation-devel.engineering.redhat.com/site/documentation/en-US/Red_Hat_JBoss_BPM_Suite/6.1/html-single/Administration_And_Configuration_Guide/#Setting_up_the_Database
Comment 5 Jan Hrcek 2015-03-03 09:26:23 UTC 
Wikram, I think it would be better to include one or two sentences explicitly stating that if user wants to use UTF 8, he should should consult his DB vendor documentation (not just for Postgres) and set up the encoding at the level of database in order for dashbuilder to work correctly.
Comment 6 Vikram Goyal 2015-03-03 11:20:28 UTC 
Ok. I am going to add this note in the Installation Guide as that is where the user is told to setup persistence for DashBuilder.

This has been added here [1].

Leaving the bug as is, as it is not a documentation bug.

[1] https://documentation-devel.engineering.redhat.com/site/documentation/en-US/Red_Hat_JBoss_BPM_Suite/6.1/html-single/Installation_Guide/#Setting_up_Persistence_for_Dashbuilder