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 On Aug 30, 2012, at 9:55 PM, Chris O'Kelly <[email protected]> wrote: > 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: [email protected] > [mailto:[email protected]] 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 <[email protected]> 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 <[email protected]> >> 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: [email protected] >>> [mailto:[email protected]] 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 <[email protected]> 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? >>> >> >
