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

Re: mySQL Query not working

Expand Messages
  • Willy De la Court
    ... Hash: SHA1 ... Made a mistake here this is only the domain alias part the other query is the standard alias translation    SELECT       goto    FROM
    Message 1 of 6 , Aug 1, 2009
    • 0 Attachment
      -----BEGIN PGP SIGNED MESSAGE-----
      Hash: SHA1

      On Saturday 01 August 2009, Tino Donderwinkel wrote:
      > >
      > > On Friday 31 July 2009, Tino Donderwinkel wrote:
      > > > I have a virtual_alias map setup that uses this mySQL query;
      > > >
      > > > SELECT rcpt FROM aliases WHERE alias = '%s' OR alias = (SELECT
      > > > CONCAT('%u@', alias_of) FROM domains WHERE domain = '%d')
      > >
      > > hmm I think the way the % vars are replaced is the problem, try this.
      > >
      > > CONCAT('%u', '@', alias_of)
      > >
      > > or the easier way (this is what I use.
      > >
      > >   SELECT
      > >       goto
      > >    FROM
      > >       alias,
      > >       alias_domain
      > >    WHERE
      > >       alias_domain.alias_domain = '%d' AND
      > >       alias.address = concat('%u', '@', alias_domain.target_domain) AND
      > >       alias.active = 1 AND
      > >       alias_domain.active = 1
      > > I have all domains in 1 table. Changing that might have some implications.
      > >
      Made a mistake here this is only the domain alias part the other query is the
      standard alias translation

         SELECT
            goto
         FROM
            alias
         WHERE
            address = '%s' AND
            active = 1

      virtual_alias_maps =
         proxy:mysql:/etc/postfix/mysql/virtual-alias-maps.cf,
         proxy:mysql:/etc/postfix/mysql/virtual-alias-alias-maps.cf



      > I've also tried;
      >
      > SELECT rcpt FROM aliases WHERE alias IN ('%s', (SELECT CONCAT('%u', '@',
      > `alias_of`) FROM domains WHERE domain = '%d'))
      >
      > This works in mysql under all conditions...
      >
      > e.g. SELECT rcpt FROM aliases WHERE alias IN ('@...', (SELECT
      > CONCAT('', '@', `alias_of`) FROM domains WHERE domain = 'test.com'))
      > gives me: user@...

      I don't think postmap will ever replace %s with '@...' I can be mistaken.
      I think the %s is always a full address with user and domain part.
      Can someone comment on this?

      >> but postmap gives me an empty result...
      >>

      - --
      Simple things make people happy.
      Willy De la Court
      PGP Public Key at http://www.linux-lovers.be/download/public_key.asc
      PGP Key fingerprint = 784E E18F 7F85 9C7C AC1A D5FB FE08 686C 37C7 A689
      -----BEGIN PGP SIGNATURE-----
      Version: GnuPG v1.4.9 (GNU/Linux)

      iEYEARECAAYFAkp0O0kACgkQ/ghobDfHpon5kACggPwalWx5UyK8fY9DQDp/kfcC
      9uEAoJBbDJ0t//9UcVPS+EIKp9115HHg
      =wDI/
      -----END PGP SIGNATURE-----
    • mouss
      ... What does this do that is not done with: SELECT alias_of FROM domains WHERE domain = %d ? ... Since postfix will lookup domains in many maps, suppressing
      Message 2 of 6 , 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
        )
      Your message has been successfully submitted and would be delivered to recipients shortly.