postfix/trivial-rewrite

Discussion in 'HOWTO-Related Questions' started by digitalage, Jun 28, 2009.

  1. digitalage

    digitalage New Member

    Hello,

    I followed Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Debian Lenny) to configure email on my server. Everything went smooth and I could used it for about 1 month. For 1 day, no emails enter my server and some errors appear in logs:

    Code:
    Jun 28 12:04:43 hostname postfix/master[3302]: daemon started -- version 2.5.5, configuration /etc/postfix
    Jun 28 12:04:43 hostname postfix/qmgr[3310]: 58D86A34E0: from=<>, size=3758, nrcpt=1 (queue active)
    Jun 28 12:04:43 hostname postfix/proxymap[3317]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    Jun 28 12:04:43 hostname postfix/proxymap[3317]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    Jun 28 12:04:43 hostname postfix/trivial-rewrite[3316]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Jun 28 12:04:44 hostname postfix/master[3302]: warning: process /usr/lib/postfix/trivial-rewrite pid 3316 exit status 1
    Jun 28 12:04:44 hostname postfix/master[3302]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling
    Jun 28 12:04:45 hostname postfix/master[3302]: reload configuration /etc/postfix
    Jun 28 12:04:47 hostname postfix/pickup[3437]: 1FAABA3507: uid=0 from=<root>
    Jun 28 12:04:50 hostname postfix/master[3302]: reload configuration /etc/postfix
    Jun 28 12:05:44 hostname postfix/proxymap[3750]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    Jun 28 12:05:44 hostname postfix/proxymap[3750]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    Jun 28 12:05:44 hostname postfix/trivial-rewrite[3749]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Jun 28 12:05:45 hostname postfix/cleanup[3450]: warning: problem talking to service rewrite: Connection reset by peer
    Jun 28 12:05:45 hostname postfix/qmgr[3310]: warning: problem talking to service rewrite: Success
    Jun 28 12:05:45 hostname postfix/master[3302]: warning: process /usr/lib/postfix/trivial-rewrite pid 3749 exit status 1
    Jun 28 12:05:45 hostname postfix/master[3302]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling
    Jun 28 12:06:45 hostname postfix/proxymap[3755]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    Jun 28 12:06:45 hostname postfix/cleanup[3450]: 1FAABA3507: message-id=<20090628090447.1FAABA3507@server.myhost.com>
    Jun 28 12:06:45 hostname postfix/proxymap[3755]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    Jun 28 12:06:45 hostname postfix/trivial-rewrite[3754]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Jun 28 12:06:46 hostname postfix/qmgr[3310]: warning: problem talking to service rewrite: Success
    Jun 28 12:06:46 hostname postfix/master[3302]: warning: process /usr/lib/postfix/trivial-rewrite pid 3754 exit status 1
    Jun 28 12:06:46 hostname postfix/pickup[3437]: EEB19A33F7: uid=0 from=<root>
    Jun 28 12:06:46 hostname postfix/cleanup[3757]: EEB19A33F7: message-id=<20090628090646.EEB19A33F7@server.myhost.com>
    Jun 28 12:06:47 hostname postfix/trivial-rewrite[3758]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Jun 28 12:06:47 hostname postfix/pickup[3437]: EFA95A34E7: uid=0 from=<root>
    Jun 28 12:06:48 hostname postfix/qmgr[3310]: warning: problem talking to service rewrite: Success
    Jun 28 12:06:48 hostname postfix/cleanup[3757]: warning: problem talking to service rewrite: Connection reset by peer
    Jun 28 12:06:48 hostname postfix/master[3302]: warning: process /usr/lib/postfix/trivial-rewrite pid 3758 exit status 1
    Jun 28 12:06:49 hostname postfix/trivial-rewrite[3760]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Jun 28 12:06:50 hostname postfix/qmgr[3310]: warning: problem talking to service rewrite: Success
    Jun 28 12:06:50 hostname postfix/cleanup[3757]: warning: problem talking to service rewrite: Connection reset by peer
    Jun 28 12:06:50 hostname postfix/master[3302]: warning: process /usr/lib/postfix/trivial-rewrite pid 3760 exit status 1
    Jun 28 12:06:50 hostname postfix/master[3302]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling
    
    Note: the real domain name was replaced with "myhost.com"

    I really don't understand what's going on. I need some help, please.
     
    Last edited: Jun 29, 2009
  2. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    It seems as if MySQL isn't running. What's the output of
    Code:
    netstat -tap
    ?
     
  3. digitalage

    digitalage New Member

    # netstat -tap | grep mysql
    Code:
    tcp        0      0 localhost:mysql         *:*                     LISTEN      12566/mysqld
    tcp        0      0 localhost:mysql         localhost:51517         TIME_WAIT   -
    tcp        0      0 localhost:mysql         localhost:51516         TIME_WAIT   -
    tcp        0      0 localhost:mysql         localhost:51515         TIME_WAIT   -
    tcp        0      0 localhost:mysql         localhost:51514         TIME_WAIT   -
    
     
  4. digitalage

    digitalage New Member

    MySQL works fine, I guess. I can connect to databases using PhpMyAdmin. Also, if I run /etc/init.d/mysql restart, I get the following output:

    Code:
    Jun 29 17:22:48 hostname mysqld[13464]: 090629 17:22:48  InnoDB: Shutdown completed; log sequence number 0 233495
    Jun 29 17:22:48 hostname mysqld[13464]: 090629 17:22:48 [Note] /usr/sbin/mysqld: Shutdown complete
    Jun 29 17:22:48 hostname mysqld[13464]:
    Jun 29 17:22:48 hostname mysqld_safe[13997]: ended
    Jun 29 17:22:49 hostname mysqld_safe[14073]: started
    Jun 29 17:22:49 hostname mysqld[14077]: 090629 17:22:49  InnoDB: Started; log sequence number 0 233495
    Jun 29 17:22:49 hostname mysqld[14077]: 090629 17:22:49 [Note] /usr/sbin/mysqld: ready for connections.
    Jun 29 17:22:49 hostname mysqld[14077]: Version: '5.0.51a-24+lenny1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Debian)
    Jun 29 17:22:50 hostname /etc/mysql/debian-start[14117]: Upgrading MySQL tables if necessary.
    Jun 29 17:22:50 hostname /etc/mysql/debian-start[14123]: Looking for 'mysql' in: /usr/bin/mysql
    Jun 29 17:22:50 hostname /etc/mysql/debian-start[14123]: Looking for 'mysqlcheck' in: /usr/bin/mysqlcheck
    Jun 29 17:22:50 hostname /etc/mysql/debian-start[14123]: This installation of MySQL is already upgraded to 5.0.51a, use --force if you still need to run mysql_upgrade
    Jun 29 17:22:50 hostname /etc/mysql/debian-start[14129]: Checking for insecure root accounts.
    Jun 29 17:22:50 hostname /etc/mysql/debian-start[14133]: Triggering myisam-recover for all MyISAM tables
    Jun 29 17:23:18 hostname postfix/smtpd[30085]: fatal: watchdog timeout
    Jun 29 17:23:19 hostname postfix/master[3302]: warning: process /usr/lib/postfix/smtpd pid 30085 exit status 1
    Jun 29 17:23:19 hostname postfix/master[3302]: warning: /usr/lib/postfix/smtpd: bad command startup -- throttling
    Jun 29 17:23:48 hostname postfix/proxymap[3874]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    Jun 29 17:23:48 hostname postfix/proxymap[3874]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    Jun 29 17:23:48 hostname postfix/trivial-rewrite[14167]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Jun 29 17:23:48 hostname postfix/trivial-rewrite[14168]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Jun 29 17:23:48 hostname postfix/trivial-rewrite[14169]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Jun 29 17:23:48 hostname postfix/trivial-rewrite[14170]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Jun 29 17:23:48 hostname postfix/proxymap[23943]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    Jun 29 17:23:48 hostname postfix/proxymap[23943]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    Jun 29 17:23:48 hostname postfix/trivial-rewrite[14171]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Jun 29 17:23:48 hostname postfix/trivial-rewrite[14172]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    
    ... (the same message, several times)
    
    Jun 29 17:23:48 hostname postfix/trivial-rewrite[14216]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Jun 29 17:23:49 hostname postfix/smtpd[8074]: warning: problem talking to service rewrite: Success
    Jun 29 17:23:49 hostname postfix/smtpd[866]: warning: problem talking to service rewrite: Connection reset by peer
    Jun 29 17:23:49 hostname postfix/master[3302]: warning: process /usr/lib/postfix/trivial-rewrite pid 14167 exit status 1
    Jun 29 17:23:49 hostname postfix/master[3302]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling
    Jun 29 17:23:49 hostname postfix/smtpd[3678]: warning: problem talking to service rewrite: Success
    Jun 29 17:23:49 hostname postfix/smtpd[31378]: warning: problem talking to service rewrite: Connection reset by peer
    Jun 29 17:23:49 hostname postfix/master[3302]: warning: process /usr/lib/postfix/trivial-rewrite pid 14168 exit status 1
    Jun 29 17:23:49 hostname postfix/smtpd[32233]: warning: problem talking to service rewrite: Connection reset by peer
    Jun 29 17:23:49 hostname postfix/smtpd[30244]: warning: problem talking to service rewrite: Success
    Jun 29 17:23:49 hostname postfix/master[3302]: warning: process /usr/lib/postfix/trivial-rewrite pid 14169 exit status 1
    Jun 29 17:23:49 hostname postfix/smtpd[30193]: warning: problem talking to service rewrite: Success
    Jun 29 17:23:49 hostname postfix/master[3302]: warning: process /usr/lib/postfix/trivial-rewrite pid 14170 exit status 1
    Jun 29 17:23:49 hostname postfix/smtpd[31429]: warning: problem talking to service rewrite: Success
    Jun 29 17:23:49 hostname postfix/smtpd[30400]: warning: problem talking to service rewrite: Connection reset by peer
    Jun 29 17:23:49 hostname postfix/master[3302]: warning: process /usr/lib/postfix/trivial-rewrite pid 14171 exit status 1
    Jun 29 17:23:49 hostname postfix/smtpd[31807]: warning: problem talking to service rewrite: Connection reset by peer
    
    ... this group of messages, several times)
    
    Jun 29 17:23:48 hostname postfix/trivial-rewrite[14172]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    
    ... (the same message, several times)
    
    Jun 29 17:23:49 hostname postfix/smtpd[31429]: warning: problem talking to service rewrite: Success
    Jun 29 17:23:49 hostname postfix/smtpd[30400]: warning: problem talking to service rewrite: Connection reset by peer
    Jun 29 17:23:49 hostname postfix/master[3302]: warning: process /usr/lib/postfix/trivial-rewrite pid 14171 exit status 1
    Jun 29 17:23:49 hostname postfix/smtpd[31807]: warning: problem talking to service rewrite: Connection reset by peer
    
    ... this group of messages, several times)
    
    
     
  5. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    What's in /etc/hosts?
    Is the MySQL login that Postfix uses correct?
     
  6. digitalage

    digitalage New Member

    hostname:~# cat /etc/hosts
    Code:
    127.0.0.1       localhost
    127.0.1.1       hostname.myprovider.com        hostname
    
    # The following lines are desirable for IPv6 capable hosts
    ::1     localhost ip6-localhost ip6-loopback
    fe00::0 ip6-localnet
    ff00::0 ip6-mcastprefix
    ff02::1 ip6-allnodes
    ff02::2 ip6-allrouters
    ff02::3 ip6-allhosts
    Notes: My hostname was replaced with the word "hostname"


    I don't know how to answer to the 2nd question. I assume MySQL login is fine, otherwise it didn't work in first place for 1 month. I didn't do any upgrade or update to packages since the install. The MySQL user that Postfix is using is mail_admin.

    # postconf -n shows:
    Code:
    alias_database = hash:/etc/aliases
    alias_maps = hash:/etc/aliases
    append_dot_mydomain = no
    biff = no
    broken_sasl_auth_clients = yes
    config_directory = /etc/postfix
    content_filter = amavis:[127.0.0.1]:10024
    html_directory = /usr/share/doc/postfix/html
    inet_interfaces = all
    mailbox_size_limit = 0
    message_size_limit = 31457280
    mydestination = server.mydomain.com, localhost, localhost.localdomain
    myhostname = server.mydomain.com
    mynetworks = 127.0.0.0/8
    myorigin = /etc/mailname
    proxy_read_maps = $local_recipient_maps $mydestination $virtual_alias_maps $virtual_alias_domains $virtual_mailbox_maps $virtual_mailbox_domains $relay_recipient_maps $relay_domains $canonical_maps $sender_canonical_maps $recipient_canonical_maps $relocated_maps $transport_maps $mynetworks $virtual_mailbox_limit_maps
    readme_directory = /usr/share/doc/postfix
    receive_override_options = no_address_mappings
    recipient_delimiter = +
    relayhost =
    smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
    smtpd_banner = $myhostname ESMTP $mail_name (Debian/GNU)
    smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_unauth_destination
    smtpd_sasl_auth_enable = yes
    smtpd_sasl_authenticated_header = yes
    smtpd_tls_cert_file = /etc/postfix/smtpd.cert
    smtpd_tls_key_file = /etc/postfix/smtpd.key
    smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
    smtpd_use_tls = yes
    transport_maps = proxy:mysql:/etc/postfix/mysql-virtual_transports.cf
    virtual_alias_domains =
    virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf
    virtual_gid_maps = static:5000
    virtual_mailbox_base = /home/vmail
    virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf
    virtual_mailbox_limit_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailbox_limit_maps.cf
    virtual_mailbox_limit_override = yes
    virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf
    virtual_maildir_extended = yes
    virtual_maildir_limit_message = "The user you are trying to reach is over quota."
    virtual_overquota_bounce = yes
    virtual_uid_maps = static:5000
    Note: My domain was replaced with the word "mydomain.com"

    Searching the internet I found this link where someone suggests to grant privileges again to the MySQL user that Postfix is using. I'm afraid I don't know how to do this and if it's appropriate to my issue, so I didn't touch the server.

    Any idea?


    Edit: I found another user having the same issue. He gave up on finding the problem, but I wish I'll find the cause. Hope it won't take too long, otherwise I'll be forced to rebuild the server because my main emails comes on it.
     
    Last edited: Jun 30, 2009
  7. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    What's the output of
    Code:
    ifconfig
    ?
    Can you log in to MySQL like this?
    Code:
    mysql -u mail_admin -p
    Then type in the password that you use in the mysql*.cf files.
     
  8. digitalage

    digitalage New Member

    Everything looks good, either. I don't know why would you need ifconfig - I sent it as private message to stay away of public.


    I checked also passwords used in MySql, like this:
    # cat /etc/postfix/mysql-virtual_* | grep password
    Code:
    password = xxx
    password = xxx
    password = xxx
    password = xxx
    password = xxx
    password = xxx
    
    ... and I got the same answer. Then I logged into console of MySql:

    # /etc/postfix# mysql -u mail_admin -p
    Code:
    Enter password: xxx
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 41
    Server version: 5.0.51a-24+lenny1-log (Debian)
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mail               |
    +--------------------+
    2 rows in set (0.00 sec)
    
    mysql> use mail
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +----------------+
    | Tables_in_mail |
    +----------------+
    | domains        |
    | forwardings    |
    | transport      |
    | users          |
    +----------------+
    4 rows in set (0.00 sec)
    
    mysql> quit
    Bye
    
     
  9. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    Can you modify /etc/hosts so that it looks as follows?

    Code:
    127.0.0.1       localhost.localdomain localhost
    192.168.0.1      hostname.myprovider.com        hostname
    
    # The following lines are desirable for IPv6 capable hosts
    ::1     localhost ip6-localhost ip6-loopback
    fe00::0 ip6-localnet
    ff00::0 ip6-mcastprefix
    ff02::1 ip6-allnodes
    ff02::2 ip6-allrouters
    ff02::3 ip6-allhosts
     
  10. digitalage

    digitalage New Member

    I changed that and the issue is still there. During my research in logs, I found some lines which might or might not be related to this issue. I took them before the last change - you can see the date:

    Code:
    Jun 30 18:15:07 postfix/pickup[7303]: F0157A3551: uid=0 from=<root>
    Jun 30 18:15:13 postfix/master[4812]: warning: service "smtp" (25) has reached its process limit "100": new clients may experience noticeable delays
    Jun 30 18:15:13 postfix/master[4812]: warning: to avoid this condition, increase the process count in master.cf or reduce the service time per client
    Jun 30 18:15:13 postfix/master[4812]: warning: see http://www.postfix.org/STRESS_README.html for examples of stress-adapting configuration settings
    ...
    Jul  1 09:27:32 209-145-MZA-enternet postfix/smtpd[7724]: fatal: watchdog timeout
    Jul  1 09:27:33 postfix/master[4812]: warning: process /usr/lib/postfix/smtpd pid 7724 exit status 1
    Jul  1 09:27:33 postfix/master[4812]: warning: /usr/lib/postfix/smtpd: bad command startup -- throttling
    ...
    Jul  1 11:02:12 postfix/trivial-rewrite[4191]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    Jul  1 11:02:13 postfix/qmgr[4068]: warning: problem talking to service rewrite: Success
    Jul  1 11:02:13 postfix/master[3306]: warning: process /usr/lib/postfix/trivial-rewrite pid 4191 exit status 1
    Jul  1 11:02:13 postfix/master[3306]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling
    Jul  1 11:02:35 postfix/qmgr[4068]: fatal: watchdog timeout
    Jul  1 11:02:36 postfix/master[3306]: warning: process /usr/lib/postfix/qmgr pid 4068 exit status 1
    Jul  1 11:02:36 postfix/master[3306]: warning: /usr/lib/postfix/qmgr: bad command startup -- throttling
    

    I checked the config using the following commands:
    # cat /etc/postfix/mysql-virtual_domains.cf
    Code:
    user = mail_admin
    password = xxx
    dbname = mail
    query = SELECT domain AS virtual FROM domains WHERE domain='%s'
    hosts = 127.0.0.1
    
    # cat /etc/postfix/mysql-virtual_* | grep user
    Code:
    user = mail_admin
    user = mail_admin
    query = SELECT email FROM users WHERE email='%s'
    user = mail_admin
    user = mail_admin
    query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'
    user = mail_admin
    query = SELECT quota FROM users WHERE email='%s'
    user = mail_admin
    
    # cat /etc/postfix/mysql-virtual_* | grep dbname
    Code:
    dbname = mail
    dbname = mail
    dbname = mail
    dbname = mail
    dbname = mail
    dbname = mail
    
    # cat /etc/postfix/mysql-virtual_* | grep query
    Code:
    query = SELECT domain AS virtual FROM domains WHERE domain='%s'
    query = SELECT email FROM users WHERE email='%s'
    query = SELECT destination FROM forwardings WHERE source='%s'
    query = SELECT CONCAT(SUBSTRING_INDEX(email,'@',-1),'/',SUBSTRING_INDEX(email,'@',1),'/') FROM users WHERE email='%s'
    query = SELECT quota FROM users WHERE email='%s'
    query = SELECT transport FROM transport WHERE domain='%s'
    
    # cat /etc/postfix/mysql-virtual_* | grep hosts
    Code:
    hosts = 127.0.0.1
    hosts = 127.0.0.1
    hosts = 127.0.0.1
    hosts = 127.0.0.1
    hosts = 127.0.0.1
    hosts = 127.0.0.1
    
    Everything looks good. However, if I look at my logs, I see lines that I don't know to "translate". Particularly, these:
    Code:
    Jul  2 23:29:24 postfix/proxymap[3859]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    and
    Code:
    Jul  2 23:29:24 postfix/trivial-rewrite[3923]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem
    I don't know what is proxymap and proxy:mysql. Are you kind to explain me (maybe it's useful for others too), like to a novice, how the system works? How is the email received by the server and what is its way to the mailbox, in this particular setup?
     
  11. digitalage

    digitalage New Member

    I don't know if you noticed, just to make sure I'll tell you that I used different names for the name of the host and the name of the email server, such as this:


    # cat /etc/hosts
    Code:
    127.0.0.1       localhost.localdomain          localhost
    127.0.1.1       hostname.myprovider.com        hostname
    
    Relevant Postfix config:
    # postconf -n
    Code:
    ...
    append_dot_mydomain = no
    ...
    inet_interfaces = all
    ...
    mydestination = server.mydomain.com, localhost, localhost.localdomain
    myhostname = server.mydomain.com
    mynetworks = 127.0.0.0/8
    myorigin = /etc/mailname
    ...
    relayhost =
    ...
    
    # cat /etc/mailname
    Code:
    server.mydomain.com
    
    Please notice the name server.mydomain.com versus hostname.myprovider.com.

    server.mydomain.com is registered in my DNS - if I ping from outside, it resolves fine. hostname.myprovider.com is not registered in my DNS but it resolves because it's in my provider's DNS. There should be no reason why it wouldn't work this setup. Correct me if I'm wrong.
     
  12. digitalage

    digitalage New Member

    you're still with me?

    Falko, you're still with me? Please, give me a sign.
    I've my email down for more than one week and I desperately need to fix this.

    Thank you.
     
  13. falko

    falko Super Moderator Howtoforge Staff Moderator HowtoForge Supporter ISPConfig Developer

    I have no idea what's wrong... :(
     
  14. pilot9

    pilot9 New Member

    problem with tables....

    I had a very similar problem to the one laid out by digitalage with respect to log errors and some aspects of set up After fussing for several hours, I finally remembered that I was using prefixes for my Mysql tables... Not knowing how to make the prefixes explicit in the drupal-xxx-.cf files, I just added them directly to the table calls, so

    ....PREFIXmailfix_domains... and PREFIXmailfix_users...

    solved the problem.

    Hope this helps.

    regards,
    Erik
     
  15. digitalage

    digitalage New Member

    Thank you pilot9 for your response. It's now too late for me, I already reinstalled linux on that server.

    However, I'd like to know more details about this issue and how to avoid it in the future. I don't understand your case. Are you nice to explain a bit more detailed? If you can, please clear up the prefixes staff, as I don't understand why and when is it write to use them. I assume you installed drupal, and after that the hell began. Am I write?
     
  16. pilot9

    pilot9 New Member

    MySql prefixes

    Yes, I had installed Drupal, which gives you a nice option to use table prefixes for multisite installations in the same database. However, most recipes don't take them into consideration, so they are easy to miss. You are left with authentication errors, which are difficult to track sometimes. And as far as I know, the only more general solution to this sort of problem is keep careful track of everything! For relatively inexperienced sysadmin like myself, it is a mess and a climb up a steep learning curve... One general solution I would love to find (and have not found) is a comprehensive debugging recipe/howto, a series of steps to tackle certain kinds of very common errors (like authentication errors) where logs do not typically point to the problem directly...) When I have a little more time, I will sketch one out and maybe SPTM (smarter people than me) can flesh it out.

    In my case, I had to go through my postfix (also dovecot, PAM, SASL) configuration files and add the MySQL database table prefixes accordingly. I had been working from a combination of Falko Timme's Virtual Users And Domains With Postfix, Courier, MySQL And SquirrelMail (Debian Lenny) recipe along with Alex Saavedra's Drupal + Postfix Integration Under Ubuntu 8.04 (Hardy) recipe and Christoph Haas's excellent tutorial Howto: ISP-style Email Server with Debian-Etch and Postfix 2.3 (also a Lenny updated version.
    Basically, wherever a call was made to MySql, I needed to add table prefixes to the tables... so for example:

    I added table prefixe [--PREFIX--] to the lines below. So if my prefix was "MySite" then [--PREFIX--]sometable becomes MySitesometable .

    1. In /etc/postfix/sasl/smtpd.conf

    sql_select: SELECT pass FROM [--PREFIX--]users WHERE mail='%u' AND status=1

    Note: "users" is of course the table in the database with the passwords and so on. The database itself and the columns in the tables themselves do NOT need to be prefixed!

    2. In /etc/dovecot/dovecot-sql.conf

    password_query = SELECT mail AS user, pass AS password FROM [--PREFIX--]users WHERE mail='%u';

    Note: "user" and "password" above are column names to retrieve your user's signin names and pws to access their email. "users" again is the table where that info is stored (and so needs to be prefixed).

    3. and in /etc/pam.d/smtp

    auth required pam_mysql.so user=USER passwd=PW host=127.0.0.1 db=MYDB table=[--PREFIX--]users usercolumn=mail passwdcolumn=pass crypt=0

    account sufficient pam_mysql.so user=USER passwd=PW host=127.0.0.1 db=MYDB table=[--PREFIX--]users usercolumn=mail passwdcolumn=pass crypt=0

    Note that MYDB, USER and PW are the relevant Database, the system user that accesses the database (eg "Mail" or whatever) and its password.

    4. and in the Postfix configuration files in /etc/postfix/...

    4a. /etc/postfix/drupal-domains.cf

    query=SELECT domain_name AS domain FROM [--PREFIX--]mailfix_domains WHERE domain_name='%s'


    4b. /etc/postfix/drupal-mailboxes.cf

    query=SELECT CONCAT(md.domain_name, '/', LEFT(u.mail, LOCATE('@', u.mail) - 1),'/') AS maildir FROM [--PREFIX--]mailfix_domains md JOIN ([--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid) ON md.domain_id=mu.domain_id WHERE u.status=1 AND u.mail='%s'

    4c. /etc/postfix/drupal-forward.cf

    query=SELECT mu.forward FROM [--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid WHERE u.mail='%s' AND LENGTH(mu.forward)>0

    4d. /etc/postfix/drupal-recipient-bcc.cf

    query=SELECT mu.incoming_bcc FROM [--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid WHERE u.status=1 AND u.mail='%s' AND LENGTH(mu.incoming_bcc)>0

    4e. /etc/postfix/drupal-sender-bcc.cf

    query=SELECT mu.outgoing_bcc FROM [--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid WHERE u.status=1 AND u.mail='%s' AND LENGTH(mu.outgoing_bcc)>0

    4f. /etc/postfix/drupal-quota.cf

    query=SELECT mu.quota FROM [--PREFIX--]mailfix_users mu JOIN [--PREFIX--]users u ON mu.uid=u.uid WHERE u.mail='%s'


    The thing about MySQL is that it is very unforgiving. One little space or an added " ` " somewhere and you are going to get something like authentication errors. I spent a lot of time combing all the MySQL calls and then going ever so carefully over the whole process of how the different parts of the server call one another before I remembered these prefixes. The silver lining was taking a few steps up the learning curve...

    Pilot
     
  17. digitalage

    digitalage New Member

    great post

    Pilot, your answer is very detailed and much appreciated. Though the approach to multi-site it's logical, I wasn't aware of how to get there. Thank you for explanation and links. I'm sure many will find this post helpful.
     
  18. dipps

    dipps New Member

    Getting somewhere

    Hi guys, I was having a similar problem: after learning a lot of things I got past it, so thought I'd share. This site has helped me in the past.

    Nov 10 18:53:06 server2 postfix/trivial-rewrite[8681]: warning: connect to mysql server 127.0.0.1: Lost connection to MySQL server at 'reading initial communication packet', system error: 0
    Nov 10 18:53:06 server2 postfix/trivial-rewrite[8681]: fatal: mysql:/etc/postfix/mysql/virtual-alias-maps.cf(0,lock|fold_fix): table lookup problem​

    First thing I did was to check that the mysql queries worked from command line. I did that with postmap -q and mysql (logging onto mysql as the postfix user, same as postfix will do). This worked fine. I had a good look at it with phpmyadmin and the info seemed to be there, but still, trivial-rewrite couldn't do mysql lookups.

    I read that "hosts localhost" and "hosts 127.0.0.1" worked differently in mysql, so tried both those in the query. Didn't make any difference. I tried making a connection to the socket instead... "hosts unix:/var/run/mysqld/mysqld.sock" and that failed too.

    (For the record: connect to mysql on localhost, and the client makes a unix domain, socket connection, NOT a network connection. If you connect to 127.0.0.1 then it does networking. And I thought localhost and 127.0.0.1 meant the same thing :( )

    I edited /etc/postfix/master.cf so it would NOT chroot trivial-rewrite. Amazingly... postfix did its first mysql lookup, using hosts unix:

    But I wanted to put trivial-rewrite back in its chroot, so wanted the network connection to 127.0.0.1. About this time, I found that even command-line connections to 127.0.0.1 failed:

    # mysql --host=127.0.0.1 -u root -p
    (connection refused)​

    So it wasn't just postfix having trouble. "netstat -nap" showed mysql listening on 127.0.0.1:3306, as wanted. I turned on mysql's logging, and could see that it wasn't getting any requests: just sitting there idle while postfix, mysql client etc asked stuff and were refused.

    I revisited the privileges tables in mysql - made sure that the postfix user could connect from host 127.0.0.1. I'm not sure I did that exactly right so I won't post the details yet. Still mysql lookups failed at 127.0.0.1.

    I had already checked that iptables firewall was allowing access to port 3306.

    About this time, a google result sent me to read README.Debian. (A bit embarrassing that I hadn't already, but there ya go.) The actual file on Lenny was /usr/share/doc/mysql-server-5.0/README.Debian.gz and it gave me the puzzle piece I needed:

    If your connection is aborted immediately see if "mysqld: all" or similar is in /etc/hosts.allow and read hosts_access(5).​

    Sigh... I added mysqld: 127.0.0.1 to hosts.allow and it all came good. The mail.log started complaining about the SQL syntax, which was really good news at the time, that was something I could fix!

    I'm not finished yet still have things to fix on this postfix/mysql/etc mailserver, but this was a step forwards. Hope it helps someone.
     
  19. digitalage

    digitalage New Member

    Dipps, thank you for your post.

    As far as I'm aware of, it makes sense to have a rule in /etc/hosts.allow when there is a rule in /etc/hosts.deny which blocks everything (ie: "all: all") or part of it ("mysql: all"). For the beginner, the explanation: first rule denies everything, in which case we need a rule in /etc/hosts.allow to allow mysql, the second blocks mysql from the public IP, in which case wee need a rule in /etc/hosts.allow to override this for the specific part we need mysql access (local - 127.0.0.1, or public_ip).

    I find this link useful for those who need more informations about how hosts.deny/hosts.allow works:
    http://linux.about.com/od/commands/l/blcmdl5_hostsal.htm.

    In short, this is what we need to read from the link above:
     
  20. reason8

    reason8 New Member

    Issue resolved.

    The update added "proxy" to the beginning of the mysql instances.
    mysql:/etc/postfix/mysql-virtual_transports.cf

    Email started flowing shortly thereafter.

    Why was this proxy enabled? What is it's reason?
     

Share This Page