hi ich think the problem will generated in**exim-group-router.conf.inc. if i try to do the query manually with phpmyadmin i get allways syntax errors at the line "from domains d, groups g, group_contents c, users u" from the virtual_dom_groups: query but im not an mysql guru , to trace the problem down. *Holger* * * *# exim-group-router.conf.inc # # A group is a list of users # # if a group is flaged public # then anyone on the internet can write to it # else only members can write to it # # If not public non member sender will re,ceive a "550 Unknown user" message virtual_dom_groups:   driver = redirect   allow_fail   senders = ${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}' }}}}   data = ${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.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 * *# * On 14.04.23 00:29, Gordon Dickens wrote:

Hi Holger,

Sorry but I have not yet upgraded to Exim 4.95 or MySql 8. Nevertheless, I am sure that I will be upgrading within a few months. Please let us know what you finally determine is the problem and how that you fix it so that the rest of us can benefit from your experience.

Thanks!

Gordon Dickens

On 4/13/23 00:46, Holger Glaess 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(a)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

_______________________________________________ users mailing list -- users(a)exim4u.org To unsubscribe send an email to users-leave(a)exim4u.org

_______________________________________________ users mailing list --users(a)exim4u.org To unsubscribe send an email tousers-leave(a)exim4u.org

2023-04-14 12:03, Odhiambo Washington wrote:

'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'

Couldn't you just put backticks around the newly reserved word though?

hi FYI the backtick "version" works ! Holger On 15.04.23 14:56, Odhiambo Washington wrote:

On Fri, Apr 14, 2023 at 9:38 PM Holger Glaess <glaess(a)glaessixs.de> wrote:

hi

i try first with backticks

i allready prepare the solution with mygroups but question here is.

where i have to change the group name inside the php code for the admin gui ?

In my setup, I changed all these files: [15:51 exim4u ]$ find . -type f -exec grep -li 'mygroups' {} \; ./exim4u/admingroup.php ./exim4u/admingroupaddsubmit.php ./exim4u/admingroupchange.php ./exim4u/admingroupchangesubmit.php ./exim4u/admingroupcontentaddsubmit.php ./exim4u/admingroupcontentdeletesubmit.php ./exim4u/admingroupdelete.php

So you can do it easily with:

cd exim4u find . -type f -exec sed -i.BAK 's/groups/mygroups/g' {} \;

-- 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]

_______________________________________________ users mailing list --users(a)exim4u.org To unsubscribe send an email tousers-leave(a)exim4u.org