0

I have debian 7.8 and using postfix, mysql with roundcube and pure-ftpd-mysql. All working perfectly with mysql Ver 14.14 Distrib 5.5.40 since 2014.

I try to update mysql to 5.7 in order to play with the new full index search mysql functionnality on massive database but I failed.

After updating mysql server to 5.7 phpmyadmin was working great but I had to add:

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

In conf in order to remove the default value error. My backup and table upgrade sucessfully and all my website was working fine.

But I had massive issue with postfix and pureftpd.

Could connect anymore to ftp even if all dabatase of pureftpd was there and working. I check all the conf everything seems fine.

I can connect to roundcube successfully and see my old email but I cant send or receive anymore email. I received this error in my log :

postfix/cleanup[27758]: warning: mysql:/etc/postfix/mysql_virtual_alias_maps.cf is unavailable. unsupported dictionary type: mysql

I try many things but nothing worked I end up using my full backup and restore the entire system. RIght now everything back to normal 100% perfect but with 5.5 mysql.

Maybe I did something wrong during the process. What I did :

Entire backup of my system and sql database and conf

apt-get remove mysql-server mysql-client mysql-common

apt-get autoremove

apt-get autoclean

apt-get install libaio1

dpkg -i mysql-apt-config_0.8.3-1_all.deb

apt-get update

apt-get intall mysql-server

service mysql start

restoring mysql database and upgrading table

Thanks for the help!

Improve this question
1
  • I competly forgot about courier and dint restart service maybe its because of that. but i restart pureftpd-mysql dont know what happend there.
    – Pierre-luc
    Jul 11, 2017 at 23:56

2 Answers 2

Reset to default
0

Check the output of postconf -m and see if mysql is listed. If not then use: 

sudo apt-get install postfix-mysql

This will add mysql support to postfix.

Improve this answer
1
  • Yes I try this and restart service but no result.
    – Pierre-luc
    Jul 11, 2017 at 7:01
0

Answering this mainly because it comes up high on a google search: The problem is the sql-mode, in particular the "STRICT_TRANS_TABLES" entry seems to cause problems for postfix virtual lookups in mysql 5.7 (or higher?). Edit your sql-mode to exclude this and all will be well...

Now, as for the why... I'm not clear... I had a theory that it was due to dates like 000-000-000 etc in one column, but having updated all of these the issue remained.

EDIT: Problem was the choice of table name "virtual_alias_domains". This appears to start with a reserved word, so it must be fully quoted. Changing my query to use backticks around the table name solved the problem, ie select blah from virtual_alias_domains etc

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .