1 Topic by Rashef

  • Rashef
  • Rashef
  • Member
  • Offline
  • From: London
  • Registered: 2009-05-22
  • Posts: 93

Topic: Problem with definer in remote MySQL

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 0.9.8
- Linux/BSD distribution name and version: Ubuntu 18.04.1 LTS
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro?
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Hi,

I have a fresh new installation of iredmail, performed following the instruction for using an external MySQL server. I also added sa_bayes.

I can log into iRedAdmin, but at the beginning I could not log into Sogo. The problem was the one described here: https://forum.iredmail.org/topic11776-i … error.html

I solved the issue following this suggestion: https://forum.iredmail.org/post51660.html#p51660

I was able to log into sogo and I tried to send an email between two internal accounts (from test1@mydomain.com to test2@mydomain.com). The message was "sent" in Sogo, but never arrived to the receiver and in the logs I see:

Oct  3 05:28:15 mail amavis[2361]: (02361-04) (!!)TROUBLE in check_mail: gen_mail_id FAILED: find_or_save_addr: failed to insert addr test1@mydomain.com: sql exec: err=1449, HY000, DBD::mysql::st execute failed: The user specified as a definer ('admin_iredmail'@'10.133.59.88') does not exist at (eval 108) line 173. at (eval 110) line 116.
Oct  3 05:28:15 mail amavis[2361]: (02361-04) (!)PRESERVING EVIDENCE in /var/lib/amavis/tmp/amavis-20181003T052815-02361-Fi2KOkkn
Oct  3 05:28:15 mail postfix/amavis/smtp[2730]: 6C1B87F018: to=<test1@mydomain.com>, relay=127.0.0.1[127.0.0.1]:10024, delay=991, delays=991/0.1/0.01/0.05, dsn=4.5.0, status=deferred (host 127.0.0.1[127.0.0.1] said: 451 4.5.0 Error in processing, id=02361-04, gen_mail_id FAILED: find_or_save_addr: failed to insert addr test1@mydomain.com: sql exec: err=1449, HY000, DBD::mysql::st execute failed: The user specified as a definer ('admin_iredmail'@'10.133.59.88') does not exist at (eval 108) line 173. at (eval 110) line 116. (in reply to end of DATA command))

I don't see any other view in the DB with that definer... maybe it is in some config file?

----

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

2 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,792

Re: Problem with definer in remote MySQL

Seems you have incorrect SQL server address/port/username/password in Amavisd config file. Check settings "@storage_sql_dsn" and "@lookup_sql_dsn".

on Debian/Ubuntu, Amavisd config files are stored under /etc/amavis/conf.d/, iRedMail configures "50-user".

3 Reply by Rashef (edited by Rashef 2018-10-04 00:16:17)

  • Rashef
  • Rashef
  • Member
  • Offline
  • From: London
  • Registered: 2009-05-22
  • Posts: 93

Re: Problem with definer in remote MySQL

In MySQL I have:

mysql> SELECT User,Host FROM mysql.user;
+------------------+--------------+
| User             | Host         |
+------------------+--------------+
| amavisd          | 10.133.59.88 |
| ...        | ... |
+------------------+--------------+
15 rows in set (0.00 sec)

I checked and I tested same username and password that i found in the config file you suggested, from 10.133.59.88: 

# mysql -u amavisd -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5600
Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Seems to work perfectly?

Edit 1: Also:

mysql> select * from information_schema.views where definer = 'admin_iredmail@%';
Empty set (0.03 sec)

mysql> select * from `mysql`.`proc` where definer = 'admin_iredmail@%';
Empty set (0.00 sec)

Edit 2: Also, Dovecot's logs are full of:

Oct  3 05:48:01 mail dovecot: dict(3616): Error: sql dict: commit failed: The user specified as a definer ('admin_iredmail'@'10.133.59.88') does not exist

I have even recreated the user, but no luck...

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,792

Re: Problem with definer in remote MySQL

Rashef wrote:

I checked and I tested same username and password that i found in the config file you suggested, from 10.133.59.88: 

# mysql -u amavisd -p
Enter password:

You should try with '-h':

mysql -h <remote-mysql-server-ip> -u amavisd -p

5 Reply by Rashef (edited by Rashef 2018-10-04 04:09:45)

  • Rashef
  • Rashef
  • Member
  • Offline
  • From: London
  • Registered: 2009-05-22
  • Posts: 93

Re: Problem with definer in remote MySQL

ZhangHuangbin wrote:

You should try with '-h':

mysql -h <remote-mysql-server-ip> -u amavisd -p

I think I pasted the wrong text in my previous message (LOL), but it was the same:

# mysql -h 10.133.60.23 -u amavisd -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6363
Server version: 5.7.23-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

6 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 30,792

Re: Problem with definer in remote MySQL

Do you actually have sql user 'admin_iredmail'@'10.133.59.88'?

7 Reply by Rashef

  • Rashef
  • Rashef
  • Member
  • Offline
  • From: London
  • Registered: 2009-05-22
  • Posts: 93

Re: Problem with definer in remote MySQL

Yes I did... anyway, I gave that up and I am trying to achieve a different configuration. We can consider this "closed".