1

Topic: Change mysql port in iredmail -> roundcube error

After installing iRedMail I want to change the MySQL port used. In order to do that I've changed these files:

/etc/my.cnf

/var/www/phpmyadmin/config.inc.php  --> I added the line:   $cfg['Servers'][$i]['port'] = '2222';

/var/www/roundcubemail/config/config.inc.php --> $config['db_dsnw'] = 'mysqli://roundcube:qqSRIW3nmc44UROk4Sesyuc9WY0eon@127.0.0.1:2222/roundcubemail';
Here I've added only the :2222 after 127.0.0.1.

/var/www/iredadmin/settings.py

/etc/httpd/conf.d/awstats.conf

/etc/httpd/conf.d/cluebringer.conf

But after the change of the port number in all of these files, I can't access to roundcube.

Anybody knows if I must change any other file related to roundcube?

Thanks in advance

==== Required information ====
- iRedMail version: 0.8.7
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): MySQL
- Linux/BSD distribution name and version: CentOS 6.5
- Related log if you're reporting an issue:
====

----

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

2

Re: Change mysql port in iredmail -> roundcube error

Can you post the results of netstat -tulpn please?

Can you connect to the mysql database through any other means (phpmyadmin, mysql workbench, etc)?

Wehn you say you can't get to roundcube, does that mean you get a 404, or you can't log in?

3

Re: Change mysql port in iredmail -> roundcube error

Hello,

Netstat results at the end.

I can connect to the mysql database with myphpadmin and iredadmin, for example. Also when I get to roundcube, I can't log in (sometimes I receive an error like user/password is incorrect and sometimes I do receive nothing (I've also stopped iptables).

What I've seen when I change the ports is that in the file /var/log/dovecot.log I receive these errors:

Jul 28 22:04:32 auth-worker(1231): Error: mysql: Query timed out (no free connections for 60 secs): SELECT password FROM mail
box WHERE username='postmaster@infojobplace.com' AND active='1'
Jul 28 22:04:32 auth-worker(1231): Error: sql(postmaster@infojobplace.com,127.0.0.1): Password query failed: Not connected to
database
Jul 28 22:04:32 auth: Error: auth worker: Aborted request: Lookup timed out
Jul 28 22:04:32 auth-worker(1232): Error: mysql(127.0.0.1): Connect failed to database (vmail): Can't connect to MySQL server
on '127.0.0.1' (111) - waiting for 1 seconds before retry
Jul 28 22:04:33 auth-worker(1232): Error: mysql(127.0.0.1): Connect failed to database (vmail): Can't connect to MySQL server
on '127.0.0.1' (111) - waiting for 5 seconds before retry
Jul 28 22:04:34 imap-login: Info: Disconnected (auth failed, 1 attempts in 62 secs): user=<postmaster@infojobplace.com>, meth
od=PLAIN, rip=127.0.0.1, lip=127.0.0.1, secured, session=<MCvYakb/twB/AAAB>
Jul 28 22:04:38 auth-worker(1232): Error: mysql(127.0.0.1): Connect failed to database (vmail): Can't connect to MySQL server
on '127.0.0.1' (111) - waiting for 25 seconds before retry
Jul 28 22:05:03 auth-worker(1232): Error: mysql(127.0.0.1): Connect failed to database (vmail): Can't connect to MySQL server
on '127.0.0.1' (111) - waiting for 125 seconds before retry

Then I've modified the file /etc/dovecot/dovecot-mysql.conf and I've modified it adding the port and restarted dovecot:

driver = mysql
default_pass_scheme = CRYPT
connect = host=127.0.0.1 port=2222 dbname=vmail user=vmail password=3L

Now I can login to roundcube but when I try to send a mail I receive the error:

Error SMTP (451): Failed to add recipient user[at]mydomain.com (4.3.0 <user[at]mydomain.com>: Temporary lookup failure

I cannot receive mails neither.

If the port is 3306 (default installation and without modifying dovecot-mysql.conf), all works properly.

The netstat results are:

[root@server log]# netstat -tulpn
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name
tcp        0      0 127.0.0.1:9998              0.0.0.0:*                   LISTEN      848/amavisd (master
tcp        0      0 0.0.0.0:110                 0.0.0.0:*                   LISTEN      820/dovecot
tcp        0      0 0.0.0.0:2222                0.0.0.0:*                   LISTEN      796/mysqld
tcp        0      0 127.0.0.1:10031             0.0.0.0:*                   LISTEN      837/perl
tcp        0      0 0.0.0.0:143                 0.0.0.0:*                   LISTEN      820/dovecot
tcp        0      0 127.0.0.1:53                0.0.0.0:*                   LISTEN      576/named
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      594/sshd
tcp        0      0 0.0.0.0:24                  0.0.0.0:*                   LISTEN      820/dovecot
tcp        0      0 0.0.0.0:25                  0.0.0.0:*                   LISTEN      937/master
tcp        0      0 127.0.0.1:953               0.0.0.0:*                   LISTEN      576/named
tcp        0      0 0.0.0.0:4190                0.0.0.0:*                   LISTEN      820/dovecot
tcp        0      0 127.0.0.1:7777              0.0.0.0:*                   LISTEN      1035/python
tcp        0      0 0.0.0.0:993                 0.0.0.0:*                   LISTEN      820/dovecot
tcp        0      0 0.0.0.0:995                 0.0.0.0:*                   LISTEN      820/dovecot
tcp        0      0 127.0.0.1:10024             0.0.0.0:*                   LISTEN      848/amavisd (master
tcp        0      0 127.0.0.1:10025             0.0.0.0:*                   LISTEN      937/master
tcp        0      0 0.0.0.0:587                 0.0.0.0:*                   LISTEN      937/master
tcp        0      0 :::80                       :::*                        LISTEN      947/httpd
tcp        0      0 ::1:53                      :::*                        LISTEN      576/named
tcp        0      0 :::22                       :::*                        LISTEN      594/sshd
tcp        0      0 ::1:953                     :::*                        LISTEN      576/named
tcp        0      0 :::443                      :::*                        LISTEN      947/httpd
udp        0      0 127.0.0.1:53                0.0.0.0:*                               576/named
udp        0      0 0.0.0.0:783                 0.0.0.0:*                               531/portreserve
udp        0      0 ::1:53                      :::*                                    576/named


Thanks in advance again.

4

Re: Change mysql port in iredmail -> roundcube error

Did you update Postfix/Dovecot config files?

- Postfix: /etc/postfix/mysql/*
- Dovecot: /etc/dovecot/*

5 (edited by fcoexposito 2014-07-30 23:33:51)

Re: Change mysql port in iredmail -> roundcube error

I've modified all the ports in all the files inside /etc/postfix/mysql/.
Also I've added the port in the file /etc/dovecot/dovecot-mysql.conf. This file looks like that:

driver = mysql
default_pass_scheme = CRYPT
connect = host=127.0.0.1 port=2222 dbname=vmail user=vmail password=3LI9hFA053wj7d0cGWwDkzZkxU5nEa
# Required by 'doveadm mailbox ...'.
iterate_query = SELECT username AS user FROM mailbox
password_query = SELECT password FROM mailbox WHERE username='%u' AND active='1'
user_query = SELECT \
    '%u' AS master_user, \
    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 domain.backupmx=0 \
    AND domain.active=1 \
    AND mailbox.active=1

Also I've added the port in the file /etc/dovecot/dovecot-used-quota.conf. This file looks like that:

connect = host=127.0.0.1 port=2222 dbname=vmail user=vmailadmin password=NAjMMZ19NF8whnGN3FqlYLz8zfoUyk
map {
    pattern = priv/quota/storage
    table = used_quota
    username_field = username
    value_field = bytes
}
map {
    pattern = priv/quota/messages
    table = used_quota
    username_field = username
    value_field = messages
}

Also I've added the port in the file /etc/dovecot/dovecot-share-folder.conf, as previous files.


But after restart server I can't send/receive mails. In /var/log/maillog I receive:

Jul 30 17:13:05 server postfix/smtpd[1175]: connect from localhost.localdomain[127.0.0.1]
Jul 30 17:13:05 server postfix/proxymap[944]: warning: connect to mysql server 127.0.0.1: Can't connect t
o MySQL server on '127.0.0.1' (111)
Jul 30 17:13:05 server postfix/trivial-rewrite[948]: warning: proxy:mysql:/etc/postfix/mysql/virtual_mail
box_domains.cf: table lookup problem
Jul 30 17:13:05 server postfix/trivial-rewrite[948]: warning: virtual_mailbox_domains lookup failure
Jul 30 17:13:05 server postfix/trivial-rewrite[948]: warning: proxy:mysql:/etc/postfix/mysql/virtual_mail
box_domains.cf: table lookup problem
Jul 30 17:13:05 server postfix/trivial-rewrite[948]: warning: virtual_mailbox_domains lookup failure
Jul 30 17:13:05 server postfix/smtpd[1175]: NOQUEUE: reject: RCPT from localhost.localdomain[127.0.0.1]:
451 4.3.0 <postmaster@domain.com>: Temporary lookup failure; from=<postmaster@domain.com> to=
<mail@hotmail.com> proto=ESMTP helo=<www.domain.com>
Jul 30 17:13:05 server roundcube: SMTP Error: SMTP error: Failed to add recipient 'mail@hotmail.co
m' in /var/www/roundcubemail-1.0.1/program/lib/Roundcube/rcube.php on line 1504 (POST /mail/?_task=mail&_
unlock=loading1406733188450&_lang=es?_task=mail&_action=send)
Jul 30 17:13:05 server postfix/smtpd[1175]: disconnect from localhost.localdomain[127.0.0.1]



I've also tried to restart iredapd as in the post below, but it doesn't work.
http://www.iredmail.org/forum/topic4150 … d-yes.html


Thanks.

6

Re: Change mysql port in iredmail -> roundcube error

fcoexposito wrote:

Jul 30 17:13:05 server postfix/proxymap[944]: warning: connect to mysql server 127.0.0.1: Can't connect t
o MySQL server on '127.0.0.1' (111)

Can you connect to MySQL server through port 2222 with MySQL command line tool? e.g. "mysql" like below:

$ mysql -h 127.0.0.1 -p 2222 -u xxxxx ...

7

Re: Change mysql port in iredmail -> roundcube error

Yes, I can.

[root@server mysql]# mysql -h 127.0.0.1 -P 2222 -u vmail -p

8

Re: Change mysql port in iredmail -> roundcube error

What do the mysql logs show?

9

Re: Change mysql port in iredmail -> roundcube error

I've activated this log in my.cnf with the command:

log=/tmp/mysql.log

When I send a mail the log shows (I truncate the file before click on send mail in roundcube):

[root@server tmp]# more mysql.log
140731 23:36:33     7 Connect   roundcube@localhost on roundcubemail
                    7 Query     SET NAMES 'utf8'
                    7 Query     SELECT vars, ip, changed, now() AS ts FROM session WHERE sess_id = 'unlv0vc3a34gbdqal5jd7
1f020'
                    7 Query     SELECT * FROM users WHERE user_id = '1'
                    7 Query     SELECT * FROM identities WHERE del <> 1 AND user_id = '1' AND identity_id = 1 ORDER BY `s
tandard` DESC, name ASC, identity_id ASC
140731 23:36:34     8 Connect   vmail@localhost on vmail
                    8 Query     SELECT password FROM mailbox WHERE username='postmaster@domain.com' AND active='1'
                    7 Quit
[root@server tmp]#

In /var/log/maillog appears the same errors as I wrote in previous post.

In /var/log/mysqld.log is empty. I truncate it before send mail also.

It is needed another log file info?

10

Re: Change mysql port in iredmail -> roundcube error

if you have correct SQL server address, port number, SQL username/password in Postfix config files under /etc/postfix/mysql/, it should be working as expected without any error in /var/log/maillog. Could you please try this command and paste its output?

# postmap -v -q 'postmaster@your_domain.com' mysql:/etc/postfix/mysql/virtual_mailbox_maps.cf

11 (edited by fcoexposito 2014-08-04 16:40:24)

Re: Change mysql port in iredmail -> roundcube error

The output is:

postmap: name_mask: ipv4
postmap: inet_addr_local: configured 3 IPv4 addresses
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: user = vmail
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: password = xxx
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: dbname = vmail
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: result_format = %s
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: option_file = <NULL>
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: option_group = <NULL>
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: tls_key_file = <NULL>
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: tls_cert_file = <NULL>
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: tls_CAfile = <NULL>
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: tls_CApath = <NULL>
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: tls_ciphers = <NULL>
postmap: cfg_get_bool: /etc/postfix/mysql/virtual_mailbox_maps.cf: tls_verify_cert = on
postmap: cfg_get_int: /etc/postfix/mysql/virtual_mailbox_maps.cf: expansion_limit = 0
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: query = SELECT CONCAT(mailbox.storagenode, '/', mailbox.maildir, '/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
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: domain =
postmap: cfg_get_str: /etc/postfix/mysql/virtual_mailbox_maps.cf: hosts = 127.0.0.1
postmap: dict_open: mysql:/etc/postfix/mysql/virtual_mailbox_maps.cf
postmap: dict_mysql_get_active: attempting to connect to host 127.0.0.1
postmap: warning: connect to mysql server 127.0.0.1: Can't connect to MySQL server on '127.0.0.1' (111)
postmap: fatal: table mysql:/etc/postfix/mysql/virtual_mailbox_maps.cf: query error: Connection refused


The etc/postfix/mysql/virtual_mailbox_maps.cf configuration file contains:

user        = vmail
password    = xxx
hosts       = 127.0.0.1
port        = 2222
dbname      = vmail
query       = SELECT CONCAT(mailbox.storagenode, '/', mailbox.maildir, '/Maildir/') FROM mailbox,domain WHER
E mailbox.username='%s' AND mailbox.active=1 AND mailbox.enabledeliver=1 AND domain.domain = mailbox.domain
AND domain.active=1

If I change the parameter hosts = 127.0.0.1 for hosts = localhost, the command you've sent works properly. Then I've created a user vmail with access to the host 127.0.0.1 as you can see in the table mysql.user.

CREATE USER 'vmail'@'127.0.0.1' IDENTIFIED BY  '***';
GRANT ALL PRIVILEGES ON * . * TO  'vmail'@'127.0.0.1' IDENTIFIED BY  '***' WITH GRANT OPTION

mysql> SELECT User, Host FROM mysql.user;
+-------------+------------------+
| User        | Host             |
+-------------+------------------+
| root        | 127.0.0.1        |
| vmail       | 127.0.0.1        |
| amavisd     | localhost        |
| cluebringer | localhost        |
| iredadmin   | localhost        |
| root        | localhost        |
| roundcube   | localhost        |
| vmail       | localhost        |
| vmailadmin  | localhost        |
| root        | vps37114.ovh.net |
+-------------+------------------+

But after that when I execute your command, it shows the same error.

And if I try to connect like that, it works properly:
mysql -u vmail -h 127.0.0.1 -P 2222 -p

12

Re: Change mysql port in iredmail -> roundcube error

fcoexposito wrote:

postmap: fatal: table mysql:/etc/postfix/mysql/virtual_mailbox_maps.cf: query error: Connection refused

it says 'connection refused'.

could you please paste the full MySQL config file "my.cnf" here?

13

Re: Change mysql port in iredmail -> roundcube error

This is the content of the file:

#---------------------------------------------------------------------
# This file is part of iRedMail, which is an open source mail server
# solution for Red Hat(R) Enterprise Linux, CentOS, Debian and Ubuntu.
#
# iRedMail is free software: you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# iRedMail is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with iRedMail.  If not, see <http://www.gnu.org/licenses/>.
#---------------------------------------------------------------------

#
# Sample MySQL configuration file. It should be localted at:
#
#   - RHEL/CentOS:  /etc/my.cnf
#   - FreeBSD (www/mysql56-server): /var/db/mysql/my.cnf
#
# Shipped within iRedMail project:
#   http://www.iredmail.org/
#

[mysqld]
#bind-address            = 127.0.0.1
port                    = 2222
#default-character-set   = utf8
#datadir                 = /var/lib/mysql
#socket                  = /var/lib/mysql/mysql.sock
#user                    = mysql
log=/tmp/mysql.log
#general_log = 1
#general_log_file = /var/log/mysql.log

#slow_query_log
#slow_query_log_file = /var/log/mysql_slow.log

skip-external-locking
#skip-bdb
#skip-networking

query-cache-type    = 1
# Use up to 64Mb of RAM to store query results.
query-cache-size    = 64M
query_cache_limit   = 10M

thread_cache_size   = 8

#max_connections=1024
#max_connect_errors=150

# If you got error message like "The total number of locks exceeds the lock tabl
e size",
# please try to increase 'innodb_buffer_pool_size' to a reasonable value.
# Restarting MySQL server is required to make new value work.
# You can get current value with SQL command:
#
#   mysql> SHOW ENGINE INNODB STATUS \G
#          [...]
#          Buffer pool size   512
#          [...]
#
#innodb_buffer_pool_size=512M

# Creates each new table using its own .ibd file for storing data and indexes,
# rather than in the system tablespace (ibdata*).
# Reference:
# http://dev.mysql.com/doc/refman/5.5/en/ … _innodb_fi
le_per_table
innodb_file_per_table

#ssl-ca =
ssl-cert = /etc/pki/tls/certs/iRedMail_CA.pem
ssl-key = /etc/pki/tls/private/iRedMail.key
ssl-cipher = ALL

[client]
default-character-set=utf8

#[mysql.server]
#user        = mysql
#basedir     = /var/lib

#[mysqld_safe]
#log-error   = /var/log/mysqld.log
#pid-file    = /var/run/mysqld/mysqld.pid

14

Re: Change mysql port in iredmail -> roundcube error

No idea. Did you try to reboot server?

15

Re: Change mysql port in iredmail -> roundcube error

Yes.... I've done it!

I'll try to reinstall the server and I'll attach a file with the complete process.