After migrating to SQL Server 2005, autocomplete for users doesn't work

[Dan Pincas]

Hey folks! We're evaluating JIRA in our organization and it is absolutely spectacular. However, we're running into an issue just after migrating from the hSQLDB to MS SQL Server 2005. Not sure if that's the culprit, but as far as I can tell we didn't encounter this issue until the migration.

Anyway, the problem is that whenever we encounter a field that relies on the autocomplete for user names, it doesn't seem to return ANY matches:

Since there's no way to bring up the full user search dialog (which works fine, by the way), we're really stuck here as we can't add any users to our project roles.

So I'm just trying to figure out -- what went wrong! We followed all of the steps when doing the migration, especially making sure the collation of 'SQL_Latin1_General_CP437_CI_AI' was selected so that things would be encoded properly and case-insensitive.

I'm really at a loss as to where to begin checking for the cause of the problem. I tried searching for a similar issue but only found some vague similarities in JIRA 2.3 -- nothing in a recent version.

Any ideas? I even tried issuing a request directly to:

/rest/api/1.0/users/picker?query=d&showAvatar=true&_=1319429713276

And still got no results :-(

We've got our users synchronized (read-only) via LDAP, and our users screen shows we have 21780 total users (big organization), but only 19 are active (this is intentional). Again, this worked fine before migration.

Edit: We ran SQL Server Profiler to monitor what T-SQL the website was running to do the autocomplete, but as far as we could tell, the autocomplete action triggered no stored procedures and no T-SQL statements. So, basically, the autocomplete isn't even hitting our database?

[Jo-Anne MacLeod · Accepted solution]

Under the general configuration do you have autocomplete turned on?

http://confluence.atlassian.com/x/CAISCw

[Dan Pincas]

Yes, this was turned on, but your answer led my (extremely blind?) eyes to this configuration setting:

User Searching By Full Name

Default: ON if you have less than 5,000 users (or OFF if you have more than 5,000 users).

Well, it went and turned itself off, so the user searching was disabled. I turned it on, and everything works fine now :-D

I feel silly for not looking in here, but I didn't think any configuration options had changed. In retrospect, this probably had nothing to do with SQL Server, but perhaps after adding the 10,000 or so users, the configuration option didn't change until we restarted the server? So we didn't notice it until our migration?

Anyway, you're amazing, thank you so much for pointing my head in the right direction!

[JamieA]

I'd try setting the following in your <home>/jira-config.properties file:

jira.ajax.autocomplete.userpicker.limit = 100000

It should not come into play because you only have a few active users, but it's worth doing to rule it out.

BTW just because you don't see a query, don't necessarily mean it's not running, the list of users should be cached. But if you hit the rest api directly and get nothing, then it isn't running.

The other thing to check is that everyone has the Browse Users global permission, but if they can search using the popup then they must already have that.

[Dan Pincas]

This was very close to the problem we had -- apparently "User Searching By Full Name" automatically disables itself (by default) if you have more than 5,000 users. Sigh.

Thank you anyway!!!