[pfx] Re: temporary lookup error with utf8mb4 characters
Gerald Galster via Postfix-users: > > > Wietse Venema via Postfix-users : > > > > And here is a more conservative patch for MySQL client retries. > > > > It closes the server connection after every error, and it delays > > making a new server connection only after specific errors. > > > > Closing the connection eliminates the possibility that the client > > becomes stuck. > > dict_mysql.c: In function 'plmysql_connect_single': > dict_mysql.c:709:13: error: too few arguments to function 'plmysql_down_host' > 709 | plmysql_down_host(host); > > > After adding RETRY_CONN_INTV to plmysql_down_host it works. > My version of 20230417-mysql-charset-patch.txt contained > plmysql_down_host(host). > > > Setting charset = testcharset yields: > > postfix/proxymap[14072]: warning: dict_mysql: mysql_set_character_set > 'testcharset' failed: Can't initialize character set testcharset (path: > compiled_in) > postfix/postmap[14078]: fatal: table > proxy:mysql:/etc/postfix/relay_domains.mysql.cf: query error: Application > error > > Mysql connects then pause for about a minute. > > Introducing a simple sql syntax error yields: > > postfix/proxymap[15610]: warning: mysql:/etc/postfix/relay_domains.mysql.cf: > query failed: You have an error in your SQL syntax... > > There is no pause, every new request hits MySQL. Well, almost. I spent some time this morning analyzing code, because I was suspicious that this 'no delay after error' fix did not cause the code to go into a fast retry loop. The reason it does not loop is that there will be a delay of up to one second, because the delay is in effect while some deadline >= current_time (the time is measured with one-second resolution). While the delay is in effect, I discovered that the MySQL client will reply with "not found" instead of "error". That bug was introduced in Postfix 3.2, a 'missing initialization' error that none of my compilers reported. I can't eliminate the up-to-one-second delay (changing the code to delay while 'deadline > current_time'), because the MySQL client would go into a fast retry loop. Fixing that would require more invasive changes than I can reasonably do in a stable release. Consdering that - the 60-second delay after error has been in effect since the MySQL client was adopted into Postfix on 19991208, - the same 60-second delay afer error exists in the PostgreSQL client, - there have been no other complaints about the 60-second delay, I'll not change the stable Postfix releases except for the "not found instead of error" bug that I mentioned above. Again, thanks for the assistance with testing. I ended up setting up a MySQL test locally. I should have done that 24 years ago. Wietse ___ Postfix-users mailing list -- postfix-users@postfix.org To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
> Wietse Venema via Postfix-users : > > And here is a more conservative patch for MySQL client retries. > > It closes the server connection after every error, and it delays > making a new server connection only after specific errors. > > Closing the connection eliminates the possibility that the client > becomes stuck. dict_mysql.c: In function 'plmysql_connect_single': dict_mysql.c:709:13: error: too few arguments to function 'plmysql_down_host' 709 | plmysql_down_host(host); After adding RETRY_CONN_INTV to plmysql_down_host it works. My version of 20230417-mysql-charset-patch.txt contained plmysql_down_host(host). Setting charset = testcharset yields: postfix/proxymap[14072]: warning: dict_mysql: mysql_set_character_set 'testcharset' failed: Can't initialize character set testcharset (path: compiled_in) postfix/postmap[14078]: fatal: table proxy:mysql:/etc/postfix/relay_domains.mysql.cf: query error: Application error Mysql connects then pause for about a minute. Introducing a simple sql syntax error yields: postfix/proxymap[15610]: warning: mysql:/etc/postfix/relay_domains.mysql.cf: query failed: You have an error in your SQL syntax... There is no pause, every new request hits MySQL. Best regards, Gerald ___ Postfix-users mailing list -- postfix-users@postfix.org To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
And here is a more conservative patch for MySQL client retries.
It closes the server connection after every error, and it delays
making a new server connection only after specific errors.
Closing the connection eliminates the possibility that the client
becomes stuck.
Wietse
20230417
Cleanup: in the MySQL client, temporarily stay away from a
server only if the last error was caused by a connection-level
or protocol-level failure. File: global/dict_mysql.c.
diff -ur /var/tmp/postfix-3.9-20230416/src/global/dict_mysql.c
./src/global/dict_mysql.c
--- /var/tmp/postfix-3.9-20230416/src/global/dict_mysql.c 2023-04-16
16:44:39.0 -0400
+++ ./src/global/dict_mysql.c 2023-04-17 19:17:02.0 -0400
@@ -108,6 +108,7 @@
/* Application-specific. */
#include "dict_mysql.h"
+#include "mysql/errmsg.h"
/* MySQL 8.x API change */
@@ -179,7 +180,7 @@
static int plmysql_query(DICT_MYSQL *, const char *, VSTRING *, MYSQL_RES **);
static void plmysql_dealloc(PLMYSQL *);
static void plmysql_close_host(HOST *);
-static void plmysql_down_host(HOST *);
+static void plmysql_down_host(HOST *, int);
static void plmysql_connect_single(DICT_MYSQL *, HOST *);
static const char *dict_mysql_lookup(DICT *, const char *);
DICT *dict_mysql_open(const char *, int, int);
@@ -546,7 +547,16 @@
* See what we got.
*/
if (query_error) {
- plmysql_down_host(host);
+ switch (mysql_errno(host->db)) {
+ case CR_COMMANDS_OUT_OF_SYNC:
+ case CR_SERVER_GONE_ERROR:
+ case CR_SERVER_LOST:
+ plmysql_down_host(host, RETRY_CONN_INTV);
+ break;
+ default:
+ plmysql_down_host(host, 0);
+ break;
+ }
if (errno == 0)
errno = ENOTSUP;
if (first_result) {
@@ -609,7 +619,7 @@
} else {
msg_warn("connect to mysql server %s: %s",
host->hostname, mysql_error(host->db));
- plmysql_down_host(host);
+ plmysql_down_host(host, RETRY_CONN_INTV);
}
}
@@ -625,11 +635,11 @@
* plmysql_down_host - close a failed connection AND set a "stay away from
* this host" timer
*/
-static void plmysql_down_host(HOST *host)
+static void plmysql_down_host(HOST *host, int delay)
{
mysql_close(host->db);
host->db = 0;
-host->ts = time((time_t *) 0) + RETRY_CONN_INTV;
+host->ts = time((time_t *) 0) + delay;
host->stat = STATFAIL;
event_cancel_timer(dict_mysql_event, (void *) host);
}
___
Postfix-users mailing list -- postfix-users@postfix.org
To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
>>> I've patched postfix 3.7.4 on a low volume server. >> >> Thank you! >> >>> "charset" has to be present and defined in all mysql configs, otherwise >>> startup fails: >>> (no backwards compatibility) >>> >>> postfix/proxymap[3996]: fatal: /etc/postfix/test.mysql.cf: bad string >>> length 0 < 1: charset = >> >> Grr. In the patch, >> >>cfg_get_int(stuff, "charset", "", 1, 0) >> >> should be >> >>cfg_get_int(stuff, "charset", "", 0, 0) > > That should be cfg_get_str. Thanks, I modified dict_mysql.c, recompiled and now it works as expected. Best regards, Gerald___ Postfix-users mailing list -- postfix-users@postfix.org To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
Wietse Venema via Postfix-users: > Gerald Galster via Postfix-users: > > > > > Wietse Venema via Postfix-users : > > > > > >>> My conclusion to hard-solve this issue on my system is transform all > > >>> tables to utf8mb4. > > >>> > > >>> But: > > > > > >>> - I don't see any option to change default charset on mysql_table > > >>> connector, maybe should be interesting add this option on configuration > > >>> file. > > >> > > >> Is there such an API? > > > > > > Based on documentation, perhaps mysql_set_character_set() can do that. > > > https://dev.mysql.com/doc/c-api/8.0/en/mysql-set-character-set.html > > > > > > Attached is patch 20230417-mysql-charset-patch.txt that adds a > > > "charset" parameter to the Postfix MySQL configuration file. > > > > > > I've patched postfix 3.7.4 on a low volume server. > > Thank you! > > > "charset" has to be present and defined in all mysql configs, otherwise > > startup fails: > > (no backwards compatibility) > > > > postfix/proxymap[3996]: fatal: /etc/postfix/test.mysql.cf: bad string > > length 0 < 1: charset = > > Grr. In the patch, > > cfg_get_int(stuff, "charset", "", 1, 0) > > should be > > cfg_get_int(stuff, "charset", "", 0, 0) That should be cfg_get_str. Wietse > > Setting "charset" to the non-default cp1250 works (from mysql general_log): > > > > (terminal encoding utf8) > > # postmap -q "bl?.com" mysql:/etc/postfix/relay_domains.mysql.cf > > Connect postfix@localhost on postfix using Socket > > Query SET NAMES cp1250 > > Query SELECT destination as relaydestination FROM relay WHERE domain = > > 'bl?.com' > > Quit > > (postfix restart) > > > > (terminal encoding latin1) > > # postmap -q "bl?.com" mysql:/etc/postfix/relay_domains.mysql.cf > > Connect postfix@localhost on postfix using Socket > > Query SET NAMES cp1250 > > Query SELECT destination as relaydestination FROM relay WHERE domain = > > 'bl?.com' > > > > Unfortunately I can't help with mix collation error as this mysql 8 instance > > is configured with utf8mb4/utf8_bin, skip-character-set-client-handshake and > > all tables are utf8mb4. I could not trigger a collation error. > > No problem, I am happy that the patch does not break something that > works without the patch. > > > +# .IP "\fBcharset\fR (empty for backwards compatibility)" > > +# The default client character set (and implicitly, the > > +# collation order). According to MySQL documentation the > > +# built-in default is "latin1"; for SMTP, "utf8" would be > > +# more appropriate. > > > > As of mysql 8.0 the default character set is utf8mb4: > > https://dev.mysql.com/blog-archive/mysql-8-0-collations-migrating-from-older-collations/ > > I'll delete the comnment about "latin1" as it is MySQL version dependent. > > > Historically utf8 had been a mysql alias for utf8mb3: > > https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html > > Again, thanks for what you could test. The error handling should > be better because the new code will no longer skip a connection > for 60s after every errror, but only after an error that involves a > really messed-up connection. > > Wietse > ___ > Postfix-users mailing list -- postfix-users@postfix.org > To unsubscribe send an email to postfix-users-le...@postfix.org > ___ Postfix-users mailing list -- postfix-users@postfix.org To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
Gerald Galster via Postfix-users: > > > Wietse Venema via Postfix-users : > > > >>> My conclusion to hard-solve this issue on my system is transform all > >>> tables to utf8mb4. > >>> > >>> But: > > > >>> - I don't see any option to change default charset on mysql_table > >>> connector, maybe should be interesting add this option on configuration > >>> file. > >> > >> Is there such an API? > > > > Based on documentation, perhaps mysql_set_character_set() can do that. > > https://dev.mysql.com/doc/c-api/8.0/en/mysql-set-character-set.html > > > > Attached is patch 20230417-mysql-charset-patch.txt that adds a > > "charset" parameter to the Postfix MySQL configuration file. > > > I've patched postfix 3.7.4 on a low volume server. Thank you! > "charset" has to be present and defined in all mysql configs, otherwise > startup fails: > (no backwards compatibility) > > postfix/proxymap[3996]: fatal: /etc/postfix/test.mysql.cf: bad string length > 0 < 1: charset = Grr. In the patch, cfg_get_int(stuff, "charset", "", 1, 0) should be cfg_get_int(stuff, "charset", "", 0, 0) > Setting "charset" to the non-default cp1250 works (from mysql general_log): > > (terminal encoding utf8) > # postmap -q "bl?.com" mysql:/etc/postfix/relay_domains.mysql.cf > Connect postfix@localhost on postfix using Socket > Query SET NAMES cp1250 > Query SELECT destination as relaydestination FROM relay WHERE domain = > 'bl?.com' > Quit > (postfix restart) > > (terminal encoding latin1) > # postmap -q "bl?.com" mysql:/etc/postfix/relay_domains.mysql.cf > Connect postfix@localhost on postfix using Socket > Query SET NAMES cp1250 > Query SELECT destination as relaydestination FROM relay WHERE domain = > 'bl?.com' > > Unfortunately I can't help with mix collation error as this mysql 8 instance > is configured with utf8mb4/utf8_bin, skip-character-set-client-handshake and > all tables are utf8mb4. I could not trigger a collation error. No problem, I am happy that the patch does not break something that works without the patch. > +# .IP "\fBcharset\fR (empty for backwards compatibility)" > +# The default client character set (and implicitly, the > +# collation order). According to MySQL documentation the > +# built-in default is "latin1"; for SMTP, "utf8" would be > +# more appropriate. > > As of mysql 8.0 the default character set is utf8mb4: > https://dev.mysql.com/blog-archive/mysql-8-0-collations-migrating-from-older-collations/ I'll delete the comnment about "latin1" as it is MySQL version dependent. > Historically utf8 had been a mysql alias for utf8mb3: > https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html Again, thanks for what you could test. The error handling should be better because the new code will no longer skip a connection for 60s after every errror, but only after an error that involves a really messed-up connection. Wietse ___ Postfix-users mailing list -- postfix-users@postfix.org To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
> Wietse Venema via Postfix-users : > >>> My conclusion to hard-solve this issue on my system is transform all >>> tables to utf8mb4. >>> >>> But: > >>> - I don't see any option to change default charset on mysql_table >>> connector, maybe should be interesting add this option on configuration >>> file. >> >> Is there such an API? > > Based on documentation, perhaps mysql_set_character_set() can do that. > https://dev.mysql.com/doc/c-api/8.0/en/mysql-set-character-set.html > > Attached is patch 20230417-mysql-charset-patch.txt that adds a > "charset" parameter to the Postfix MySQL configuration file. I've patched postfix 3.7.4 on a low volume server. "charset" has to be present and defined in all mysql configs, otherwise startup fails: (no backwards compatibility) postfix/proxymap[3996]: fatal: /etc/postfix/test.mysql.cf: bad string length 0 < 1: charset = postfix/smtpd[3995]: warning: dict_proxy_open: service proxymap: Application error postfix/master[3989]: warning: process /usr/libexec/postfix/proxymap pid 3996 exit status 1 postfix/master[3989]: warning: /usr/libexec/postfix/proxymap: bad command startup -- throttling Setting "charset" to the non-default cp1250 works (from mysql general_log): (terminal encoding utf8) # postmap -q "blü.com" mysql:/etc/postfix/relay_domains.mysql.cf Connect postfix@localhost on postfix using Socket Query SET NAMES cp1250 Query SELECT destination as relaydestination FROM relay WHERE domain = 'blü.com' Quit (postfix restart) (terminal encoding latin1) # postmap -q "bl?.com" mysql:/etc/postfix/relay_domains.mysql.cf Connect postfix@localhost on postfix using Socket Query SET NAMES cp1250 Query SELECT destination as relaydestination FROM relay WHERE domain = 'blü.com' Unfortunately I can't help with mix collation error as this mysql 8 instance is configured with utf8mb4/utf8_bin, skip-character-set-client-handshake and all tables are utf8mb4. I could not trigger a collation error. +++ ./proto/mysql_table 2023-04-17 11:24:16.0 -0400 @@ -79,6 +79,11 @@ # .nf #dbname = customer_database # .fi +# .IP "\fBcharset\fR (empty for backwards compatibility)" +# The default client character set (and implicitly, the +# collation order). According to MySQL documentation the +# built-in default is "latin1"; for SMTP, "utf8" would be +# more appropriate. As of mysql 8.0 the default character set is utf8mb4: https://dev.mysql.com/blog-archive/mysql-8-0-collations-migrating-from-older-collations/ Historically utf8 had been a mysql alias for utf8mb3: https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html Best regards, Gerald ___ Postfix-users mailing list -- postfix-users@postfix.org To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
Wietse Venema via Postfix-users:
> > My conclusion to hard-solve this issue on my system is transform all
> > tables to utf8mb4.
> >
> > But:
> > - I don't see any option to change default charset on mysql_table
> > connector, maybe should be interesting add this option on configuration
> > file.
>
> Is there such an API?
Based on documentation, perhaps mysql_set_character_set() can do that.
https://dev.mysql.com/doc/c-api/8.0/en/mysql-set-character-set.html
Attached is patch 20230417-mysql-charset-patch.txt that adds a
"charset" parameter to the Postfix MySQL configuration file.
I don't have a MySQL testbed; someone else would have to test this,
or this would have to wait until I have time to set up MySQL.
> > - mix collation error should raise 1 error, but next queries should be
> > work ok, this could be considered and issue right?.
>
> For the Postfix MySQL client the expected result of a query is:
>
> - found,
>
> - not found,
>
> - error.
>
> The client does not distinguish between errors, and all errors have
> the same result (skip this connection for 60s). That code is almost
> 20 years old, so I wonder if you are doing something unusal that
> other people aren't doing.
>
> Based on https://dev.mysql.com/doc/c-api/8.0/en/mysql-next-result.html
> I suppose that the client could distinguish between
> errors that indicate a connection error and other errors. But that
> would be a major code change.
It is possible to distinguish between errors without having tp
restructure code.
Attached is patch 20230417-mysql-retry-patch.txt that more selectively
backs off from a server connection.
Again if someone else can test this, great, otherwise this will
have to wait.
Wietse
20230417
Cleanup: in the MySQL client, temporarily stay away from a
server only if the last error was caused by connection-level
failure. File: global/dict_mysql.c.
diff '--exclude=man' '--exclude=html' '--exclude=README_FILES'
'--exclude=INSTALL' '--exclude=.indent.pro' -r -ur
/var/tmp/postfix-3.9-20230416/src/global/dict_mysql.c ./src/global/dict_mysql.c
--- /var/tmp/postfix-3.9-20230416/src/global/dict_mysql.c 2023-04-16
16:44:39.0 -0400
+++ ./src/global/dict_mysql.c 2023-04-17 11:07:47.0 -0400
@@ -108,6 +108,7 @@
/* Application-specific. */
#include "dict_mysql.h"
+#include "mysql/errmsg.h"
/* MySQL 8.x API change */
@@ -546,7 +547,14 @@
* See what we got.
*/
if (query_error) {
- plmysql_down_host(host);
+ switch (mysql_errno(host->db)) {
+ case CR_COMMANDS_OUT_OF_SYNC:
+ case CR_SERVER_GONE_ERROR:
+ case CR_SERVER_LOST:
+ plmysql_down_host(host);
+ default:
+ break;
+ }
if (errno == 0)
errno = ENOTSUP;
if (first_result) {
diff '--exclude=man' '--exclude=html' '--exclude=README_FILES'
'--exclude=INSTALL' '--exclude=.indent.pro' -r -ur
/var/tmp/postfix-3.9-20230416/HISTORY ./HISTORY
--- /var/tmp/postfix-3.9-20230416/HISTORY 2023-04-16 17:09:29.0
-0400
+++ ./HISTORY 2023-04-17 11:01:00.531589777 -0400
@@ -27055,3 +27055,9 @@
Cleanup: in source-code comments, replaced redundant (and
sometimes incomplete) lookup table configuration info with
a reference to the corresponding *_table(5) manpage.
+
+20230417
+
+ Cleanup: in the MySQL client, make the default characterset
+ (and collation) configurable (the MySQL defaults are latin1
+ and latin1_swedish_ci). File: global/dict_mysql.c.
diff '--exclude=man' '--exclude=html' '--exclude=README_FILES'
'--exclude=INSTALL' '--exclude=.indent.pro' -r -ur
/var/tmp/postfix-3.9-20230416/proto/mysql_table ./proto/mysql_table
--- /var/tmp/postfix-3.9-20230416/proto/mysql_table 2022-12-27
18:01:00.0 -0500
+++ ./proto/mysql_table 2023-04-17 11:24:16.0 -0400
@@ -79,6 +79,11 @@
# .nf
# dbname = customer_database
# .fi
+# .IP "\fBcharset\fR (empty for backwards compatibility)"
+# The default client character set (and implicitly, the
+# collation order). According to MySQL documentation the
+# built-in default is "latin1"; for SMTP, "utf8" would be
+# more appropriate.
# .IP "\fBquery\fR"
# The SQL query template used to search the database, where \fB%s\fR
# is a substitute for the address Postfix is trying to resolve,
diff '--exclude=man' '--exclude=html' '--exclude=README_FILES'
'--exclude=INSTALL' '--exclude=.indent.pro' -r -ur
/var/tmp/postfix-3.9-20230416/src/global/dict_mysql.c ./src/global/dict_mysql.c
--- /var/tmp/postfix-3.9-20230416/src/global/dict_mysql.c 2023-04-16
16:44:39.0 -0400
+++ ./src/global/dict_mysql.c 2023-04-17 11:09:35.0 -0400
@@ -147,6 +147,7 @@
char *username;
char *password;
char *dbname;
+char *charset;
ARGV *hosts;
PLMYSQL *pldb;
#if
[pfx] Re: temporary lookup error with utf8mb4 characters
V?ctor Rubiella Monfort via Postfix-users:
> Hi, I have more info and I try to explain it better:
>
> First of all I have smtputf8_enable = no (disabled).
>
> I have several databases related with several mysql_virtual maps:
>
> - Some with utf8 + utf8_general_ci collation
>
> - Another ones with latin1 + latin1_spanish_ci.
>
> I'm using mysql-postfix (mysql_table) lookups, not postgres.
> "proxy:mysql:/XXX.cf".
>
> I can reproduce same issue with both cf files (tables with utf8 and
> tables with latin1).
>
> As I say before, the worst part is when error is raised during about 1
> minute all lookups raises failures.
>
> Error is easy to reproduce manually calling to "postmap -q
> "emailWithspecialchar" "proxy:mysql:/XXX.cf"
>
> Debugging I observe 2 things.
>
> - adding CONVERT('%s' using ascii) fix the issue but I don't want/like
> add converts on all my sql queries...
>
> - adding COLLATE utf8_general_ci raises error "this collate is not valid
> for utf8mb4". This error shows me than mysql_table lookup connections
> are using "utf8mb4" charset by default.
>
> My conclusion to hard-solve this issue on my system is transform all
> tables to utf8mb4.
>
> But:
>
> - I don't see any option to change default charset on mysql_table
> connector, maybe should be interesting add this option on configuration
> file.
Is there such an API?
> - mix collation error should raise 1 error, but next queries should be
> work ok, this could be considered and issue right?.
For the Postfix MySQL client the expected result of a query is:
- found,
- not found,
- error.
The client does not distinguish between errors, and all errors have
the same result (skip this connection for 60s). That code is almost
20 years old, so I wonder if you are doing something unusal that
other people aren't doing.
Based on https://dev.mysql.com/doc/c-api/8.0/en/mysql-next-result.html
I suppose that the client could distinguish between
errors that indicate a connection error and other errors. But that
would be a major code change.
It would help if you could show the warning that Postfix logs.
mysql:/file/name: query failed (mysql_next_result): >>>THIS TEXT
> - with "smtputf8_enable = no" I should be able to work without this kind
> of issues right?
No. With "smtputf8_enable = no", Postfix will not verify that a
query contains malformed text. This can result in errors from the
MySQL server.
On the other hand, with "smtputf8_enable = yes", Postfix will skip
a query that contains malformed UTF-8, thus avoiding errors from
the MySQL server.
> For modern protocols I can undestant change to utf8, but utf8mb4? this
> is much more expensive for the database, is it really necessary?
By design UTF-8 is a multi-byte encoding for all non-ASCII characters.
The only single-byte in UTF-8 is the ASCII subset.
Wietse
___
Postfix-users mailing list -- postfix-users@postfix.org
To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
Viktor Dukhovni via Postfix-users: > On Fri, Apr 14, 2023 at 01:06:16PM -0400, Wietse Venema via Postfix-users > wrote: > > > Wietse Venema via Postfix-users: > > > As for the temp error becoming persistent, the Postfix pgsql: client > > > code returns an error when it gets an error from all of the hosts > > > configured in the Postfix pgsql: client configuration file, or when > > > all hosts have been flagged as 'down'. If a host returns an error > > > then the Postfix pgsql: client code flags that host as 'down', and > > > resets that 'down' state after about 60 seconds. > > > > As implemented, the Postfix pgsql: clien code treats all errors as > > a connection failure, and skips the connection for 60 seconds. That > > may not be optimal when an error is data dependent. > > FWIW, the OP's issue was with MySQL, not Postgres... The database > should be configured for client and server encoding of UTF8. Oops. Where did I get that from. There is no hard-coded encoding type in the Postfix mysql client. With smtputf8_enable=yes, Postfix will accept email addresses with well-formed UTF8 (and ASCII) but not Latin1. Wietse ___ Postfix-users mailing list -- postfix-users@postfix.org To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
Hi, I have more info and I try to explain it better:
First of all I have smtp_utf8 = no (disabled).
I have several databases related with several mysql_virtual maps:
- Some with utf8 + utf8_general_ci collation
- Another ones with latin1 + latin1_spanish_ci.
I'm using mysql-postfix (mysql_table) lookups, not postgres.
"proxy:mysql:/XXX.cf".
I can reproduce same issue with both cf files (tables with utf8 and
tables with latin1).
As I say before, the worst part is when error is raised during about 1
minute all lookups raises failures.
Error is easy to reproduce manually calling to "postmap -q
"emailWithspecialchar" "proxy:mysql:/XXX.cf"
Debugging I observe 2 things.
- adding CONVERT('%s' using ascii) fix the issue but I don't want/like
add converts on all my sql queries...
- adding COLLATE utf8_general_ci raises error "this collate is not valid
for utf8mb4". This error shows me than mysql_table lookup connections
are using "utf8mb4" charset by default.
My conclusion to hard-solve this issue on my system is transform all
tables to utf8mb4.
But:
- I don't see any option to change default charset on mysql_table
connector, maybe should be interesting add this option on configuration
file.
- mix collation error should raise 1 error, but next queries should be
work ok, this could be considered and issue right?.
- with "smtputf8_enable = no" I should be able to work without this kind
of issues right?
For modern protocols I can undestant change to utf8, but utf8mb4? this
is much more expensive for the database, is it really necessary?
**
El 14/4/23 a las 20:46, Viktor Dukhovni via Postfix-users escribió:
On Fri, Apr 14, 2023 at 01:06:16PM -0400, Wietse Venema via Postfix-users wrote:
Wietse Venema via Postfix-users:
As for the temp error becoming persistent, the Postfix pgsql: client
code returns an error when it gets an error from all of the hosts
configured in the Postfix pgsql: client configuration file, or when
all hosts have been flagged as 'down'. If a host returns an error
then the Postfix pgsql: client code flags that host as 'down', and
resets that 'down' state after about 60 seconds.
As implemented, the Postfix pgsql: clien code treats all errors as
a connection failure, and skips the connection for 60 seconds. That
may not be optimal when an error is data dependent.
FWIW, the OP's issue was with MySQL, not Postgres... The database
should be configured for client and server encoding of UTF8.
___
Postfix-users mailing list -- postfix-users@postfix.org
To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
On Fri, Apr 14, 2023 at 01:06:16PM -0400, Wietse Venema via Postfix-users wrote: > Wietse Venema via Postfix-users: > > As for the temp error becoming persistent, the Postfix pgsql: client > > code returns an error when it gets an error from all of the hosts > > configured in the Postfix pgsql: client configuration file, or when > > all hosts have been flagged as 'down'. If a host returns an error > > then the Postfix pgsql: client code flags that host as 'down', and > > resets that 'down' state after about 60 seconds. > > As implemented, the Postfix pgsql: clien code treats all errors as > a connection failure, and skips the connection for 60 seconds. That > may not be optimal when an error is data dependent. FWIW, the OP's issue was with MySQL, not Postgres... The database should be configured for client and server encoding of UTF8. -- Viktor. ___ Postfix-users mailing list -- postfix-users@postfix.org To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
Wietse Venema via Postfix-users:
> As for the temp error becoming persistent, the Postfix pgsql: client
> code returns an error when it gets an error from all of the hosts
> configured in the Postfix pgsql: client configuration file, or when
> all hosts have been flagged as 'down'. If a host returns an error
> then the Postfix pgsql: client code flags that host as 'down', and
> resets that 'down' state after about 60 seconds.
As implemented, the Postfix pgsql: clien code treats all errors as
a connection failure, and skips the connection for 60 seconds. That
may not be optimal when an error is data dependent.
What error did Postfix log for a bad query? It would be helpful if
you could look in your logs for the exact error message.
The relevant code is below my signature.
Wietse
if ((res = PQexec(host->db, vstring_str(query))) != 0) {
...
switch ((status = PQresultStatus(res))) {
case PGRES_TUPLES_OK:
case PGRES_COMMAND_OK:
/* Success. */
if (msg_verbose)
msg_info("dict_pgsql: successful query from host %s",
host->hostname);
event_request_timer(dict_pgsql_event, (void *) host,
dict_pgsql->idle_interval);
return (res);
case PGRES_FATAL_ERROR:
msg_warn("pgsql query failed: fatal error from host %s: %s",
host->hostname, PQresultErrorMessage(res));
break;
case PGRES_BAD_RESPONSE:
msg_warn("pgsql query failed: protocol error, host %s",
host->hostname);
break;
default:
msg_warn("pgsql query failed: unknown code 0x%lx from host %s",
(unsigned long) status, host->hostname);
break;
}
} else {
...
msg_warn("pgsql query failed: fatal error from host %s: %s",
host->hostname, PQerrorMessage(host->db));
}
...
/*
* XXX An error occurred. Clean up memory and skip this connection.
*/
...
plpgsql_down_host(dict_pgsql, host);
___
Postfix-users mailing list -- postfix-users@postfix.org
To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
V?ctor Rubiella Monfort via Postfix-users:
> Hi again,
>
> I realized than same error is raised when database is in utf8 if email
> contains utf8mb4 characters.
>
> Which is the convenient database collation for postfix? We can force
> postfix to accept only utf8 characters?.
With "smtputf8_enable = yes" in main.cf, Postfix will accept
well-formed UTF-8. However, before Postfix 3.8, the Postfix pgsql:
client code sets the client encoding to LATIN1.
The client encoding is configurable (default: UTF8) with Postfix
3.8 which is to be released soon (postfix-3.8.0-RC1 was published
a few days ago).
As for the temp error becoming persistent, the Postfix pgsql: client
code returns an error when it gets an error from all of the hosts
configured in the Postfix pgsql: client configuration file, or when
all hosts have been flagged as 'down'. If a host returns an error
then the Postfix pgsql: client code flags that host as 'down', and
resets that 'down' state after about 60 seconds.
In your case having multiple hosts configured in the Postfix pgsql:
client configuration would not help, because they would have the
same error.
Wietse
>
>
> El 13/4/23 a las 18:36, V?ctor Rubiella Monfort via Postfix-users escribi?:
> > When mysql_table lookup is executing nonascii characters and database
> > is in latin1, not only fails query, all sesion/connection is corrupted
> > and produces a lot of "temporary lookup table" errors until sesion is
> > recreated (about 1 minute later).
> >
> > Today some external ip was trying to deliver an email with special
> > character on one on my legacy servers (with latin1) and produces this
> > errors.
> >
> > I can understant that lookup fails for query with special characters,
> > but main issue was for all raised failures for other accounts and
> > lookups during 1-2 minutes. This is a knew issue?.
> >
> >
> > I deploy an workaround using "CONVERT('%s' using ascii)" until not
> > pass all database tables to utf8.
> >
> > The main problem debuging this issue was "proxy:mysql" , "proxy" was
> > hiding original collation error and only shows regular lookup errors
> > on postfix log, when user "postmap" to debug, I only see root cause
> > when execute without "proxy".
> >
> > postfix versions tested:
> >
> > postfix 3.5.17-0+deb11u1
> > postfix-mysql??? 3.5.17-0+deb11u1
> >
> > postfix 3.5.15-0+deb11u1
> > postfix-mysql??? 3.5.15-0+deb11u1
> >
> >
> >
> >
> > ___
> > Postfix-users mailing list -- postfix-users@postfix.org
> > To unsubscribe send an email to postfix-users-le...@postfix.org
> ___
> Postfix-users mailing list -- postfix-users@postfix.org
> To unsubscribe send an email to postfix-users-le...@postfix.org
>
___
Postfix-users mailing list -- postfix-users@postfix.org
To unsubscribe send an email to postfix-users-le...@postfix.org
[pfx] Re: temporary lookup error with utf8mb4 characters
Hi again,
I realized than same error is raised when database is in utf8 if email
contains utf8mb4 characters.
Which is the convenient database collation for postfix? We can force
postfix to accept only utf8 characters?.
El 13/4/23 a las 18:36, Víctor Rubiella Monfort via Postfix-users escribió:
When mysql_table lookup is executing nonascii characters and database
is in latin1, not only fails query, all sesion/connection is corrupted
and produces a lot of "temporary lookup table" errors until sesion is
recreated (about 1 minute later).
Today some external ip was trying to deliver an email with special
character on one on my legacy servers (with latin1) and produces this
errors.
I can understant that lookup fails for query with special characters,
but main issue was for all raised failures for other accounts and
lookups during 1-2 minutes. This is a knew issue?.
I deploy an workaround using "CONVERT('%s' using ascii)" until not
pass all database tables to utf8.
The main problem debuging this issue was "proxy:mysql" , "proxy" was
hiding original collation error and only shows regular lookup errors
on postfix log, when user "postmap" to debug, I only see root cause
when execute without "proxy".
postfix versions tested:
postfix 3.5.17-0+deb11u1
postfix-mysql 3.5.17-0+deb11u1
postfix 3.5.15-0+deb11u1
postfix-mysql 3.5.15-0+deb11u1
___
Postfix-users mailing list -- postfix-users@postfix.org
To unsubscribe send an email to postfix-users-le...@postfix.org
___
Postfix-users mailing list -- postfix-users@postfix.org
To unsubscribe send an email to postfix-users-le...@postfix.org
