Following upgrade to 7.6 DVCS synch appears frozen

Stopped getting BitBucket updates right after the upgrade which seemingly went ok. DVCS will not sync although it appears loaded.

In the atlasian.jira.log we have a message as per the below

2017-11-28 18:57:24,457 WaitForAO ERROR      [c.a.j.p.dvcs.scheduler.DvcsScheduler] Exception waiting for AO to initialize
com.querydsl.core.QueryException: Caught SQLServerException for select "AO_E8B6CC_MESSAGE"."ID", "AO_E8B6CC_MESSAGE"."ADDRESS", "AO_E8B6CC_MESSAGE"."PAYLOAD", "AO_E8B6CC_MESSAGE"."PAYLOAD_TYPE", "AO_E8B6CC_MESSAGE"."PRIORITY", max("AO_E8B6CC_MESSAGE_QUEUE_ITEM"."RETRIES_COUNT") as "RETRIES_COUNT" from "jiraschema"."AO_E8B6CC_MESSAGE" "AO_E8B6CC_MESSAGE" left join "jiraschema"."AO_E8B6CC_MESSAGE_QUEUE_ITEM" "AO_E8B6CC_MESSAGE_QUEUE_ITEM" on "AO_E8B6CC_MESSAGE"."ID" = "AO_E8B6CC_MESSAGE_QUEUE_ITEM"."MESSAGE_ID" where "AO_E8B6CC_MESSAGE"."ID" = ? group by "AO_E8B6CC_MESSAGE"."ID", "AO_E8B6CC_MESSAGE"."ADDRESS", "AO_E8B6CC_MESSAGE"."PAYLOAD", "AO_E8B6CC_MESSAGE"."PAYLOAD_TYPE", "AO_E8B6CC_MESSAGE"."PRIORITY"
 at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
 at com.querydsl.sql.Configuration.translate(Configuration.java:453)
 at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:502)
 at com.atlassian.jira.plugins.dvcs.dao.impl.querydsl.MessageDaoQueryDsl.lambda$getById$8(MessageDaoQueryDsl.java:196)
 at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.lambda$execute$0(DatabaseAccessorImpl.java:66)
 at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.executeInternal(DefaultTransactionalExecutor.java:86)
 at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.lambda$execute$0(DefaultTransactionalExecutor.java:42)
 at com.atlassian.jira.database.DatabaseAccessorImpl.runInManagedTransaction(DatabaseAccessorImpl.java:125)
 ... 3 filtered
 at java.lang.reflect.Method.invoke(Method.java:498)
 at com.atlassian.plugin.util.ContextClassLoaderSettingInvocationHandler.invoke(ContextClassLoaderSettingInvocationHandler.java:26)
 at com.sun.proxy.$Proxy273.runInManagedTransaction(Unknown Source)
 ... 3 filtered
 at java.lang.reflect.Method.invoke(Method.java:498)
 at com.atlassian.plugin.osgi.bridge.external.HostComponentFactoryBean$DynamicServiceInvocationHandler.invoke(HostComponentFactoryBean.java:136)
 at com.sun.proxy.$Proxy273.runInManagedTransaction(Unknown Source)
 at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.runInStartedOrExistingTransaction(JiraHostConnectionAccessor.java:130)
 at com.atlassian.sal.jira.rdbms.JiraHostConnectionAccessor.execute(JiraHostConnectionAccessor.java:60)
 at com.atlassian.sal.core.rdbms.DefaultTransactionalExecutor.execute(DefaultTransactionalExecutor.java:39)
 at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.execute(DatabaseAccessorImpl.java:64)
 at com.atlassian.pocketknife.internal.querydsl.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:40)
 at com.atlassian.jira.plugins.dvcs.dao.impl.querydsl.MessageDaoQueryDsl.getById(MessageDaoQueryDsl.java:180)
 at com.atlassian.jira.plugins.dvcs.dao.impl.querydsl.MessageQueueItemDaoQueryDsl.getByQueueAndMessage(MessageQueueItemDaoQueryDsl.java:180)
 at com.atlassian.jira.plugins.dvcs.service.MessagingServiceImpl.fail(MessagingServiceImpl.java:346)
 at com.atlassian.jira.plugins.dvcs.service.MessagingServiceImpl.lambda$initRunningToFail$0(MessagingServiceImpl.java:176)
 at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
 at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
 at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1374)
 at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:481)
 at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)
 at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
 at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
 at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
 at java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:418)
 at com.atlassian.jira.plugins.dvcs.dao.impl.querydsl.util.PseudoStream.consumeAllInTable(PseudoStream.java:50)
 at com.atlassian.jira.plugins.dvcs.dao.impl.querydsl.MessageQueueItemDaoQueryDsl.getByState(MessageQueueItemDaoQueryDsl.java:233)
 at com.atlassian.jira.plugins.dvcs.service.MessagingServiceImpl.initRunningToFail(MessagingServiceImpl.java:169)
 at com.atlassian.jira.plugins.dvcs.service.MessagingServiceImpl.onStart(MessagingServiceImpl.java:498)
 at com.atlassian.jira.plugins.dvcs.scheduler.DvcsScheduler.lambda$onStart$0(DvcsScheduler.java:68)
 at java.lang.Thread.run(Thread.java:745)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:331)
 at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
 at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
 at com.querydsl.sql.AbstractSQLQuery.fetch(AbstractSQLQuery.java:446)
 ... 41 more

Running the same query as above in the SQL server (same SQL server and instance as before the update), it appears that the query will not execute because it's trying to order by ntext fields ("AO_E8B6CC_MESSAGE"."PAYLOAD"). This is inline with the error shown in the log. 

Seemingly we have the wrong statement issued to the SQL server. Not sure if this is a bug or something went wrong in the upgrade. Any ideas/suggestions?

 

7 answers

This is breaking for us too and causing havoc on our DEV teams that rely on the DVCS integration as part of our review process. 

Would be great to get an update on this.

@Jeremy Durnell @Casey Daniell

I had a look at the bug that Andrew created, and it appears we're currently trying to narrow down the cause of the issue.

My recommendation would be to watch the bug itself, so you will receive automatic updates to your email when there are any changes.

Also, if you vote on the bug, it helps show the number of users that are running into the issue so we can include this in our research.

Thank you both for your understanding!

Regards,

Shannon

Shannon,

I do not understand.  My organization pays a large sum of money to your company so that we can use JIRA.  This issue has been outstanding for weeks and is seriously impacting my ability to research defects, as well as my colleges' ability to do the same.  Regardless of the amount of people who "upvote" the issue, it is affecting one of your customers.  I've got a lot of holiday calls to make here in the Dallas, TX area to people who use, or are considering using, your product in the future.  Of course, I'll be expressing my frustration to them as well as it is top and foremost in my mind at the moment.  It's ironic that a product which is advertised as a tool to help organizations with their development processes is experiencing such a critical failure in their own QA process.  This is a major, customer impacting defect.  It's critical that you fix this immediately.  Nobody here wants to hear anything about upvoting the issue.  We want it fixed. 

0 votes

Hi there,

Can you please confirm also your MS SQL version and your Bitbucket version?

Kind regards,

Shannon

Hello Shannon,

MS SQL 2014 and Bitbucket cloud

Thank you for confirming that. Could you confirm what version of Jira you came from initially?

In my investigation, I found that it seems that one of the columns (such as PAYLOAD) needs to be nvarchar, and this is what is causing it. 

I found a few other cases in our system, and they're already working through an investigation, which means it's likely a bug.

I will keep you posted on what we find out.

Shannon

Great,

It was 7.1.9. 

We figured the nvarchar gap but I didn't want to go dropping tables without any visibility on consequences. 

Is there a rough ETA on a possible fix because DVCS has been down for a week now. 

No ETA at this moment, as we're still doing the initial investigation, but it has been escalated internally.

I don't have a public link to provide you but as many of these teams are either in Sydney or the US I will be able to update you tomorrow at least to say whether or not we have found more details on the case, if not a fix.

Shannon

0 votes

Hi Pantelis,

Sorry to hear about this problem.  I have created a new bug for this problem in https://jira.atlassian.com/browse/JSWSERVER-16243

I would recommend watching this issue for updates.  As for possible work-arounds until this issue is resolved:

  1. It might be possible to avoid this problem by migrating the Jira database to a different database type such as Postgres
  2. Alternative to that, it might be possible to Roll back the Jira upgrade as a means to revert this upgrade and restore this functionality.

I understand that neither of these are simple quick fixes, but we starting to see more reported cases like this which tends to indicate that this bug should be getting more attention very soon.

Regards,
Andy

0 votes

Can you thkn about changing DB by:
ALTER TABLE AO_E8B6CC_MESSAGE ALTER COLUMN PAYLOAD NVARCHAR(MAX)

To avoid
--------
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator
----------
works for me

My concern making the same change is breaking the upgrade path, or inadvertently breaking some other piece of functionality.  

This does seem like a ridiculously simple fix -- there are other varchar(255) datatypes in this table.

 

Why Atlassian is taking so long to implement, and support, what is a simple fix on the surface is concerning when this is a critical part of many development teams SDLC.

I did change to nvarchar(max) for all AO_E8B6CC_% and made collation to be same for them as well for other columns i DVCS tables.  GITHUB is now connected and works. Something like this can be used:

SELECT CONCAT( 'ALTER TABLE ' ,T.name, ' ALTER COLUMN ', C.name, ' nvarchar(max) COLLATE SQL_Latin1_General_CP437_CI_AI;') FROM   sys.tables AS T INNER JOIN sys.columns C ON T.object_id = C.object_id WHERE  collation_name IS NOT NULL AND collation_name = 'SQL_Latin1_General_CP437_CI_AI' AND T.name like 'AO_E8B6CC_%' and type_name(c.user_type_id) ='ntext'

Will Atlassian support this change, if implemented.

I identified the tables to change with

select * from INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE in ('TEXT', 'NTEXT')
and TABLE_NAME like 'AO_E8B6CC_%'

Which gives me:  
AO_E8B6CC_SYNC_EVENT
AO_E8B6CC_SYNC_EVENT
AO_E8B6CC_ISSUE_MAPPING_V2
AO_E8B6CC_ISSUE_MAPPING_V2
AO_E8B6CC_CHANGESET_MAPPING
AO_E8B6CC_CHANGESET_MAPPING
AO_E8B6CC_CHANGESET_MAPPING
AO_E8B6CC_REPOSITORY_MAPPING
AO_E8B6CC_SYNC_AUDIT_LOG
AO_E8B6CC_COMMIT

And this SQL will give me the correct alter statement preserving the nullability of a column.

select
'alter table [dbo].' + quotename(TABLE_NAME)
+ ' alter column ' + quotename(COLUMN_NAME)
+ ' nvarchar(max) '
+ case when IS_NULLABLE = 'NO' then 'not null' else 'null' end
+ ';'
from
INFORMATION_SCHEMA.COLUMNS
where
DATA_TYPE in ('NTEXT')
and TABLE_NAME like 'AO_E8B6CC_%'

Ideally, Atlassian will support these changes and let me get our critically needed GIT / JIRA integration functional again -- our DEV process is currently severely impacted by this issue.

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,088 views 13 18
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot