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.000000000 -0400
+++ ./src/global/dict_mysql.c 2023-04-17 11:07:47.000000000 -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.000000000
-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.000000000 -0500
+++ ./proto/mysql_table 2023-04-17 11:24:16.000000000 -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.000000000 -0400
+++ ./src/global/dict_mysql.c 2023-04-17 11:09:35.000000000 -0400
@@ -147,6 +147,7 @@
char *username;
char *password;
char *dbname;
+ char *charset;
ARGV *hosts;
PLMYSQL *pldb;
#if defined(MYSQL_VERSION_ID) && MYSQL_VERSION_ID >= 40000
@@ -602,6 +603,13 @@
host->port,
(host->type == TYPEUNIX ? host->name : 0),
CLIENT_MULTI_RESULTS)) {
+ if (*dict_mysql->charset != 0
+ && mysql_set_character_set(host->db, dict_mysql->charset) != 0) {
+ msg_warn("dict_mysql: mysql_set_character_set '%s' failed: %s",
+ dict_mysql->charset, mysql_error(host->db));
+ plmysql_down_host(host);
+ return;
+ }
if (msg_verbose)
msg_info("dict_mysql: successful connection to host %s",
host->hostname);
@@ -646,6 +654,7 @@
dict_mysql->username = cfg_get_str(p, "user", "", 0, 0);
dict_mysql->password = cfg_get_str(p, "password", "", 0, 0);
dict_mysql->dbname = cfg_get_str(p, "dbname", "", 1, 0);
+ dict_mysql->charset = cfg_get_str(p, "charset", "", 1, 0);
dict_mysql->result_format = cfg_get_str(p, "result_format", "%s", 1, 0);
dict_mysql->option_file = cfg_get_str(p, "option_file", NULL, 0, 0);
dict_mysql->option_group = cfg_get_str(p, "option_group", "client", 0, 0);
@@ -826,6 +835,7 @@
myfree(dict_mysql->username);
myfree(dict_mysql->password);
myfree(dict_mysql->dbname);
+ myfree(dict_mysql->charset);
myfree(dict_mysql->query);
myfree(dict_mysql->result_format);
if (dict_mysql->option_file)
_______________________________________________
Postfix-users mailing list -- postfix-users@postfix.org
To unsubscribe send an email to postfix-users-le...@postfix.org
