can't connect to database

[kkmaslowski]

Hi. I tried to run mailzu on 2 different mail servers without success. First server is running CentOS 7 (php 5.4, mariadb 5.5), second one is Ubuntu 18.04 (php 7.2, mariadb 10.1). Both are running iredmail. I can't connect to database on both of them. Error on CentOS 7: Error connecting to database: DB Error: connect failed

Error on Ubuntu 18.04: Error connecting to database: DB Error: extension not found

I tried to run with safeMode on and off without success. I can authenticate, but connection to DB does not work. Any hints?

[gnanet]

please add some detail about your setup:

what do you have set in mailzu/config/config.php for the dbType ? Recent PHP needs to have mysqli there

$conf['auth']['dbType'] = 'mysqli';

For the Centos7 variant, please test the SQL connection data you provided in the mailzu/config/config.php from shell with exaclty the same values (ensure that a localhost is tcp localhost, and not mariadb-s magical unix socket and plugin-auth)

-- IMPORTANT: Replace mailservice with the database name, that your amavis is using
-- Sample user for mailzu could be created using the query below in MariaDB
GRANT USAGE ON *.* TO 'mailservice'@'localhost' IDENTIFIED BY PASSWORD('pass4mailservice');
GRANT ALL PRIVILEGES ON `mailservice`.* TO 'mailservice'@'localhost';
FLUSH PRIVILEGES;

For the sample user above in mailzu/config/config.php the relevant lines are like these:

$conf['db']['dbType'] = 'mysqli';
// Database user who can access the amavisd database
$conf['db']['dbUser'] = 'mailservice';
// Password for above user to access the amavisd database
$conf['db']['dbPass'] = 'pass4mailservice';
// Name of database
$conf['db']['dbName'] = 'mailservice';
// Database host specification (hostname[:port]) [localhost]
$conf['db']['hostSpec'] = 'localhost:3306';

// For PHP 7 it is the best to set safeMode off
$conf['app']['safeMode'] = 0;

On a Debianized(ubuntu) system install these packages (or their ubuntu equivalent):

apt install php-cli php-curl php-db php-gd php-imap php-intl php-mbstring php-mcrypt php-mysql php-net-socket php-pear php-xml php-zip pkg-php-tools

Ensure that pear-modules, which were not available as deb-package are installed too:

pear channel-update pear.php.net
pear install Mail_mimeDecode
pear install XML_Parser

I am curious if you can resolve the issue with my suggestions, because in PHP the db connection should be really straightforward to set-up, and to debug with details from the webservers / php error-log

[kkmaslowski]

Hi. I switched dbtype to mysqli and it worked! Thanks for quick answer and please update documentation and comments in config.php (now there is only information about mysql db type).

Now I have another problem- Mailzu does not find any messages. I enabled debug. When I click 'Site Quarantine' -> 'Search' I got no results. In log I see query:

[Fri, 11 Oct 2019 08:31:15] 195.136.114.93      [DEBUG SQL QUERY]: SELECT
            msgs.time_num,
            msgs.from_addr,
            msgs.mail_id,
            msgs.subject,
            msgs.spam_level,
            msgs.content,
            msgrcpt.rs,
            msgs.quar_type,
            recip.email
            FROM msgs
            INNER JOIN msgrcpt      ON msgs.mail_id = msgrcpt.mail_id
             LEFT JOIN maddr AS sender  ON msgs.sid = sender.id
             LEFT JOIN maddr AS recip   ON msgrcpt.rid = recip.id
            WHERE  msgs.content in ('S', 'B', 'V', 'H')   AND msgrcpt.rs in ('', 'v')

            AND msgs.quar_type <> ''
            ORDER BY msgs.time_num DESC 

I tried to run it manually in mysql client and found that two columns are missing: 'msgrcpt.rs' and 'recip.email'. When I remove them query returns messages. Maybe schema used by amavis in iredmail is incompatible with Mailzu?

[gnanet]

Be aware that LEFT JOIN maddr AS recip and LEFT JOIN maddr AS sender allow the SQL query to lookup senders and recipients email-address in the table maddr at the same time.

Also a quick check in iRedMails sources shows me, the msgrcpt table has a column named rs https://bitbucket.org/zhb/iredmail/src/d97300a9276f97613b3902d1bb37809342673ad3/iRedMail/samples/amavisd/amavisd.mysql#lines-241

[kkmaslowski]

Sorry, my fault. Select is working but not returning any messages:

MariaDB [amavisd]> SELECT msgs.time_num,msgs.from_addr,msgs.mail_id,msgs.subject,msgs.spam_level,msgs.content,msgrcpt.rs,msgs.quar_type,recip.email FROM msgs INNER JOIN msgrcpt ON msgs.mail_id = msgrcpt.mail_id LEFT JOIN maddr AS sender ON msgs.sid = sender.id LEFT JOIN maddr AS recip ON msgrcpt.rid = recip.id WHERE msgs.content in ('S', 'B', 'V', 'H') AND msgrcpt.rs in ('', 'v') AND msgs.quar_type <> '' ORDER BY msgs.time_num DESC; Empty set (0.00 sec)

I found that column quar_type for all messages is empty. When I delete 'AND msgs.quar_type <> ' when it returns 13 messages in quarantine. 'Site Quarantine Summary' is also empty.

[gnanet]

I think the next thing you need to check is the configuration of amavis itself.

I saw one conversation that seems to be similar to your current problem

https://amavis-users.amavis.narkive.com/hPxGPAUW/amavisd-release-does-not-work-with-sql-quarantine-missing-quar-type-q

[kkmaslowski]

Thanks @gnanet. It was a problem with iRedMail. Database quarantine was not fully enabled. I wrote post about it here: https://forum.iredmail.org/topic16098-spamquarantineto-empty-in-policy-table-quarantine-not-working.html

I also found different updated version of Mailzu in BSD ports: https://bugs.freebsd.org/bugzilla/show_bug.cgi?id=230712, https://www.freshports.org/security/mailzu/ Maybe you can review patches from there and merge them? For example there is Polish language in it.

[gnanet]

Thank you for pointing on the BSD ports. I will have a look at this, to see how much i can take over from their fixes/changes

[kkmaslowski]

Thanks for continuing work on this project. Great work!