Well,. This test was done on mysql 3.23.49, so dono :)
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Ilja Booij
Sent: Friday, March 05, 2004 7:56 PM
To: dbmail@dbmail.org
Subject: Re: [Dbmail] CAP domain results in "no such user" mail bounce
Hmm,
I read somewhere that MySQL would *not* use the index when doing this.
This might have changed then.. (Or maybe I, or the other source, were
completely wrong). Perhaps my own test was wrong. Maybe the table was
too small, and the planner found that a sequential search was cheaper?
Anyway: If MySQL is able to use the index regardless of the
lower-clause, then the current code will run without any problems. In
the PostgreSQL tables, an index is made for lower(alias), and in MySQL
this is not needed.
when searching for an alias, this is done case-insensitively, by always
using lower(alias).
Problem solved, I guess?
John, thanks for finding this out :)
Ilja
John Hansen wrote:
>>Problem is that MySQL will not use the index if the query says
>
> 'lower(column)'.
>
> mysql> explain select a from test where lower(a)=lower('abc');
>
+-------+-------+---------------+------------+---------+------+------+--
> -----------------------+
> | table | type | possible_keys | key | key_len | ref | rows |
> Extra |
>
+-------+-------+---------------+------------+---------+------+------+--
> -----------------------+
> | test | index | NULL | test_index | 201 | NULL | 3 |
> where used; Using index |
>
+-------+-------+---------------+------------+---------+------+------+--
> -----------------------+
> 1 row in set (0.00 sec)
>
> The above tells me that's not true.
>
> ... John
>
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
> Behalf Of Ilja Booij
> Sent: Thursday, March 04, 2004 10:11 PM
> To: dbmail@dbmail.org
> Subject: Re: [Dbmail] CAP domain results in "no such user" mail bounce
>
> Hi
>
> John Hansen wrote:
>
>
>>Ok,...
>>
>>In mysql you only need an index on column. It does not need an index
>>on
>>lower(column) as it will still use the index, so that's good news, no?
>
> Problem is that MySQL will not use the index if the query says
> 'lower(column)'.
>
>>You still need constraints tho, other wise it could be possible to add
>
>
>>two or more identical columns.
>
> Inserting two similar aliases is no problem. In fact, having several
> similar aliases is a feature :) Usernames must be unique though.
>
>>Unique index on lower() in pg will work,. But dono how to do that in
>>mysql.
>
> In MySQL, having a unique index will work. The uniqueness is tested
> case-insensitively. I've just run a quick test to verify this.
>
> Ilja
>
>>Regards,
>>
>>John
>>
>>-----Original Message-----
>>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
>>Behalf Of Ilja Booij
>>Sent: Thursday, March 04, 2004 1:32 AM
>>To: dbmail@dbmail.org
>>Subject: Re: [Dbmail] CAP domain results in "no such user" mail bounce
>>
>>Just when I was ready to commit the changes, I found out that MySQL
>>cannot have an index like:
>>
>>INDEX (lower(alias)), because it cannot have expressions in an index..
>>
>>So, the whole plan is a no-go. Unless we can come up with some other
>>way to do the same thing. We could change all strings that we need to
>
> check
>
>> case insensitively to lowercase before feeding it them to the query
>>for instance. Are there any convenient functions functions for this
>>(that also work with characters other than A-Z?
>>
>>Ilja
>>
>>
>>
>>
>>>>-----Original Message-----
>>>>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
>>>>Behalf Of Ilja Booij
>>>>Sent: Wednesday, March 03, 2004 11:30 PM
>>>>To: dbmail@dbmail.org
>>>>Subject: Re: [Dbmail] CAP domain results in "no such user" mail
>>>>bounce
>>>>
>>>>well, after some more reading, I guess it's a bit different:
>>>>
>>>>IMAP RFC3501 takes no position on case sensitivity in mailbox names.
>>>>So, we can support either case sensitive or case insensitive mailbox
>>>>names. Currently, mailboxes are case sensitive when using
>>>>PostgreSQL,
>>
>>
>>>>and case insensitive when using MySQL. This might not be what we
>>>>want. We would probably like to have case sensitivity to be
>>>>consistent, whichever database backend we use.
>>>>
>>>>any opinions?
>>>>
>>>>Ilja
>>>>
>>>>John Hansen wrote:
>>>>
>>>>
>>>>
>>>>
>>>>>Ilja,
>>>>>
>>>>>If you wan't RFC'ism, you should put code in the software to do the
>>>>>comparison, not in the SQL, mysql is not case sensitive,. So on
>>>>>mysql dbmail would not conform to the RFC in this case.
>>>>>
>>>>>... John
>>>>>
>>>>>-----Original Message-----
>>>>>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
>>>>>Behalf Of Ilja Booij
>>>>>Sent: Wednesday, March 03, 2004 10:05 PM
>>>>>To: dbmail@dbmail.org
>>>>>Subject: Re: [Dbmail] CAP domain results in "no such user" mail
>>>>>bounce
>>>>>
>>>>>In DBMail 1.2.3 the queries were all defined in the backend driver.
>>>>>In
>>>>
>>>>
>>>>
>>>>>DBMail 2.0, the backend driver has become much smaller and simpler,
>>>>>and most functionality has been moved to db.c, which is in use by
>>>>>all
>>>>
>>>>
>>>>(read:
>>>>
>>>>
>>>>
>>>>>both) backends.
>>>>>
>>>>>Anyway, your suggestions should work.
>>>>>
>>>>>By the way, I don't think we should lowercase the mailboxes, as RFC
>>>>>3501 takes no position on case-sensitivity of mailbox names, except
>>>>>for "INBOX", which should always be case insensitive.
>>>>>
>>>>>Case insensitivity is limited to aliases (including domain aliases)
>>>>>and usernames, I guess.
>>>>>
>>>>>Ilja
>>>>>
>>>>>John Hansen wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>I'm confused,.... aren't the sql queries defined in each backend
>>>>>
>>>>>
>>>>>driver?
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>If not,
>>>>>>lower(column)=lower("value%"), and
>>>>>>lower(column) like lower("value%")
>>>>>>
>>>>>>should do the trick.
>>>>>>
>>>>>>... John
>>>>>>-----Original Message-----
>>>>>>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
>>>>>>Behalf Of Ilja Booij
>>>>>>Sent: Wednesday, March 03, 2004 8:47 PM
>>>>>>To: dbmail@dbmail.org
>>>>>>Subject: Re: [Dbmail] CAP domain results in "no such user" mail
>>>>>>bounce
>>>>>>
>>>>>>We can't use ILIKE, as it's not supported by MySQL. It's also not
>>>>>>a
>>
>>
>>>>>>part of SQL92, is it? I'll look for a way around this.
>>>>>>
>>>>>>Ilja
>>>>>>
>>>>>>John Hansen wrote:
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Probably a bug from being ported to postgres, as mysql is not
>>>>>>>case
>>
>>
>>>>>>>sensitive, but postgresql is.
>>>>>>>
>>>>>>>As such, all comparisons in where clauses should be cast using
>>>>>>>lower()
>>>>>>
>>>>>>
>>>>>>
>>>>>>>on both sides of the comparison sign. Or in the case of LIKE, use
>>>>>>
>>>>>>
>>>>>>ILIKE.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Regards,
>>>>>>>
>>>>>>>John
>>>>>>>
>>>>>>>-----Original Message-----
>>>>>>>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
>>>>>>>Behalf Of David
>>>>>>>Sent: Tuesday, February 24, 2004 9:47 AM
>>>>>>>To: dbmail@dbmail.org
>>>>>>>Subject: [Dbmail] CAP domain results in "no such user" mail
>>>>>>>bounce
>>>>>>>
>>>>>>>
>>>>>>>I noticed that when dbmail checks for a delivery point, if the
>>>>>>>domain
>>>>
>>>>
>>>>
>>>>>>>name does not match one listed in the aliases table case for
>>>>>>>case,
>>
>>
>>>>>>>it
>>>>
>>>>
>>>>
>>>>>>>will bounced the mail with "so such user". Has this comparison
>>>>>>>always
>>>>>>
>>>>>>
>>>>>>
>>>>>>>been case sensitive? For example, if [EMAIL PROTECTED] is in the
>>>>>>>aliases
>>>>>>
>>>>>>
>>>>>>
>>>>>>>table and then I get a mail address to [EMAIL PROTECTED], dbmail
>>>>>>>rejects
>>>>
>>>>
>>>>
>>>>>>>it. Actually it will reject anything not spelled exactly as
>>>>>>>"example.com". This is the error message generated.
>>>>>>>
>>>>>>>dbmail/smtp[31935]: bounce.c,bounce: sending 'no such user'
>>>>>>>bounce
>>
>>
>>>>>>>for
>>>>>>
>>>>>>
>>>>>>
>>>>>>>destination [EMAIL PROTECTED]
>>>>>>>
>>>>>>>Is this a postfix problem, PostgreSQL problem, or a dbmail
>>
>>problem?
>>
>>
>>>>>>>__________________________________
>>>>>>>Do you Yahoo!?
>>>>>>>Yahoo! Mail SpamGuard - Read only the mail you want.
>>>>>>>http://antispam.yahoo.com/tools
>>>>>>>_______________________________________________
>>>>>>>Dbmail mailing list
>>>>>>>Dbmail@dbmail.org
>>>>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>>>>>_______________________________________________
>>>>>>>Dbmail mailing list
>>>>>>>Dbmail@dbmail.org
>>>>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>>>>
>>>>>>
>>>>>>_______________________________________________
>>>>>>Dbmail mailing list
>>>>>>Dbmail@dbmail.org
>>>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>>>>
>>>>>>
>>>>>>_______________________________________________
>>>>>>Dbmail mailing list
>>>>>>Dbmail@dbmail.org
>>>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>>>
>>>>>
>>>>>_______________________________________________
>>>>>Dbmail mailing list
>>>>>Dbmail@dbmail.org
>>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>>>
>>>>>
>>>>>_______________________________________________
>>>>>Dbmail mailing list
>>>>>Dbmail@dbmail.org
>>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>>
>>>>
>>>>_______________________________________________
>>>>Dbmail mailing list
>>>>Dbmail@dbmail.org
>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>>
>>>>
>>>>_______________________________________________
>>>>Dbmail mailing list
>>>>Dbmail@dbmail.org
>>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>>
>>>_______________________________________________
>>>Dbmail mailing list
>>>Dbmail@dbmail.org
>>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>
>>_______________________________________________
>>Dbmail mailing list
>>Dbmail@dbmail.org
>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>
>>
>>_______________________________________________
>>Dbmail mailing list
>>Dbmail@dbmail.org
>>https://mailman.fastxs.nl/mailman/listinfo/dbmail
>
> _______________________________________________
> Dbmail mailing list
> Dbmail@dbmail.org
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>
>
> _______________________________________________
> Dbmail mailing list
> Dbmail@dbmail.org
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
Dbmail mailing list
Dbmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
