jira-software bitbucket integration DVCS

[amartinezbitbucket]

After change a DVCS accounts, we cant sync with the repos.

Repos can be listed and added , but show this error on atlassian-jira.log

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`jira`.`AO_E8B6CC_MESSAGE_QUEUE_ITEM`, CONSTRAINT `fk_ao_e8b6cc_message_queue_item_message_id` FOREIGN KEY (`MESSAGE_ID`) REFERENCES `AO_E8B6CC_MESSAGE` (`ID`))

[Andy Heinzer]

Just to circle back to this thread, it appears that in the support case Arturo was able to get around this problem by running the following sql commands against the Jira database:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE AO_E8B6CC_MESSAGE_TAG;
TRUNCATE AO_E8B6CC_MESSAGE;
TRUNCATE AO_E8B6CC_ORGANIZATION_MAPPING;
TRUNCATE AO_E8B6CC_REPOSITORY_MAPPING;
SET FOREIGN_KEY_CHECKS = 1;

Of course, I would recommend if other users find themselves with the same problem, create a backup of your database first, and I'd also recommend that you stop Jira before you run these SQL commands in order to clear out any caches that might be using these.  After clearing these tables, you will need to add the DVCS account back to Jira once more to be able to have that connection work in Jira.

[Andy Heinzer]

Could you let us know what version of Jira Software you are using here?  There was a similar bug documented in https://jira.atlassian.com/browse/JSWSERVER-16513 that was fixed in the 7.6.3 release.   Granted that notes a postgres database on the back end, but the message is very similar to your mysql error.

If you are using a version before 7.6.3, I would recommend upgrading Jira here in order to resolve this problem.

[amartinezbitbucket]

Thanks for reply.

The version is V7.7.0

[Andy Heinzer]

I found a few other support cases that showed identical errors. However each of these appears to have a different resolution.   These were on the 7.6.0 and 7.6.2 versions where we saw this error, however I have not found a clear existing bug report for this specific version/error.

Could you try these SQL queries to and let me know the results

select * from "AO_E8B6CC_MESSAGE_QUEUE_ITEM" WHERE "MESSAGE_ID" not in (select "ID" from "AO_E8B6CC_MESSAGE");
select * from "AO_E8B6CC_MESSAGE" WHERE "ID" not in (select "MESSAGE_ID" from "AO_E8B6CC_MESSAGE_QUEUE_ITEM");

[Compras_Eurohelp]

mysql> select * from AO_E8B6CC_MESSAGE_QUEUE_ITEM WHERE MESSAGE_ID not in (select ID from AO_E8B6CC_MESSAGE);
Empty set (0,00 sec)

mysql> select * from AO_E8B6CC_MESSAGE WHERE ID not in (select MESSAGE_ID from AO_E8B6CC_MESSAGE_QUEUE_ITEM);
Empty set (0,00 sec)

[amartinezbitbucket]

mysql> select * from AO_E8B6CC_MESSAGE_QUEUE_ITEM WHERE MESSAGE_ID not in (select ID from AO_E8B6CC_MESSAGE);
Empty set (0,00 sec)

mysql> select * from AO_E8B6CC_MESSAGE WHERE ID not in (select MESSAGE_ID from AO_E8B6CC_MESSAGE_QUEUE_ITEM);
Empty set (0,00 sec)

[Andy Heinzer]

Hi Arturo,

Sorry those steps did not appear to help.  I have created a support case on your behalf in https://getsupport.atlassian.com/servicedesk/customer/portal/20/GHS-117961

Please see the steps in that case to gather some more information for us so that we can better troubleshoot this problem.

Thanks,

Andy