True. But totally irrelevent to the discussion. Making MySQL treat domain names as case-sensitive doesn't help when domain names are case-insensitive.
> -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf > Of Curtis Maurand > Sent: Thursday, March 04, 2004 9:40 AM > To: [email protected] > Subject: RE: [Dbmail] CAP domain results in "no such user" mail bounce > > > > if you want MySQL to be case sensitive: > > create table tablename ( > name varchar(120) binary, > ..., > ); > > Curtis > > > On Wed, 3 Mar 2004, Rod K wrote: > > > If I may make a suggestion. > > > > Since MySQL is case-insensitive by default, there shouldn't be a need to > > worry about the case of the text in the field when using MySQL. > In the case > > of Postgres and the concern that an app other than dbmail is inserting a > > user, you can use INSERT/UPDATE triggers to coerce the domain > to lower case. > > Since this is enforced at the db level it wouldn't matter where the > > insert/update came from. > > > > > -----Original Message----- > > > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf > > > Of Ilja Booij > > > Sent: Wednesday, March 03, 2004 9:58 AM > > > To: [email protected] > > > Subject: Re: [Dbmail] CAP domain results in "no such user" mail bounce > > > > > > > > > The solution I can think of now is the following: > > > > > > make sure all aliases (or at least their domain part) go into the > > > database in lowercase. Whenever we check a domain alias, we first > > > convert the domain string to lowercase. We can then feed it to a query > > > without using lower(). > > > > > > I do see a problem with this though: What if somebody uses > another tool > > > to insert aliases into the database? Then a non-lowercase domain can > > > slip in, which will not be found in a case-sensitive search. > > > > > > Ilja > > > > > > > > > Ilja Booij wrote: > > > > > > > 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: [email protected] > > > >>> 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: [email protected] > > > >>>> 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: [email protected] > > > >>>>> 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: [email protected] > > > >>>>>> 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 > > > >>>>>> [email protected] > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > >>>>>> _______________________________________________ > > > >>>>>> Dbmail mailing list > > > >>>>>> [email protected] > > > >>>>>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > >>>>> > > > >>>>> > > > >>>>> > > > >>>>> _______________________________________________ > > > >>>>> Dbmail mailing list > > > >>>>> [email protected] > > > >>>>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > >>>>> > > > >>>>> > > > >>>>> _______________________________________________ > > > >>>>> Dbmail mailing list > > > >>>>> [email protected] > > > >>>>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > >>>> > > > >>>> > > > >>>> > > > >>>> _______________________________________________ > > > >>>> Dbmail mailing list > > > >>>> [email protected] > > > >>>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > >>>> > > > >>>> > > > >>>> _______________________________________________ > > > >>>> Dbmail mailing list > > > >>>> [email protected] > > > >>>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > >>> > > > >>> > > > >>> > > > >>> _______________________________________________ > > > >>> Dbmail mailing list > > > >>> [email protected] > > > >>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > >>> > > > >>> > > > >>> _______________________________________________ > > > >>> Dbmail mailing list > > > >>> [email protected] > > > >>> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > >> > > > >> > > > >> _______________________________________________ > > > >> Dbmail mailing list > > > >> [email protected] > > > >> https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > > _______________________________________________ > > > > Dbmail mailing list > > > > [email protected] > > > > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > _______________________________________________ > > > Dbmail mailing list > > > [email protected] > > > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > > > > > > > > > _______________________________________________ > > Dbmail mailing list > > [email protected] > > https://mailman.fastxs.nl/mailman/listinfo/dbmail > > > > -- > -- > Curtis Maurand > mailto:[EMAIL PROTECTED] > http://www.maurand.com > > > _______________________________________________ > Dbmail mailing list > [email protected] > https://mailman.fastxs.nl/mailman/listinfo/dbmail > >
