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

mySQL Query not working

Expand Messages
  • Tino Donderwinkel
    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
    Message 1 of 6 , Jul 31, 2009
    • 0 Attachment
      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
      ... 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
      Message 2 of 6 , Jul 31, 2009
      • 0 Attachment
        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





        --
        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
      • 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 3 of 6 , Aug 1, 2009
        • 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 4 of 6 , Aug 1, 2009
          • 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 5 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 6 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.