Redundant database query?

[Ken Murchison]

Patrick Gibson wrote:
> Hi Ken,
> 
> Here is my configuration in imapd.conf:
> 
> sasl_sql_hostnames: localhost
> sasl_sql_user: cyrus
> sasl_sql_passwd: *******
> sasl_sql_database: RETRIX_TRANSITION
> sasl_sql_statement: select PASSWORD from USER_ACCOUNTS where USERNAME='%u'
> 
> This seems to be consistent with many of the examples I've found; 
> however, I'm open to new ideas that would eliminate this extra query.

You won't get rid of the second query for any of the SASL mechanisms. 
The only thing you can do is keep it from selecting the *same* column twice.


> 
> On 28-Oct-04, at 1:53 PM, Ken Murchison wrote:
> 
>> Patrick Gibson wrote:
>>
>>> I've got cyrus-imapd 2.2.8 setup to authenticate via a MySQL 
>>> database. I've enabled logging on the database while I'm setting 
>>> things up, and I've noticed that when I login, Cyrus runs the same 
>>> query twice:
>>>    47 Connect     cyrus at localhost on RETRIX_TRANSITION
>>>    47 Query       START TRANSACTION
>>>    47 Query       select PASSWORD from USER_ACCOUNTS where 
>>> USERNAME='patrick'
>>>    47 Query       select PASSWORD from USER_ACCOUNTS where 
>>> USERNAME='patrick'
>>>    47 Query       COMMIT
>>> Any idea as to why this would be happening?
>>
>>
>> What does your sql_select option look like?  What SASL mechanism 
>> generated this query.
>>
>> My guess is that SASL is trying to fetch both userPassword and a 
>> legacy mech-specific secret (e.g. cmusaslsecretDIGEST-MD5), but since 
>> you apparently aren't using the %p variable in your sql_select option 
>> (you've hardcoded the property as "PASSWORD", the same column gets 
>> selected twice.
>>
>> -- 
>> Kenneth Murchison     Oceana Matrix Ltd.
>> Software Engineer     21 Princeton Place
>> 716-662-8973 x26      Orchard Park, NY 14127
>> --PGP Public Key--    http://www.oceana.com/~ken/ksm.pgp
>>
> 
> ---
> Cyrus Home Page: http://asg.web.cmu.edu/cyrus
> Cyrus Wiki/FAQ: http://cyruswiki.andrew.cmu.edu
> List Archives/Info: http://asg.web.cmu.edu/cyrus/mailing-list.html
> 


-- 
Kenneth Murchison     Oceana Matrix Ltd.
Software Engineer     21 Princeton Place
716-662-8973 x26      Orchard Park, NY 14127
--PGP Public Key--    http://www.oceana.com/~ken/ksm.pgp
---
Cyrus Home Page: http://asg.web.cmu.edu/cyrus
Cyrus Wiki/FAQ: http://cyruswiki.andrew.cmu.edu
List Archives/Info: http://asg.web.cmu.edu/cyrus/mailing-list.html