Dovecot error with MySQL

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

Dovecot error with MySQL

Cesar
This post was updated on .
Hello People

Can anybody help me?

My scenario:
- One computer with:
a) postfix-2.6.6-2.2.el6_1.x86_64
b) mysql-5.1.66-2.el6_3.x86_64
c) dovecot-2.0.9-2.el6_1.1.x86_64
d) dovecot-mysql-2.0.9-2.el6_1.1.x86_64

Postfix and Dovecot auth over MySQL

The Problem:
- logwatch show me:
 Dovecot disconnects:
    auth failed, 1 attempts: 1 Time(s)
    in IDLE: 2 Time(s)
    no auth attempts: 6 Time(s)
    no reason: 6 Time(s)
 
 **Unmatched Entries**
    dovecot: auth: Error: mysql: Query failed, retrying: MySQL server has gone away: 125 Time(s)
(125 Times Wow)

For example part of maillog show me:
About MySQL auth failed:
Mar 10 03:55:05 gateway dovecot: auth: Error: mysql: Query failed, retrying: MySQL server has gone away
Mar 10 03:55:05 gateway dovecot: auth: mysql: Connected to localhost (mail)
Mar 10 03:55:05 gateway dovecot: pop3-login: Login: user=user-name@domain.com>, method=PLAIN, rip=192.168.100.31, lip=190.1
28.232.2, mpid=16352
Mar 10 03:55:05 gateway dovecot: pop3(user-name@domain.com): Disconnected: Logged out top=0/0, retr=0/0, del=0/0, size=0

The Question:
How to fix it? (i don't want to see the dovecot auth error mysql)

I will be very grateful to anyone who can help me
Best Regards
Cesar
Reply | Threaded
Open this post in threaded view
|

Re: Dovecot error wuth MySQL

Reindl Harald-2


Am 14.03.2013 09:28, schrieb Cesar:

> Hello PeopleCan anybody help me?My scenario:- One computer with:a)
> postfix-2.6.6-2.2.el6_1.x86_64b) mysql-5.1.66-2.el6_3.x86_64c)
> dovecot-2.0.9-2.el6_1.1.x86_64d) dovecot-mysql-2.0.9-2.el6_1.1.x86_64Postfix
> and Dovecot auth over MySQLThe Problem:- logwatch show me: Dovecot
> disconnects:    auth failed, 1 attempts: 1 Time(s)    in IDLE: 2 Time(s)  
> no auth attempts: 6 Time(s)    no reason: 6 Time(s)  **Unmatched Entries**  
> dovecot: auth: Error: mysql: Query failed, retrying: MySQL server has gone
> away: 125 Time(s)(125 Times Wow)For example part of maillog show me:About
> MySQL auth failed:Mar 10 03:55:05 gateway dovecot: auth: Error: mysql: Query
> failed, retrying: MySQL server has gone awayMar 10 03:55:05 gateway dovecot:
> auth: mysql: Connected to localhost (mail)Mar 10 03:55:05 gateway dovecot:
> pop3-login: Login: user=[hidden email]>, method=PLAIN,
> rip=192.168.100.31, lip=190.128.232.2, mpid=16352Mar 10 03:55:05 gateway
> dovecot: pop3([hidden email]): Disconnected: Logged out top=0/0,
> retr=0/0, del=0/0, size=0

> The Question:How to fix it? (i don't want to see
> the dovecot auth error mysql)I will be very grateful to anyone who can help
> me

not not shutdown mysqld or filter in /etc/rsyslog.conf which
would be a silly idea because the next time with a problem
you supress maybe important messages and starts eeking for help



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

Re: Dovecot error wuth MySQL

Robert Schetterer-2
In reply to this post by Cesar
Am 14.03.2013 09:28, schrieb Cesar:

> Hello PeopleCan anybody help me?My scenario:- One computer with:a)
> postfix-2.6.6-2.2.el6_1.x86_64b) mysql-5.1.66-2.el6_3.x86_64c)
> dovecot-2.0.9-2.el6_1.1.x86_64d) dovecot-mysql-2.0.9-2.el6_1.1.x86_64Postfix
> and Dovecot auth over MySQLThe Problem:- logwatch show me: Dovecot
> disconnects:    auth failed, 1 attempts: 1 Time(s)    in IDLE: 2 Time(s)  
> no auth attempts: 6 Time(s)    no reason: 6 Time(s)  **Unmatched Entries**  
> dovecot: auth: Error: mysql: Query failed, retrying: MySQL server has gone
> away: 125 Time(s)(125 Times Wow)For example part of maillog show me:About
> MySQL auth failed:Mar 10 03:55:05 gateway dovecot: auth: Error: mysql: Query
> failed, retrying: MySQL server has gone awayMar 10 03:55:05 gateway dovecot:
> auth: mysql: Connected to localhost (mail)Mar 10 03:55:05 gateway dovecot:
> pop3-login: Login: user=[hidden email]>, method=PLAIN,
> rip=192.168.100.31, lip=190.128.232.2, mpid=16352Mar 10 03:55:05 gateway
> dovecot: pop3([hidden email]): Disconnected: Logged out top=0/0,
> retr=0/0, del=0/0, size=0The Question:How to fix it? (i don't want to see
> the dovecot auth error mysql)I will be very grateful to anyone who can help
> me
>
>
>
> -----
> Best Regards
> Cesar
> --
> View this message in context: http://dovecot.2317879.n4.nabble.com/Dovecot-error-wuth-MySQL-tp40765.html
> Sent from the Dovecot mailing list archive at Nabble.com.
>

looks like your mysql server was not reachable


Best Regards
MfG Robert Schetterer

--
[*] sys4 AG

http://sys4.de, +49 (89) 30 90 46 64
Franziskanerstraße 15, 81669 München

Sitz der Gesellschaft: München, Amtsgericht München: HRB 199263
Vorstand: Patrick Ben Koetter, Axel von der Ohe, Marc Schiffbauer
Aufsichtsratsvorsitzender: Joerg Heidrich
Reply | Threaded
Open this post in threaded view
|

Re: Dovecot error wuth MySQL

Jerry-146
In reply to this post by Cesar
On Thu, 14 Mar 2013 01:28:23 -0700 (PDT)
Cesar articulated:

> Hello PeopleCan anybody help me?My scenario:- One computer with:a)
> postfix-2.6.6-2.2.el6_1.x86_64b) mysql-5.1.66-2.el6_3.x86_64c)
> dovecot-2.0.9-2.el6_1.1.x86_64d)
> dovecot-mysql-2.0.9-2.el6_1.1.x86_64Postfix and Dovecot auth over
> MySQLThe Problem:- logwatch show me: Dovecot disconnects:    auth
> failed, 1 attempts: 1 Time(s)    in IDLE: 2 Time(s) no auth attempts:
> 6 Time(s)    no reason: 6 Time(s)  **Unmatched Entries** dovecot:
> auth: Error: mysql: Query failed, retrying: MySQL server has gone
> away: 125 Time(s)(125 Times Wow)For example part of maillog show
> me:About MySQL auth failed:Mar 10 03:55:05 gateway dovecot: auth:
> Error: mysql: Query failed, retrying: MySQL server has gone awayMar
> 10 03:55:05 gateway dovecot: auth: mysql: Connected to localhost
> (mail)Mar 10 03:55:05 gateway dovecot: pop3-login: Login:
> user=[hidden email]>, method=PLAIN, rip=192.168.100.31,
> lip=190.128.232.2, mpid=16352Mar 10 03:55:05 gateway dovecot:
> pop3([hidden email]): Disconnected: Logged out top=0/0,
> retr=0/0, del=0/0, size=0The Question:How to fix it? (i don't want to
> see the dovecot auth error mysql)I will be very grateful to anyone
> who can help

The line wrapping is making this mess extremely difficult to read and
decipher. Are you even sure the MySQL server is even running? Have you
tried shutting down your system and rebooting and observing what
transpires? Do you have log entries from the MySQL and Dovecot servers?

Post the unaltered output of:

        postconf -n
        dovecot -n

It might be helpful to post the MySQL file that dovecot is using to
contact the MySQL server also.


--
Jerry ♔

Disclaimer: off-list followups get on-list replies or get ignored.
Please do not ignore the Reply-To header.
__________________________________________________________________

Reply | Threaded
Open this post in threaded view
|

Re: Dovecot error wuth MySQL

Cesar
This post was updated on .
Thanls for your answers (about pf my problem with Dovecot and MySQL)

Re-Edit:
Are you even sure the MySQL server is even running? = Yes
Have you tried shutting down your system and rebooting and observing what
transpires? = Yes
Do you have log entries from the MySQL and Dovecot servers? = The "Logs" do not say anything interesting, only reports the problem


This is the answers:
postconf -n:

alias_database = hash:/etc/aliases
alias_maps = hash:/etc/aliases
broken_sasl_auth_clients = yes
command_directory = /usr/sbin
config_directory = /etc/postfix
daemon_directory = /usr/libexec/postfix
data_directory = /var/lib/postfix
debug_peer_level = 2
header_checks = regexp:/etc/postfix/header_checks
html_directory = no
inet_interfaces = all
inet_protocols = ipv4
mail_owner = postfix
mailq_path = /usr/bin/mailq.postfix
manpage_directory = /usr/share/man
mydestination = localhost
mydomain = [the-domain].com
myhostname = mail.[the-domain].com
mynetworks_style = host
myorigin = $mydomain
newaliases_path = /usr/bin/newaliases.postfix
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
queue_directory = /var/spool/postfix
readme_directory = /usr/share/doc/postfix-2.6.6/README_FILES
sample_directory = /usr/share/doc/postfix-2.6.6/samples
sendmail_path = /usr/sbin/sendmail.postfix
setgid_group = postdrop
smtpd_recipient_restrictions = permit_sasl_authenticated, permit_mynetworks, reject_unauth_destination
smtpd_sasl_auth_enable = yes
smtpd_sasl_path = private/auth
smtpd_sasl_security_options = noanonymous
smtpd_sasl_type = dovecot
smtpd_tls_cert_file = /etc/pki/tls/certs/mail.[the-domain].com.crt
smtpd_tls_key_file = /etc/pki/tls/private/mail.[the-domain].com.key
smtpd_tls_loglevel = 1
smtpd_tls_security_level = may
smtpd_tls_session_cache_database = btree:/var/lib/postfix/smtpd_tls_cache.db
smtpd_tls_session_cache_timeout = 3600s
tls_random_source = dev:/dev/urandom
transport_maps = hash:/etc/postfix/transport
unknown_local_recipient_reject_code = 550
virtual_alias_maps = proxy:mysql:/etc/postfix/mysql_virtual_alias_maps.cf,    proxy:mysql:/etc/postfix/mysql_virtual_alias_domain_maps.cf,    proxy:mysql:/etc/postfix/mysql_virtual_alias_domain_catchall_maps.cf
virtual_gid_maps = static:5000
virtual_mailbox_domains = proxy:mysql:/etc/postfix/mysql_virtual_domains_maps.cf
virtual_mailbox_maps = proxy:mysql:/etc/postfix/mysql_virtual_mailbox_maps.cf,    proxy:mysql:/etc/postfix/mysql_virtual_alias_domain_mailbox_maps.cf
virtual_transport = dovecot
virtual_uid_maps = static:5000

dovecot -n:
# 2.0.9: /etc/dovecot/dovecot.conf
# OS: Linux 2.6.32-279.el6.x86_64 x86_64 CentOS release 6.3 (Final)
auth_mechanisms = plain login cram-md5
dict {
  quotadict = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
disable_plaintext_auth = no
first_valid_gid = 5000
first_valid_uid = 5000
last_valid_gid = 5000
last_valid_uid = 5000
mail_gid = 5000
mail_location = maildir:%hMaildir
mail_plugins = quota
mail_uid = 5000
mbox_write_locks = fcntl
namespace {
  inbox = yes
  location =
  prefix = INBOX.
  separator =
  type = private
}
passdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
plugin {
  quota = dict:user::proxy::quotadict
  quota_warning = storage=95%% quota-warning 95 %u
  quota_warning2 = storage=80%% quota-warning 80 %u
}
postmaster_address = postmaster@[the-domain].com
service auth-worker {
  user = $default_internal_user
}
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0660
    user = postfix
  }
  unix_listener auth-userdb {
    group = vmail
    mode = 0600
    user = vmail
  }
}
service dict {
  unix_listener dict {
    user = vmail
  }
}
service quota-warning {
  executable = script /etc/dovecot/quota-warning.sh
  unix_listener quota-warning {
    user = vmail
  }
  user = vmail
}
ssl_cert = </etc/pki/tls/certs/mail.[the-domain].com.crt
ssl_cipher_list = ALL:!LOW:!SSLv2
ssl_key = </etc/pki/tls/private/mail.[the-domain].com.key
userdb {
  driver = prefetch
}
userdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
protocol lda {
  mail_plugins = quota
}
protocol imap {
  mail_plugins = quota imap_quota
}
protocol pop3 {
  mail_plugins = quota
  pop3_uidl_format = %08Xu%08Xv
}

May be the configuration of MySQL,
<b>Note: I don't want to use write cache, read cache is welcome,
this is my my.cnf file

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
user = mysql

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

default-storage-engine = InnoDB
innodb_flush_log_at_trx_commit=1
sync_binlog=1
skip-external-locking
#max_delayed_threads = 0
connect_timeout = 15
wait_timeout = 60
interactive_timeout = 60
bind-address = 127.0.0.1

log_error = /var/log/mysqld.log
log_warnings = 0
local_infile = 0
skip-symbolic-links
max_connections = 200
max_user_connections = 170
back_log = 50
max_join_size = 256M
max_allowed_packet = 16M

key_buffer_size = 64M
myisam-recover = BACKUP,FORCE
query_cache_size = 64M
query_cache_type = 1
query_cache_limit = 1M
delay_key_write = OFF

table_open_cache = 4096
thread_cache_size = 64
tmp_table_size = 64M
max_tmp_tables = 64
max_heap_table_size = 64M
open_files_limit = 8192
thread_stack = 256K


innodb_thread_concurrency = 8
innodb_log_file_size = 5M
innodb_file_per_table = 1
innodb_buffer_pool_size = 128M
innodb_flush_method = O_DIRECT

long_query_time = 10
slow_query_log= 1
slow_query_log_file=/var/log/mysqld-slow.log
log-queries-not-using-indexes
log-short-format= 1

[myisamchk]
key_buffer                     = 512M
sort_buffer_size               = 512M
read_buffer                    = 16M
write_buffer                   = 16M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Best Regards
Cesar
Reply | Threaded
Open this post in threaded view
|

Re: Dovecot error with MySQL

Cesar
Hi Jerry
can you or anybody help me please?

In addition to my previous post where I show the configurations (dovecot postconf-n and-n) added more files that dovecot is using to contact the MySQL server:

dovecot.conf:
protocols = imap pop3 lmtp
dict {
  quotadict = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
!include conf.d/*.conf
!include_try /etc/dovecot/local.conf

File /etc/docevot/conf.d/auth-sql.conf.ext:
passdb {
  driver = sql
}
userdb {
  driver = prefetch
}
userdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf.ext
}

dovecot-dict-sql.conf.ext:
connect = host=localhost dbname=mail user=mail_admin password=[mysql-passwd-Access]
map {
  pattern = priv/quota/storage
  table = quota2
  username_field = username
  value_field = bytes
}
map {
  pattern = priv/quota/messages
  table = quota2
  username_field = username
  value_field = messages
}
dovecot-sql.conf.ext (number of lines is only for reference)
(line 1:)driver = mysql
(line 2:)connect = host=localhost dbname=mail user=mail_admin password=[mysql-passwd-Access]
(line 3:)default_pass_scheme = MD5
(line 4:)user_query = SELECT CONCAT('/var/mail/vhosts/', maildir) AS home, 5000 AS uid, 5000 AS gid, CONCAT('*:bytes=', quota) as quota_rule FROM mailbox WHERE username = '%u' AND active='1'
(line 5:)password_query = SELECT username AS user, password, CONCAT('/var/mail/vhosts/', maildir) AS userdb_home, 5000 AS userdb_uid, 5000 AS userdb_gid, CONCAT('*:bytes=', quota) as userdb_quota_rule FROM mailbox WHERE username = '%u' AND active='1'

In the expectation of your reply I say goodbye.
Best Regards
Cesar
Reply | Threaded
Open this post in threaded view
|

Re: Dovecot error wuth MySQL

Noel Butler
In reply to this post by Robert Schetterer-2
On Thu, 2013-03-14 at 11:32 +0100, Robert Schetterer wrote:

> Am 14.03.2013 09:28, schrieb Cesar:



> > dovecot: auth: Error: mysql: Query failed, retrying: MySQL server has gone
> > away: 125 Time(s)(125 Times Wow)For example part of maillog show me:About
> > MySQL auth failed:Mar 10 03:55:05 gateway dovecot: auth: Error: mysql: Query



>
> looks like your mysql server was not reachable
>


I've seen this before with people who play silly buggers with the
timeout values with mysql, if the OP has not done so, enable logging on
mysql and see what it is doing, else,  undo the changes and leave the
mysql timeout values at default.



signature.asc (501 bytes) Download Attachment