1 Topic by poehoes (edited by poehoes 2022-09-07 22:36:38)

  • poehoes
  • Member
  • Offline
  • Registered: 2017-06-21
  • Posts: 6

Topic: No login in Sogo after databases restore (and email not working)

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release): 1.5.1
- Deployed with iRedMail Easy or the downloadable installer? download
- Linux/BSD distribution name and version: lxc virtual Ubuntu 16.04 on host Ubuntu 20.04
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): remote MySQL in docker
- Web server (Apache or Nginx): Nginx
- Manage mail accounts with iRedAdmin-Pro? No
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.
====

Hi,

This is just for archiving to help some else with this problem: No login in Sogo after Database restore

So i had a mysql-crash, and had to install a new server (eg spin up a new docker image with a newer MariaBD-database that couldn't use my old database-volume).

Luckily procedure to restore databases from iredmail have been described here: docs.iredmail.org/backup.restore.html

Problem is that the restore of the Sogo-databases did not include the (working) view on the Vmail-database mailbox with the useraccounts.

Things to consider when restoring database is that you restore all iredmail-accounts for each database, including SELECT-permissions for sogo-user on vmail.mailbox-table.
I don't remember in which order i restored the databases so maybe it is helpful to restore vmail-database first, and then the sogo-database.

In the backup-file (made by standard iredmail backup-script) the view defined as sql-statement as such:

DROP TABLE IF EXISTS `users`;
/*!50001 DROP VIEW IF EXISTS `users`*/;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
/*!50001 CREATE VIEW `users` AS SELECT 
 1 AS `c_uid`,
 1 AS `c_name`,
 1 AS `c_password`,
 1 AS `c_cn`,
 1 AS `mail`,
 1 AS `domain`,
 1 AS `c_webmail`,
 1 AS `c_calendar`,
 1 AS `c_activesync`*/;

but that didn't work. For Mariadb (v10.5.16) i had to rewrite this to:

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `sogo`.`users` AS
select
    `vmail`.`mailbox`.`username` AS `c_uid`,
    `vmail`.`mailbox`.`username` AS `c_name`,
    `vmail`.`mailbox`.`password` AS `c_password`,
    `vmail`.`mailbox`.`name` AS `c_cn`,
    `vmail`.`mailbox`.`username` AS `mail`,
    `vmail`.`mailbox`.`domain` AS `domain`,
    `vmail`.`mailbox`.`enablesogowebmail` AS `c_webmail`,
    `vmail`.`mailbox`.`enablesogocalendar` AS `c_calendar`,
    `vmail`.`mailbox`.`enablesogoactivesync` AS `c_activesync`
from
    `vmail`.`mailbox`
where
    `vmail`.`mailbox`.`enablesogo` = 1
    and `vmail`.`mailbox`.`active` = 1;

----

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

2 Reply by poehoes (edited by poehoes 2022-08-26 15:24:43)

  • poehoes
  • Member
  • Offline
  • Registered: 2017-06-21
  • Posts: 6

Re: No login in Sogo after databases restore (and email not working)

Found this explanation for moving from old to new iredmail-server:

docs.iredmail.org/migrate.to.new.iredmail.server.html#solution-1-export-and-import-sql-database

But the VIEW query still doesn't work in MariaDB.

3 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: No login in Sogo after databases restore (and email not working)

The SQL command used to create VIEW for SOGo is out of date, i updated it moment ago, please try again:
https://docs.iredmail.org/migrate.to.ne … l-database

4 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: No login in Sogo after databases restore (and email not working)

By the way, you can always find the SQL commands in iRedMail source code.

5 Reply by poehoes

  • poehoes
  • Member
  • Offline
  • Registered: 2017-06-21
  • Posts: 6

Re: No login in Sogo after databases restore (and email not working)

Hi, i have another, but connected issue. The mails are not inserted in the database: after sending a mail i get this message from the Reporting-MTA:
Diagnostic-Code: smtp; 451 4.5.0 Error in processing, id=10474-02, gen_mail_id
    FAILED: find_or_save_addr: failed to insert addr user@email.com: sql
    exec: err=1449, HY000, DBD::mysql::st execute failed: The user specified as
    a definer ('admin_iredmail'@'<EDITED-MAILSERVERNAME') does not exist at (eval 99)
    line 172. at (eval 101) line 115.
Probably because i made the users by hand... I don't understand the part in the error-message about a definer?

6 Reply by poehoes (edited by poehoes 2022-08-31 01:00:18)

  • poehoes
  • Member
  • Offline
  • Registered: 2017-06-21
  • Posts: 6

Re: No login in Sogo after databases restore (and email not working)

Right,

Found this query to find the user admin_iredmail:
SELECT DEFINER,'events' FROM INFORMATION_SCHEMA.EVENTS union SELECT DEFINER,'routines' FROM INFORMATION_SCHEMA.ROUTINES union SELECT DEFINER,'triggers' FROM INFORMATION_SCHEMA.TRIGGERS union SELECT DEFINER, 'views' FROM INFORMATION_SCHEMA.VIEWS;

The user is in a trigger: used_quota_before_insert

And I changed the trigger to:
CREATE DEFINER=`iredadmin`@`SERVER_NAME` TRIGGER `used_quota_before_insert`

    BEFORE INSERT ON `used_quota` FOR EACH ROW
    BEGIN
        SET NEW.domain = SUBSTRING_INDEX(NEW.username, '@', -1);
    END

Ai, had to do the same for TRIGGER maddr_email_raw

7 Reply by joinnowuk

  • joinnowuk
  • Member
  • Offline
  • Registered: 2022-09-01
  • Posts: 1

Re: No login in Sogo after databases restore (and email not working)

I don't understand the part in the error-message about a definer?

8 Reply by poehoes (edited by poehoes 2022-09-07 22:35:16)

  • poehoes
  • Member
  • Offline
  • Registered: 2017-06-21
  • Posts: 6

Re: No login in Sogo after databases restore (and email not working)

Well, the story continues:

Making the triggers is the first step, but then you have grant permission to Trigger:
GRANT Trigger ON amavisd.maddr TO 'iredadmin'@'<servername>';
GRANT Trigger ON vmail.used_quota TO 'iredadmin'@'<servername>';

9 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: No login in Sogo after databases restore (and email not working)

we don't offer upgrade tutorial for remote MySQL server. sad

10 Reply by poehoes (edited by poehoes 2022-09-07 22:35:03)

  • poehoes
  • Member
  • Offline
  • Registered: 2017-06-21
  • Posts: 6

Re: No login in Sogo after databases restore (and email not working)

Maybe there is an overview of all SQL-queries that you need to configure a new (remote) MYSQL (actually MariaDB) server?

I now got the error:
host 127.0.0.1[127.0.0.1] said: 451 4.5.0 Error in
    processing, id=11914-06, gen_mail_id FAILED: find_or_save_addr: failed to
    insert addr <emailaddress>: sql exec: err=1143, 42000,
    DBD::mysql::st execute failed: SELECT command denied to user
    'iredadmin'@'<mailserver>' for column 'email' in table 'maddr' at (eval 99)
    line 172. at (eval 101) line 115. (in reply to end of DATA command)

So i gave iredadmin SELECT, and UPDATE permissions.
GRANT Select ON amavisd.maddr TO 'iredadmin'@'<servername>';
GRANT Update ON amavisd.maddr TO 'iredadmin'@'<servername>';

When everything is working i'll compile an upgrade tutorial...

11 Reply by ZhangHuangbin

  • ZhangHuangbin
  • ZhangHuangbin
  • iRedMail Developers
  • Offline
  • Registered: 2009-05-06
  • Posts: 31,082

Re: No login in Sogo after databases restore (and email not working)

iRedAdmin accesses "amavisd" database as sql user "amavisd", not "iredadmin" user. This error doesn't make any sense to me.

Do you have correct sql usernames/passwords in iRedAdmin config file (/opt/www/iredadmin/settings.py)?