1

Topic: Postfix SMTP server: errors

Periodically, the mail     postmaster@mail.mydomain.tld I get these messages:

Transcript of session follows.

Out: 220 mail.mydomain.tld ESMTP Postfix (Debian/GNU)
In:  EHLO [10.0.0.216]
Out: 250-mail.mydomain.tld
Out: 250-PIPELINING
Out: 250-SIZE 15728640
Out: 250-ETRN
Out: 250-STARTTLS
Out: 250-AUTH PLAIN LOGIN
Out: 250-AUTH=PLAIN LOGIN
Out: 250-ENHANCEDSTATUSCODES
Out: 250-8BITMIME
Out: 250 DSN
In:  AUTH PLAIN
Out: 334
In:  AHNwQGF2aXRlay5ydQBSaGVuanFHZmhqa20=
Out: 235 2.7.0 Authentication successful
In:  MAIL FROM:<sp@avitek.ru> SIZE=1973882
Out: 250 2.1.0 Ok
In:  RCPT TO:<b????@sendermail.com>
Out: 451 4.3.0 <b    @sendermail.com>: Temporary lookup failure
In:  RSET
Out: 250 2.0.0 Ok
In:  RSET
Out: 250 2.0.0 Ok
In:  QUIT
Out: 221 2.0.0 Bye


Out: 220 mail.mydomain.tld ESMTP Postfix (Debian/GNU)
In:  HELO mail-server.prostosoft.ru
Out: 250 mail.mydomain.tld
In:  MAIL FROM:<user2@somedomain.com>
Out: 250 2.1.0 Ok
In:  RCPT TO:<user@mail.mydomain.tld>
Out: 451 4.3.0 <user@mail.mydomain.tld>: Temporary lookup failure
In:  QUIT
Out: 221 2.0.0 Bye

----

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

2

Re: Postfix SMTP server: errors

I'm a little confused. Seems you have a DNS lookup issue on your mail server.

Since postfix is running chrooted in /var/spool/postfix/ by default in iRedMail, it need several files for DNS lookup.

- /var/spool/postfix/etc/hosts. It should be same as /etc/hosts. You can simply copy from /etc/hosts. Keep them synced.
- /var/spool/postfix/etc/resolv.conf. It defines IP addresses of DNS servers. You should simply copy from /etc/resolv.conf, keep them synced.

Other two files are:
- /var/spool/postfix/etc/localtime
- /var/spool/postfix/etc/services

So please check these 4 files, and make sure they are up-to-date and correct.

3

Re: Postfix SMTP server: errors

cat /var/log/mail.log | grep warning

Apr 18 17:16:51 mail postfix/proxymap[20271]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:18:23 mail postfix/proxymap[20271]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:23:02 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:23:02 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:24:30 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:24:30 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:25:40 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:25:40 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:26:51 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:26:51 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:29:14 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:29:14 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:31:52 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:31:52 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:34:34 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:34:34 mail postfix/proxymap[20361]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:37:46 mail postfix/proxymap[20568]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:37:46 mail postfix/proxymap[20568]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:39:31 mail postfix/proxymap[20568]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:39:31 mail postfix/proxymap[20568]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:42:42 mail postfix/proxymap[20568]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:42:42 mail postfix/proxymap[20568]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:44:33 mail postfix/proxymap[20568]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:44:33 mail postfix/proxymap[20568]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:45:35 mail postfix/proxymap[20568]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:45:35 mail postfix/proxymap[20568]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:48:12 mail postfix/proxymap[20568]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:48:12 mail postfix/proxymap[20568]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:56:00 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:56:00 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:57:22 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 17:57:22 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 18:00:07 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 18:00:07 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 18:01:11 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 18:01:11 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 18:03:40 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 18:03:40 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 18:07:17 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 18:07:17 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 18:10:00 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 18 18:10:00 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections
Apr 19 09:49:42 mail postfix/smtpd[27562]: warning: network_biopair_interop: error reading 5 bytes from the network: Connection reset by peer
Apr 19 17:28:13 mail postfix/proxymap[31825]: warning: mysql query failed: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
Apr 19 17:28:13 mail postfix/trivial-rewrite[31826]: warning: transport_maps lookup failure
Apr 19 18:56:14 mail postfix/proxymap[32442]: warning: mysql query failed: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
Apr 19 18:56:14 mail postfix/trivial-rewrite[32444]: warning: transport_maps lookup failure
Apr 19 18:56:38 mail postfix/trivial-rewrite[32444]: warning: transport_maps lookup failure
Apr 19 18:56:39 mail postfix/trivial-rewrite[32444]: warning: transport_maps lookup failure
Apr 19 18:56:40 mail postfix/trivial-rewrite[32444]: warning: transport_maps lookup failure
Apr 19 18:56:44 mail postfix/trivial-rewrite[32444]: warning: transport_maps lookup failure
Apr 19 18:56:48 mail postfix/trivial-rewrite[32444]: warning: transport_maps lookup failure
Apr 19 18:56:51 mail postfix/trivial-rewrite[32444]: warning: transport_maps lookup failure
Apr 19 18:56:53 mail postfix/trivial-rewrite[32444]: warning: transport_maps lookup failure

4

Re: Postfix SMTP server: errors

busby wrote:

Apr 18 17:56:00 mail postfix/proxymap[21063]: warning: connect to mysql server 127.0.0.1: Too many connections

You can try to increase MySQL server setting "max_connections" in its config file (/etc/my.cnf, or /etc/mysql/my.cnf).

[mysqld]
set-variable = max_connections=1024

5 (edited by Znekar 2011-07-02 21:02:24)

Re: Postfix SMTP server: errors

Since about 2 days I have this problems too:

Transcript of session follows.

 Out: 220 poseidon.mydomain.tld ESMTP Postfix (Ubuntu)
 In:  EHLO dd6936.domain.tld
 Out: 250-poseidon.mydomain.tld
 Out: 250-PIPELINING
 Out: 250-SIZE 15728640
 Out: 250-ETRN
 Out: 250-STARTTLS
 Out: 250-AUTH PLAIN LOGIN
 Out: 250-AUTH=PLAIN LOGIN
 Out: 250-ENHANCEDSTATUSCODES
 Out: 250-8BITMIME
 Out: 250 DSN
 In:  MAIL FROM:<www-data@senderdomain.tld> SIZE=3281
 Out: 250 2.1.0 Ok
 In:  RCPT TO:<user@mydomain.tld>
     ORCPT=rfc822;guestbook_check@senderdomain.tld
 Out: 451 4.3.0 <www-data@senderdomain.tld>: Temporary lookup failure
 In:  DATA
 Out: 554 5.5.1 Error: no valid recipients
 In:  RSET
 Out: 250 2.0.0 Ok
 In:  QUIT
 Out: 221 2.0.0 Bye


For other details, see the local mail logfile

I got more than 500 of such Emails since the last 2 days.

The problems with too much mySQL connections I don´t see in my logfiles.

Any suggestions?

BTW
- /var/spool/postfix/etc/hosts
- /var/spool/postfix/etc/resolv.conf
- /var/spool/postfix/etc/localtime
- /var/spool/postfix/etc/services
are the same like in /etc

6

Re: Postfix SMTP server: errors

Did you apply this patch?
http://www.iredmail.org/forum/topic1972 … denly.html

7

Re: Postfix SMTP server: errors

ZhangHuangbin wrote:

Did you apply this patch?
http://www.iredmail.org/forum/topic1972 … denly.html

Yes, I did.

8 (edited by Znekar 2011-07-05 05:33:04)

Re: Postfix SMTP server: errors

I have 2 more examples.

Email 1:

Transcript of session follows.

 Out: 220 poseidon.mydomain.tld ESMTP Postfix (Ubuntu)
 In:  EHLO j121po145.domain.tld
 Out: 250-poseidon.mydomain.tld
 Out: 250-PIPELINING
 Out: 250-SIZE 15728640
 Out: 250-ETRN
 Out: 250-STARTTLS
 Out: 250-AUTH PLAIN LOGIN
 Out: 250-AUTH=PLAIN LOGIN
 Out: 250-ENHANCEDSTATUSCODES
 Out: 250-8BITMIME
 Out: 250 DSN
 In:  MAIL FROM:<testfahrer@senderdomain.tld> BODY=8BITMIME
     ENVID=15__1_1501309757223_702_1309757401
 Out: 250 2.1.0 Ok
 In:  RCPT TO:<myuser@mydomain.tld> NOTIFY=FAILURE
 Out: 451 4.3.0 <testfahrer@senderdomain.tld>: Temporary lookup failure
 In:  DATA
 Out: 554 5.5.1 Error: no valid recipients
 In:  QUIT
 Out: 221 2.0.0 Bye


For other details, see the local mail logfile

Log Email 1:

Jul  4 22:10:36 poseidon postfix/smtpd[14957]: connect from j121po145.domain.tld[nnn.nnn.nnn.nnn]
Jul  4 22:10:36 poseidon postfix/trivial-rewrite[14922]: warning: transport_maps lookup failure
Jul  4 22:10:36 poseidon postfix/smtpd[14957]: NOQUEUE: reject: RCPT from j121po145.domain.tld[nnn.nnn.nnn.nnn]: 451 4.3.0 <testfahrer@senderdomain.tld>: Temporary lookup failure; from=<testfahrer@senderdomain.tld> to=<myuser@mydomain.tld> proto=ESMTP helo=<j121po145.domain.tld>
Jul  4 22:10:36 poseidon postfix/cleanup[14958]: 41E43252E04: message-id=<20110704201036.41E43252E04@poseidon.mydomain.tld>
Jul  4 22:10:36 poseidon postfix/qmgr[1513]: 41E43252E04: from=<double-bounce@poseidon.mydomain.tld>, size=1187, nrcpt=1 (queue active)
Jul  4 22:10:36 poseidon postfix/smtpd[14957]: disconnect from j121po145.domain.tld[nnn.nnn.nnn.nnn]

Email2:

Transcript of session follows.

 Out: 220 poseidon.mydomain.tld ESMTP Postfix (Ubuntu)
 In:  EHLO j121po155.domain.tld
 Out: 250-poseidon.mydomain.tld
 Out: 250-PIPELINING
 Out: 250-SIZE 15728640
 Out: 250-ETRN
 Out: 250-STARTTLS
 Out: 250-AUTH PLAIN LOGIN
 Out: 250-AUTH=PLAIN LOGIN
 Out: 250-ENHANCEDSTATUSCODES
 Out: 250-8BITMIME
 Out: 250 DSN
 In:  MAIL FROM:<??hr_geld@senderdomain.tld> BODY=8BITMIME
     ENVID=15__1_1511309431064_883_1309431281
 Out: 250 2.1.0 Ok
 In:  RCPT TO:<myuser@mydomain.tld> NOTIFY=FAILURE
 Out: 451 4.3.0 <  hr_geld@senderdomain.tld>: Temporary lookup failure
 In:  DATA
 Out: 554 5.5.1 Error: no valid recipients
 In:  QUIT
 Out: 221 2.0.0 Bye


For other details, see the local mail logfile

Log Email 2:

Jul  4 22:09:58 poseidon postfix/smtpd[14957]: connect from j121po155.domain.tld[nnn.nnn.nnn.nnn]
Jul  4 22:09:58 poseidon postfix/proxymap[14923]: warning: mysql query failed: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
Jul  4 22:09:58 poseidon postfix/trivial-rewrite[14922]: warning: transport_maps lookup failure
Jul  4 22:09:58 poseidon postfix/trivial-rewrite[14922]: warning: transport_maps lookup failure
Jul  4 22:09:58 poseidon postfix/smtpd[14957]: NOQUEUE: reject: RCPT from j121po155.domain.tld[nnn.nnn.nnn.nnn]: 451 4.3.0 <  hr_geld@senderdomain.tld>: Temporary lookup failure; from=<??hr_geld@senderdomain.tld> to=<myuser@mydomain.tld> proto=ESMTP helo=<j121po155.domain.tld>
Jul  4 22:09:58 poseidon postfix/cleanup[14958]: 86471252E02: message-id=<20110704200958.86471252E02@poseidon.mydomain.tld>
Jul  4 22:09:58 poseidon postfix/qmgr[1513]: 86471252E02: from=<double-bounce@poseidon.mydomain.tld>, size=1183, nrcpt=1 (queue active)
Jul  4 22:09:58 poseidon postfix/trivial-rewrite[14922]: warning: transport_maps lookup failure
Jul  4 22:09:58 poseidon postfix/smtpd[14957]: disconnect from j121po155.domain.tld[nnn.nnn.nnn.nnn]

It seems there is a problem with comparing data from the email with data in the database due the collation. This two emails want to be delivered all 10 minutes and for every of the mails I get an email like you see obove. In my opinion these emails are spam. The good thing is that this mails never get delivered but the warning mails bother me.

Sadly it happens not only to clearly noticable spammails. I have some wanted emails too that can´t get deliverd because of this. But of course the mailservers of these emails don´t try endless to deliver.

Is there some more information I can give to find what is wrong? Please let me know if I can give more information.

Thanks for every help in advance.

9

Re: Postfix SMTP server: errors

Znekar wrote:

Jul  4 22:09:58 poseidon postfix/proxymap[14923]: warning: mysql query failed: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='
Jul  4 22:09:58 poseidon postfix/trivial-rewrite[14922]: warning: transport_maps lookup failure

It's clear here.
Did you change mysql query in postfix lookup file (in 'transport_maps' setting)? Please fix it first.

10

Re: Postfix SMTP server: errors

ZhangHuangbin wrote:

Did you change mysql query in postfix lookup file (in 'transport_maps' setting)? Please fix it first.

Thanks for the hint ZhangHuangbin, but I cant see where the problem is. Perhaps because I am not a SQL specialist. wink

The query in /etc/postfix/mysql/transport_maps_domain.cf looks like:

query       = SELECT transport FROM domain WHERE domain='%s' AND active=1

and the one in /etc/postfix/mysql/transport_maps_user.cf is:

query       = SELECT mailbox.transport FROM mailbox,domain WHERE mailbox.username='%s' AND mailbox.domain='%d' AND mailbox.domain=domain.domain AND mailbox.transport<>'' AND mailbox.active=1 AND mailbox.enabledeliver=1 AND domain.backupmx=0 AND domain.active=1

Is there anything wrong or did I check the wrong files? In fact the files date is the one from installation day. So the files was not changed since then.

11

Re: Postfix SMTP server: errors

Queries in lookup files are OK. Did you change MySQL structure? Because it complains " Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='".
Please paste output of below SQL commands:

$ mysql -uroot -p
mysql> USE vmail;
mysql> DESC mailbox;
mysql> DESC domain;

12

Re: Postfix SMTP server: errors

Here is what I get. I paste all that you can see also the server version. Perhaps it is important...

#  mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21094
Server version: 5.1.49-1ubuntu8.1 (Ubuntu)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE vmail;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> DESC mailbox;
+--------------------------+-----------------+------+-----+---------------------+-------+
| Field                    | Type            | Null | Key | Default             | Extra |
+--------------------------+-----------------+------+-----+---------------------+-------+
| username                 | varchar(255)    | NO   | PRI | NULL                |       |
| password                 | varchar(255)    | NO   |     |                     |       |
| name                     | varchar(255)    | NO   |     |                     |       |
| storagebasedirectory     | varchar(255)    | NO   |     | /var/vmail          |       |
| storagenode              | varchar(255)    | NO   |     | vmail1              |       |
| maildir                  | varchar(255)    | NO   |     |                     |       |
| quota                    | bigint(20)      | NO   |     | 0                   |       |
| bytes                    | bigint(20)      | NO   |     | 0                   |       |
| messages                 | bigint(20)      | NO   |     | 0                   |       |
| domain                   | varchar(255)    | NO   | MUL |                     |       |
| transport                | varchar(255)    | NO   |     |                     |       |
| department               | varchar(255)    | NO   | MUL |                     |       |
| rank                     | varchar(255)    | NO   |     | normal              |       |
| employeeid               | varchar(255)    | YES  | MUL |                     |       |
| enablesmtp               | tinyint(1)      | NO   | MUL | 1                   |       |
| enablesmtpsecured        | tinyint(1)      | NO   | MUL | 1                   |       |
| enablepop3               | tinyint(1)      | NO   | MUL | 1                   |       |
| enablepop3secured        | tinyint(1)      | NO   | MUL | 1                   |       |
| enableimap               | tinyint(1)      | NO   | MUL | 1                   |       |
| enableimapsecured        | tinyint(1)      | NO   | MUL | 1                   |       |
| enabledeliver            | tinyint(1)      | NO   | MUL | 1                   |       |
| enablemanagesieve        | tinyint(1)      | NO   | MUL | 1                   |       |
| enablemanagesievesecured | tinyint(1)      | NO   | MUL | 1                   |       |
| enablesieve              | tinyint(1)      | NO   | MUL | 1                   |       |
| enablesievesecured       | tinyint(1)      | NO   | MUL | 1                   |       |
| enableinternal           | tinyint(1)      | NO   | MUL | 1                   |       |
| lastlogindate            | datetime        | NO   |     | 0000-00-00 00:00:00 |       |
| lastloginipv4            | int(4) unsigned | NO   |     | 0                   |       |
| lastloginprotocol        | char(255)       | NO   |     |                     |       |
| disclaimer               | text            | NO   |     | NULL                |       |
| passwordlastchange       | datetime        | NO   | MUL | 0000-00-00 00:00:00 |       |
| created                  | datetime        | NO   |     | 0000-00-00 00:00:00 |       |
| modified                 | datetime        | NO   |     | 0000-00-00 00:00:00 |       |
| expired                  | datetime        | NO   | MUL | 9999-12-31 00:00:00 |       |
| active                   | tinyint(1)      | NO   | MUL | 1                   |       |
| local_part               | varchar(255)    | NO   |     |                     |       |
+--------------------------+-----------------+------+-----+---------------------+-------+
36 rows in set (0.01 sec)

mysql> DESC domain;
+-----------------------+--------------+------+-----+---------------------+-------+
| Field                 | Type         | Null | Key | Default             | Extra |
+-----------------------+--------------+------+-----+---------------------+-------+
| domain                | varchar(255) | NO   | PRI |                     |       |
| description           | text         | NO   |     | NULL                |       |
| disclaimer            | text         | NO   |     | NULL                |       |
| aliases               | int(10)      | NO   |     | 0                   |       |
| mailboxes             | int(10)      | NO   |     | 0                   |       |
| maxquota              | bigint(20)   | NO   |     | 0                   |       |
| quota                 | bigint(20)   | NO   |     | 0                   |       |
| transport             | varchar(255) | NO   |     | dovecot             |       |
| backupmx              | tinyint(1)   | NO   | MUL | 0                   |       |
| defaultuserquota      | bigint(20)   | NO   |     | 1024                |       |
| defaultuseraliases    | text         | NO   |     | NULL                |       |
| defaultpasswordscheme | varchar(10)  | NO   |     |                     |       |
| minpasswordlength     | int(10)      | NO   |     | 0                   |       |
| maxpasswordlength     | int(10)      | NO   |     | 0                   |       |
| created               | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| modified              | datetime     | NO   |     | 0000-00-00 00:00:00 |       |
| expired               | datetime     | NO   | MUL | 9999-12-31 00:00:00 |       |
| active                | tinyint(1)   | NO   | MUL | 1                   |       |
+-----------------------+--------------+------+-----+---------------------+-------+
18 rows in set (0.00 sec)

13

Re: Postfix SMTP server: errors

I have exactly the same problem.
I also get the following warning:

warning: mysql query failed: Illegal mix of collations (ascii_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '='

Due to the missing collation info in the mysql output from Znekar, I added the table info from phpmyadmin for Information. Here you can see that the collation of all domain and mail address fields are set to ascii_general_ci.

Post's attachments

mailbox.png 131.98 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

14

Re: Postfix SMTP server: errors

find attached also the image of the domain table structure

Post's attachments

domain.png
domain.png 82.58 kb, file has never been downloaded. 

You don't have the permssions to download the attachments of this post.

15

Re: Postfix SMTP server: errors

michburt wrote:

find attached also the image of the domain table structure

I was having the same problem and changing the collation for the columns fixed the problem. It seems that every time I was having that problem it was also affecting other SMTP transactions. Eventually the messages come through because the sending end tries again but I would say you have to change it on the following tables and columns:

- alias: address, goto, domain
- alias_domain: alias_domain, target_domain
- domain: domain
- mailbox: username, domain

That fixed the problem when the server was getting messages like: é@domainname.com

I would say it's wise to also make changes to tables like:

- recipient_bcc_domain
- recipient_bcc_user
- sender_bcc_domain
- sender_bcc_user

As that might also cause problems.

16

Re: Postfix SMTP server: errors

I'm confused what trigger this issue on your servers. Any logs (e.g. invalid sender/recipient email addresses, subjects, etc)?

17 (edited by Znekar 2011-07-08 00:04:47)

Re: Postfix SMTP server: errors

atros wrote:

I was having the same problem and changing the collation for the columns fixed the problem. It seems that every time I was having that problem it was also affecting other SMTP transactions.

I can confirm that. Now the spamer stoped sending to my server and I don´t have any problems anymore.

@ZhangHuangbin: If you want I can send you logfiles. Just tell me which one you need and I´ll send them via Email to you.

edit: Sorry, quoted bad. I ment that I confirm that the problem affecting other SMTP transactions. I didn´t change anything on my server.

18

Re: Postfix SMTP server: errors

Hi Znekar,

May i know how you fixed this issue? Any SQL command or others? What's the trigger and why it happened?

19

Re: Postfix SMTP server: errors

ZhangHuangbin wrote:

Hi Znekar,

May i know how you fixed this issue? Any SQL command or others? What's the trigger and why it happened?

Well, I don´t fix anything. The mailserver which wanted to send spam to me gave up try to send. At the moment everything is fine, but I am afraid that the problem will be back again sooner or later. sad

As I offered before I can send you logfiles if they can help you to find what was the problem.

atros wrote:

Eventually the messages come through because the sending end tries again but I would say you have to change it on the following tables and columns...

@atros: Did you change that tables and colums to UTF8?

20

Re: Postfix SMTP server: errors

atros wrote:
michburt wrote:

find attached also the image of the domain table structure

I was having the same problem and changing the collation for the columns fixed the problem. It seems that every time I was having that problem it was also affecting other SMTP transactions. Eventually the messages come through because the sending end tries again but I would say you have to change it on the following tables and columns:

- alias: address, goto, domain
- alias_domain: alias_domain, target_domain
- domain: domain
- mailbox: username, domain

That fixed the problem when the server was getting messages like: é@domainname.com

I would say it's wise to also make changes to tables like:

- recipient_bcc_domain
- recipient_bcc_user
- sender_bcc_domain
- sender_bcc_user

As that might also cause problems.

Did You change ascii_general_ci to utf8_general_ci ?