SQL error regarding max_join_size on Jira/Confluence upgrade

Question

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?

unknown · Answer

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".

Peter Van de Voorde · Answer

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