Loading ...
Sorry, an error occurred while loading the content.

256265Re: [SOLVED] RE: mySQL Query not working

Expand Messages
  • mouss
    Aug 1, 2009
    • 0 Attachment
      Tino Donderwinkel a écrit :
      > The problems was in here:
      > http://www.postfix.org/mysql_table.5.html
      >
      > %u When the input key is an address of the form
      > user@domain, %u is replaced by the SQL
      > quoted local part of the address. Other-
      > wise, %u is replaced by the entire search
      > string. If the localpart is empty, the
      > query is suppressed and returns no results.
      >
      > The solution is very dirty;
      >
      > SELECT rcpt FROM aliases WHERE alias IN ('%s',(SELECT REPLACE('%s', '@%d',
      > CONCAT('@', alias_of)) FROM domains WHERE domain = '%d'))
      >

      What does this do that is not done with:

      SELECT alias_of FROM domains WHERE domain = '%d'

      ?


      > I'd really like to find out WHY the query is suppressed when %u is used and
      > %u is empty....
      >

      Since postfix will lookup domains in many maps, suppressing the lookup
      reduces overhead. for example, with the suggested query above, postfix
      doesn't need to search for "joe@domain'. it will directly search for
      '@domain'.

      for the rare case where you want to check that it is a domain (or
      @domain), use SQL for that. for example, if

      > select substring_index('user@domain', '@', 1);
      ...
      user
      > select substring_index('user@domain', '@', -1);
      ...
      domain
      >

      (yes, this doesn't handle the ugly 'joe@domain1@domain', but do you want
      to?).


      note that your lengthy query can be rewritten to avoid the overhead:


      SELECT rcpt FROM aliases, domains WHERE domain = '%d' AND
      alias = CONCAT(
      substring_index('%s', '@', 1),
      '@',
      alias_of
      )
    • Show all 6 messages in this topic