Following upgrade to 7.6 DVCS synch appears frozen

[Pantelis Nicolaou]

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?

[Jeremy Durnell]

Would be great to get an update on this.

[Shannon S]

@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

[Jeremy Durnell]

Thanks for getting this fixed.

[Casey Daniell]

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. 

[Carl Kullman]

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

[Casey Daniell]

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.

[Carl Kullman]

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'

[Casey Daniell]

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.

[Andy Heinzer]

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

[Andy Heinzer]

Just to update this thread, this bug has been fixed in Jira 7.6.3 as well as 7.7.1.  So any versions above those lines are expected to have this fix.

[Pantelis Nicolaou]

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. 

[Shannon S]

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

[Pantelis Nicolaou]

Hello Shannon,

MS SQL 2014 and Bitbucket cloud

[Shannon S]

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

[Shannon S]

Hi there,

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

Kind regards,

Shannon