Dec 29, 2023: iRedMail-1.6.8 has been released.

**sameh.onaissi** · 2016-10-27 05:28:05

sameh.onaissi
Member
Offline

Registered: 2016-10-14
Posts: 29

Topic: SOGo MySQL connection

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

Hello all,

I have installed SOGo, and created its database. A sogo service restart created the tables in said database.

I can log in to all email accounts using roundcube, however I can not get into any of them using SOGo web UI.

/var/log/sogo/sogo/log shows the following:

2016-10-26 21:24:21.657 sogod[20989] ERROR: could not open MySQL4 connection to database 'sogo': Can't connect to MySQL server on '127.0.0.1' (111)
Oct 26 21:24:21 sogod [20989]: [ERROR] <0x0x5647f64f9e18[GCSChannelManager]> could not open channel <MySQL4Channel[0x0x5647f67befa8] connection=0x(null)> for mysql://127.0.0.1/sogo/sogo_view
Oct 26 21:24:21 sogod [20989]: [WARN] <0x0x5647f64f9e18[GCSChannelManager]> will prevent opening of this channel 5 seconds after 2016-10-26 21:24:21 +0000
Oct 26 21:24:21 sogod [20989]: [ERROR] <0x0x5647f6804cb8[SQLSource]> failed to acquire channel for URL: mysql://sogo:secret@127.0.0.1:5432/sogo/sogo_view
Oct 26 21:24:21 sogod [20989]: SOGoRootPage Login from '1xx.xxx.xxx.xxx' for user 'postmaster@ixxxxxxxxs.com' might not have worked - password policy: 65535 grace: -1 expire: -1 bound: 0
Oct 26 21:24:21 sogod [20989]: 190.211.140.13 "POST /SOGo/connect HTTP/1.1" 403 34/85 0.002 - - 64K
sXXX@mailserver:/etc/apache2/conf-enabled$

My sogo.conf MySQL configs are:

SOGoProfileURL = "mysql://sogo:secret@localhost:5432/sogo/sogo_user_profile";
OCSFolderInfoURL = "mysql://sogo:secret@localhost:5432/sogo/sogo_folder_info";
OCSSessionsFolderURL = "mysql://sogo:secret@localhost:5432/sogo/sogo_sessions_folder";

Any idea what the issue is?

----

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

**ZhangHuangbin** · 2016-10-27 07:46:56

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 30,770

Re: SOGo MySQL connection

Seems you didn't install mysql package for sogo: sope4.9-gdl1-mysql.

**sameh.onaissi** · 2016-10-27 22:06:40

sameh.onaissi
Member
Offline

Registered: 2016-10-14
Posts: 29

Re: SOGo MySQL connection

ZhangHuangbin wrote:

Seems you didn't install mysql package for sogo: sope4.9-gdl1-mysql.

Hi ZhangHuangbin,

It is installed:

# dpkg -s sope4.9-gdl1-mysql
Package: sope4.9-gdl1-mysql
Status: install ok installed
Priority: extra
Section: web
Installed-Size: 180
Maintainer: Inverse Support <support@inverse.ca>
Architecture: amd64
Source: sope
Version: 4.9.r1664.20161021
Provides: sope4.9-db-connector
Depends: gnustep-base-runtime (>= 1.24.0), libc6 (>= 2.4), libgnustep-base1.24 (>= 1.24.0), libmysqlclient18 (>= 5.5.24+dfsg-1), libobjc4 (>= 4.2.1), libsope-gdl1-4.9 (>= 4.9.r1664.20161021)
Suggests: mysql-server (>= 5.0)
Description: MySQL connector for SOPE's fork of the GNUstep database environment
SOPE is a framework for developing web applications and services. The
name "SOPE" (SKYRiX Object Publishing Environment) is inspired by ZOPE.
.
This package contains the mysql connector for SOPE's fork of the
GNUstep database libraries.

Any other ideas?

**ZhangHuangbin** · 2016-10-27 22:36:52

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 30,770

Re: SOGo MySQL connection

sameh.onaissi wrote:

Oct 26 21:24:21 sogod [20989]: [ERROR] <0x0x5647f64f9e18[GCSChannelManager]> could not open channel <MySQL4Channel[0x0x5647f67befa8] connection=0x(null)> for mysql://127.0.0.1/sogo/sogo_view

Why it shows sql table/view "sogo_view"? in iRedMail, it's "users" table.
please check your sogo.conf.

**sameh.onaissi** · 2016-10-27 22:51:31

sameh.onaissi
Member
Offline

Registered: 2016-10-14
Posts: 29

Re: SOGo MySQL connection

ZhangHuangbin wrote:

sameh.onaissi wrote:
Oct 26 21:24:21 sogod [20989]: [ERROR] <0x0x5647f64f9e18[GCSChannelManager]> could not open channel <MySQL4Channel[0x0x5647f67befa8] connection=0x(null)> for mysql://127.0.0.1/sogo/sogo_view
Why it shows sql table/view "sogo_view"? in iRedMail, it's "users" table.
please check your sogo.conf.

I created a view based on the installation guide here: http://www.iredmail.org/docs/sogo-centos-6-mysql.html

sogo.conf has:

SOGoUserSources =
(
{
type = sql;
id = vmail_mailbox;
viewURL = "mysql://sogo:secret@127.0.0.1:5432/sogo/sogo_view";
canAuthenticate = YES;
isAddressBook = YES;
userPasswordAlgorithm = ssha;
}
);

I tried changing tto sogo/users; and still same error.

**ZhangHuangbin** · 2016-10-28 07:44:22

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 30,770

Re: SOGo MySQL connection

Does the VIEW (sogo_view) actually exist? How did you create it?

**sameh.onaissi** · 2016-10-28 08:14:47

sameh.onaissi
Member
Offline

Registered: 2016-10-14
Posts: 29

Re: SOGo MySQL connection

I created the view sogo.users using:

CREATE VIEW sogo.users (c_uid, c_name, c_password, c_cn, mail, domain) AS SELECT username, username, password, name, username, domain FROM vmail.mailbox WHERE enablesogo=1 AND active=1;

That is what is in the Centos guide.
That view exists and I can view it in sogo db (select * from sogo.users;)

The sogo.conf file is different than the one in the centos guide so I edited accordingly.

I changed sogo_view to usrers / to sogo.users / to sogo_users and none of that worked, I still got:

Oct 28 00:12:32 sogod [11557]: [ERROR] <0x0x557beb5fa0b8[GCSChannelManager]> could not open channel <MySQL4Channel[0x0x557beb676948] connection=0x(null)> for mysql://127.0.0.1/sogo/sogo.users

**ted** · 2016-10-28 17:06:22

ted
Member
Offline

Registered: 2011-05-29
Posts: 15

Re: SOGo MySQL connection

Got a similar issue.

Oct 28 10:54:36 sogod [29831]: |SOGo| starting method 'POST' on uri '/SOGo/connect'
2016-10-28 10:54:36.086 sogod[29831] <MySQL4Channel[0x0x7f41e0d0a560] connection=0x0x7f41e0b4c100> SQL: SELECT c_password FROM users WHERE c_uid = 'a@b.c';
2016-10-28 10:54:36.086 sogod[29831] <MySQL4Channel[0x0x7f41e0d0a560] connection=0x0x7f41e0b4c100> query has results, entering fetch-mode.
Oct 28 10:54:36 sogod [29831]: SOGoRootPage Login from '217.231.245.94' for user 'a@b.c' might not have worked - password policy: 65535 grace: -1 expire: -1 bound: 0
Oct 28 10:54:36 sogod [29831]: |SOGo| request took 0.001393 seconds to execute
Oct 28 10:54:36 sogod [29831]: 1.2.3.4 "POST /SOGo/connect HTTP/1.1" 403 34/90 0.002 - - 0

Server installed freshly 2 months ago with latest iRedMail and Ubuntu 16 LTS, using MySQL backenend.

Worked nice all the time, but now not anymore, maybe cause of an sogo upgrade?

**ted** · 2016-10-28 17:44:44

ted
Member
Offline

Registered: 2011-05-29
Posts: 15

Re: SOGo MySQL connection

Just noticed that recently added accounts have sha512 encrypted passwords, and older accounts salted md5 in sogo.users, and also in vmail.mailbox

What's that about?

**ZhangHuangbin** · 2016-10-28 21:30:36

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 30,770

Re: SOGo MySQL connection

I guess i didn't fully understand your issue due to miss some log or config file. How about try this:

*) Setup a virtual machine and install iRedMail with SOGo and same SQL/LDAP backend.
*) Copy the /etc/sogo/sogo.conf from virtual machine to your mail server, replace the MYSQL password in it, then restart SOGo service.

**ZhangHuangbin** · 2016-10-28 21:39:15

ZhangHuangbin
iRedMail Developers
Offline

Registered: 2009-05-06
Posts: 30,770

Re: SOGo MySQL connection

Uploaded a sample sogo.conf moment ago:
http://pastebin.com/7XevuLAn

**sameh.onaissi** · 2016-10-28 22:35:23

sameh.onaissi
Member
Offline

Registered: 2016-10-14
Posts: 29

Re: SOGo MySQL connection

ZhangHuangbin wrote:

Uploaded a sample sogo.conf moment ago:
http://pastebin.com/7XevuLAn

THAT DID IT!
For some reason the sogo.conf file was structured differently than the one you posted.

I used your config file and edited the mysql info, changed password algorithm to SSHA and all worked well.

Now to set it up for outlook and mac Mail client.

*fingers crossed*

Thank you ZhangHuangbin!

Dec 29, 2023: iRedMail-1.6.8 has been released.

[ Closed ] SOGo MySQL connection

Posts: 12

1 Topic by sameh.onaissi 2016-10-27 05:28:05

Topic: SOGo MySQL connection

2 Reply by ZhangHuangbin 2016-10-27 07:46:56

Re: SOGo MySQL connection

3 Reply by sameh.onaissi 2016-10-27 22:06:40 (edited by sameh.onaissi 2016-10-27 22:11:54)

Re: SOGo MySQL connection

4 Reply by ZhangHuangbin 2016-10-27 22:36:52

Re: SOGo MySQL connection

5 Reply by sameh.onaissi 2016-10-27 22:51:31

Re: SOGo MySQL connection

6 Reply by ZhangHuangbin 2016-10-28 07:44:22

Re: SOGo MySQL connection

7 Reply by sameh.onaissi 2016-10-28 08:14:47

Re: SOGo MySQL connection

8 Reply by ted 2016-10-28 17:06:22

Re: SOGo MySQL connection

9 Reply by ted 2016-10-28 17:44:44

Re: SOGo MySQL connection

10 Reply by ZhangHuangbin 2016-10-28 21:30:36

Re: SOGo MySQL connection

11 Reply by ZhangHuangbin 2016-10-28 21:39:15

Re: SOGo MySQL connection

12 Reply by sameh.onaissi 2016-10-28 22:35:23

Re: SOGo MySQL connection

Posts: 12