SQL error when connecting project to tenant

performance

(mike) #1

Hi,

I’m getting an error when trying to connect a project to a tenant, which I suspect is due to the very large scale that we’re running at (multiple thousands of projects, hundreds of environments, dozens of tenants).

To reproduce the error:

  • Click “Tenants” in the top menu
  • Select a tenant
  • Click “Connect Project”
  • Select a project from the list
  • Select an environment from the list
  • Click “Add connection”

The following error then appears (@ids_56 to @ids_1951 do appear in the error message but I’ve truncated them below for brevity):


Connect to a Project
Exception occured while executing a reader for SELECT [Id] from [IdsInUse] WITH (NOLOCK) WHERE [Id] IN (@ids_0, @ids_1, @ids_2, @ids_3, @ids_4, @ids_5, @ids_6, @ids_7, @ids_8, @ids_9, @ids_10, @ids_11, @ids_12, @ids_13, @ids_14, @ids_15, @ids_16, @ids_17, @ids_18, @ids_19, @ids_20, @ids_21, @ids_22, @ids_23, @ids_24, @ids_25, @ids_26, @ids_27, @ids_28, @ids_29, @ids_30, @ids_31, @ids_32, @ids_33, @ids_34, @ids_35, @ids_36, @ids_37, @ids_38, @ids_39, @ids_40, @ids_41, @ids_42, @ids_43, @ids_44, @ids_45, @ids_46, @ids_47, @ids_48, @ids_49, @ids_50, @ids_51, @ids_52, @ids_53, @ids_54, @ids_55, ... etc ... @ids_1952, @ids_1953, @ids_1954, @ids_1955, @ids_1956, @ids_1957, @ids_1958, @ids_1959, @ids_1960, @ids_1961, @ids_1962, @ids_1963, @ids_1964, @ids_1965, @ids_1966, @ids_1967, @ids_1968, @ids_1969, @ids_1970, @ids_1971, @ids_1972)
SQL Error 8623 - The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

This specific tenant has about 300 projects already connected. Most of them have the same set of about 10-12 environments, which totals around 2000 environment instances listed on the page and that number is very close to the @ids_1972 in the error message which might be significant.

This is preventing us from connecting any more projects to our tenant, which is blocking some upcoming production releases.

Could you please advise?

Thanks,

Mike

P.S. As a bonus, there’s a typo in the error message as well - “occured” -> “occurred”.


(mike) #2

Btw, this is against v2018.1.3 on our live OctopusDeploy Server, but we’re also seeing the same error in v2018.3.8 on our lab server.


(Shane Gill) #3

Hi Mike,

Thanks for getting in touch.

I think this is happening when we attempt to validate that the tenant’s project/environment connections are all valid. Because you have so many it is causing the query to refuse.

I have created a GitHub issue that you can track and contribute to: https://github.com/OctopusDeploy/Issues/issues/4443

I am working on this now and will ship it as soon as it is finished to unblock your production release. I will also fix the typo. :wink:

Cheers,
Shane


(Shane Gill) #4

Hi Mike,

We are shipping the fix for this issue in version 2018.3.10. I hope you find that you can connect your tenants to projects once more.

If there is anything else I can help with please let me know.

Cheers,
Shane


(mike) #5

Thanks Shane.

I think we’re blocked waiting on resolution of another issue that we’ve found in version 2018.3.8 before we can upgrade:

This doesn’t happen in our current PROD version (2018.1.3) so we’d potentially be introducing this issue into our PROD environment if it’s not already fixed in 2018.3.10. We’ll test the latest version in our lab and let you know what we find.

Cheers,

Mike


(Shane Gill) #6

Hi Mike,

The variable set issue hasn’t been fixed in 2018.3.10.

The underlying SQL parameters error is an ongoing problem for us and rears its head in different places. I was going to figure out a permanent fix for the problem but it will take some time. Considering it is blocking you from upgrading I can just fix the issue Leslie has reported and worry about a more permanent solution later.

Cheers,
Shane


(mike) #7

Thanks Shane.

I think that’s the only other blocking issue, but I’ll check with Leslie as he’s got a better handle on the outstanding issues.

Cheers,

Mike


(leslie.lintott) #8

Hi Shane, Mike

So the Variable Set and Script Module issue is the only one preventing us from upgrading our Production stack at the moment. The other 2 issues (filtering tenants: Error when filtering Projects within a tenant and the account and spn bug https://github.com/OctopusDeploy/Issues/issues/4284) are already fixed, as well as the one reported here.

Cheers
Leslie


(Shane Gill) #9

Hi Mike and Leslie,

I have created a new issue to address specifically what Leslie reported: https://github.com/OctopusDeploy/Issues/issues/4452

It has been fixed, we probably ship the fix on Monday.

Cheers,
Shane


(Shane Gill) #10

We ended up shipping today, the fix is in 2018.3.11.


(mike) #11

Fab. Thanks Shane.

We’ll deploy 2018.3.11 to our lab environment and confirm what we see.

Cheers,

M