On Thu, Apr 13, 2023 at 7:47��AM Holger Glaess <glaess@glaessixs.de> wrote:

hi

i get the error bellow since the update to exim 4.95 and mysql8.

i remove just the orginal names and ips from log.

everthing was fine befor the update ( i had to move to mysql 8 due the
old mysql version was outdated. )

any hint for me to fix this ?

Holger

2023-04-13 05:17:16 failed to expand "${if eq{Y}{${lookup mysql{select
g.is_public from groups g, domains d where d.enabled = '1' and d.domain
= '${quote_mysql:$domain}' and d.domain_id = g.domain_id and g.enabled =
'1' and g.name = '${quote_mysql:$local_part}'}}} {$sender_address}
{${lookup mysql{select u.username from domains d, groups g,
group_contents c, users u where d.enabled = '1' and d.domain =
'${quote_mysql:$domain}' and d.domain_id = g.domain_id and g.name =
'${quote_mysql:$local_part}' and g.enabled = '1' and g.is_public = 'N'
and c.member_id = u.user_id and d.domain_id = u.domain_id and u.enabled
= '1' and u.username = '${quote_mysql:$sender_address}' }}}}" while
checking a list: lookup of "select g.is_public from groups g, domains d
where d.enabled = '1' and d.domain = 'oneworlddapp.de' and d.domain_id =
g.domain_id and g.enabled = '1' and g.name = 'r.maehnhoefer'" gave
DEFER: MYSQL: query failed: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near 'groups g, domains d where d.enabled = '1' and
d.domain = 'xxxxxx.de' and d' at line 1

2023-04-13 05:17:16 H=xx-xx.xx.de [xx.xx.xx.xx]
X=TLS1.3:TLS_AES_256_GCM_SHA384:256 CV=no F=<> temporarily rejected RCPT
<yyyyy@xxxxxxx.de>: MYSQL: query failed: You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'groups g, domains d where d.enabled =
'1' and d.domain = 'xxxxxxxxx.de' and d' at line 1

'groups' is a reserved word in MySQL version 8.0.2.

How I solved this long time ago was to:
1. Dump my exim4u db to file

2. Edit the file so that the table 'groups' is renamed to 'mygroups' or whatever you want as long as it's not called 'groups'

3. Import the DB again

4. Edit the��exim-group-router.conf.inc so that the table being used in the query is 'mygroups'

``` table

-- Table structure for table `mygroups`
--

DROP TABLE IF EXISTS `mygroups`;
/*!40101 SET @saved_cs_client �� = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mygroups` (
�� `id` int(11) NOT NULL AUTO_INCREMENT,
�� `domain_id` mediumint(8) unsigned NOT NULL,
�� `name` varchar(64) NOT NULL,
�� `is_public` char(1) NOT NULL DEFAULT 'Y',
�� `enabled` tinyint(1) NOT NULL DEFAULT 1,
�� PRIMARY KEY (`id`),
�� UNIQUE KEY `group_name` (`domain_id`,`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

```

virtual_dom_mygroups:
�� driver = redirect
�� allow_fail
�� senders = ${if eq{Y}{${lookup mysql{select g.is_public \
�� from mygroups g, domains d \
�� where d.enabled = '1' and d.domain = '${quote_mysql:$domain}' and \
�� d.domain_id = g.domain_id and g.enabled = '1' and \
�� g.name = '${quote_mysql:$local_part}'}}} \
�� {$sender_address} \
�� {${lookup mysql{select u.username \
�� from domains d, mygroups g, group_contents c, users u \
�� where d.enabled = '1' and d.domain = '${quote_mysql:$domain}' and \
�� d.domain_id = g.domain_id and g.name = '${quote_mysql:$local_part}' and \
�� g.enabled = '1' and \
�� g.is_public = 'N' and c.member_id = u.user_id and \
�� d.domain_id = u.domain_id and u.enabled = '1' \
�� and u.username = '${quote_mysql:$sender_address}' }}}}
�� data = ${lookup mysql{ \
�� select u.username \
�� from domains d, mygroups g, group_contents c, users u \
�� where d.enabled �� = '1' �� and \
�� d.domain �� = '${quote_mysql:$domain}' �� and \
�� d.domain_id �� = g.domain_id �� and \
�� g.enabled �� = '1' �� and \
�� g.id �� = c.group_id �� and \
�� c.member_id �� = u.user_id �� and \
�� d.domain_id �� = u.domain_id �� and \
�� u.enabled �� = '1' �� and \
�� g.name �� = '${quote_mysql:$local_part}'} }
�� local_part_suffix = -*
�� local_part_suffix_optional
�� retry_use_local_part
�� reply_transport = address_reply
�� pipe_transport = address_pipe

```

I don't know if the same applies to MariaDB, but I would strongly suggest a move to MariaDB.

HTH

Best regards,
Odhiambo WASHINGTON,
Nairobi,KE
+254 7 3200 0004/+254 7 2274 3223
"Oh, the cruft.",��egrep -v '^$|^.*#'��\_(��)_/��:-)

[How to ask smart questions:��http://www.catb.org/~esr/faqs/smart-questions.html]