1

Topic: Dovecot sql dict commit failed

==== Required information ====
- iRedMail version (check /etc/iredmail-release):  0.9.5-1
- Linux/BSD distribution name and version: Ubuntu 16.04.1 LTS
- Store mail accounts in which backend (LDAP/MySQL/PGSQL):  MySQL
- Web server (Apache or Nginx): Apache
- Manage mail accounts with iRedAdmin-Pro?: No
- Related log if you're reporting an issue:
====

Every few seconds I am getting this in the dovecot log.

Jan 13 10:30:40 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:40 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:40 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:40 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:41 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:41 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:41 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:41 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:41 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:41 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:42 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:42 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:42 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:42 dict(10273): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:42 dict(10273): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:42 dict(10273): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:43 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:43 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:43 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:43 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:43 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:43 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:44 dict(10273): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:44 dict(10273): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:44 dict(10273): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:44 dict(10273): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:44 dict(10273): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:44 dict(10273): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:45 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:45 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist
Jan 13 10:30:45 dict(10127): Error: sql dict: commit failed: The user specified as a definer ('root'@'localhost') does not exist

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2

Re: Dovecot sql dict commit failed

Did you change any Dovecot settings? show us command output please: dovecot -n

3 (edited by ein_frank 2017-01-14 17:43:45)

Re: Dovecot sql dict commit failed

Nothing is changed.


# 2.2.22 (fe789d2): /etc/dovecot/dovecot.conf
# Pigeonhole version 0.4.13 (7b14904)
# OS: Linux 4.4.0-57-generic x86_64 Ubuntu 16.04.1 LTS
auth_default_realm = xxx.xxx
auth_master_user_separator = *
auth_mechanisms = PLAIN LOGIN
dict {
  acl = mysql:/etc/dovecot/dovecot-share-folder.conf
  quotadict = mysql:/etc/dovecot/dovecot-used-quota.conf
}
first_valid_uid = 2000
last_valid_uid = 2000
listen = * [::]
log_path = /var/log/dovecot.log
mail_gid = 2000
mail_location = maildir:%Lh/Maildir/:INDEX=%Lh/Maildir/
mail_plugins = quota mailbox_alias acl
mail_uid = 2000
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope encoded-character vacati                                                                                                                                                             on subaddress comparator-i;ascii-numeric relational regex imap4flags copy includ                                                                                                                                                             e variables body enotify environment mailbox date index ihave duplicate mime for                                                                                                                                                             everypart extracttext
namespace {
  inbox = yes
  location =
  mailbox Archive {
    auto = no
    special_use = \Archive
  }
  mailbox Archives {
    auto = no
    special_use = \Archive
  }
  mailbox "Deleted Messages" {
    auto = no
    special_use = \Trash
  }
  mailbox Drafts {
    auto = subscribe
    special_use = \Drafts
  }
  mailbox Junk {
    auto = subscribe
    special_use = \Junk
  }
  mailbox "Junk E-mail" {
    auto = no
    special_use = \Junk
  }
  mailbox Sent {
    auto = subscribe
    special_use = \Sent
  }
  mailbox "Sent Items" {
    auto = no
    special_use = \Sent
  }
  mailbox "Sent Messages" {
    auto = no
    special_use = \Sent
  }
  mailbox Spam {
    auto = no
    special_use = \Junk
  }
  mailbox Trash {
    auto = subscribe
    special_use = \Trash
  }
  prefix =
  separator = /
  type = private
}
namespace {
  list = children
  location = maildir:%%Lh/Maildir/:INDEX=%%Lh/Maildir/Shared/%%u
  prefix = Shared/%%u/
  separator = /
  subscriptions = yes
  type = shared
}
passdb {
  args = /etc/dovecot/dovecot-mysql.conf
  driver = sql
}
passdb {
  args = /etc/dovecot/dovecot-master-users
  driver = passwd-file
  master = yes
}
plugin {
  acl = vfile
  acl_shared_dict = proxy::acl
  auth_socket_path = /var/run/dovecot/auth-master
  mailbox_alias_new = Sent Messages
  mailbox_alias_new2 = Sent Items
  mailbox_alias_old = Sent
  mailbox_alias_old2 = Sent
  quota = dict:user::proxy::quotadict
  quota_grace = 10%%
  quota_rule = *:storage=1G
  quota_warning = storage=100%% quota-warning 100 %u
  quota_warning2 = storage=95%% quota-warning 95 %u
  quota_warning3 = storage=90%% quota-warning 90 %u
  quota_warning4 = storage=85%% quota-warning 85 %u
  sieve = %Lh/sieve/dovecot.sieve
  sieve_before = /var/vmail/sieve/dovecot.sieve
  sieve_dir = %Lh/sieve
  sieve_global_dir = /var/vmail/sieve
}
protocols = pop3 imap sieve lmtp
service auth {
  unix_listener /var/spool/postfix/private/dovecot-auth {
    group = postfix
    mode = 0666
    user = postfix
  }
  unix_listener auth-master {
    group = vmail
    mode = 0666
    user = vmail
  }
  unix_listener auth-userdb {
    group = vmail
    mode = 0660
    user = vmail
  }
}
service dict {
  unix_listener dict {
    group = vmail
    mode = 0660
    user = vmail
  }
}
service imap-login {
  process_limit = 500
  service_count = 1
}
service lmtp {
  executable = lmtp -L
  inet_listener lmtp {
    address = 127.0.0.1
    port = 24
  }
  process_min_avail = 5
  unix_listener /var/spool/postfix/private/dovecot-lmtp {
    group = postfix
    mode = 0600
    user = postfix
  }
  user = vmail
}
service managesieve-login {
  inet_listener sieve {
    address = 127.0.0.1
    port = 4190
  }
}
service pop3-login {
  service_count = 1
}
service quota-warning {
  executable = script /usr/local/bin/dovecot-quota-warning.sh
  unix_listener quota-warning {
    group = vmail
    mode = 0660
    user = vmail
  }
}
ssl = required
ssl_cert = </etc/ssl/certs/iRedMail.crt
ssl_cipher_list = ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDH                                                                                                                                                             E-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:                                                                                                                                                             DHE-DSS-AES128-GCM-SHA256:kEDH+AESGCM:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128                                                                                                                                                             -SHA256:ECDHE-RSA-AES128-SHA:ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDH                                                                                                                                                             E-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-RSA-AES128                                                                                                                                                             -SHA256:DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256:DHE-DSS-A                                                                                                                                                             ES256-SHA:DHE-RSA-AES256-SHA:ECDHE-RSA-DES-CBC3-SHA:ECDHE-ECDSA-DES-CBC3-SHA:AES                                                                                                                                                             128-GCM-SHA256:AES256-GCM-SHA384:AES128-SHA256:AES256-SHA256:AES128-SHA:AES256-S                                                                                                                                                             HA:AES:CAMELLIA:DES-CBC3-SHA:!aNULL:!eNULL:!EXPORT:!DES:!RC4:!MD5:!PSK:!aECDH:!E                                                                                                                                                             DH-DSS-DES-CBC3-SHA:!EDH-RSA-DES-CBC3-SHA:!KRB5-DES-CBC3-SHA
ssl_dh_parameters_length = 2048
ssl_key = </etc/ssl/private/iRedMail.key
ssl_prefer_server_ciphers = yes
ssl_protocols = !SSLv2 !SSLv3
userdb {
  args = /etc/dovecot/dovecot-mysql.conf
  driver = sql
}
protocol lda {
  auth_socket_path = /var/run/dovecot/auth-master
  lda_mailbox_autocreate = yes
  lda_mailbox_autosubscribe = yes
  log_path = /var/log/dovecot-sieve.log
  mail_plugins = quota mailbox_alias acl sieve
  postmaster_address = root
}
protocol lmtp {
  info_log_path = /var/log/dovecot-lmtp.log
  lmtp_save_to_detail_mailbox = yes
  mail_plugins = quota sieve
  postmaster_address = postmaster
  recipient_delimiter = +
}
protocol imap {
  imap_client_workarounds = tb-extra-mailbox-sep
  mail_max_userip_connections = 30
  mail_plugins = quota mailbox_alias acl imap_quota imap_acl
}
protocol pop3 {
  mail_max_userip_connections = 30
  mail_plugins = quota mailbox_alias acl
  pop3_client_workarounds = outlook-no-nuls oe-ns-eoh
  pop3_uidl_format = %08Xu%08Xv
}

4

Re: Dovecot sql dict commit failed

/etc/dovecot/dovecot-share-folder.conf
/etc/dovecot/dovecot-used-quota.conf

Both contain an valid user/password.

connect = host=127.0.0.1 dbname=vmail user=vmailadmin password=xxx

5

Re: Dovecot sql dict commit failed

i saw one similar report before with a remote SQL server, but seems you're running local sql server.

please turn on debug mode in Dovecot and show us detailed debug log related to this issue.
fyi: http://www.iredmail.org/docs/debug.dovecot.html

6

Re: Dovecot sql dict commit failed

Weird enough, I havent encountered this anymore after I posted this thread. I think I was using imapsync at the time, ill try it next month again if it happens together with imapsync.

7

Re: Dovecot sql dict commit failed

Feel free to create a new forum thread if it happens again.