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

Re: mySQL Query not working

Expand Messages
  • mouss
    ... this doesn t work unless he mixes his tables more than a little... OP could try UNION, but better keep things simple: virtual_alias_maps =
    Message 1 of 6 , Aug 1 2:23 AM
    • 0 Attachment
      Willy De la Court a écrit :
      > 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
      >
      >

      this doesn't work unless he "mixes" his tables more than a little...

      OP could try UNION, but better keep things simple:

      virtual_alias_maps =
      proxy:mysql:/.../valias.cf
      proxy:mysql:/.../domainalias.cf

      and have one query for "user" aliases and one query for "domain" aliases.
    • Tino Donderwinkel
      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
      Message 2 of 6 , Aug 1 3:14 AM
      • 0 Attachment
        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'))

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

        Tino



        -----Oorspronkelijk bericht-----
        Van: owner-postfix-users@...
        [mailto:owner-postfix-users@...] Namens Tino Donderwinkel
        Verzonden: vrijdag 31 juli 2009 23:59
        Aan: postfix-users@...
        Onderwerp: mySQL Query not working

        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')

        Postmap -q @... mysql:/etc/postfix/aliases gives me NO results.

        If I change the query to:

        SELECT rcpt FROM aliases WHERE alias = '%s'

        Postmap -q @... mysql:/etc/postfix/aliases gives me the expected
        result; user@....

        But this way I have 'disabled' my own quick and dirty implementation of
        'alias' domains.
        What's wrong in the second part of the query? (the query works in mysql;
        there are no syntax errors)

        Btw; the domains table is like this;

        domain | alias_of
        domain.com
        aliasdomain.com | domain.com

        The (SELECT CONCAT('%u@', alias_of) FROM domains WHERE domain = '%d') part
        of the query located the aliases for an 'aliased' domain. (So if an alias
        for domain.com exists, this alias is also found in aliasdomain.com)

        Any ideas what's messing things up?

        Thanks,

        Tino Donderwinkel
      • 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 3 of 6 , Aug 1 5:55 AM
        • 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 4 of 6 , Aug 1 8:59 AM
          • 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.