Loading...

Details

Type: Bug
Resolution: Unresolved
Priority: Low
Fix Version/s: None
Affects Version/s: 6.4.3
Component/s: Apps - Confluence Questions
Labels:
- pse-request
- questions-for-confluence

Support reference count:
3
Symptom Severity:
Severity 3 - Minor
Bug Fix Policy:
View Atlassian Server bug fix policy

Description

Summary

Confluence Questions voting feature requires running the following database query

select sum("tmp"."S")
from ((select cast("CONTENT"."CREATOR" as varchar(4000 char)) "USER_KEY", sum(case when "AO_B1DBB9_VOTE"."WEIGHT" = 1 then 10 when "AO_B1DBB9_VOTE"."WEIGHT" = -1 then -2 else 0 end) "S"
from "AO_B1DBB9_VOTE" "AO_B1DBB9_VOTE"
inner join "CONTENT" "CONTENT"
on "CONTENT"."CONTENTID" = "AO_B1DBB9_VOTE"."TARGET_ID"
where "CONTENT"."CREATOR" = '8a414d795dbf73ce015dc0d516c3000d'
group by "CONTENT"."CREATOR")
union
(select "AO_B1DBB9_ACCEPTANCE"."ANSWER_USER_KEY" "USER_KEY", sum(15) "S"
from "AO_B1DBB9_ACCEPTANCE" "AO_B1DBB9_ACCEPTANCE"
where "AO_B1DBB9_ACCEPTANCE"."ANSWER_USER_KEY" = '8a414d795dbf73ce015dc0d516c3000d' and not lower("AO_B1DBB9_ACCEPTANCE"."ACCEPTOR_USER_KEY") = '8a414d795dbf73ce015dc0d516c3000d'
group by "AO_B1DBB9_ACCEPTANCE"."ANSWER_USER_KEY")
union
(select "AO_B1DBB9_BOUNTY"."AWARDEE_KEY" "USER_KEY", sum("AO_B1DBB9_BOUNTY"."POINTS") "S"
from "AO_B1DBB9_BOUNTY" "AO_B1DBB9_BOUNTY"
where "AO_B1DBB9_BOUNTY"."AWARDEE_KEY" = '8a414d795dbf73ce015dc0d516c3000d'
group by "AO_B1DBB9_BOUNTY"."AWARDEE_KEY")
union
(select "AO_B1DBB9_BOUNTY"."AWARDER_KEY" "USER_KEY", cast(-sum("AO_B1DBB9_BOUNTY"."POINTS") as number(19,0)) "S"
from "AO_B1DBB9_BOUNTY" "AO_B1DBB9_BOUNTY"
where "AO_B1DBB9_BOUNTY"."AWARDER_KEY" = '8a414d795dbf73ce015dc0d516c3000d'
group by "AO_B1DBB9_BOUNTY"."AWARDER_KEY")) "tmp"

We currently advise our customers to convert all our ActiveObjects tables to NVARCHAR as described here AO-386 and in this KB as well How to upgrade active objects columns from varchar to nvarchar in SQL Server.

This is the default column types when you install Confluence Questions:

Table	Column	Column Type
CONTENT	CREATOR	VARCHAR2(255)
AO_B1DBB9_VOTE	USER_KEY	VARCHAR2(255)
AO_B1DBB9_BOUNTY	AWARDEE_KEY	VARCHAR2(255)
AO_B1DBB9_BOUNTY_ACCEPTANCE	ANSWER_USER_KEY	VARCHAR2(255)

Expected Results

Confluence Questions should use NVARCHAR as the column types to be consistent with our ActiveObjects recommendations

Actual Results

If you have misconfigured your columns types (converted those columns to NVARCHAR), then you will get the following error in the logs:

2018-03-02 11:05:48,622 WARN [http-nio-9090-exec-12] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:
  ->[PluginReadWriteTx]: PROPAGATION_REQUIRED,ISOLATION_DEFAULT (Session #1065688915)
 -- referer: http://example.com:9090/questions | url: /rest/questions/1.0/experts/reputation | traceId: a37cc79a0126cc61 | userName: 123456
2018-03-02 11:05:48,623 ERROR [http-nio-9090-exec-12] [common.error.jersey.ThrowableExceptionMapper] toResponse Uncaught exception thrown by REST service: StatementCallback; uncategorized SQLException for SQL [select sum("tmp"."S")
from ((select cast("CONTENT"."CREATOR" as varchar(4000 char)) "USER_KEY", sum(case when "AO_B1DBB9_VOTE"."WEIGHT" = 1 then 10 when "AO_B1DBB9_VOTE"."WEIGHT" = -1 then -2 else 0 end) "S"
from "AO_B1DBB9_VOTE" "AO_B1DBB9_VOTE"
inner join "CONTENT" "CONTENT"
on "CONTENT"."CONTENTID" = "AO_B1DBB9_VOTE"."TARGET_ID"
where "CONTENT"."CREATOR" = '8a414d795dbf73ce015dc0d516c3000d'
group by "CONTENT"."CREATOR")
union
(select "AO_B1DBB9_ACCEPTANCE"."ANSWER_USER_KEY" "USER_KEY", sum(15) "S"
from "AO_B1DBB9_ACCEPTANCE" "AO_B1DBB9_ACCEPTANCE"
where "AO_B1DBB9_ACCEPTANCE"."ANSWER_USER_KEY" = '8a414d795dbf73ce015dc0d516c3000d' and not lower("AO_B1DBB9_ACCEPTANCE"."ACCEPTOR_USER_KEY") = '8a414d795dbf73ce015dc0d516c3000d'
group by "AO_B1DBB9_ACCEPTANCE"."ANSWER_USER_KEY")
union
(select "AO_B1DBB9_BOUNTY"."AWARDEE_KEY" "USER_KEY", sum("AO_B1DBB9_BOUNTY"."POINTS") "S"
from "AO_B1DBB9_BOUNTY" "AO_B1DBB9_BOUNTY"
where "AO_B1DBB9_BOUNTY"."AWARDEE_KEY" = '8a414d795dbf73ce015dc0d516c3000d'
group by "AO_B1DBB9_BOUNTY"."AWARDEE_KEY")
union
(select "AO_B1DBB9_BOUNTY"."AWARDER_KEY" "USER_KEY", cast(-sum("AO_B1DBB9_BOUNTY"."POINTS") as number(19,0)) "S"
from "AO_B1DBB9_BOUNTY" "AO_B1DBB9_BOUNTY"
where "AO_B1DBB9_BOUNTY"."AWARDER_KEY" = '8a414d795dbf73ce015dc0d516c3000d'
group by "AO_B1DBB9_BOUNTY"."AWARDER_KEY")) "tmp"]; SQL state [72000]; error code [12704]; ORA-12704: character set mismatch
; nested exception is java.sql.SQLException: ORA-12704: character set mismatch

Workaround

The current workaround is to convert your database column types of the following tables as below:

Table	Column	Column Type
CONTENT	CREATOR	VARCHAR2(255)
AO_B1DBB9_VOTE	USER_KEY	VARCHAR2(255)
AO_B1DBB9_BOUNTY	AWARDEE_KEY	VARCHAR2(255)
AO_B1DBB9_BOUNTY_ACCEPTANCE	ANSWER_USER_KEY	VARCHAR2(255)

Attachments

Issue Links

mentioned in: Page Loading...

Confluence Questions requires VARCHAR column type while we advise converting all AO tables to NVARCHAR