1

Topic: iredadmin issues after upgrade to 0.9.7

==== Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.7
- Linux/BSD distribution name and version: ubuntu 16.04
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): mysql
- Web server (Apache or Nginx):apache
- Manage mail accounts with iRedAdmin-Pro? no
- [IMPORTANT] Related original log or error message is required if you're experiencing an issue.

Hello,

After performing the upgrade to 0.9.7 i am experiencing the following issues in iredadmin.

in iredadmin when i am attempting to add a new domain i am getting the following error message at the top of the page

Error: (1054, "Unknown column 'goto' in 'where clause'")

after going through the process of adding a new domain, it does not show up, nor do the existing ones.

As well when attempting to add a new user i am getting the following error message at the top of the web page,

Error: (1054, "Unknown column 'alias.goto' in 'field list'")

The existing accounts for a particular domain are being listed, but i am unable to edit them or add a new account.

----

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

2

Re: iredadmin issues after upgrade to 0.9.7

Please upgrade iRedMail to the latest iRedMail-0.9.7, it contains some SQL structure change.
http://www.iredmail.org/docs/upgrade.ir … 0.9.7.html

3 (edited by andrew88 2017-07-04 05:13:11)

Re: iredadmin issues after upgrade to 0.9.7

ZhangHuangbin wrote:

Please upgrade iRedMail to the latest iRedMail-0.9.7, it contains some SQL structure change.
http://www.iredmail.org/docs/upgrade.ir … 0.9.7.html

i did perform the upgrade already, hence why i am now having issues.  Are you saying i should re run these sql commands again? 

Create required new SQL tables

Please connect to MySQL server as MySQL root user, and execute SQL commands below to create required new tables:

USE vmail;

CREATE TABLE IF NOT EXISTS alias_moderators (
    id BIGINT(20) UNSIGNED AUTO_INCREMENT,
    address VARCHAR(255) NOT NULL DEFAULT '',
    moderator VARCHAR(255) NOT NULL DEFAULT '',
    domain VARCHAR(255) NOT NULL DEFAULT '',
    dest_domain VARCHAR(255) NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    UNIQUE INDEX (address, moderator),
    INDEX (domain),
    INDEX (dest_domain)
) ENGINE=InnoDB;

-- Forwardings. it contains
--  - members of mail alias account
--  - per-account alias addresses
--  - per-user mail forwarding addresses
CREATE TABLE IF NOT EXISTS forwardings (
    id BIGINT(20) UNSIGNED AUTO_INCREMENT,
    address VARCHAR(255) NOT NULL DEFAULT '',
    forwarding VARCHAR(255) NOT NULL DEFAULT '',
    domain VARCHAR(255) NOT NULL DEFAULT '',
    dest_domain VARCHAR(255) NOT NULL DEFAULT '',
    -- defines whether it's a standalone mail alias account. 0=no, 1=yes.
    is_list TINYINT(1) NOT NULL DEFAULT 0,
    -- defines whether it's a mail forwarding address of mail user. 0=no, 1=yes.
    is_forwarding TINYINT(1) NOT NULL DEFAULT 0,
    -- defines whether it's a per-account alias address. 0=no, 1=yes.
    is_alias TINYINT(1) NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (id),
    UNIQUE INDEX (address, forwarding),
    INDEX (domain),
    INDEX (dest_domain),
    INDEX (is_list),
    INDEX (is_alias)
) ENGINE=InnoDB;

Migrate mail accounts

Please download script used to migrate mail accounts, and run it directly:

cd /root/
wget https://bitbucket.org/zhb/iredmail/raw/ … s_table.py
python migrate_sql_alias_table.py

once all of that is completed, i can see the new tables in vmail with the show tables command, and when i run the migrate script i get these errors;

root@mail:~# python migrate_sql_alias_table.py
* Read SQL username/password from iRedAdmin config file: /opt/www/iredadmin/settings.pyc
* Backend: mysql
Traceback (most recent call last):
  File "migrate_sql_alias_table.py", line 55, in <module>
    records = db.select('alias', what='address,goto,moderators,domain,active,islist,is_alias')
  File "/usr/lib/python2.7/dist-packages/web/db.py", line 682, in select
    return self.query(qout, processed=True)
  File "/usr/lib/python2.7/dist-packages/web/db.py", line 644, in query
    self._db_execute(db_cursor, sql_query)
  File "/usr/lib/python2.7/dist-packages/web/db.py", line 587, in _db_execute
    out = cur.execute(query, params)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 226, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 'goto' in 'field list'")

4

Re: iredadmin issues after upgrade to 0.9.7

Did you already dropped columns in "vmail.alias" table before running "migrate_sql_alias_table.py"?

5

Re: iredadmin issues after upgrade to 0.9.7

ZhangHuangbin wrote:

Did you already dropped columns in "vmail.alias" table before running "migrate_sql_alias_table.py"?

no, i did the migrate first and then dropped the columns

6

Re: iredadmin issues after upgrade to 0.9.7

Please upgrade iRedAdmin open source edition to the latest 0.8.
http://www.iredmail.org/docs/upgrade.ir … release-08

7

Re: iredadmin issues after upgrade to 0.9.7

That resolved the issue.  Thank you fro the help!