1 Topic by dark-vex

  • dark-vex
  • Member
  • Offline
  • Registered: 2017-10-02
  • Posts: 13

Topic: Re-creation of SOGo view fails

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version (check /etc/iredmail-release):
1.4.0 MARIADB edition.
- Deployed with iRedMail Easy or the downloadable installer?
downloadable installer
- Linux/BSD distribution name and version: Debian 10
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- 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, I'm trying to upgrade from 1.4.0 to 1.4.1 (https://docs.iredmail.org/upgrade.iredm … 1.4.1.html) and I'm stuck at the step "For MySQL and MariaDB backends" in particular at the re-creation of SOGo view.

When I perform "mysql sogo < /tmp/sogo.mysql", MySQL return this errror:

root@mail2:~# mysql sogo < /tmp/sogo.mysql
ERROR 4092 (42S02) at line 1: Unknown VIEW: 'sogo.users'

I'm attaching the full logs/commands executed:

# Step Add new SQL columns in vmail.mailbox table for per-user SOGo webmail / calendar / activesync service control
root@mail2:~# wget -O /tmp/iredmail.mysql [url]https://github.com/iredmail/iRedMail/raw/1.4.1/update/1.4.1/iredmail.mysql[/url]
--2021-09-08 12:02:15--  [url]https://github.com/iredmail/iRedMail/raw/1.4.1/update/1.4.1/iredmail.mysql[/url]
Resolving github.com (github.com)... 140.82.121.4
Connecting to github.com (github.com)|140.82.121.4|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: [url]https://raw.githubusercontent.com/iredmail/iRedMail/1.4.1/update/1.4.1/iredmail.mysql[/url] [following]
--2021-09-08 12:02:15--  [url]https://raw.githubusercontent.com/iredmail/iRedMail/1.4.1/update/1.4.1/iredmail.mysql[/url]
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.111.133, 185.199.108.133, 185.199.109.133, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.111.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 289 [text/plain]
Saving to: '/tmp/iredmail.mysql'

/tmp/iredmail.mysql                     100%[=============================================================================>]     289  --.-KB/s    in 0s

2021-09-08 12:02:16 (6.05 MB/s) - '/tmp/iredmail.mysql' saved [289/289]

root@mail2:~# mysql vmail < /tmp/iredmail.mysql
root@mail2:~# rm -f /tmp/iredmail.mysql

# Step SOGo: Re-create SQL VIEW and update config file
root@mail2:~# wget -O /tmp/sogo.mysql [url]https://github.com/iredmail/iRedMail/raw/1.4.1/update/1.4.1/sogo.mysql[/url]
--2021-09-08 12:02:23--  [url]https://github.com/iredmail/iRedMail/raw/1.4.1/update/1.4.1/sogo.mysql[/url]
Resolving github.com (github.com)... 140.82.121.3
Connecting to github.com (github.com)|140.82.121.3|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: [url]https://raw.githubusercontent.com/iredmail/iRedMail/1.4.1/update/1.4.1/sogo.mysql[/url] [following]
--2021-09-08 12:02:24--  [url]https://raw.githubusercontent.com/iredmail/iRedMail/1.4.1/update/1.4.1/sogo.mysql[/url]
Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 185.199.110.133, 185.199.111.133, 185.199.108.133, ...
Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|185.199.110.133|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 373 [text/plain]
Saving to: '/tmp/sogo.mysql'

/tmp/sogo.mysql                         100%[=============================================================================>]     373  --.-KB/s    in 0s

2021-09-08 12:02:24 (9.28 MB/s) - '/tmp/sogo.mysql' saved [373/373]

root@mail2:~# mysql sogo < /tmp/sogo.mysql
ERROR 1054 (42S22) at line 3: Unknown column 'enablesogo' in 'where clause'
root@mail2:~# rm -f /tmp/sogo.mysql

----

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

2 Reply by dark-vex (edited by dark-vex 2021-09-08 18:53:38)

  • dark-vex
  • Member
  • Offline
  • Registered: 2017-10-02
  • Posts: 13

Re: Re-creation of SOGo view fails

I forgot to mention, I have tried editing /tmp/sogo.mysql and comment the DROP in the first line but then still failing with a different error

root@mail2:~# mysql sogo < /tmp/sogo.mysql
ERROR 1054 (42S22) at line 3: Unknown column 'enablesogo' in 'where clause'
MariaDB [vmail]> describe mailbox;
+--------------------------+-----------------+------+-----+---------------------+-------+
| Field                    | Type            | Null | Key | Default             | Extra |
+--------------------------+-----------------+------+-----+---------------------+-------+
| username                 | varchar(255)    | NO   | PRI |                     |       |
| password                 | varchar(255)    | NO   |     |                     |       |
| name                     | varchar(255)    | NO   |     |                     |       |
| language                 | varchar(5)      | NO   |     | en_US               |       |
| storagebasedirectory     | varchar(255)    | NO   |     | /var/vmail          |       |
| storagenode              | varchar(255)    | NO   |     | vmail1              |       |
| maildir                  | varchar(255)    | NO   |     |                     |       |
| quota                    | bigint(20)      | NO   |     | 0                   |       |
| domain                   | varchar(255)    | NO   | MUL |                     |       |
| transport                | varchar(255)    | NO   |     |                     |       |
| department               | varchar(255)    | NO   | MUL |                     |       |
| rank                     | varchar(255)    | NO   |     | normal              |       |
| employeeid               | varchar(255)    | YES  | MUL |                     |       |
| isadmin                  | tinyint(1)      | NO   | MUL | 0                   |       |
| isglobaladmin            | tinyint(1)      | NO   | MUL | 0                   |       |
| enablesmtp               | tinyint(1)      | NO   | MUL | 1                   |       |
| enablesmtpsecured        | tinyint(1)      | NO   | MUL | 1                   |       |
| enablepop3               | tinyint(1)      | NO   | MUL | 1                   |       |
| enablepop3secured        | tinyint(1)      | NO   | MUL | 1                   |       |
| enableimap               | tinyint(1)      | NO   | MUL | 1                   |       |
| enableimapsecured        | tinyint(1)      | NO   | MUL | 1                   |       |
| enabledeliver            | tinyint(1)      | NO   | MUL | 1                   |       |
| enablelda                | tinyint(1)      | NO   | MUL | 1                   |       |
| enablemanagesieve        | tinyint(1)      | NO   | MUL | 1                   |       |
| enablemanagesievesecured | tinyint(1)      | NO   | MUL | 1                   |       |
| enablesieve              | tinyint(1)      | NO   | MUL | 1                   |       |
| enablesievesecured       | tinyint(1)      | NO   | MUL | 1                   |       |
| enableinternal           | tinyint(1)      | NO   | MUL | 1                   |       |
| enabledoveadm            | tinyint(1)      | NO   | MUL | 1                   |       |
| enablelib-storage        | tinyint(1)      | NO   | MUL | 1                   |       |
| lastlogindate            | datetime        | NO   |     | 0000-00-00 00:00:00 |       |
| lastloginipv4            | int(4) unsigned | NO   |     | 0                   |       |
| lastloginprotocol        | char(255)       | NO   |     |                     |       |
| disclaimer               | text            | YES  |     | NULL                |       |
| passwordlastchange       | datetime        | NO   | MUL | 0000-00-00 00:00:00 |       |
| created                  | datetime        | NO   |     | 0000-00-00 00:00:00 |       |
| modified                 | datetime        | NO   |     | 0000-00-00 00:00:00 |       |
| expired                  | datetime        | NO   | MUL | 9999-12-31 00:00:00 |       |
| active                   | tinyint(1)      | NO   | MUL | 1                   |       |
| bytes                    | bigint(20)      | NO   |     | 0                   |       |
| messages                 | bigint(20)      | NO   |     | 0                   |       |
| enablelmtp               | tinyint(1)      | NO   | MUL | 1                   |       |
| settings                 | text            | YES  |     | NULL                |       |
| enableindexer-worker     | tinyint(1)      | NO   | MUL | 1                   |       |
| allow_nets               | text            | YES  |     | NULL                |       |
| enableimaptls            | tinyint(1)      | NO   | MUL | 1                   |       |
| enablepop3tls            | tinyint(1)      | NO   | MUL | 1                   |       |
| enablesievetls           | tinyint(1)      | NO   | MUL | 1                   |       |
| mailboxformat            | varchar(50)     | NO   |     | maildir             |       |
| mailboxfolder            | varchar(50)     | NO   |     | Maildir             |       |
| enablequota-status       | tinyint(1)      | NO   | MUL | 1                   |       |
| enablesogowebmail        | char(1)         | NO   |     | y                   |       |
| enablesogocalendar       | char(1)         | NO   |     | y                   |       |
| enablesogoactivesync     | char(1)         | NO   |     | y                   |       |
+--------------------------+-----------------+------+-----+---------------------+-------+
54 rows in set (0.006 sec)

EDIT:
I have figured out looking on github repository that from iRedMail version 0.9.5 the mailbox table should contain the column "enablesogo" but on mine installation it wasn't present.
On this system I did a fresh install with iRedMail 1.2.1 but I have imported a backup from an older version so probably this is the reason why it was missing..

There is a script for perform a sanity check of the vmail db?

3 Reply by ZhangHuangbin

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

Re: Re-creation of SOGo view fails

dark-vex wrote:

There is a script for perform a sanity check of the vmail db?

No.
You have to compare it with our latest sql structure file:
https://github.com/iredmail/iRedMail/tr … s/iredmail