Hi there, I have had this kind of errors quite frequently (using IPSConfig 3.0.3.3 on Debian Squeeze) - and now it is bothering me as many people that want to send mail geht "unexpected reply: 451 4.3.5 : Client host rejected: Server configuration error" Code: Nov 3 10:37:34 eins postfix/trivial-rewrite[8905]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem Nov 3 10:37:36 eins postfix/trivial-rewrite[8914]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem Nov 3 10:39:36 eins postfix/trivial-rewrite[8923]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem Nov 3 10:39:38 eins postfix/trivial-rewrite[8924]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem Nov 3 10:41:35 eins postfix/trivial-rewrite[9004]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem Nov 3 10:41:37 eins postfix/trivial-rewrite[9005]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem We have no overload problem here, even at the moments the messages occur the load (top) is below 0.1 . Nothing is visible in the MySQL logs. The problem has always been here since the beginning, but often it was not bothering too much. At the beginning, I was testing the mailserver so we could have had overload problems. I then changed Code: max_connections = 500 max_user_connections = 500 wait_timeout = 15 as indicated by someone here. It did not help, for we have no overload problem. This is very typical: Code: Nov 3 10:44:44 eins postfix/smtpd[9074]: warning: 61.19.66.247: hostname Nat-Pool-61-19-66-247.cdma.cat.net.th verification failed: Name or service not known Nov 3 10:44:44 eins postfix/smtpd[9074]: connect from unknown[61.19.66.247] Nov 3 10:44:45 eins postfix/proxymap[9032]: warning: mysql query failed: MySQL server has gone away Nov 3 10:44:45 eins postfix/trivial-rewrite[9063]: fatal: proxy:mysql:/etc/postfix/mysql-virtual_domains.cf(0,lock|fold_fix): table lookup problem Nov 3 10:44:46 eins postfix/master[29367]: warning: process /usr/lib/postfix/trivial-rewrite pid 9063 exit status 1 Nov 3 10:44:47 eins pop3d: Connection, ip=[::ffff:217.92.21.225] Nov 3 10:44:47 eins postfix/smtpd[9001]: warning: mysql query failed: MySQL server has gone away Nov 3 10:44:47 eins postfix/smtpd[9001]: warning: mysql:/etc/postfix/mysql-virtual_client.cf: table lookup problem Nov 3 10:44:47 eins postfix/smtpd[9001]: NOQUEUE: reject: RCPT from unknown[61.19.66.247]: 451 4.3.5 : Client host rejected: Server configuration error; from= to=<xxxx> proto=ESMTP helo= Our total database space on a machine with 8 GB RAM is 953 MByte. We have no cpu-intensive things here, and as I said, the load is most of the time below 0.1 (on an 4-core machine) The only thing I changed yesterday is that I enabled replication as a master (which works) for only ONE of the databases. (BTW: Same changes worked fine with ISPConfig 2) MySQL load (even if it is very low) appears to play a role. When I have a look at the MYSQL status, I see no red numbers that indicate a performance problem normally. In the last 15 hours, MYSQL had to process about 8 queries per second. Not too much, hm? Any help is appreciated..
No.. Not in /var/log/messages, not in /var/log/mysql/mysql-bin.err - /var/log/mysql.log is empty. It is said that MYSQL logging can be turned on while running..? I don't know how to do it nor found something when googlin'.. Further investigation gave: /etc/postfix/mysql-virtual_client.cf has access to the Postfix blacklist. The blacklist contained only three rows, but I have deleted them. I think it could have been a race condition: IPSConfig or Postfix throws two queries at MYSQL at the same time: Maybe to check "sender" with one query and "client" with another almost simultaneously. As there are many postfix processes (anvil, pickup, qmgr etc.) this could likely happen. And the postfix timeout must be very low. And mysql_virtual_client.cf is not the only spot the problems occur. Must be similar every time postfix interacts with MySQL. Besides, I have no problem with MySQL. However, 20% of the queries get smashed (!): Code: max. gleichzeitige Verbindungen 24 --- --- Fehlversuche 7 12,79 0,58% Abgebrochen 247 451,14 20,43% Insgesamt 1,209 2,208,22 100,00%
I dont think that there is any general problem in this setup as it works quite nicely on many large mailservers and I dont see such problems on our mailsystems. On website scripts in busy websites you might have hundreds of simultanious accesses to the same database table, mysql is made to handle this. How many mail accounts do you have on that server? Also queries in state "Abgebrochen" dont have to be same queries that were logged in the postfix table.
OK - could you point me to an example my.cnf please that runs fine with ISConfig3? Exactly. With ISPConfig 2 on a much weaker machine and the same occupation, I've almost never had these logs. 25 so far. ISPConfig2 handled more that 100. True, but at the moment I see what is hitting on the MySQL and it is almost exclusively ispconfig. ispconfig alone has usually more than 10 processes open / sleeping with 50% of them over 10 seconds duration. Is that normal?
the default configuration of all supported Linux distributions is fine. No need to alter them, jsut add: max_connections = 500 max_user_connections = 500 in the [mysqld] section of the file. Comparuing ispconfig 2 and 3 makes no sense. The setup is not comparable. Thats is as if you compare windows with Linux. ISPConfig 3 handles much higher loads then ispconfig 2. Thats a very small setup which can definately not have a bottleneck in mysql. ISPConfig 3 is known to handle a few thousand accounts per server and even on our server here we have a few hundred in the default configuration. Yes, thats normal and does not indicate any problems.
That is exactly what I have already since you told me a while ago... and I have had maximum 24 simultaneous connections open so far. I would not have used ISPConfig2 if it had been remotely like Windows So.. still having the same problems. I am not on a virtual host but the root / is in a LVM volume. All the other volumes are not busy now.
Here is a copy of the postfix main.cf from a working server: Code: # See /usr/share/postfix/main.cf.dist for a commented, more complete version # Debian specific: Specifying a file name will cause the first # line of that file to be used as the name. The Debian default # is /etc/mailname. #myorigin = /etc/mailname smtpd_banner = $myhostname ESMTP $mail_name (Debian/GNU) biff = no # appending .domain is the MUA's job. append_dot_mydomain = no # Uncomment the next line to generate "delayed mail" warnings #delay_warning_time = 4h # TLS parameters smtpd_tls_cert_file = /etc/postfix/smtpd.crt smtpd_tls_key_file = /etc/postfix/smtpd.key smtpd_use_tls = yes smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache # See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for # information on enabling SSL in the smtp client. myhostname = server1234.domain.tld alias_maps = hash:/etc/aliases alias_database = hash:/etc/aliases myorigin = /etc/mailname mydestination = server1234.domain.tld, localhost, localhost.localdomain relayhost = mynetworks = 127.0.0.0/8 [::1]/128 mailbox_size_limit = 0 recipient_delimiter = + inet_interfaces = all virtual_alias_domains = virtual_alias_maps = proxy:mysql:/etc/postfix/mysql-virtual_forwardings.cf, mysql:/etc/postfix/mysql-virtual_email2email.cf virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql-virtual_domains.cf virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailboxes.cf virtual_mailbox_base = /home/vmail/ virtual_uid_maps = static:5000 virtual_gid_maps = static:5000 smtpd_sasl_auth_enable = yes broken_sasl_auth_clients = yes smtpd_recipient_restrictions = permit_mynetworks, permit_sasl_authenticated, reject_invalid_hostname,reject_non_fqdn_hostname,reject_unknown_recipient_domain,reject_non_fqdn_recipient,reject_non_fqdn_sender,reject_unknown_sender_domain,reject_unknown_recipient_domain, check_recipient_access mysql:/etc/postfix/mysql-virtual_recipient.cf, reject_unauth_destination transport_maps = proxy:mysql:/etc/postfix/mysql-virtual_transports.cf relay_domains = mysql:/etc/postfix/mysql-virtual_relaydomains.cf virtual_create_maildirsize = yes virtual_mailbox_extended = yes virtual_mailbox_limit_maps = proxy:mysql:/etc/postfix/mysql-virtual_mailbox_limit_maps.cf virtual_mailbox_limit_override = yes virtual_maildir_limit_message = "The user you are trying to reach is over quota." virtual_overquota_bounce = yes 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 smtpd_sender_restrictions = check_sender_access mysql:/etc/postfix/mysql-virtual_sender.cf smtpd_client_restrictions = check_client_access mysql:/etc/postfix/mysql-virtual_client.cf maildrop_destination_concurrency_limit = 1 maildrop_destination_recipient_limit = 1 virtual_transport = maildrop header_checks = regexp:/etc/postfix/header_checks mime_header_checks = regexp:/etc/postfix/mime_header_checks nested_header_checks = regexp:/etc/postfix/nested_header_checks body_checks = regexp:/etc/postfix/body_checks content_filter = amavis:[127.0.0.1]:10024 receive_override_options = no_address_mappings readme_directory = /usr/share/doc/postfix html_directory = /usr/share/doc/postfix/html message_size_limit = 0 smtpd_sasl_authenticated_header = yes smtpd_tls_security_level = may virtual_maildir_extended = yes relay_recipient_maps = mysql:/etc/postfix/mysql-virtual_relayrecipientmaps.cf and here the my.cnf Code: # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [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 max_connections = 500 max_user_connections = 500 # 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 # # * Fine Tuning # key_buffer = 16M max_allowed_packet = 16M thread_stack = 128K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP #max_connections = 100 #table_cache = 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. #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 #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * BerkeleyDB # # Using BerkeleyDB is now discouraged as its support will cease in 5.1.12. 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! # You might want to disable InnoDB to shrink the mysqld process by circa 100MB. #skip-innodb # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI "tinyca". # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem skip-innodb [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M # # * NDB Cluster # # See /usr/share/doc/mysql-server-*/README.Debian for more information. # # The following configuration is read by the NDB Data Nodes (ndbd processes) # not from the NDB Management Nodes (ndb_mgmd processes). # # [MYSQL_CLUSTER] # ndb-connectstring=127.0.0.1 # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/
The mysql and postfix config file are almost identical. Will post more details later. After having turned on mysql.log, I've seen something: Mail mail-related queries are 10fold: Code: 546 Query SELECT access FROM mail_access WHERE source='[email protected]' and type = 'sender' and active = 'y' and server_id = 1 546 Query SELECT access FROM mail_access WHERE source='gmail.com' and type = 'sender' and active = 'y' and server_id = 1 546 Query SELECT access FROM mail_access WHERE source='com' and type = 'sender' and active = 'y' and server_id = 1 546 Query SELECT access FROM mail_access WHERE source='postcodeloterijnl2011@' and type = 'sender' and active = 'y' and server_id = 1 545 Query SELECT access FROM mail_access WHERE source='unknown' and type = 'client' and active = 'y' 545 Query SELECT access FROM mail_access WHERE source='189.113.36.82' and type = 'client' and active = 'y' 545 Query SELECT access FROM mail_access WHERE source='189.113.36' and type = 'client' and active = 'y' 545 Query SELECT access FROM mail_access WHERE source='189.113' and type = 'client' and active = 'y' 545 Query SELECT access FROM mail_access WHERE source='189' and type = 'client' and active = 'y' 546 Query SELECT access FROM mail_access WHERE source='[email protected]' and type = 'sender' and active = 'y' and server_id = 1 546 Query SELECT access FROM mail_access WHERE source='gmail.com' and type = 'sender' and active = 'y' and server_id = 1 546 Query SELECT access FROM mail_access WHERE source='com' and type = 'sender' and active = 'y' and server_id = 1 546 Query SELECT access FROM mail_access WHERE source='postcodeloterijnl2011@' and type = 'sender' and active = 'y' and server_id = 1 545 Query SELECT access FROM mail_access WHERE source='unknown' and type = 'client' and active = 'y' 545 Query SELECT access FROM mail_access WHERE source='189.113.36.82' and type = 'client' and active = 'y' 545 Query SELECT access FROM mail_access WHERE source='189.113.36' and type = 'client' and active = 'y' 545 Query SELECT access FROM mail_access WHERE source='189.113' and type = 'client' and active = 'y' 545 Query SELECT access FROM mail_access WHERE source='189' and type = 'client' and active = 'y' 546 Query SELECT access FROM mail_access WHERE source='[email protected]' and type = 'sender' and active = 'y' and server_id = 1 546 Query SELECT access FROM mail_access WHERE source='gmail.com' and type = 'sender' and active = 'y' and server_id = 1 546 Query SELECT access FROM mail_access WHERE source='com' and type = 'sender' and active = 'y' and server_id = 1 546 Query SELECT access FROM mail_access WHERE source='postcodeloterijnl2011@' and type = 'sender' and active = 'y' and server_id = 1 545 Query SELECT access FROM mail_access WHERE source='unknown' and type = 'client' and active = 'y' 545 Query SELECT access FROM mail_access WHERE source='189.113.36.82' and type = 'client' and active = 'y' 545 Query SELECT access FROM mail_access WHERE source='189.113.36' and type = 'client' and active = 'y' 545 Query SELECT access FROM mail_access WHERE source='189.113' and type = 'client' and active = 'y' 545 Query SELECT access FROM mail_access WHERE source='189' and type = 'client' and active = 'y' The exact same series query is repeated at once about ten times. How can that happen?
After a LONG session on the terminals: I have no f***ing clue what it is and I need a workaround. As far as I see, it it mostly postfix's trivial-rewrite programm that dies. I've seen two differences in you my.cnf setup: I did not have skip-bdb and skip-innodb . "skip-innodb" is working - I have no InnoDB here. But as soon as I say "skip-bdb" mysql does not restart! I have no idea why. I've tried to give MYSQL even more resources than you said, but to no avail. We NEVER have more that 0.1 server load when the problems occur. It is very often at the very first contact of a mailserver coming via SMTP. Next, I've avoided to use postfix's proxy command proxy:mysql:/etc/postfix.. but mysql:/etc/postfix instead so the commands do not get queued but can hit the mysqlserver with more threads (why not, we have 4 cpus?). No difference. What I'd suggest is: Postfix uses no longer MySQL to ask for data, but flat files. Is there a chance that ISPConfig produces those flat file on demand? I think it was like this back in IPSConfig 2...? I have restarted the whole server just in case.. As long as we cannot identify the error (and it does definitely not look like), I really need a workaround. My Clients are beginning to realize something is wrong.
Youcan write a plugiun for that. But we dont plan to change the postfix setup in ipconfig 3. I really understand that you have a problem there with your installation but if there are thousands of installations which are much larger then your setup and none of these have this problem, then there is no reason to change the setup. So instead of talking to change a etup which has prooven to work reliably an many systems, you shoul try to debug your mysql server by enabling mysql logging to see why postfix is not able to connect to mysql.
I did that already and have seen and when that happens, I see not even a query in the MYSQL log. The lock must have happened before. Postfix shown an error without having passed the query to MYSQL - that is what I think I see. If it would be your server and your customers, what would you do instead?
1) Is this a vserver that uses openvz or virtuozzo? If yes, then check with: cat /proc/user_beancounters if you hit any limits. 2) Try to get more verbose infos from postfix: http://www.postfix.org/DEBUG_README.html 3) E.g. create a new server from scratch as virtual server (works fine on a desktop pc with vmware or other free virtualisation software) and then check if you can reproduce the problem there and if not, compare the relevant config files for postfix and mysql.
It appears that both amavis and postfix (trivial-rewrite and smtp) are sometimes not able to access MySQL. I turned on -v for smtp and trivial-rewrite: Port 3306 is open (needed for replication) and socket might be as well functioning. Might it be that Postfix thinks a connection is still open while MySQL already closed it?
I have no virtual server, but "real iron". However, since I added wait_timeout = 1800 connect_timeout = 60 in /etc/mysql/my.cnf - this seemed to solve the problem so far. As far as I think, postfix was relying on open TCP connections where MySQL closed them already. Next week, I will revert all the other changes I made to identify the "culprit". But now my customers deserve some peace Postfix logging is really great, BTW. My current my.cnf without any comments: Code: [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] 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 skip-locking key_buffer = 384M max_allowed_packet = 64M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP max_connections = 500 max_user_connections = 500 wait_timeout = 1800 connect_timeout = 60 table_cache = 4096 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 64M myisam_sort_buffer_size = 64M query_cache_size = 32M query_cache_limit = 1M query_cache_size = 16M # Anpassung fuer die Replikation # ande 20111102 server-id = 1 log-bin = /var/log/mysql/mysql-bin.log log-error = /var/log/mysql/mysql-bin.err log-slave-updates expire_logs_days = 30 max_binlog_size = 100M binlog_do_db = xxxxxxx auto_increment_increment = 10 auto_increment_offset = 1 replicate-same-server-id = 0 skip-innodb [mysqldump] quick quote-names max_allowed_packet = 64M [mysql] [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/
maybe a solution Sorry to reopen this but i've had this problem for the longest time on Lenny. Here is the change I JUST made (so no results yet). http://wiki.bacula.org/doku.php?id=faq#mysql_server_has_gone_away Seems to be a simple lengthening of wait_timeout to a 24 hr value. If this doesn't fix, I'll post back. -T
I can confirm that increasing wait_time in mysql config seems to help with this issue. I recently had this problem and after setting wait_time to 3600 (i.e. 1 hour), "table lookup problem" and "MySQL server has gone away" errors stopped appearing. From what I have found, the default behaviour of auto reconnecting has changed between mysql 5.0 and 5.1 and with 5.1 the default client behaviour is not to reconnect after losing connection, which can be changed by enabling auto reconnections but the library used by postfix to connect to mysql probably counts on the auto reconnection and while with mysql 5.0 it reconnects automatically, with 5.1 it doesn't and the query can't be executed and it ends with an error.
No it itsn't. The database is clean, mysql reports no errors in error log and since increasing the wait_time I haven't got any errors in the posfix error log either.
