[Exim4U] Re: SQL Errors since update to exim 4.95 mysql8

14 Apr 2023

      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 ?

Holger

On 14.04.23 11:03, Odhiambo Washington wrote:
...
On Thu, Apr 13, 2023 at 7:47 AM Holger Glaess <glaess(a)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 <http://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
    <http://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
    <http://oneworlddapp.de>' and d.domain_id =
    g.domain_id and g.enabled = '1' and g.name <http://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 <http://xxxxxx.de>' and d' at line 1
2023-04-13 05:17:16 H=xx-xx.xx.de <http://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 <http://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 <http://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 <http://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 <http://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 <http://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]
_______________________________________________
users mailing list --users(a)exim4u.org
To unsubscribe send an email tousers-leave(a)exim4u.org