Users Autocomplete not working 4.0.7?

RT Users
1

Hello everyone,

I just installed RT 4.0.7 this morning (along with the latest versions of External Auth, LdapImport, and Commands by email), and now the autocomplete in user fields is not working.

It does still work in the owner field (since we have enabled Set($AutocompleteOwners, 1):wink:

I can watch the console in safari and see the User object requesting information and returning an empty array where as Owner fires and returns an array of users. Is there something weird going on with the bug fix for users without emails not showing in autocomplete?

I turned my logging level up to debug and am not getting any messages from RT back about it, but my apache logs do show the queries going to the server:

137.238.60.9 - - [30/Aug/2012:09:55:43 -0400] “GET /Helpers/Autocomplete/Users?delim=,&term=plummer HTTP/1.1” 200 3 “https://rt.geneseo.edu/Ticket/Create.html?Queue=3” “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_1) AppleWebKit/536.25 (KHTML, like Gecko) Version/6.0 Safari/536.25”
137.238.60.9 - - [30/Aug/2012:09:55:46 -0400] “GET /Helpers/Autocomplete/Users?delim=,&term=p HTTP/1.1” 200 3 “https://rt.geneseo.edu/Ticket/Create.html?Queue=3” “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_1) AppleWebKit/536.25 (KHTML, like Gecko) Version/6.0 Safari/536.25”
137.238.60.9 - - [30/Aug/2012:09:55:47 -0400] “GET /Helpers/Autocomplete/Users?delim=,&term=pl HTTP/1.1” 200 3 “https://rt.geneseo.edu/Ticket/Create.html?Queue=3” “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_1) AppleWebKit/536.25 (KHTML, like Gecko) Version/6.0 Safari/536.25”
137.238.60.9 - - [30/Aug/2012:09:55:47 -0400] “GET /Helpers/Autocomplete/Users?delim=,&term=plu HTTP/1.1” 200 3 “https://rt.geneseo.edu/Ticket/Create.html?Queue=3” “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_1) AppleWebKit/536.25 (KHTML, like Gecko) Version/6.0 Safari/536.25”
137.238.60.9 - - [30/Aug/2012:09:55:48 -0400] “GET /Helpers/Autocomplete/Users?delim=,&term=plum HTTP/1.1” 200 3 “https://rt.geneseo.edu/Ticket/Create.html?Queue=3” “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_1) AppleWebKit/536.25 (KHTML, like Gecko) Version/6.0 Safari/536.25”
137.238.60.9 - - [30/Aug/2012:09:55:48 -0400] “GET /Helpers/Autocomplete/Users?delim=,&term=plumm HTTP/1.1” 200 3 “https://rt.geneseo.edu/Ticket/Create.html?Queue=3” “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_8_1) AppleWebKit/536.25 (KHTML, like Gecko) Version/6.0 Safari/536.25”

My database is Oracle if that makes any difference.

Any information from others that have upgraded to 4.0.7 or other troubleshooting steps people have would be greatly appreciated.

Thanks!

  • Shawn Plummer
    Systems Manager SUNY Geneseo
2

I just installed RT 4.0.7 this morning (along with the latest
versions of External Auth, LdapImport, and Commands by email), and
now the autocomplete in user fields is not working.

One of the access log lines you showed was autocompleting for a user
“plummer”. Does that user have an email address?

What field were you typing into on the ticket create page?

3

That user is me. I do have an email address.

Those logs are the searches done as I type. I was typing in the requestor field.

If I type my name in the owners field it returns a value, requestors it does not. We are using the defaults from RT_Config for search values.On Aug 30, 2012, at 1:03 PM, Thomas Sibley trs@bestpractical.com wrote:

One of the access log lines you showed was autocompleting for a user
“plummer”. Does that user have an email address?

What field were you typing into on the ticket create page?

4

That user is me. I do have an email address.

Those logs are the searches done as I type. I was typing in the
requestor field.

FWIW, I can’t replicate this, so there are bound to be a few questions to rule out possibilities.

Can you search your DB directly?

select id, Name, EmailAddress from Users where Name LIKE ‘plummer%’ or EmailAddress LIKE ‘plummer%’;

5

FWIW, I can’t replicate this, so there are bound to be a few questions to rule out possibilities.

I appreciate the help. Seems an odd issue.

Can you search your DB directly?

select id, Name, EmailAddress from Users where Name LIKE ‘plummer%’ or EmailAddress LIKE ‘plummer%’;

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select id, Name, EmailAddress from Users where Name LIKE ‘plummer%’ or EmailAddress LIKE ‘plummer%’;

ID

NAME
EMAILADDRESS
23
plummer
plummer@geneseo.edu

  • Shawn
6

I appreciate the help. Seems an odd issue.

Odd indeed. Can you capture the SQL query RT is sending Oracle for the
autocompleter?

Does it return any results?

7

Please keep replies on the list for the benefit of folks searching for
answers later.On 08/30/2012 01:16 PM, Shawn Plummer wrote:

My DBA tells me that this is the query she sees in the database when
the autocomplete fires:

“SELECT * FROM ( SELECT limitquery.,rownum limitrownum FROM (
SELECT main. FROM ( SELECT DISTINCT main.id FROM Users main JOIN
Principals Principals_1 ON ( Principals_1.id = main.id ) WHERE
(Principals_1.Disabled = ‘0’) AND (lower(main.RealName) LIKE
‘%taf2%’ OR lower(main.EmailAddress) LIKE ‘taf2%’ OR
lower(main.Name) LIKE ‘taf2%’) AND
(lower(Principals_1.PrincipalType) = ‘user’) AND (main.EmailAddress
!= ‘’ AND main.EmailAddress IS NOT NULL) ) distinctquery, Users
main WHERE (main.id = distinctquery.id) ORDER BY main.Name ASC )
limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1”

If you run that (awful awful) query by hand, what does it get you?

8

Looks like it returns no rows. So that could be a problem!

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 19:24:24 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT * FROM ( SELECT limitquery.,rownum limitrownum FROM ( SELECT main. FROM ( SELECT DISTINCT main.id FROM Users main JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) WHERE (Principals_1.Disabled = ‘0’) AND (lower(main.RealName) LIKE ‘%taf2%’ OR lower(main.EmailAddress) LIKE ‘taf2%’ OR lower(main.Name) LIKE ‘taf2%’) AND (lower(Principals_1.PrincipalType) = ‘user’) AND (main.EmailAddress != ‘’ AND main.EmailAddress IS NOT NULL) ) distinctquery, Users main WHERE (main.id = distinctquery.id) ORDER BY main.Name ASC ) limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1;

no rows selected

SQL>On Aug 30, 2012, at 4:49 PM, Thomas Sibley trs@bestpractical.com wrote:

Please keep replies on the list for the benefit of folks searching for
answers later.

On 08/30/2012 01:16 PM, Shawn Plummer wrote:

My DBA tells me that this is the query she sees in the database when
the autocomplete fires:

“SELECT * FROM ( SELECT limitquery.,rownum limitrownum FROM (
SELECT main. FROM ( SELECT DISTINCT main.id FROM Users main JOIN
Principals Principals_1 ON ( Principals_1.id = main.id ) WHERE
(Principals_1.Disabled = ‘0’) AND (lower(main.RealName) LIKE
‘%taf2%’ OR lower(main.EmailAddress) LIKE ‘taf2%’ OR
lower(main.Name) LIKE ‘taf2%’) AND
(lower(Principals_1.PrincipalType) = ‘user’) AND (main.EmailAddress
!= ‘’ AND main.EmailAddress IS NOT NULL) ) distinctquery, Users
main WHERE (main.id = distinctquery.id) ORDER BY main.Name ASC )
limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1”

If you run that (awful awful) query by hand, what does it get you?

9

Hi Guys,

Have you tried separating out the subqueries there?
If it were me that’s what I would look at next. Here they are split up into individual queries (where aliases weren’t used already I have given them generic names):

distinctQuery:
SELECT DISTINCT main.id
FROM Users main
JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
WHERE (Principals_1.Disabled = ‘0’)
AND (
lower(main.RealName) LIKE ‘%taf2%’
OR lower(main.EmailAddress) LIKE ‘taf2%’
OR lower(main.Name) LIKE ‘taf2%’
)
AND (lower(Principals_1.PrincipalType) = ‘user’)
AND (
main.EmailAddress != ‘’
AND main.EmailAddress IS NOT NULL
)

limitQuery:
SELECT main.*
FROM distinctquery, Users main
WHERE (main.id = distinctquery.id)
ORDER BY main.Name ASC

thirdQuery:
SELECT limitquery.*,rownum limitrownum
FROM limitquery
WHERE rownum <= 10

finalQuery:
SELECT *
FROM thirdQuery
WHERE limitrownum >= 1;

so first run distinctQuery - This part should essentially get you the list of users who match what you are typing (in this case, users whose real name contains taf2 or whose email or username starts with taf2; who are also user’s with valid email addresses; who are not disabled). If this returns no rows there are serious problems - either there is definitely no user matching that condition or there are serious Db issues. The next three queries are trivial really. limitQuery selects the id of the matched users and orders by username. thirdQuery (I’m pretty creative when it comes to naming) limits the results to 10.

but yeah, to reiterate, I would suggest just running the deepest level query (the one I have marked distinctQuery above) to see if you get results from that. If you do, heck the other queries in order to find the point of failure. Otherwise, all I can really suggest is to take a serious look at the Users table and confirm there is a user there who matches the conditions. Make absolute sure the user is not disabled, make absolute sure that taf2 appears somewhere in the Real Name field, NOT the nickname field (or add nickname to $UserAutocompleteFields in RT_SiteConfig.pm). If your user’s are synced from LDAP/AD, then they may have these details in that system but the configuration has not yet been setup to sync these into LDAP.

I saw in an earlier post you were checking with plummer, your username, but in these queries it suggests we were looking for taf2. If taf2 is not yourself you may be running into a confusion I experienced when I installed RT - that is, I set up the External Auth plugin but not the LDAP Import plugin. Your users may not yet be in the RT database if they have not yet logged in if this is the case.

I know I’ve covered a bunch of fairly basic things here, so I am really sorry if this is all stuff you have already checked out. It can be tough to guess someone’s level of expertise over the internet, so while it’s likely you know a whole lot more than me, I didn’t want to leave anything out that might help you.

HTH

Regards

Chris O’Kelly
Web Administrator

Minecorp Australia
P: 07 3723 1000
M: 0450 586 190

Minecorp Australia
37 Murdoch Circuit
Acacia Ridge QLD 4110
www.minecorp.com.auFrom: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Shawn Plummer
Sent: Friday, 31 August 2012 9:28 AM
To: RT Users
Subject: Re: [rt-users] Users Autocomplete not working 4.0.7?

Looks like it returns no rows. So that could be a problem!

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 19:24:24 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT * FROM ( SELECT limitquery.,rownum limitrownum FROM ( SELECT main. FROM ( SELECT DISTINCT main.id FROM Users main JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) WHERE (Principals_1.Disabled = ‘0’) AND (lower(main.RealName) LIKE ‘%taf2%’ OR lower(main.EmailAddress) LIKE ‘taf2%’ OR lower(main.Name) LIKE ‘taf2%’) AND (lower(Principals_1.PrincipalType) = ‘user’) AND (main.EmailAddress != ‘’ AND main.EmailAddress IS NOT NULL) ) distinctquery, Users main WHERE (main.id = distinctquery.id) ORDER BY main.Name ASC ) limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1;

no rows selected

SQL>

10

Thanks for breaking those up! I am digging into the SQL tonight too. The distinct query appears to return no results in my testing so far.

As for the username change, that user was easier to isolate since it had less activity than I did when our DBA ran the query. The user definitely exists, has an email, and is enabled. We do use external auth and LDAP Import. Both are still working since the upgrade.

select id, Name, EmailAddress from Users where Name LIKE ‘taf2%’ or EmailAddress LIKE ‘taf2%’;
returns the user.

Hi Guys,

Have you tried separating out the subqueries there?
If it were me that’s what I would look at next. Here they are split up into individual queries (where aliases weren’t used already I have given them generic names):

distinctQuery:
SELECT DISTINCT main.id
FROM Users main
JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
WHERE (Principals_1.Disabled = ‘0’)
AND (
lower(main.RealName) LIKE ‘%taf2%’
OR lower(main.EmailAddress) LIKE ‘taf2%’
OR lower(main.Name) LIKE ‘taf2%’
)
AND (lower(Principals_1.PrincipalType) = ‘user’)
AND (
main.EmailAddress != ‘’
AND main.EmailAddress IS NOT NULL
)

limitQuery:
SELECT main.*
FROM distinctquery, Users main
WHERE (main.id = distinctquery.id)
ORDER BY main.Name ASC

thirdQuery:
SELECT limitquery.*,rownum limitrownum
FROM limitquery
WHERE rownum <= 10

finalQuery:
SELECT *
FROM thirdQuery
WHERE limitrownum >= 1;

so first run distinctQuery - This part should essentially get you the list of users who match what you are typing (in this case, users whose real name contains taf2 or whose email or username starts with taf2; who are also user’s with valid email addresses; who are not disabled). If this returns no rows there are serious problems - either there is definitely no user matching that condition or there are serious Db issues. The next three queries are trivial really. limitQuery selects the id of the matched users and orders by username. thirdQuery (I’m pretty creative when it comes to naming) limits the results to 10.

but yeah, to reiterate, I would suggest just running the deepest level query (the one I have marked distinctQuery above) to see if you get results from that. If you do, heck the other queries in order to find the point of failure. Otherwise, all I can really suggest is to take a serious look at the Users table and confirm there is a user there who matches the conditions. Make absolute sure the user is not disabled, make absolute sure that taf2 appears somewhere in the Real Name field, NOT the nickname field (or add nickname to $UserAutocompleteFields in RT_SiteConfig.pm). If your user’s are synced from LDAP/AD, then they may have these details in that system but the configuration has not yet been setup to sync these into LDAP.

I saw in an earlier post you were checking with plummer, your username, but in these queries it suggests we were looking for taf2. If taf2 is not yourself you may be running into a confusion I experienced when I installed RT - that is, I set up the External Auth plugin but not the LDAP Import plugin. Your users may not yet be in the RT database if they have not yet logged in if this is the case.

I know I’ve covered a bunch of fairly basic things here, so I am really sorry if this is all stuff you have already checked out. It can be tough to guess someone’s level of expertise over the internet, so while it’s likely you know a whole lot more than me, I didn’t want to leave anything out that might help you.

HTH

Regards

Chris O’Kelly
Web Administrator

Minecorp Australia
P: 07 3723 1000
M: 0450 586 190

Minecorp Australia
37 Murdoch Circuit
Acacia Ridge QLD 4110
www.minecorp.com.au
Sent Via a Mobile Device.

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Shawn Plummer
Sent: Friday, 31 August 2012 9:28 AM
To: RT Users
Subject: Re: [rt-users] Users Autocomplete not working 4.0.7?

Looks like it returns no rows. So that could be a problem!

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 19:24:24 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT * FROM ( SELECT limitquery.,rownum limitrownum FROM ( SELECT main. FROM ( SELECT DISTINCT main.id FROM Users main JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) WHERE (Principals_1.Disabled = ‘0’) AND (lower(main.RealName) LIKE ‘%taf2%’ OR lower(main.EmailAddress) LIKE ‘taf2%’ OR lower(main.Name) LIKE ‘taf2%’) AND (lower(Principals_1.PrincipalType) = ‘user’) AND (main.EmailAddress != ‘’ AND main.EmailAddress IS NOT NULL) ) distinctquery, Users main WHERE (main.id = distinctquery.id) ORDER BY main.Name ASC ) limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1;

no rows selected

SQL>

On Aug 30, 2012, at 4:49 PM, Thomas Sibley trs@bestpractical.com wrote:

Please keep replies on the list for the benefit of folks searching for
answers later.

On 08/30/2012 01:16 PM, Shawn Plummer wrote:

My DBA tells me that this is the query she sees in the database when
the autocomplete fires:

“SELECT * FROM ( SELECT limitquery.,rownum limitrownum FROM (
SELECT main. FROM ( SELECT DISTINCT main.id FROM Users main JOIN
Principals Principals_1 ON ( Principals_1.id = main.id ) WHERE
(Principals_1.Disabled = ‘0’) AND (lower(main.RealName) LIKE
‘%taf2%’ OR lower(main.EmailAddress) LIKE ‘taf2%’ OR
lower(main.Name) LIKE ‘taf2%’) AND
(lower(Principals_1.PrincipalType) = ‘user’) AND (main.EmailAddress
!= ‘’ AND main.EmailAddress IS NOT NULL) ) distinctquery, Users
main WHERE (main.id = distinctquery.id) ORDER BY main.Name ASC )
limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1”

If you run that (awful awful) query by hand, what does it get you?

11

I further broke the query down.

SELECT DISTINCT main.id, main.EmailAddress, main.name
FROM Users main
JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
WHERE (Principals_1.Disabled = ‘0’)
AND (
lower(main.RealName) LIKE ‘%taf2%’
OR lower(main.EmailAddress) LIKE ‘taf2%’
OR lower(main.Name) LIKE ‘taf2%’
)
AND (lower(Principals_1.PrincipalType) = ‘user’)
;

Returns the user record (I added emailaddress and name to the query values to verify they were not empty.)

Adding this line makes it return no values.

AND (
main.EmailAddress != ‘’
AND main.EmailAddress IS NOT NULL
)

Again, I can see the EmailAddress is populated as is name.

  • Shawn Plummer
    Systems ManagerOn Aug 30, 2012, at 8:55 PM, Shawn Plummer plummer@geneseo.edu wrote:

Thanks for breaking those up! I am digging into the SQL tonight too. The distinct query appears to return no results in my testing so far.

As for the username change, that user was easier to isolate since it had less activity than I did when our DBA ran the query. The user definitely exists, has an email, and is enabled. We do use external auth and LDAP Import. Both are still working since the upgrade.

select id, Name, EmailAddress from Users where Name LIKE ‘taf2%’ or EmailAddress LIKE ‘taf2%’;
returns the user.

  • Shawn Plummer
    Systems Manager

On Aug 30, 2012, at 8:09 PM, Chris O’Kelly Chris.okelly@minecorp.com.au wrote:

Hi Guys,

Have you tried separating out the subqueries there?
If it were me that’s what I would look at next. Here they are split up into individual queries (where aliases weren’t used already I have given them generic names):

distinctQuery:
SELECT DISTINCT main.id
FROM Users main
JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
WHERE (Principals_1.Disabled = ‘0’)
AND (
lower(main.RealName) LIKE ‘%taf2%’
OR lower(main.EmailAddress) LIKE ‘taf2%’
OR lower(main.Name) LIKE ‘taf2%’
)
AND (lower(Principals_1.PrincipalType) = ‘user’)
AND (
main.EmailAddress != ‘’
AND main.EmailAddress IS NOT NULL
)

limitQuery:
SELECT main.*
FROM distinctquery, Users main
WHERE (main.id = distinctquery.id)
ORDER BY main.Name ASC

thirdQuery:
SELECT limitquery.*,rownum limitrownum
FROM limitquery
WHERE rownum <= 10

finalQuery:
SELECT *
FROM thirdQuery
WHERE limitrownum >= 1;

so first run distinctQuery - This part should essentially get you the list of users who match what you are typing (in this case, users whose real name contains taf2 or whose email or username starts with taf2; who are also user’s with valid email addresses; who are not disabled). If this returns no rows there are serious problems - either there is definitely no user matching that condition or there are serious Db issues. The next three queries are trivial really. limitQuery selects the id of the matched users and orders by username. thirdQuery (I’m pretty creative when it comes to naming) limits the results to 10.

but yeah, to reiterate, I would suggest just running the deepest level query (the one I have marked distinctQuery above) to see if you get results from that. If you do, heck the other queries in order to find the point of failure. Otherwise, all I can really suggest is to take a serious look at the Users table and confirm there is a user there who matches the conditions. Make absolute sure the user is not disabled, make absolute sure that taf2 appears somewhere in the Real Name field, NOT the nickname field (or add nickname to $UserAutocompleteFields in RT_SiteConfig.pm). If your user’s are synced from LDAP/AD, then they may have these details in that system but the configuration has not yet been setup to sync these into LDAP.

I saw in an earlier post you were checking with plummer, your username, but in these queries it suggests we were looking for taf2. If taf2 is not yourself you may be running into a confusion I experienced when I installed RT - that is, I set up the External Auth plugin but not the LDAP Import plugin. Your users may not yet be in the RT database if they have not yet logged in if this is the case.

I know I’ve covered a bunch of fairly basic things here, so I am really sorry if this is all stuff you have already checked out. It can be tough to guess someone’s level of expertise over the internet, so while it’s likely you know a whole lot more than me, I didn’t want to leave anything out that might help you.

HTH

Regards

Chris O’Kelly
Web Administrator

Minecorp Australia
P: 07 3723 1000
M: 0450 586 190

Minecorp Australia
37 Murdoch Circuit
Acacia Ridge QLD 4110
www.minecorp.com.au
Sent Via a Mobile Device.

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Shawn Plummer
Sent: Friday, 31 August 2012 9:28 AM
To: RT Users
Subject: Re: [rt-users] Users Autocomplete not working 4.0.7?

Looks like it returns no rows. So that could be a problem!

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 19:24:24 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT * FROM ( SELECT limitquery.,rownum limitrownum FROM ( SELECT main. FROM ( SELECT DISTINCT main.id FROM Users main JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) WHERE (Principals_1.Disabled = ‘0’) AND (lower(main.RealName) LIKE ‘%taf2%’ OR lower(main.EmailAddress) LIKE ‘taf2%’ OR lower(main.Name) LIKE ‘taf2%’) AND (lower(Principals_1.PrincipalType) = ‘user’) AND (main.EmailAddress != ‘’ AND main.EmailAddress IS NOT NULL) ) distinctquery, Users main WHERE (main.id = distinctquery.id) ORDER BY main.Name ASC ) limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1;

no rows selected

SQL>

On Aug 30, 2012, at 4:49 PM, Thomas Sibley trs@bestpractical.com wrote:

Please keep replies on the list for the benefit of folks searching for
answers later.

On 08/30/2012 01:16 PM, Shawn Plummer wrote:

My DBA tells me that this is the query she sees in the database when
the autocomplete fires:

“SELECT * FROM ( SELECT limitquery.,rownum limitrownum FROM (
SELECT main. FROM ( SELECT DISTINCT main.id FROM Users main JOIN
Principals Principals_1 ON ( Principals_1.id = main.id ) WHERE
(Principals_1.Disabled = ‘0’) AND (lower(main.RealName) LIKE
‘%taf2%’ OR lower(main.EmailAddress) LIKE ‘taf2%’ OR
lower(main.Name) LIKE ‘taf2%’) AND
(lower(Principals_1.PrincipalType) = ‘user’) AND (main.EmailAddress
!= ‘’ AND main.EmailAddress IS NOT NULL) ) distinctquery, Users
main WHERE (main.id = distinctquery.id) ORDER BY main.Name ASC )
limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1”

If you run that (awful awful) query by hand, what does it get you?

12

More specifically removal of main.EmailAddress != ‘’ makes it return the user record.

  • Shawn Plummer
    Systems ManagerOn Aug 30, 2012, at 9:37 PM, Shawn Plummer plummer@geneseo.edu wrote:

Adding this line makes it return no values.

AND (
main.EmailAddress != ‘’
AND main.EmailAddress IS NOT NULL
)

13

Running that query returns no results.

I will look into the indexing. I know we run the rt full text indexing command every 15 minutes. As for the database indexes I will have to look into it.

hmmm… that is odd.

If you run the query:

SELECT DISTINCT main.id, main.EmailAddress, main.name
FROM Users main
JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
WHERE (Principals_1.Disabled = ‘0’)
AND (
lower(main.RealName) LIKE ‘%taf2%’
OR lower(main.EmailAddress) LIKE ‘taf2%’
OR lower(main.Name) LIKE ‘taf2%’
)
AND (lower(Principals_1.PrincipalType) = ‘user’)
AND main.EmailAddress !=‘’;

do you get a result? I am starting to suspect you may be encountering some weird IS NULL behaviour. I have read that in Oracle IS NOT NULL will attempt to use an index (if one exists) where IS NULL will not. Do you know if indexing has been setup for this column? If so, the indexes may be corrupt.

As an aside, the query is malformed anyway. Oracle treats zero-length (‘’) strings as NULL anyway, so
“main.EmailAddress !=‘’” and “main.EmailAddress IS NOT NULL” should be analogous. If running the amended query above returns results for you, you may be able to work around the issue by changing this in the autocomplete module (I am not sure whereabouts the query is defined but it shouldn’t be too tough to find), although, as you would know, this could be a problem for you when/if you update RT (also, if it were me, I would need to satisfy my curiosity over the cause, so a workaround wouldn’t be good enough :p).

Regards

Regards

Chris O’Kelly
Web Administrator

Minecorp Australia
P: 07 3723 1000
M: 0450 586 190

Minecorp Australia
37 Murdoch Circuit
Acacia Ridge QLD 4110
www.minecorp.com.au
Sent Via a Mobile Device.

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Shawn Plummer
Sent: Friday, 31 August 2012 11:37 AM
To: RT Users
Subject: Re: [rt-users] Users Autocomplete not working 4.0.7?

I further broke the query down.

SELECT DISTINCT main.id, main.EmailAddress, main.name
FROM Users main
JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
WHERE (Principals_1.Disabled = ‘0’)
AND (
lower(main.RealName) LIKE ‘%taf2%’
OR lower(main.EmailAddress) LIKE ‘taf2%’
OR lower(main.Name) LIKE ‘taf2%’
)
AND (lower(Principals_1.PrincipalType) = ‘user’)
;

Returns the user record (I added emailaddress and name to the query values to verify they were not empty.)

Adding this line makes it return no values.

AND (
main.EmailAddress != ‘’
AND main.EmailAddress IS NOT NULL
)

Again, I can see the EmailAddress is populated as is name.

  • Shawn Plummer
    Systems Manager

On Aug 30, 2012, at 8:55 PM, Shawn Plummer plummer@geneseo.edu wrote:

Thanks for breaking those up! I am digging into the SQL tonight too. The distinct query appears to return no results in my testing so far.

As for the username change, that user was easier to isolate since it had less activity than I did when our DBA ran the query. The user definitely exists, has an email, and is enabled. We do use external auth and LDAP Import. Both are still working since the upgrade.

select id, Name, EmailAddress from Users where Name LIKE ‘taf2%’ or EmailAddress LIKE ‘taf2%’;
returns the user.

  • Shawn Plummer
    Systems Manager

On Aug 30, 2012, at 8:09 PM, Chris O’Kelly Chris.okelly@minecorp.com.au wrote:

Hi Guys,

Have you tried separating out the subqueries there?
If it were me that’s what I would look at next. Here they are split up into individual queries (where aliases weren’t used already I have given them generic names):

distinctQuery:
SELECT DISTINCT main.id
FROM Users main
JOIN Principals Principals_1 ON ( Principals_1.id = main.id )
WHERE (Principals_1.Disabled = ‘0’)
AND (
lower(main.RealName) LIKE ‘%taf2%’
OR lower(main.EmailAddress) LIKE ‘taf2%’
OR lower(main.Name) LIKE ‘taf2%’
)
AND (lower(Principals_1.PrincipalType) = ‘user’)
AND (
main.EmailAddress != ‘’
AND main.EmailAddress IS NOT NULL
)

limitQuery:
SELECT main.*
FROM distinctquery, Users main
WHERE (main.id = distinctquery.id)
ORDER BY main.Name ASC

thirdQuery:
SELECT limitquery.*,rownum limitrownum
FROM limitquery
WHERE rownum <= 10

finalQuery:
SELECT *
FROM thirdQuery
WHERE limitrownum >= 1;

so first run distinctQuery - This part should essentially get you the list of users who match what you are typing (in this case, users whose real name contains taf2 or whose email or username starts with taf2; who are also user’s with valid email addresses; who are not disabled). If this returns no rows there are serious problems - either there is definitely no user matching that condition or there are serious Db issues. The next three queries are trivial really. limitQuery selects the id of the matched users and orders by username. thirdQuery (I’m pretty creative when it comes to naming) limits the results to 10.

but yeah, to reiterate, I would suggest just running the deepest level query (the one I have marked distinctQuery above) to see if you get results from that. If you do, heck the other queries in order to find the point of failure. Otherwise, all I can really suggest is to take a serious look at the Users table and confirm there is a user there who matches the conditions. Make absolute sure the user is not disabled, make absolute sure that taf2 appears somewhere in the Real Name field, NOT the nickname field (or add nickname to $UserAutocompleteFields in RT_SiteConfig.pm). If your user’s are synced from LDAP/AD, then they may have these details in that system but the configuration has not yet been setup to sync these into LDAP.

I saw in an earlier post you were checking with plummer, your username, but in these queries it suggests we were looking for taf2. If taf2 is not yourself you may be running into a confusion I experienced when I installed RT - that is, I set up the External Auth plugin but not the LDAP Import plugin. Your users may not yet be in the RT database if they have not yet logged in if this is the case.

I know I’ve covered a bunch of fairly basic things here, so I am really sorry if this is all stuff you have already checked out. It can be tough to guess someone’s level of expertise over the internet, so while it’s likely you know a whole lot more than me, I didn’t want to leave anything out that might help you.

HTH

Regards

Chris O’Kelly
Web Administrator

Minecorp Australia
P: 07 3723 1000
M: 0450 586 190

Minecorp Australia
37 Murdoch Circuit
Acacia Ridge QLD 4110
www.minecorp.com.au
Sent Via a Mobile Device.

-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Shawn Plummer
Sent: Friday, 31 August 2012 9:28 AM
To: RT Users
Subject: Re: [rt-users] Users Autocomplete not working 4.0.7?

Looks like it returns no rows. So that could be a problem!

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 19:24:24 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT * FROM ( SELECT limitquery.,rownum limitrownum FROM ( SELECT main. FROM ( SELECT DISTINCT main.id FROM Users main JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) WHERE (Principals_1.Disabled = ‘0’) AND (lower(main.RealName) LIKE ‘%taf2%’ OR lower(main.EmailAddress) LIKE ‘taf2%’ OR lower(main.Name) LIKE ‘taf2%’) AND (lower(Principals_1.PrincipalType) = ‘user’) AND (main.EmailAddress != ‘’ AND main.EmailAddress IS NOT NULL) ) distinctquery, Users main WHERE (main.id = distinctquery.id) ORDER BY main.Name ASC ) limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1;

no rows selected

SQL>

On Aug 30, 2012, at 4:49 PM, Thomas Sibley trs@bestpractical.com wrote:

Please keep replies on the list for the benefit of folks searching for
answers later.

On 08/30/2012 01:16 PM, Shawn Plummer wrote:

My DBA tells me that this is the query she sees in the database when
the autocomplete fires:

“SELECT * FROM ( SELECT limitquery.,rownum limitrownum FROM (
SELECT main. FROM ( SELECT DISTINCT main.id FROM Users main JOIN
Principals Principals_1 ON ( Principals_1.id = main.id ) WHERE
(Principals_1.Disabled = ‘0’) AND (lower(main.RealName) LIKE
‘%taf2%’ OR lower(main.EmailAddress) LIKE ‘taf2%’ OR
lower(main.Name) LIKE ‘taf2%’) AND
(lower(Principals_1.PrincipalType) = ‘user’) AND (main.EmailAddress
!= ‘’ AND main.EmailAddress IS NOT NULL) ) distinctquery, Users
main WHERE (main.id = distinctquery.id) ORDER BY main.Name ASC )
limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1”

If you run that (awful awful) query by hand, what does it get you?

14

According to this Oracle empty string = null « Semicolon and the oracle DBA and developers here testing against ‘’ an empty string will not work with oracle.

Now I will just dig around and pull that query out of the code and I should be good.

  • Shawn Plummer
    Systems ManagerOn Aug 30, 2012, at 10:03 PM, Shawn Plummer plummer@geneseo.edu wrote:

Running that query returns no results.

I will look into the indexing. I know we run the rt full text indexing command every 15 minutes. As for the database indexes I will have to look into it.

  • Shawn Plummer
    Systems Manager
15

I have fixed the problem by editing $RTHOME/share/html/Helpers/Autocomplete/Users

Anyone with Oracle will probably need to do this:
changed lines 119-120 From

$users->Limit( FIELD => $return, OPERATOR => ‘!=’, VALUE => ‘’ );
$users->Limit( FIELD => $return, OPERATOR => ‘IS NOT’, VALUE => ‘NULL’, ENTRYAGGREGATOR => ‘AND’ );

To Just:
$users->Limit( FIELD => $return, OPERATOR => ‘IS NOT’, VALUE => ‘NULL’ );

  • Shawn Plummer
    Systems ManagerOn Aug 31, 2012, at 9:40 AM, Shawn Plummer plummer@geneseo.edu wrote:

According to this http://thunderguy.com/semicolon/2003/04/26/oracle-empty-string-null/ and the oracle DBA and developers here testing against ‘’ an empty string will not work with oracle.

Now I will just dig around and pull that query out of the code and I should be good.

  • Shawn Plummer
    Systems Manager

On Aug 30, 2012, at 10:03 PM, Shawn Plummer plummer@geneseo.edu wrote:

Running that query returns no results.

I will look into the indexing. I know we run the rt full text indexing command every 15 minutes. As for the database indexes I will have to look into it.

  • Shawn Plummer
    Systems Manager
16

Anyone with Oracle will probably need to do this:

Correct me if I’m wrong, but isn’t Oracle violating the SQL spec by
treating empty strings as equivalent to null? I expect that kind of
behavior from MySQL, but from Oracle? That’s unbelievable.

http://www.linkedin.com/in/paultomblin
http://careers.stackoverflow.com/ptomblin

17

Yeah it does violate spec but it’s not exactly new or shocking behavior from Db vendors unfortunately.

Anyone with Oracle will probably need to do this:

Correct me if I’m wrong, but isn’t Oracle violating the SQL spec by
treating empty strings as equivalent to null? I expect that kind of
behavior from MySQL, but from Oracle? That’s unbelievable.

–
http://www.linkedin.com/in/paultomblin
http://careers.stackoverflow.com/ptomblin

Regards

Chris O’Kelly
Web Administrator

Minecorp Australia
P: 07 3723 1000
M: 0450 586 190

Minecorp Australia
37 Murdoch Circuit
Acacia Ridge QLD 4110
www.minecorp.com.au

18

According to this
Oracle empty string = null « Semicolon
and the oracle DBA and developers here testing against ‘’ an empty
string will not work with oracle.

I’ve filed a bug for this regression and added you as a Cc so you’ll get
updates. Thanks for the digging.

http://issues.bestpractical.com/Ticket/Display.html?id=20873

Thomas