SQL error regarding max_join_size on Jira/Confluence upgrade

We are upgrading Jira from 4.4.5 and Confluence from 3.x. Users are managed for Confluence from within Jira.

In our upgraded test instance, many spaces crashed with a SQL error relating to max_join_size.

Here's the error:

org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: Could not execute query; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)

caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

Our DBA increased this size to 20 times the default size for this parameter but it didn't resolve the problem. Our DBA says he's not sure why JIRA or confluence needs to examine over 20 billion rows of combined data, but this type of programming is concerning! He has increased the max_join_size to some "cringeworthy" variable, well above what it normally should be.

Is this normal? Is there something else we should be looking at?

2 answers

This widget could not be displayed.

Hi Kathleen,

What is the upgrade path you are following for both instances?

So do you skip straigth to the latest version or do you update to the next major version (so for JIRA that would be 4.4.5 -> 5.2.11 -> 6.2.5, and for Confluence that would be 3.x -> 4.3.7 -> 5.5.1)

That might be the cause of this problem.

Best regards,

Peter

This widget could not be displayed.

That's interesting. It was my impression that it was important to do it in steps as you say, but this is what we got from Atlassian:

To upgrade Confluence from 3.5.16, I recommend the following steps:
1. Create a test instance, if you haven't already. 
2. Upgrade the test instance from 3.5.16 to 5.0.3. 
3. Test to make sure the upgrade went well. 
4. Upgrade the test instance from 5.0.3 to the most recent version of Confluence. 
5. Test again. 
6. If all went well, proceed with the upgrade on your production instance.

We're currently at step 3. I'm guessing that having to set an extraordinarily high parameter like that counts as "not going well".

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Monday in Confluence

Why start from scratch? Introducing four new templates for Confluence Cloud

Hi my Community friends!  For those who don't know me, I'm a product marketer on the Confluence Cloud team - nice to meet you! For those of you who do, you know that I've been all up in your Co...

505 views 7 6
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