After setting up postfix to use mysql as lookup backend for alias or virtual or whatever table, everything seems ok. Mails are delivered.
But sometimes strange “Postfix SMTP server: errors from ” e-mails are received. Usually more than one in the same second, then there can be no errors for days or weeks. Then they appear again. There is a “Temporary lookup failure” or “queue file write error” cited in those error e-mails but everything seems to work ok again. Even those e-mails that get error on first try get delivered on second try some minutes later.
One thing to look out for is
“warning: mysql query failed: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘='”
or something similar in mail logs.
If something like that is also present, then the problem is, that some messages use non US-ASCII characters in FROM or TO fields and postfix works under different locale than what is stored in mysql tables. In this case, postfix works under utf8 and mysql tables were latin1.
To see, what mysql uses, log on to mysql console and use
SHOW CREATE TABLE tbl_name;
for the tables that are used in proxy:mysql: lookups.
If these tables are indeed in one of those character set, converting them to the other might help.
As ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; changes also data types, if necessary, it might be wise to use
ALTER TABLE tbl_name CHARACTER SET charset_name;
for example:
alter table alias_domain character set utf8 collate utf8_general_ci;
Then also individual text fields must be converted like so:
alter table vacation modify email varchar(255) character set utf8;
There should be no more errors like that ever again.
And to why postfix gives usually a lot of error messages at once – if postfix gets one error, it remembers it for some time. In this situation, at least cache time is not measured in hours 🙂