Problems with postfix-mysql

Discussion in 'Installation/Configuration' started by Pelle, Nov 6, 2005.

Thread Status:
Not open for further replies.
  1. Pelle

    Pelle New Member

    I have followered the tut:
    http://howtoforge.com/virtual_postfix_mysql_quota_courier

    I finished the tutorial with no problems, now I'm trying to test the mail system by sending a mail to a given email I have put in to the database!

    It looks like there are severel problems, but the first is that postfix can not access the mysql database?

    syslog:
    Code:
    Nov  6 05:22:44 houston2 postfix/smtpd[32212]: connect from xproxy.gmail.com[66.249.82.199]
    Nov  6 05:22:45 houston2 postfix/proxymap[32213]: warning: connect to mysql server 127.0.0.1: #HY000Host 'gmlitconsultis' is not allowed to connect to this MySQL server
    Nov  6 05:22:45 houston2 postfix/proxymap[32213]: warning: connect to mysql server 127.0.0.1: #HY000Host 'gmlitconsultis' is not allowed to connect to this MySQL server
    Nov  6 05:22:45 houston2 postfix/trivial-rewrite[32214]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,100): table lookup problem
    Nov  6 05:22:46 houston2 postfix/smtpd[32212]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
    
    How can I solve this, Have tried looking for solutions for the connect problem (found this http://forums.mysql.com/read.php?86,23619,23619#msg-23619 ) but dosent help, because the solution there was to create the database user, but I have.. and the password is correct!

    Then the next problem I think I have a solution for:
    Code:
    /etc/postfix/mysql-virtual_domains.cf(0,100): table lookup problem
    
    If I look at this file then

    Code:
    user = mail_admin
    password = mail_admin_password
    dbname = mail
    table = domains
    [COLOR=Yellow]select_field = 'virtual'[/COLOR]
    where_field = domain
    hosts = 127.0.0.1
    
    but in this table, 'virtual' dosent exist only the collumm "domain" do.
    Should this bee domain instead?
     
  2. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    The table lookup problem is resulting from the problem that your user is not allowed to connect to the MySQL database.

    Did you grant the correct privileges to that user? Did you run
    Code:
    FLUSH PRIVILEGES;
    afterwards? This is important because the changes become valid only after that command.
     
  3. Pelle

    Pelle New Member

    Yes I used FLUSH PRIVILEGES;

    Did it again... But no change...

    I tried to logon with the user and pass from the console and it worked
    Code:
    houston2:/etc/postfix# mysql -u pureftpd -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 3847 to server version: 4.1.11-Debian_4sarge2-log
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> show databases;
    
     
  4. Pelle

    Pelle New Member

    Again I have tried to change the hosts with the domain for the server in mysql-*.cf files

    Code:
    Nov  6 08:59:49 houston2 postfix/smtpd[1462]: connect from zproxy.gmail.com[64.233.162.202]
    Nov  6 08:59:49 houston2 postfix/proxymap[1463]: warning: connect to mysql server houston2.ustabilserverplads.dk: Can't connect to MySQL server on 'houston2.ustabilserverplads.dk' (111)
    Nov  6 08:59:49 houston2 postfix/proxymap[1463]: warning: connect to mysql server houston2.ustabilserverplads.dk: Can't connect to MySQL server on 'houston2.ustabilserverplads.dk' (111)
    Nov  6 08:59:49 houston2 postfix/trivial-rewrite[1464]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,100): table lookup problem
    Nov  6 08:59:50 houston2 postfix/smtpd[1462]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
    Nov  6 08:59:50 houston2 postfix/smtpd[1462]: warning: problem talking to service rewrite: Success
    Nov  6 08:59:50 houston2 postfix/master[1458]: warning: process /usr/lib/postfix/trivial-rewrite pid 1464 exit status 1
    Nov  6 08:59:51 houston2 postfix/trivial-rewrite[1465]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,100): table lookup problem
    Nov  6 08:59:52 houston2 postfix/smtpd[1462]: warning: premature end-of-input on private/rewrite socket while reading input attribute name
    Nov  6 08:59:52 houston2 postfix/smtpd[1462]: warning: problem talking to service rewrite: Success
    Nov  6 08:59:52 houston2 postfix/master[1458]: warning: process /usr/lib/postfix/trivial-rewrite pid 1465 exit status 1
    Nov  6 08:59:52 houston2 postfix/master[1458]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling
    
    any idea?


    I have pureftpd-mysql using the same database, and here there is no problem for this to use the mysql!
     
    Last edited: Nov 6, 2005
  5. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    From your log file I see that Postfix tries to connect to MySQL on houston2.ustabilserverplads.dk instead of 127.0.0.1. So either you allow remote connections to that MySQL user, or you change the configuration so that Postfix tries to connect on 127.0.0.1.
     
  6. Pelle

    Pelle New Member

    Forgot to mention that I changed the hosts on purpose, to see if it will make anydifferens!

    But no, the same log file is being written just with 127.0.0.1 instead of houston2.ustabilserverplads.dk and still having problems.
     
  7. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Please post the output of
    Code:
    netstat -tap
     
  8. Pelle

    Pelle New Member

    Here:

    Code:
            Active Internet connections (servers and established)
    Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
    tcp        0      0 *:imaps                 *:*                     LISTEN     20482/couriertcpd
    tcp        0      0 *:tcpmux                *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:20034                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:pop3s                 *:*                     LISTEN     20393/couriertcpd
    tcp        0      0 *:32771                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:32772                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:40421                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:32773                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:32774                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 houston2.ustabils:10024 *:*                     LISTEN     18883/amavisd (mast
    tcp        0      0 houston2.ustabils:10025 *:*                     LISTEN     27690/master
    tcp        0      0 *:31337                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 houston2.ustabils:mysql *:*                     LISTEN     15027/mysqld
    tcp        0      0 *:ircd                  *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:systat                *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:pop3                  *:*                     LISTEN     30180/couriertcpd
    tcp        0      0 *:5742                  *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:imap2                 *:*                     LISTEN     30164/couriertcpd
    tcp        0      0 *:sunrpc                *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:finger                *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:netstat               *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:www                   *:*                     LISTEN     970/apache2
    tcp        0      0 *:54320                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:sieve                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:27665                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:ingreslock            *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:ftp                   *:*                     LISTEN     20841/pure-ftpd (SE
    tcp        0      0 170.70-86-143.re:domain *:*                     LISTEN     7263/named
    tcp        0      0 houston2.ustabil:domain *:*                     LISTEN     7263/named
    tcp        0      0 *:ssh                   *:*                     LISTEN     1435/sshd
    tcp        0      0 *:nntp                  *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:socks                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:smtp                  *:*                     LISTEN     27690/master
    tcp        0      0 *:12345                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 houston2.ustabilser:953 *:*                     LISTEN     7263/named
    tcp        0      0 *:12346                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:https                 *:*                     LISTEN     970/apache2
    tcp        0      0 *:635                   *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:49724                 *:*                     LISTEN     13755/portsentry
    tcp        0      0 *:uucp                  *:*                     LISTEN     13755/portsentry
    tcp        0   4096 170.70-86-143.rever:ssh port363.ds1-abc.a:10846 ESTABLISHED27449/1
    tcp        0      0 170.70-86-143.rever:ssh port363.ds1-abc.a:10845 ESTABLISHED27447/0
    
    
     
  9. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Ok, and what's in /etc/mysql/my.cnf?
     
  10. Pelle

    Pelle New Member

    Code:
    [client]
    port		= 3306
    socket		= /var/run/mysqld/mysqld.sock
    
    # Here is entries for some specific programs
    # The following values assume you have at least 32M ram
    
    # This was formally known as [safe_mysqld]. Both versions are currently parsed.
    [mysqld_safe]
    socket		= /var/run/mysqld/mysqld.sock
    nice		= 0
    
    [mysqld]
    #
    # * Basic Settings
    #
    user		= mysql
    pid-file	= /var/run/mysqld/mysqld.pid
    socket		= /var/run/mysqld/mysqld.sock
    port		= 3306
    basedir		= /usr
    datadir		= /var/lib/mysql
    tmpdir		= /tmp
    language	= /usr/share/mysql/english
    skip-external-locking
    #
    # For compatibility to other Debian packages that still use
    # libmysqlclient10 and libmysqlclient12.
    old_passwords	= 1
    #
    # localhost which is more compatible and is not less secure.
    bind-address		= 127.0.0.1
    #
    # * Fine Tuning
    #
    key_buffer		= 16M
    max_allowed_packet	= 16M
    thread_stack		= 128K
    #
    # * Query Cache Configuration
    #
    query_cache_limit	= 1048576
    query_cache_size        = 16777216
    query_cache_type        = 1
    #
    # * Logging and Replication
    #
    # Both location gets rotated by the cronjob.
    # Be aware that this log type is a performance killer.
    #log		= /var/log/mysql.log
    #log		= /var/log/mysql/mysql.log
    #
    # Error logging goes to syslog. This is a Debian improvement :)
    #
    # Here you can see queries with especially long duration
    #log-slow-queries	= /var/log/mysql/mysql-slow.log
    #
    # The following can be used as easy to replay backup logs or for replication.
    #server-id		= 1
    log-bin			= /var/log/mysql/mysql-bin.log
    # See /etc/mysql/debian-log-rotate.conf for the number of files kept.
    max_binlog_size         = 104857600
    #binlog-do-db		= include_database_name
    #binlog-ignore-db	= include_database_name
    #
    # * BerkeleyDB
    #
    # According to an MySQL employee the use of BerkeleyDB is now discouraged
    # and support for it will probably cease in the next versions.
    skip-bdb
    #
    # * InnoDB
    #
    # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
    # Read the manual for more InnoDB related options. There are many!
    #
    # * Security Features
    #
    # Read the manual, too, if you want chroot!
    # chroot = /var/lib/mysql/
    #
    # If you want to enable SSL support (recommended) read the manual or my
    # HOWTO in /usr/share/doc/mysql-server/SSL-MINI-HOWTO.txt.gz
    # ssl-ca=/etc/mysql/cacert.pem
    # ssl-cert=/etc/mysql/server-cert.pem
    # ssl-key=/etc/mysql/server-key.pem
    
    
    
    [mysqldump]
    quick
    quote-names
    max_allowed_packet	= 16M
    
    [mysql]
    #no-auto-rehash	# faster start of mysql but no tab completition
    
    [isamchk]
    key_buffer		= 16M
    
     
  11. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Looks ok, too. :confused:
     
  12. Pelle

    Pelle New Member

    Hey,

    I have increased it to be something with the postfix-mysql because it gives the same error if I just set the database up on another machine...

    Should I remove the programs with the apt-get --purge remove atribute and start over or?
     
  13. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Do you use Debian testing or unstable? Because in the tutorial I use Debian stable...
     
  14. Pelle

    Pelle New Member

    I use stable (sarge)
     
  15. oaky

    oaky New Member

    I think I have a similar problem, but I am not sure... ;)

    Code:
    Nov  9 19:19:15 m34s12 courierpop3login: LOGIN FAILED, ip=[::ffff:62.47.187.132]
    Nov  9 19:34:26 m34s12 authdaemond.mysql: failed to connect to mysql server (server=localhost, userid=mail_admin )
    Nov  9 19:34:31 m34s12 courierpop3login: LOGIN FAILED, ip=[::ffff:62.47.187.132]
    Nov  9 19:41:11 m34s12 authdaemond.mysql: failed to connect to mysql server (server=localhost, userid=mail_admin )
    Nov  9 19:41:16 m34s12 courierpop3login: LOGIN FAILED, ip=[::ffff:62.47.187.132]
    Nov  9 19:43:14 m34s12 authdaemond.mysql: failed to connect to mysql server (server=localhost, userid=mail_admin )
    Nov  9 19:43:19 m34s12 courierpop3login: LOGIN FAILED, ip=[::ffff:62.47.187.132]
    Nov  9 19:47:01 m34s12 authdaemond.mysql: failed to connect to mysql server (server=localhost, userid=mail_admin )
    Nov  9 19:49:30 m34s12 authdaemond.mysql: failed to connect to mysql server (server=localhost, userid=mail_admin )
    Nov  9 19:49:35 m34s12 courierpop3login: LOGIN FAILED, ip=[::ffff:62.47.187.132]
    Nov  9 19:49:37 m34s12 authdaemond.mysql: failed to connect to mysql server (server=localhost, userid=mail_admin )
    Nov  9 19:49:42 m34s12 courierpop3login: LOGIN FAILED, ip=[::ffff:62.47.187.132]
    Nov  9 19:49:49 m34s12 authdaemond.mysql: failed to connect to mysql server (server=localhost, userid=mail_admin )
    Nov  9 19:49:54 m34s12 courierpop3login: LOGIN FAILED, ip=[::ffff:62.47.187.132]
    Nov  9 19:49:56 m34s12 authdaemond.mysql: failed to connect to mysql server (server=localhost, userid=mail_admin )
    Nov  9 19:50:01 m34s12 courierpop3login: LOGIN FAILED, ip=[::ffff:62.47.187.132]
    Nov  9 19:50:24 m34s12 authdaemond.mysql: failed to connect to mysql server (server=localhost, userid=mail_admin )
    Nov  9 19:50:29 m34s12 courierpop3login: LOGIN FAILED, ip=[::ffff:62.47.187.132]
    I was thinking if "server=localhost" could be the problem, because it should be "server=127.0.0.1"?!?!

    But in my /etc/mysql/my.cnf the "bind-adress" should be ok.

    Code:
    # Instead of skip-networking the default is now to listen only on
    # localhost which is more compatible and is not less secure.
    bind-address            = 127.0.0.1
    Could it be the problem? And if yes, how can I solve it?

    Thanks

    Michael
     
  16. till

    till Super Moderator Howtoforge Staff HowtoForge Supporter ISPConfig Developer

    Have a look in the table mysql.user and mysql.db. Is the host field of the user mail_admin set to "localhost" or "%"? Have you restarted the mysql server after changing the permissions and adding the mail_admin user?
     
  17. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Please check if your MySQL server is running.
     
  18. oaky

    oaky New Member

    Code:
    m34s12:~# mysql -u mail_admin -p
    Enter password:
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 159 to server version: 4.0.24_Debian-10sarge1-log
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> SELECT * from mail.users;
    +--------------------------+---------------+----------+
    | email                    | password      | quota    |
    +--------------------------+---------------+----------+
    | [email]michael@m34s12.vlinux.de[/email] | mne0b8lAMlBBw | 10485760 |
    | [email]markus@m34s12.vlinux.de[/email]  | Vh3YVzrrnNkRY | 10485760 |
    +--------------------------+---------------+----------+
    2 rows in set (0.00 sec)
    
    Mysql is running and mail_admin has access to mail.users.
     
  19. oaky

    oaky New Member

    Before it was "localhost" and "localhost.localdomain". (howto)

    I change it to "%" and stopped and restartet mysql.
     
  20. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    What's in /etc/courier/authmysqlrc? Do you have authmodulelist="authmysql" in /etc/courier/authdaemonrc?
     
Thread Status:
Not open for further replies.

Share This Page