ich hätte da ein Problem bei einer Lookup-tables in einer neuen Postfix-Installation. Folgendes Szenario:
Ein Mailserver zieht mit all seinen Accounts in einer alten MySQl-DB "mailcow" um in eine Datenbank "postfix", die von Postfixadmin bedient werden kann. In der main.cf auf beiden Servern stehen diese Einträge für die Lookup-Tables:
Code: Alles auswählen
proxy_read_maps = proxy:mysql:/etc/postfix/sql/mysql_virtual_sender_acl.cf,
proxy:mysql:/etc/postfix/sql/mysql_tls_enforce_out_policy.cf,
proxy:mysql:/etc/postfix/sql/mysql_tls_enforce_in_policy.cf,
$local_recipient_maps
$mydestination
$virtual_alias_maps
$virtual_alias_domains
$virtual_mailbox_maps
$virtual_mailbox_domains
$relay_recipient_maps
$relay_domains
$canonical_maps
$sender_canonical_maps
$recipient_canonical_maps
$relocated_maps
$transport_maps
$mynetworks
$smtpd_sender_login_maps
Code: Alles auswählen
Mar 14 09:46:26 mx postfix/smtpd[11493]: warning: proxy:mysql:/etc/postfix/sql/mysql_virtual_sender_acl.cf lookup error for "info@example.tld"
Code: Alles auswählen
# mysql_virtual_sender_acl.cf
user = postfix
password = secret
hosts = localhost
dbname = postfix
query = SELECT goto FROM alias WHERE address='%s' AND active='1' AND domain IN (SELECT domain FROM domain WHERE domain='%d' AND active='1') UNION SELECT logged_in_as FROM sender_acl WHERE send_as='@%d' OR send_as='%s' AND logged_in_as NOT IN (SELECT goto FROM alias WHERE address='%s') UNION SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = '%d' AND alias.address = CONCAT('%u', '@', alias_domain.target_domain) AND alias.active ='1' AND alias_domain.active='1'
Auf dem alten Server server1.com funktioniert die Abfrage:
Code: Alles auswählen
SELECT goto FROM alias WHERE address='wh@server1.com' AND active='1' AND domain IN (SELECT domain FROM domain WHERE domain='server1.com' AND active='1') UNION SELECT logged_in_as FROM sender_acl WHERE send_as='@%d' OR send_as='%s' AND logged_in_as NOT IN (SELECT goto FROM alias WHERE address='%s') UNION SELECT goto FROM alias,alias_domain WHERE alias_domain.alias_domain = '%d' AND alias.address = CONCAT('%u', '@', alias_domain.target_domain) AND alias.active ='1' AND alias_domain.active='1';
+----------------+
| goto |
+----------------+
| wh@server1.com |
+----------------+
Code: Alles auswählen
ERROR 1271 (HY000): Illegal mix of collations for operation 'UNION'
MariaDB [postfix]> SHOW VARIABLES LIKE '%char%';
Code: Alles auswählen
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.001 sec)
MariaDB [postfix]> SHOW VARIABLES LIKE '%collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------+--------------------+
Code: Alles auswählen
MariaDB [mailcow]> SHOW VARIABLES LIKE '%char%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (1.839 sec)
MariaDB [mailcow]> SHOW VARIABLES LIKE '%collation%';
+----------------------+--------------------+
| Variable_name | Value |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_general_ci |
| collation_server | utf8mb4_general_ci |
+----------------------+--------------------+
Code: Alles auswählen
MariaDB [postfix]> SHOW CREATE TABLE alias \G;
*************************** 1. row ***************************
Table: alias
Create Table: CREATE TABLE `alias` (
`address` varchar(255) COLLATE latin1_general_ci NOT NULL,
`goto` text COLLATE latin1_general_ci NOT NULL,
`domain` varchar(255) COLLATE latin1_general_ci NOT NULL,
`created` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
`modified` datetime NOT NULL DEFAULT '2000-01-01 00:00:00',
`active` tinyint(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`address`),
KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Postfix Admin - Virtual Aliases'
MariaDB [postfix]> SHOW CREATE TABLE sender_acl \G;
*************************** 1. row ***************************
Table: sender_acl
Create Table: CREATE TABLE `sender_acl` (
`logged_in_as` varchar(255) NOT NULL,
`send_as` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.000 sec)
Code: Alles auswählen
MariaDB [mailcow]> SHOW CREATE TABLE alias \G;
*************************** 1. row ***************************
Table: alias
Create Table: CREATE TABLE `alias` (
`address` varchar(255) NOT NULL,
`goto` text NOT NULL,
`domain` varchar(255) NOT NULL,
`created` datetime NOT NULL DEFAULT '2016-01-01 00:00:00',
`modified` datetime NOT NULL DEFAULT '2016-01-01 00:00:00',
`active` tinyint(1) NOT NULL DEFAULT 1,
PRIMARY KEY (`address`),
KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.010 sec)
ERROR: No query specified
MariaDB [mailcow]> SHOW CREATE TABLE sender_acl \G;
*************************** 1. row ***************************
Table: sender_acl
Create Table: CREATE TABLE `sender_acl` (
`logged_in_as` varchar(255) NOT NULL,
`send_as` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.000 sec)
ERROR: No query specified
Code: Alles auswählen
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
Code: Alles auswählen
character-set-server = utf8
collation-server = utf8_unicode_ci
Dabei halte ich das nicht für die Lösung, da der alte Server ebenfalls die Einstellungen
Code: Alles auswählen
/etc/mysql/mariadb.conf.d/50-server.cnf:collation-server = utf8mb4_general_ci
/etc/mysql/mariadb.conf.d/50-server.cnf:character-set-server = utf8mb4
Wie bekomme ich die Tabelle sender_acl auf Gleichklang, damit die MySQL-Query nicht fehlschlägt?
Beste Grüße
BrotherJ