SMTP hangs when MySQL is down

classic Classic list List threaded Threaded
31 messages Options
12
Reply | Threaded
Open this post in threaded view
|

SMTP hangs when MySQL is down

Sebastian Wiesinger-10
Hi,

I'm using Postfix with MySQL via proxy:mysql maps. The documentation
states that mails should get deferred if no mysql server is reachable.

However when I shut down MySQL, SMTP transaction freeze after I enter
the "MAIL FROM:<...>" statement.

Any ideas how I can change that? There seems to be no timeout, I left
the SMTP dialog open for a few minutes at least.

The logfile shows:

postfix/proxymap[2160]: warning: connect to mysql server localhost: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
postfix/trivial-rewrite[2159]: fatal: proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_maps.cf(0,lock|fold_fix): table lookup problem
postfix/master[30733]: warning: process /usr/lib/postfix/trivial-rewrite pid 2159 exit status 1
postfix/trivial-rewrite[2161]: fatal: proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_maps.cf(0,lock|fold_fix): table lookup problem
postfix/smtpd[1372]: warning: problem talking to service rewrite: Success
postfix/master[30733]: warning: process /usr/lib/postfix/trivial-rewrite pid 2161 exit status 1
postfix/master[30733]: warning: /usr/lib/postfix/trivial-rewrite: bad command startup -- throttling


postfix is Debian Version 2.7.1-1+squeeze1

postconf -n:

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
home_mailbox = Maildir/
html_directory = /usr/share/doc/postfix/html
inet_interfaces = 127.0.0.1, [::1], x.x.x.x
inet_protocols = ipv4, ipv6
mailbox_command = /usr/bin/procmail -a "$EXTENSION"
mailbox_size_limit = 0
mydestination = mx.example.com, localhost.example.com, localhost
myhostname = mx.example.com
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
myorigin = /etc/mailname
readme_directory = /usr/share/doc/postfix
recipient_delimiter = +
relay_domains = proxy:mysql:$config_directory/sql/mysql_relay_domains_maps.cf
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_exceptions_networks =
smtpd_sasl_path = private/auth
smtpd_sasl_security_options = noanonymous
smtpd_sasl_type = dovecot
smtpd_tls_cert_file = /etc/ssl/certs/my.crt
smtpd_tls_key_file = /etc/ssl/private/my.key
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtpd_use_tls = yes
virtual_alias_maps = proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_maps.cf,   proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_maps.cf,   proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_catchall_maps.cf
virtual_gid_maps = static:8
virtual_mailbox_base = /var/vmail
virtual_mailbox_domains = proxy:mysql:/etc/postfix/sql/mysql_virtual_domains_maps.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/sql/mysql_virtual_mailbox_maps.cf,   proxy:mysql:/etc/postfix/sql/mysql_virtual_alias_domain_mailbox_maps.cf
virtual_minimum_uid = 101
virtual_transport = dovecot-sa
virtual_uid_maps = static:111


--
New GPG Key: 0x93A0B9CE (F4F6 B1A3 866B 26E9 450A  9D82 58A2 D94A 93A0 B9CE)
Old GPG Key-ID: 0x76B79F20 (0x1B6034F476B79F20)
'Are you Death?' ... IT'S THE SCYTHE, ISN'T IT? PEOPLE ALWAYS NOTICE THE SCYTHE.
            -- Terry Pratchett, The Fifth Elephant
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Reindl Harald-2


Am 05.12.2011 01:34, schrieb Sebastian Wiesinger:
> I'm using Postfix with MySQL via proxy:mysql maps. The documentation
> states that mails should get deferred if no mysql server is reachable

yes, on the delivering server

if your configuration is mysql-based and mysqld is down
your server must not accept messages because missing
the list of valid RCPT

a replication slave would be a good idea

so you can receive the messages even if your backends are
a mysql storage because the received messages will be deferred
in this case locally and the sender gets a 250 OK



signature.asc (270 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Sahil Tandon-4
In reply to this post by Sebastian Wiesinger-10
On Mon, 2011-12-05 at 01:34:17 +0100, Sebastian Wiesinger wrote:

> I'm using Postfix with MySQL via proxy:mysql maps. The documentation
> states that mails should get deferred if no mysql server is reachable.
>
> However when I shut down MySQL, SMTP transaction freeze after I enter
> the "MAIL FROM:<...>" statement.
>
> Any ideas how I can change that? There seems to be no timeout, I left
> the SMTP dialog open for a few minutes at least.

Do not use SQL in virtual_mailbox_domains[1]; instead, set the latter to
a regular list.  Then, even when MySQL is down, Postfix will defer mail
with 4.3.0 instead of appearing to freeze.

[1] Actually, you should avoid using SQL or LDAP for any tables used by
    the trivial-rewrite(8) daemon.  For context, see:

    http://article.gmane.org/gmane.mail.postfix.user/209024
    http://article.gmane.org/gmane.mail.postfix.user/168112
    http://article.gmane.org/gmane.mail.postfix.user/140543

--
Sahil Tandon
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Sebastian Wiesinger-10
* Sahil Tandon <[hidden email]> [2011-12-05 03:24]:

> > I'm using Postfix with MySQL via proxy:mysql maps. The documentation
> > states that mails should get deferred if no mysql server is reachable.
> >
> > However when I shut down MySQL, SMTP transaction freeze after I enter
> > the "MAIL FROM:<...>" statement.
> >
> > Any ideas how I can change that? There seems to be no timeout, I left
> > the SMTP dialog open for a few minutes at least.
>
> Do not use SQL in virtual_mailbox_domains[1]; instead, set the latter to
> a regular list.  Then, even when MySQL is down, Postfix will defer mail
> with 4.3.0 instead of appearing to freeze.

Hi Sahil,

that's not really an option for me, I need these lists in MySQL. It
seems I have to live with it and make MySQL as stable as possible.

> [1] Actually, you should avoid using SQL or LDAP for any tables used by
>     the trivial-rewrite(8) daemon.  For context, see:

Thanks for the context but I'm still not clear on why there is no way
for postfix to delay every incoming mail when that happens. Is it
because local mail (injected by sendmail interface) would probably get
lost?

Could you explain this in a bit more detail?

Thank You & Regards

Sebastian

--
New GPG Key: 0x93A0B9CE (F4F6 B1A3 866B 26E9 450A  9D82 58A2 D94A 93A0 B9CE)
Old GPG Key-ID: 0x76B79F20 (0x1B6034F476B79F20)
'Are you Death?' ... IT'S THE SCYTHE, ISN'T IT? PEOPLE ALWAYS NOTICE THE SCYTHE.
            -- Terry Pratchett, The Fifth Elephant
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Reindl Harald-2

Am 05.12.2011 10:42, schrieb Sebastian Wiesinger:
>> Do not use SQL in virtual_mailbox_domains[1]; instead, set the latter to
>> a regular list.  Then, even when MySQL is down, Postfix will defer mail
>> with 4.3.0 instead of appearing to freeze.
>
> Hi Sahil,
>
> that's not really an option for me, I need these lists in MySQL. It
> seems I have to live with it and make MySQL as stable as possible.

there is no need not use mysql for any postfix configuration
since 2009 ALL or mailservices are mysql-backed inclduing mail-storage and
there are much more options used than on most other mailservers out there

as said, use replication and the slave as fallback for postfix

[root@mail:~]$ cat /etc/postfix/mysql-transport.cf
user     = dbmailro
password = **************
dbname   = dbmail
hosts    = unix:/var/lib/mysql/mysql.sock inet:10.0.0.120:3307
query    = select transport from dbma_transports where mydestination='%s' or mydestination='%d' order by transport
desc limit 1;
__________________________________

normally mysql is rock stable and never down

rebuild mysql-packages at your own the remove the idiotic restarts
on update which most distributions do, stop mailservices before restart
mysqld and start them after that
__________________________________

finally work with scripts to maintain services
this way i do a "dbmail-stop-all.sh; reboot" and change the
Before/After of systemd to make sure all services are started
in the right order - thats why i throwed away the mysql-package
from Fedora 15 and make my own things

[root@mail:~]$ cat /scripts/dbmail-stop-all.sh
#!/bin/bash
/sbin/service crond stop
/sbin/service postfix stop
/sbin/service dovecot stop
/sbin/service dbmail-imapd stop
/sbin/service dbmail-lmtpd stop
/sbin/service dbmail-pop3d stop
/sbin/service dbmail-timsieved stop
/sbin/service mysqld stop

[root@mail:~]$ cat /scripts/dbmail-start-all.sh
#!/bin/bash
/sbin/service mysqld start
/sbin/service dbmail-lmtpd start
/sbin/service dovecot start
/sbin/service postfix start
/sbin/service dbmail-imapd start
/sbin/service dbmail-pop3d start
/sbin/service dbmail-timsieved start
/sbin/service crond start


signature.asc (270 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Sahil Tandon-4
In reply to this post by Sebastian Wiesinger-10
On Mon, 2011-12-05 at 10:42:30 +0100, Sebastian Wiesinger wrote:

> * Sahil Tandon <[hidden email]> [2011-12-05 03:24]:
> > > I'm using Postfix with MySQL via proxy:mysql maps. The documentation
> > > states that mails should get deferred if no mysql server is reachable.
> > >
> > > However when I shut down MySQL, SMTP transaction freeze after I enter
> > > the "MAIL FROM:<...>" statement.
> > >
> > > Any ideas how I can change that? There seems to be no timeout, I left
> > > the SMTP dialog open for a few minutes at least.
> >
> > Do not use SQL in virtual_mailbox_domains[1]; instead, set the latter to
> > a regular list.  Then, even when MySQL is down, Postfix will defer mail
> > with 4.3.0 instead of appearing to freeze.
>
> that's not really an option for me, I need these lists in MySQL. It
> seems I have to live with it and make MySQL as stable as possible.

Is your list of virtual mailbox domains that large or dynamic that it
must be only in SQL?  Note that you can still have virtual_mailbox_maps
reference an SQL location; it is just virtual_mailbox_domains (and
anything else that is used by trivial-rewrite(8)) that causes the
"stalling" symptoms you describe above.

> > [1] Actually, you should avoid using SQL or LDAP for any tables used by
> >     the trivial-rewrite(8) daemon.  For context, see:
>
> Thanks for the context but I'm still not clear on why there is no way
> for postfix to delay every incoming mail when that happens. Is it
> because local mail (injected by sendmail interface) would probably get
> lost?
>
> Could you explain this in a bit more detail?

Victor explains well in the posts to which I linked in my original
reply.

--
Sahil Tandon
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Sahil Tandon-4
In reply to this post by Reindl Harald-2
On Mon, 2011-12-05 at 10:59:35 +0100, Reindl Harald wrote:

> Am 05.12.2011 10:42, schrieb Sebastian Wiesinger:
> >> Do not use SQL in virtual_mailbox_domains[1]; instead, set the
> >> latter to a regular list.  Then, even when MySQL is down, Postfix
> >> will defer mail with 4.3.0 instead of appearing to freeze.
> >
> > Hi Sahil,
> >
> > that's not really an option for me, I need these lists in MySQL. It
> > seems I have to live with it and make MySQL as stable as possible.
>
> there is no need not use mysql for any postfix configuration since
> 2009 ALL or mailservices are mysql-backed inclduing mail-storage and
> there are much more options used than on most other mailservers out
> there

This is tangential to the topic.

> normally mysql is rock stable and never down

That's great, but: the OP's question is explicitly about how Postfix
functions when MySQL *is* down.  The answer to that question - as noted
earlier - depends on which facet of Postfix is impacted, which in turn
depends on the parameters/tables configured to query an SQL backend.

--
Sahil Tandon
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Sebastian Wiesinger-10
In reply to this post by Sahil Tandon-4
* Sahil Tandon <[hidden email]> [2011-12-06 01:54]:
> > that's not really an option for me, I need these lists in MySQL. It
> > seems I have to live with it and make MySQL as stable as possible.
>
> Is your list of virtual mailbox domains that large or dynamic that it
> must be only in SQL?  Note that you can still have virtual_mailbox_maps
> reference an SQL location; it is just virtual_mailbox_domains (and
> anything else that is used by trivial-rewrite(8)) that causes the
> "stalling" symptoms you describe above.

Hi Sahil,

not large but users can add their own virtual domain aliases. I could
move the virtual domains out of SQL but not the domain aliases.

> > Could you explain this in a bit more detail?
>
> Victor explains well in the posts to which I linked in my original
> reply.

I read these but it's not clear to me. So the transport lookup doesn't
work, but why does that prevent postfix from doing a 4xx error code?

Regards

Sebastian

--
New GPG Key: 0x93A0B9CE (F4F6 B1A3 866B 26E9 450A  9D82 58A2 D94A 93A0 B9CE)
Old GPG Key-ID: 0x76B79F20 (0x1B6034F476B79F20)
'Are you Death?' ... IT'S THE SCYTHE, ISN'T IT? PEOPLE ALWAYS NOTICE THE SCYTHE.
            -- Terry Pratchett, The Fifth Elephant
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Wietse Venema
Sebastian Wiesinger:
> I read these but it's not clear to me. So the transport lookup doesn't
> work, but why does that prevent postfix from doing a 4xx error code?

You should remove the domains from SQL (or LDAP or other slow tables).

Once you do that, smtpd will reply 4xx after email address lookup
error (it won't reply 4xx when trivial-rewrite can't look up the
domain).

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Reindl Harald-2


Am 07.12.2011 16:59, schrieb Wietse Venema:
> Sebastian Wiesinger:
>> I read these but it's not clear to me. So the transport lookup doesn't
>> work, but why does that prevent postfix from doing a 4xx error code?
>
> You should remove the domains from SQL (or LDAP or other slow tables).
>
> Once you do that, smtpd will reply 4xx after email address lookup
> error (it won't reply 4xx when trivial-rewrite can't look up the
> domain).

the question was WHY it won't


signature.asc (270 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Wietse Venema
Reindl Harald:

> Am 07.12.2011 16:59, schrieb Wietse Venema:
> > Sebastian Wiesinger:
> >> I read these but it's not clear to me. So the transport lookup doesn't
> >> work, but why does that prevent postfix from doing a 4xx error code?
> >
> > You should remove the domains from SQL (or LDAP or other slow tables).
> >
> > Once you do that, smtpd will reply 4xx after email address lookup
> > error (it won't reply 4xx when trivial-rewrite can't look up the
> > domain).
>
> the question was WHY it won't

Yes it was. I point the attention to the RIGHT problem, which is
fixing the suboptimal configuration that does domain queries from
SQL.

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Reindl Harald-2


Am 07.12.2011 17:17, schrieb Wietse Venema:

> Reindl Harald:
>> Am 07.12.2011 16:59, schrieb Wietse Venema:
>>> Sebastian Wiesinger:
>>>> I read these but it's not clear to me. So the transport lookup doesn't
>>>> work, but why does that prevent postfix from doing a 4xx error code?
>>>
>>> You should remove the domains from SQL (or LDAP or other slow tables).
>>>
>>> Once you do that, smtpd will reply 4xx after email address lookup
>>> error (it won't reply 4xx when trivial-rewrite can't look up the
>>> domain).
>>
>> the question was WHY it won't
>
> Yes it was. I point the attention to the RIGHT problem, which is
> fixing the suboptimal configuration that does domain queries from
> SQL.
this is only "suboptimal" because the behavior to hang instead
a tempfail, you CAN NOT remive domains from sql if you maintain
your servers over sql-backends and automatisms where as example
10 different machine have to use instantly the same configuration


signature.asc (270 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

/dev/rob0
On Wednesday 07 December 2011 10:21:03 Reindl Harald wrote:

> Am 07.12.2011 17:17, schrieb Wietse Venema:
> > Reindl Harald:
> >> Am 07.12.2011 16:59, schrieb Wietse Venema:
> >>> Sebastian Wiesinger:
> >>>> I read these but it's not clear to me. So the transport lookup
> >>>> doesn't work, but why does that prevent postfix from doing a
> >>>> 4xx error code?
> >>>
> >>> You should remove the domains from SQL (or LDAP or other slow
> >>> tables).
> >>>
> >>> Once you do that, smtpd will reply 4xx after email address
> >>> lookup error (it won't reply 4xx when trivial-rewrite can't
> >>> look up the domain).
> >>
> >> the question was WHY it won't

I would guess that the answer to that would require understanding of
Postfix design decisions. It's not likely something which can be
easily covered in an email post.

> > Yes it was. I point the attention to the RIGHT problem, which is
> > fixing the suboptimal configuration that does domain queries from
> > SQL.
>
> this is only "suboptimal" because the behavior to hang instead
> a tempfail, you CAN NOT remive domains from sql if you maintain
> your servers over sql-backends and automatisms where as example
> 10 different machine have to use instantly the same configuration

Nonsense. Recognition of new domains need not be instant. Email is
itself not instant messaging. Domain owners should expect reasonable
delays in changes of domain hosting, and are responsible to plan
accordingly. Your expectations are absurd.

A simple workaround, as I am sure has been suggested on this list
numerous times, is to run a query from a cron job to populate and
postmap the domain lists.
--
    Offlist mail to this address is discarded unless
    "/dev/rob0" or "not-spam" is in Subject: header
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Reindl Harald-2


Am 07.12.2011 17:42, schrieb /dev/rob0:
> Nonsense. Recognition of new domains need not be instant. Email is
> itself not instant messaging. Domain owners should expect reasonable
> delays in changes of domain hosting, and are responsible to plan
> accordingly. Your expectations are absurd.
>
> A simple workaround, as I am sure has been suggested on this list
> numerous times, is to run a query from a cron job to populate and
> postmap the domain lists.

well, so try to build a cronjob for the following setup to make
all this consistent with static files while the base-iformations
of which users exists in dbmail_aliases and domains in this
form are non-existing

you cant't simply do this without mysql-tables and yes there exists a
simply to use but complex inside web-interface for all this options and
yes they are all used and not just for fun

there are thousands of things you can not do with flat config-files

CREATE VIEW  `dbma_recipients` AS
 SELECT `dbmail_aliases`.`alias` AS `alias` FROM `dbmail_aliases`
 WHERE alias not like '%@'
 UNION (SELECT concat('@',`dbma_mta`.`mydestination`) AS `alias`
 FROM `dbma_mta`
 WHERE (`dbma_mta`.`transport` <> 'dbmail-lmtp:127.0.0.1:24'))
 UNION (SELECT `dbma_recipient_relay`.`mydestination` AS `alias`
 FROM `dbma_recipient_relay`)
 UNION (SELECT `dbma_aliases_global`.`alias` AS `alias`
 FROM `dbma_aliases_global`);

CREATE VIEW `dbma_transports` AS
 SELECT mydestination,transport
 FROM `dbma_mta`
 UNION (select mydestination,transport FROM `dbma_recipient_relay`)
 UNION (select mydestination,transport FROM `dbma_transports_error`);

myhostname                                          = myhostname
smtpd_banner                                        = $myhostname hardened ESMTP
smtp_helo_name                                      = $myhostname
inet_protocols                                      = ipv4
inet_interfaces                                     = all

unknown_local_recipient_reject_code                 = 550
unverified_recipient_reject_code                    = 550
bounce_template_file                                = /etc/postfix/bounce.cf
fast_flush_domains                                  =

mailbox_size_limit                                  = 0
recipient_delimiter                                 = +
broken_sasl_auth_clients                            = yes
anvil_rate_time_unit                                = 1800s

smtpd_client_connection_rate_limit                  = 50
smtpd_sasl_auth_enable                              = yes
smtpd_sasl_type                                     = dovecot
smtpd_sasl_path                                     = private/auth
smtpd_proxy_options                                 = speed_adjust
smtpd_helo_required                                 = yes
smtpd_discard_ehlo_keywords                         = silent-discard, etrn, dsn
smtpd_recipient_limit                               = 500

smtp_sasl_auth_enable                               = yes
smtp_sender_dependent_authentication                = yes
smtp_sasl_security_options                          = noanonymous

proxy_read_maps = proxy:mysql:/etc/postfix/mysql-mynetworks.cf
 proxy:mysql:/etc/postfix/mysql-mydestination.cf
 proxy:mysql:/etc/postfix/mysql-recipients.cf
 proxy:mysql:/etc/postfix/mysql-rewritedomains.cf
 proxy:mysql:/etc/postfix/mysql-rewritesenders.cf
 proxy:mysql:/etc/postfix/mysql-transport.cf
 proxy:mysql:/etc/postfix/mysql-sender_relay_hosts.cf
 proxy:mysql:/etc/postfix/mysql-sender_relay_hosts_auth.cf
 proxy:mysql:/etc/postfix/mysql-aliases.cf
 proxy:mysql:/etc/postfix/mysql-senderaccess.cf
 proxy:mysql:/etc/postfix/mysql-spamfilter.cf
 proxy:mysql:/etc/postfix/mysql-forwarders.cf

smtpd_helo_restrictions = permit_mynetworks
 permit_sasl_authenticated
 reject_non_fqdn_helo_hostname
 reject_invalid_helo_hostname
 reject_unknown_helo_hostname

smtpd_recipient_restrictions = permit_mynetworks
 reject_non_fqdn_recipient
 reject_non_fqdn_sender
 reject_unlisted_sender
 reject_authenticated_sender_login_mismatch
 permit_sasl_authenticated
 reject_unauth_destination
 reject_unknown_sender_domain
 reject_unknown_recipient_domain
 reject_invalid_hostname
 reject_unknown_reverse_client_hostname
 reject_unauth_pipelining
 reject_rbl_client dnsbl-1.uceprotect.net
 check_policy_service unix:/var/spool/postfix/postgrey/socket
 check_recipient_access proxy:mysql:/etc/postfix/mysql-spamfilter.cf

barracuda_smtpd_recipient_restrictions              = permit_mynetworks, reject
mynetworks                                          = 127.0.0.0/8, proxy:mysql:/etc/postfix/mysql-mynetworks.cf
mydestination                                       = proxy:mysql:/etc/postfix/mysql-mydestination.cf
local_recipient_maps                                = proxy:mysql:/etc/postfix/mysql-recipients.cf
recipient_canonical_maps                            = proxy:mysql:/etc/postfix/mysql-rewritedomains.cf
sender_canonical_maps                               = proxy:mysql:/etc/postfix/mysql-rewritesenders.cf
transport_maps                                      = proxy:mysql:/etc/postfix/mysql-transport.cf
sender_dependent_relayhost_maps                     = proxy:mysql:/etc/postfix/mysql-sender_relay_hosts.cf
smtp_sasl_password_maps                             = proxy:mysql:/etc/postfix/mysql-sender_relay_hosts_auth.cf
alias_maps                                          = proxy:mysql:/etc/postfix/mysql-aliases.cf
smtpd_sender_login_maps                             = proxy:mysql:/etc/postfix/mysql-senderaccess.cf
virtual_alias_maps                                  = proxy:mysql:/etc/postfix/mysql-forwarders.cf

body_checks_size_limit                              = 65535
queue_run_delay                                     = 240
maximal_queue_lifetime                              = 3d
bounce_queue_lifetime                               = 3d
minimal_backoff_time                                = 900
maximal_backoff_time                                = 5400
message_size_limit                                  = 36700160
max_idle                                            = 60
in_flow_delay                                       = ${stress?2}${stress:0}s

smtp_destination_recipient_limit                    = 15
smtp_initial_destination_concurrency                = 5
smtp_destination_concurrency_limit                  = 5
smtp_destination_concurrency_failed_cohort_limit    = 5
smtp_destination_rate_delay                         = 1

smtp_helo_timeout                                   = ${stress?45}${stress:180}s
smtp_mail_timeout                                   = ${stress?45}${stress:180}s
smtp_connect_timeout                                = ${stress?15}${stress:45}s

smtpd_error_sleep_time                              = ${stress?1}${stress:2}s
smtpd_soft_error_limit                              = ${stress?2}${stress:5}
smtpd_hard_error_limit                              = ${stress?5}${stress:10}
smtpd_peername_lookup                               = yes

lmtp_max_idle                                       = 600
lmtp_connection_cache_time_limit                    = 600
lmtp_connection_reuse_time_limit                    = 600
lmtp_destination_concurrency_limit                  = 30
dbmail-lmtp_destination_concurrency_limit           = 30
dbmail-lmtp_destination_rate_delay                  = 0

smtpd_use_tls                                       = yes
smtpd_tls_loglevel                                  = 1
smtpd_tls_cert_file                                 = /etc/postfix/certs/key.pem
smtpd_tls_key_file                                  = /etc/postfix/certs/key.pem
smtpd_tls_CAfile                                    = /etc/pki/tls/certs/ca-bundle.crt
smtpd_tls_security_level                            = may
smtpd_tls_received_header                           = yes
smtpd_tls_session_cache_timeout                     = 3600s
smtpd_tls_session_cache_database                    = btree:/var/lib/postfix/smtpd_scache

[root@dbmail:/etc/postfix]$ cat /etc/postfix/mysql-aliases.cf
user          = dbmailro
password      = ****
dbname        = dbmail
hosts         = unix:/var/lib/mysql/mysql.sock inet:10.0.0.120:3307
query         = select target from dbma_aliases where source like '%s'

[root@dbmail:/etc/postfix]$ cat /etc/postfix/mysql-mydestination.cf
user          = dbmailro
password      = ****
dbname        = dbmail
hosts         = unix:/var/lib/mysql/mysql.sock inet:10.0.0.120:3307
query         = select transport from dbma_mta where mydestination like '%s'

[root@dbmail:/etc/postfix]$ cat /etc/postfix/mysql-recipients.cf
user          = dbmailro
password      = ****
dbname        = dbmail
hosts         = unix:/var/lib/mysql/mysql.sock inet:10.0.0.120:3307
query         = select alias from dbma_recipients where alias like '%s'

[root@dbmail:/etc/postfix]$ cat /etc/postfix/mysql-rewritedomains.cf
user          = dbmailro
password      = ****
dbname        = dbmail
hosts         = unix:/var/lib/mysql/mysql.sock inet:10.0.0.120:3307
query         = select target from dbma_rewrite_domains where source like '%d' and ((select count(*) from
dbmail_aliases where alias like (select concat('%u', (select target from dbma_rewrite_domains where source like
'%d' limit 1)) from dbma_rewrite_domains limit 1))>0 or (select count(*) from dbmail_aliases where alias like '%u@'
limit 1)>0) limit 1

[root@dbmail:/etc/postfix]$ cat /etc/postfix/mysql-rewritesenders.cf
user          = dbmailro
password      = ****
dbname        = dbmail
hosts         = unix:/var/lib/mysql/mysql.sock inet:10.0.0.120:3307
query         = select target from dbma_rewrite_senders where source like '%s'

[root@dbmail:/etc/postfix]$ cat /etc/postfix/mysql-spamfilter.cf
user          = dbmailro
password      = ****
dbname        = dbmail
hosts         = unix:/var/lib/mysql/mysql.sock inet:10.0.0.120:3307
query         = select filter from dbma_spamfilter where domain like '%d'

[root@dbmail:/etc/postfix]$ cat /etc/postfix/mysql-transport.cf
user          = dbmailro
password      = ****
dbname        = dbmail
hosts         = unix:/var/lib/mysql/mysql.sock inet:10.0.0.120:3307
query         = select transport from dbma_transports where mydestination like '%s' or mydestination like '%d'
order by transport desc limit 1;

[root@mail:/etc/postfix]$ cat mysql-sender_relay_hosts.cf
user          = dbmailro
password      = ****
dbname        = dbmail
hosts         = unix:/var/lib/mysql/mysql.sock inet:10.0.0.120:3307
query         = select transport from dbma_sender_relay where email like '%s'

[root@mail:/etc/postfix]$ cat mysql-sender_relay_hosts_auth.cf
user          = dbmailro
password      = ****
dbname        = dbmail
hosts         = unix:/var/lib/mysql/mysql.sock inet:10.0.0.120:3307
query         = select concat(username, ':', password) from dbma_sender_relay where email like '%s'

[root@dbmail:/etc/postfix]$ cat /etc/postfix/mysql-senderaccess.cf
user          = dbmailro
password      = ****
dbname        = dbmail
hosts         = unix:/var/lib/mysql/mysql.sock inet:10.0.0.120:3307
query         = select userid from dbmail_users where ((select count(*) from dbma_mta where mydestination like
'%d') > 0 or (select count(*) from dbma_sender_relay where email like '%s') > 0) and user_idnr>3



signature.asc (270 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Sebastian Wiesinger-10
In reply to this post by Wietse Venema
* Wietse Venema <[hidden email]> [2011-12-07 17:20]:
> Yes it was. I point the attention to the RIGHT problem, which is
> fixing the suboptimal configuration that does domain queries from
> SQL.

Hi,

with all due respect but for me the important thing at the moment
would be to understand why it works the way it works. I understand
that it is apparently not the "right" way to do it but to be honest
there is not a lot information to point that out.

* There is no information regarding this limitation in trivial-rewrite(8),
  MYSQL_README or mysql_table(5). Maybe it's somewhere else where I
  did not find it.

* Every setup guide or how-to regarding MySQL and postfix that I
  found set up the domain alias table and domain table in
  MySQL. So people are using it.

* Doing a Google Search for "virtual_mailbox_domains = proxy:mysql:"
  returns 74k results, "virtual_mailbox_domains = mysql:" returns 41k
  results. So there are probably a few people using it.

* From my standpoint it makes no sense to have everything in a central
  database and then leave out the domain and domain alias tables. (But
  that is debatable)

* SMTP is not realtime messaging but customers/users nowadays expect that the
  configuration gets active at the same time they enter it into
  the system. If you can't provide that they go elsewere.

I really would like to know if it is not possible to have a temporary
error when trivial-rewrite fails to access the MySQL database. I don't
see any apparent reason for it. If there is one I would like to know.

Last but not least I would really appreciate it if that capability
would be added (make it optional by all means). I think that at least
a few people would benefit from it.

Regards,

Sebastian

--
New GPG Key: 0x93A0B9CE (F4F6 B1A3 866B 26E9 450A  9D82 58A2 D94A 93A0 B9CE)
Old GPG Key-ID: 0x76B79F20 (0x1B6034F476B79F20)
'Are you Death?' ... IT'S THE SCYTHE, ISN'T IT? PEOPLE ALWAYS NOTICE THE SCYTHE.
            -- Terry Pratchett, The Fifth Elephant
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Wietse Venema
Sebastian Wiesinger:
> I really would like to know if it is not possible to have a temporary
> error when trivial-rewrite fails to access the MySQL database. I don't
> see any apparent reason for it. If there is one I would like to know.

You have the right to ask these questions. I recommend that you
spend the energy to make your MySQL server more redundant, if you
care so much about email performance.

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Wietse Venema
Wietse Venema:
> Sebastian Wiesinger:
> > I really would like to know if it is not possible to have a temporary
> > error when trivial-rewrite fails to access the MySQL database. I don't
> > see any apparent reason for it. If there is one I would like to know.
>
> You have the right to ask these questions. I recommend that you
> spend the energy to make your MySQL server more redundant, if you
> care so much about email performance.

OK, one reply. In Postfix do not spend a lot of time on optimizing
the handling of internal errors such as broken MySQL servers.

        Wietse
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Sebastian Wiesinger-10
In reply to this post by Wietse Venema
* Wietse Venema <[hidden email]> [2011-12-08 13:09]:
> Sebastian Wiesinger:
> > I really would like to know if it is not possible to have a temporary
> > error when trivial-rewrite fails to access the MySQL database. I don't
> > see any apparent reason for it. If there is one I would like to know.
>
> You have the right to ask these questions. I recommend that you
> spend the energy to make your MySQL server more redundant, if you
> care so much about email performance.

I just don't want to have connections hang when there could be a
temporary error which would close down these connections. I don't care
so much about mail performance more about (again, in my opinion)
better error handling.

And I had hoped that perhaps this would be an improvement to postfix.
Sadly it seems it was some kind of blasphemy to question the way
postfix does handle this stuff.

But perhaps I'm only getting the wrong impression here.

Regards

Sebastian


--
New GPG Key: 0x93A0B9CE (F4F6 B1A3 866B 26E9 450A  9D82 58A2 D94A 93A0 B9CE)
Old GPG Key-ID: 0x76B79F20 (0x1B6034F476B79F20)
'Are you Death?' ... IT'S THE SCYTHE, ISN'T IT? PEOPLE ALWAYS NOTICE THE SCYTHE.
            -- Terry Pratchett, The Fifth Elephant
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

lst_hoe02
Zitat von Sebastian Wiesinger <[hidden email]>:

> * Wietse Venema <[hidden email]> [2011-12-08 13:09]:
>> Sebastian Wiesinger:
>> > I really would like to know if it is not possible to have a temporary
>> > error when trivial-rewrite fails to access the MySQL database. I don't
>> > see any apparent reason for it. If there is one I would like to know.
>>
>> You have the right to ask these questions. I recommend that you
>> spend the energy to make your MySQL server more redundant, if you
>> care so much about email performance.
>
> I just don't want to have connections hang when there could be a
> temporary error which would close down these connections. I don't care
> so much about mail performance more about (again, in my opinion)
> better error handling.
>
> And I had hoped that perhaps this would be an improvement to postfix.
> Sadly it seems it was some kind of blasphemy to question the way
> postfix does handle this stuff.
No, it means until now no one needs this so important to step up with  
code/patches to improve it. If you really need a reliable mailsystem  
you simply have to use reliable parts. If your mailsystem respond with  
4xx or simply hang in case it is not able to move any mail is just a  
matter of taste.

> But perhaps I'm only getting the wrong impression here.

Yes

Help is always welcome, simply demand how things could be better is useless.

Regards

Andreas



smime.p7s (8K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SMTP hangs when MySQL is down

Reindl Harald-2


Am 08.12.2011 14:45, schrieb [hidden email]:

> Help is always welcome, simply demand how things could be better is useless

you have a bad attitude!

demand how things could be better is useful, everywhere
because it is a hint what can be improved

you need not always to be able making things better to give
hints for imporvements and this hints can often result in a
brainstorming of developers and possibly with a small change
in the code be optimized from peopole who are knowing how to do

calling user-feedback "useless" and "why do you not patch it yourself"
was, is and will always be a bad attitude


signature.asc (270 bytes) Download Attachment
12