1 Topic by insanadair (edited by insanadair 2011-04-11 19:19:54)

  • insanadair
  • Member
  • Offline
  • Registered: 2010-07-29
  • Posts: 37

Topic: About "storagenode" field on mysql Error

Hi
My iredmail ( Backend MYSQL ) version is 0.6.0 and I upgrade 0.6.1 than upgrade 0.7.0

I havent got a problem during 0.6.0 to 0.6.1 upgrade

But 0.6.1 to 0.7.0 upgrade have a problem I use "Upgrade/iRedMail/0.6.1-0.7.0"  prosedure

on MYSQL backend only part says ---> Check domain status in postfix and dovecot

--------A---------
Edit postfix config file /etc/postfix/mysql_virtual_mailbox_maps.cf, change query = to below new setting: 

query       = SELECT CONCAT(mailbox.storagenode, '/', mailbox.maildir) FROM mailbox,domain WHERE mailbox.username='%s' AND mailbox.active='1' AND mailbox.enabledeliver='1' AND domain.domain = mailbox.domain AND domain.active='1'

--------B---------
Edit dovecot config file /etc/dovecot-mysql.conf (RHEL/CentOS) or /etc/dovecot/dovecot-mysql.conf (Debian/Ubuntu/openSUSE) or /usr/local/etc/dovecot-mysql.conf (FreeBSD): 

user_query = SELECT CONCAT(mailbox.storagebasedirectory, '/', mailbox.storagenode, '/', mailbox.maildir) AS home, CONCAT('*:bytes=', mailbox.quota*1048576) AS quota_rule FROM mailbox,domain WHERE mailbox.username='%u' AND mailbox.domain='%d' AND mailbox.enable%Ls%Lc=1 AND mailbox.domain=domain.domain AND mailbox.active=1 AND domain.backupmx=0 AND domain.active=1

But I havent got a storagenode field  and I applied upper prosedure  I have a error bellow 

dovecot: Apr 08 13:07:34 Error: auth-worker(default): sql(xxxx@xxxxx.com,1X.X.X..149): User query failed: Unknown column 'mailbox.storagenode' in 'field list'
dovecot: Apr 08 13:07:34 Info: imap-login: Internal login failure (auth failed, 1 attempts): user=<xxxx@xxxxx.com>, method=PLAIN, rip=X.X.X.149, lip=1X.X.X..18, TLS
dovecot: Apr 08 13:07:34 Error: auth-worker(default): sql(xxxx@xxxxx.com,X.X.X.49): User query failed: Unknown column 'mailbox.storagenode' in 'field list'
dovecot: Apr 08 13:07:34 Info: imap-login: Internal login failure (auth failed, 1 attempts): user=<xxxx@xxxxx.com>, method=PLAIN, rip=X.X.X.149, lip=X.X.X.18, TLS
dovecot: Apr 08 13:07:36 Error: auth-worker(default): sql(xxxx@xxxxx.com,X.X.X.237): User query failed: Unknown column 'mailbox.storagenode' in 'field list'
dovecot: Apr 08 13:07:36 Info: imap-login: Internal login failure (auth failed, 1 attempts): user=<xxxx@xxxxx.com>, method=PLAIN, rip=X.X.X.237, lip=X.X.X.18, TLS
dovecot: Apr 08 13:07:36 Error: auth-worker(default): sql(xxxx@xxxxx.com,X.X.X.237): User query failed: Unknown column 'mailbox.storagenode' in 'field list'

I fix this problem remove "storagenode" on A part I use bellow 

query      = SELECT CONCAT(mailbox.storagebasedirectory, '/', mailbox.maildir) FROM mailbox,domain WHERE mailbox.username='%s' AND mailbox.active='1' AND mailbox.enabledeliver='1' AND domain.domain = mailbox.domain AND domain.active='1'

I fix this problem remove "storagenode" on B part I use bellow 

user_query = SELECT CONCAT(mailbox.storagebasedirectory, '/', mailbox.maildir) AS home, CONCAT('*:bytes=', mailbox.quota*1048576) AS quota_rule FROM mailbox,domain WHERE mailbox.username='%u' AND mailbox.domain='%d' AND mailbox.enable%Ls%Lc=1 AND mailbox.domain=domain.domain AND mailbox.active=1 AND domain.backupmx=0 AND domain.active=1

Now it works But What is problem I dont understand ? Can I help me please Where is my mistake ?

----

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: 31,082

Re: About "storagenode" field on mysql Error

I apologize for this mistake. I didn't mention to add column 'mailbox.storagenode' in upgrade tutorial.

You can simply add a column to fix it:

mysql> USE vmail;
mysql> ALTER TABLE mailbox ADD COLUMN storagenode VARCHAR(255) NOT NULL DEFAULT '';

Synced upgrade tutorial here:
http://www.iredmail.org/wiki/index.php? … nd_dovecot

Thanks for your feedback.
[There were two users reported this issue, but i didn't updated till now. what a shame.]

3 Reply by insanadair

  • insanadair
  • Member
  • Offline
  • Registered: 2010-07-29
  • Posts: 37

Re: About "storagenode" field on mysql Error

what is for storagenode ! what is it and I can add but it is empty have a problem later time this your section

Can I add this field ?

ZhangHuangbin wrote:

I apologize for this mistake. I didn't mention to add column 'mailbox.storagenode' in upgrade tutorial.

You can simply add a column to fix it:

mysql> USE vmail;
mysql> ALTER TABLE mailbox ADD COLUMN storagenode VARCHAR(255) NOT NULL DEFAULT '';

Synced upgrade tutorial here:
http://www.iredmail.org/wiki/index.php? … nd_dovecot

Thanks for your feedback.
[There were two users reported this issue, but i didn't updated till now. what a shame.]

4 Reply by ZhangHuangbin

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

Re: About "storagenode" field on mysql Error

To be clear, i'd like to explain with some detail about this column.

in iRedMail, path of user's mailbox is generated by 3 columns:
- mailbox.storagebasedirectory. e.g. /var/vmail
- mailbox.storagenode. e.g. vmail1
- mailbox.malidir. e.g. domain.com/username/

So the mailbox is /var/vmail/vmail1/domain.com/username/.

- Normally, mailbox.storagebasedirectory won't be changed, it's used in Postfix "virtual_mailbox_base". Default is /var/vmail.
- When hard disk is nearly full, you can easily add and mount addition hard disks, e.g. /var/vmail/vmail2. And configure your admin panel (e.g. PostfixAdmin, iRedAdmin) to use new mount point for new users, so maildir of new users will be /var/vmail/vmail2/[DOMAIN_NAME]/xxx. Here, vmail2 is called storagenode.
- Value of column mailbox.maildir contains domain name and username.