1

Topic: mysql is not configured properly

==== Required information ====
- iRedMail version (check /etc/iredmail-release): 0.9.3
- Linux/BSD distribution name and version: FreeBSD 10.2 jail
- Store mail accounts in which backend (LDAP/MySQL/PGSQL): LDAP
- Web server (Apache or Nginx): NGINX
- Manage mail accounts with iRedAdmin-Pro? No
- Related log if you're reporting an issue:
====

I reinstalled iRedMail after fixing some permissions issues in jail management.
There is no errors anymore, but nothing works 'out-of-the-box'.
It seems that all components have trouble to access to MySQL databases.
For example, /var/log/sogo shows errors "could not open channel <MySQL4Channel..."

The databases are present in MySQL:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| amavisd            |
| iredadmin          |
| iredapd            |
| mysql              |
| performance_schema |
| sogo               |
| test               |
+--------------------+
8 rows in set (0.02 sec)

The users are present in MySQL:
mysql> select user,host from mysql.user;
+-----------+-------------------+
| user      | host              |
+-----------+-------------------+
| root      | 127.0.0.1         |
| root      | ::1               |
| amavisd   | localhost         |
| iredadmin | localhost         |
| iredapd   | localhost         |
| root      | localhost         |
| sogo      | localhost         |
| root      | mail.flytrace.com |
+-----------+-------------------+
8 rows in set (0.00 sec)

Some grants are present:
mysql> show grants for 'iredadmin'@'localhost';
+------------------------------------------------------------------------------------------------
------------------+
| Grants for iredadmin@localhost
                  |
+------------------------------------------------------------------------------------------------
------------------+
| GRANT USAGE ON *.* TO 'iredadmin'@'localhost' IDENTIFIED BY PASSWORD '*88E2910C47B9AE8E25B17DD1
A226072D2BB97AE1' |
| GRANT ALL PRIVILEGES ON `iredadmin`.* TO 'iredadmin'@'localhost'
                  |
+------------------------------------------------------------------------------------------------
------------------+
2 rows in set (0.01 sec)

mysql> show grants for 'iredapd'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for iredapd@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'iredapd'@'localhost' IDENTIFIED BY PASSWORD '*AC851CA84AF672FBF3C0CC5D02F7229128D831E9' |
| GRANT ALL PRIVILEGES ON `iredapd`.* TO 'iredapd'@'localhost'                                                   |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for 'sogo'@'localhost';
+-------------------------------------------------------------------------------------------------------------+
| Grants for sogo@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'sogo'@'localhost' IDENTIFIED BY PASSWORD '*03FA2C7B49675D711B3515FABE34314FA82E6B5F' |
| GRANT ALL PRIVILEGES ON `sogo`.* TO 'sogo'@'localhost'                                                      |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


Before getting all grants for anybody, I would ask the developers if they have any idea about a possible reason of my problem...

----

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

2

Re: mysql is not configured properly

Peter2121 wrote:

For example, /var/log/sogo shows errors "could not open channel <MySQL4Channel..."

Restarting SOGo service should fix this issue.

This is a sogo issue (or maybe sogo init script issue). After iRedMail installation, and reboot server, SOGo cannot fully start, but restarting SOGo service will make it work.

3

Re: mysql is not configured properly

Unfortunately, this is not a problem of restarting the service.
Once the install is finished, I restarted the jail, then I tested. Then I restarted several times, adjusting the jail config.
iredadmin does not work neither (internal error).

4

Re: mysql is not configured properly

Peter2121 wrote:

iredadmin does not work neither (internal error).

What's the error message in Apache or uwsgi log file?

5

Re: mysql is not configured properly

Oh, by the way, there is a bug in iRedMail-0.9.3 if you install iRedMail in a Jailed FreeBSD with specified mysql username and password. Check posts #53 - #58 to understand this bug (patch attached):
http://www.iredmail.org/forum/post45203.html#p45203

6 (edited by Peter2121 2015-12-30 20:34:15)

Re: mysql is not configured properly

File "/usr/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 193, in __init__
    super(Connection, self).__init__(*args, **kwargs2)
OperationalError: (1130, "Host 'mail' is not allowed to connect to this MySQL server")

Trying to connect manually to MySQL server I got the same error using 127.0.0.1 for all logins:
root@mail:~ # mysql -h 127.0.0.1 -u sogo -p
Enter password:
ERROR 1130 (HY000): Host 'mail' is not allowed to connect to this MySQL server

It works using localhost though:
root@mail:~ # mysql -h localhost -u sogo -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.

It's definitely jail installation issue...
I've opened the issue on Github https://github.com/iocage/iocage/issues/302

As a workaround, how can I adjust correctly the MySQL permissions to be able to login from all apps?

7 (edited by Peter2121 2015-12-30 22:01:09)

Re: mysql is not configured properly

ZhangHuangbin wrote:

Oh, by the way, there is a bug in iRedMail-0.9.3 if you install iRedMail in a Jailed FreeBSD with specified mysql username and password. Check posts #53 - #58 to understand this bug (patch attached):
http://www.iredmail.org/forum/post45203.html#p45203

There is no patch in your link.
I did not specify MySQL parameters using VARS.
Should I put my jail address in SQL_SERVER_ADDRESS and retry?
It takes about 10 hrs to compile anything, so I would like to know if it can help before I try...

8

Re: mysql is not configured properly

Seems i misunderstood your issue after re-read all posts in this thread.

*) Did you run iRedMail installer with command "LOCAL_ADDRESS='aa.bb.cc.dd' bash iRedMail.sh"? If so, it should grant SQL privilege to "[username]@[aa.bb.cc.dd]", not "[username]@localhost".

*) I'm not familiar with iocage jail, inside jail, when you connect to MySQL server, does MySQL server consider you're connecting from 127.0.0.1 or Jail IP "aa.bb.cc.dd"? If it's 127.0.0.1, please launch iRedMail without "LOCAL_ADDRESS=xx.xx.xx.xx", just run "bash iRedMail.sh".

9

Re: mysql is not configured properly

ZhangHuangbin wrote:

Seems i misunderstood your issue after re-read all posts in this thread.

*) Did you run iRedMail installer with command "LOCAL_ADDRESS='aa.bb.cc.dd' bash iRedMail.sh"? If so, it should grant SQL privilege to "[username]@[aa.bb.cc.dd]", not "[username]@localhost".

No, initially I run the installer without parameters.

This night I reinstalled iRedMail with LOCAL_ADDRESS='192.168.101.121' (jail main address).
Almost anything goes well after install, but there is just one big problem: the LOCAL_ADDRESS parameter is not used in sogo.conf, there is still 127.0.0.1 here. So, sogo tables are not created in MySQL database. I replaced 127.0.0.1 with 192.168.101.121 in sogo.conf and sogod daemon has created the tables after restart, so anything works now. Probably, sogo install scripts should be adjusted.

ZhangHuangbin, thank you for the assistance.

10

Re: mysql is not configured properly

Peter2121 wrote:

the LOCAL_ADDRESS parameter is not used in sogo.conf, there is still 127.0.0.1 here.

Do you mean bind address (WOPort = )?

Fixed moment ago:
https://bitbucket.org/zhb/iredmail/comm … ad97756699

11

Re: mysql is not configured properly

...idem for nginx sogo config - 127.0.0.1 in sogo.tmpl

12

Re: mysql is not configured properly

ZhangHuangbin wrote:
Peter2121 wrote:

the LOCAL_ADDRESS parameter is not used in sogo.conf, there is still 127.0.0.1 here.

Do you mean bind address (WOPort = )?

Fixed moment ago:
https://bitbucket.org/zhb/iredmail/comm … ad97756699

WOPort and MySQL connections were configured with 127.0.0.1

13

Re: mysql is not configured properly

OK, fixed. iRedMail will make sure MySQL/PostgreSQL/SOGo bind to specified $LOCAL_ADDRESS.
https://bitbucket.org/zhb/iredmail/comm … d402cbadb2

Could you help check out the latest iRedMail source code and try it again? I know it will take you several hours, but how about perform the installation before you go to bed? smile

14

Re: mysql is not configured properly

ZhangHuangbin wrote:

OK, fixed. iRedMail will make sure MySQL/PostgreSQL/SOGo bind to specified $LOCAL_ADDRESS.
https://bitbucket.org/zhb/iredmail/comm … d402cbadb2

Could you help check out the latest iRedMail source code and try it again? I know it will take you several hours, but how about perform the installation before you go to bed? smile

Thanks a lot for fixing it!
Sorry, another deployment is already started on my test server.
Sure to be able to test it, but a bit later, we have holidays here, in Europe wink
I'll drop some words here when I have the news.

15

Re: mysql is not configured properly

Peter2121 wrote:
ZhangHuangbin wrote:

OK, fixed. iRedMail will make sure MySQL/PostgreSQL/SOGo bind to specified $LOCAL_ADDRESS.
https://bitbucket.org/zhb/iredmail/comm … d402cbadb2

Could you help check out the latest iRedMail source code and try it again? I know it will take you several hours, but how about perform the installation before you go to bed? smile

Thanks a lot for fixing it!
Sorry, another deployment is already started on my test server.
Sure to be able to test it, but a bit later, we have holidays here, in Europe wink
I'll drop some words here when I have the news.

Sorry, I've just reinstalled iRedMail in jail - WOPort was still on 127.0.0.1.
Other addresses were correctly changed in sogo.conf to LOCAL_ADDRESS

16

Re: mysql is not configured properly

There is another problem in jail installation - main.cf of Postfix has mynetworks configured as 127.0.0.1, so mails from Sogo web interface cannot be sent.

17

Re: mysql is not configured properly

BTW, should I download myself the last version you are modifying, or it is downloaded by installer script?

18

Re: mysql is not configured properly

Hi Peter,

Are you trying the latest development edition or iRedMail-0.9.3? You must try the latest development edition. iRedMail-0.9.3 doesn't contain this fix.

Peter2121 wrote:

Sorry, I've just reinstalled iRedMail in jail - WOPort was still on 127.0.0.1.

I checked iRedMail source code, this should be fixed.

Peter2121 wrote:

There is another problem in jail installation - main.cf of Postfix has mynetworks configured as 127.0.0.1, so mails from Sogo web interface cannot be sent.

Fixed moment ago:
https://bitbucket.org/zhb/iredmail/comm … ba575afdd1

Peter2121 wrote:

BTW, should I download myself the last version you are modifying, or it is downloaded by installer script?

You must download it manually from here (Choose "Download repository" to download the latest development edition):
https://bitbucket.org/zhb/iredmail/downloads

19

Re: mysql is not configured properly

There are two other places where LOCAL_ADDRESS should be used:
- main.cf of postfix (mynetworks)
- sogo.tmpl of nginx

BTW, it would be better to install sogo with ActiveSync support or propose to select/deselect it during install. You can use an option of www/sogo port or www/sogo-activesync port where this option is activated by default.

20

Re: mysql is not configured properly

Peter2121 wrote:

There are two other places where LOCAL_ADDRESS should be used:
- main.cf of postfix (mynetworks)
- sogo.tmpl of nginx

Fixed days ago.

Peter2121 wrote:

BTW, it would be better to install sogo with ActiveSync support or propose to select/deselect it during install. You can use an option of www/sogo port or www/sogo-activesync port where this option is activated by default.

Fixed moment ago. Thanks for the report. smile

21

Re: mysql is not configured properly

ZhangHuangbin wrote:

You must download it manually from here (Choose "Download repository" to download the latest development edition):
https://bitbucket.org/zhb/iredmail/downloads

The script of the dev version is trying to download iRedAPD-1.8.0.tar.bz2 from http://iredmail.org/yum/misc/
This file is not available on the site, so the installation is failed.

22

Re: mysql is not configured properly

I packed iRedAPD-1.8.0-beta and uploaded to iredmail.org/yum/misc/, please try the latest iRedMail development edition again.

WARNING: This is not STABLE release (both iRedMail and iRedAPD), please do not deploy it on production server.

23

Re: mysql is not configured properly

ZhangHuangbin wrote:

Peter2121 wrote:

    BTW, it would be better to install sogo with ActiveSync support or propose to select/deselect it during install. You can use an option of www/sogo port or www/sogo-activesync port where this option is activated by default.

Fixed moment ago. Thanks for the report.

It seems that you ADDED www/sogo-activesync, but you should REPLACE www/sogo by www/sogo-activesync. This is not an addition, this is the same port with another default options.

24

Re: mysql is not configured properly

Fixed: https://bitbucket.org/zhb/iredmail/comm … freebsd.sh

Thanks for the feedback.

25 (edited by Peter2121 2016-01-28 23:34:31)

Re: mysql is not configured properly

I've just tested 36512bfbd728 - there is still a problem with database URLs in sogo.conf, 127.0.0.1 is used everywhere.
No permissions for sogo user in MySQL tables neither.
What happens?