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!

  • 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 '17 at 23:56
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.

  • Yes I try this and restart service but no result. – Pierre-luc Jul 11 '17 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.