[exim] Devuan Chimaera + Exim + MariaDB 10.5.12 weirdness (a…

All,

Upgraded my public email server from Devuan 3.1 Beowulf to Devuan 4.0
Chimaera this afternoon and Exim stopped working ... fair enough, it's a
custom build for the platform so did:

    cd /root/exim-4.94
    make clean
    make makefile
    make
    make install

then:

    service exim restart

but my system kept returning 451 Temporary local problem - like this:

    mike@public:~$ telnet localhost 25
    Trying 127.0.0.1...
    Connected to localhost.
    Escape character is '^]'.
    451 Temporary local problem - please try later
    Connection closed by foreign host.


So, ran it with some debug:

    exim -bd -d+all -q15m 2>&1 | tee /tmp/a

in order to see and capture what was going on.


Now, my system has a lot of MySQL integration for whitelists/blacklists,
virtual domains, virtual users, DKIM, aliases, vacation messages and more.

I found it bailing out in the ACL for accept connection which uses a
blacklist_hosts table and the Exim MySQL query was bailing out with:

    MYSQL: query failed: Unknown MySQL error

It was aparrent that the problem was related to look-ups so I added
debug to src/lookups/mysql.c to print additional info and still I
couldn't see what was wrong - I could trigger the error by telnetting to
localhost:25 from another window and got this (mysql related) stuff in
the logs:

    root@public:/etc/exim# grep -i mysql /tmp/c
    Lookups (built-in): lsearch wildlsearch nwildlsearch iplsearch dbm
dbmjz dbmnz dnsdb mysql passwd
    Library version: MySQL: Compile: 100512 10.5.12 [mariadb-10.5]
    Lookups (built-in): lsearch wildlsearch nwildlsearch iplsearch dbm
dbmjz dbmnz dnsdb mysql passwd
    Library version: MySQL: Compile: 100512 10.5.12 [mariadb-10.5]
    23577  search_open: mysql "NULL"
    23577    type=mysql key="SELECT address FROM blacklist_hosts WHERE
active='1'" opts=NULL
    23577  MySQL query: "SELECT address FROM blacklist_hosts WHERE
active='1'" opts 'NULL'
    23577  MYSQL new connection: host=localhost port=3306 socket=NULL
database=mail user=mail
    23577  Attempt MYSQL query: handle: 0x55cb0a75e330 query: SELECT
address FROM blacklist_hosts WHERE active='1'
    23577  MYSQL query result: handle: 0x55cb0a75e330 rc: -1
    23577  MYSQL: query failed: Unknown MySQL error
    23577  lookup deferred: MYSQL: query failed: Unknown MySQL error
    23577   failed to expand "<; ${lookup mysql{SELECT address FROM
blacklist_hosts WHERE active='1'}{${sg{$value}{\\n}{ ; }} }}" while
checking a list: lookup of "SELECT address FROM blacklist_hosts WHERE
active='1'" gave DEFER: MYSQL: query failed: Unknown MySQL error
    23577   H=localhost [127.0.0.1] temporarily rejected connection in
"connect" ACL: MYSQL: query failed: Unknown MySQL error
    23577 close MYSQL connection: localhost:3306/mail/mail
    root@public:/etc/exim#

added even more debug and found that the query was returning -1 and
failing with "Unknown MySQL error".


Running queries from MySQL (MariaDB) CLI worked fine:

    MariaDB [mail]> SELECT address FROM blacklist_hosts WHERE active='1';
    +-------------------+
    | address           |
    +-------------------+
    | 119.128.0.0/12    |
    | 185.24.233.166/24 |
    | 77.40.0.0/16      |
    | 222.184.0.0/13    |
    +-------------------+
    4 rows in set (0.000 sec)


So a change of direction... okay, does this new version of MariaDB
(10.5.12) work properly from the C library/API that Exim uses? Wrote a C
test program to connect to the database using the same credentials as
Exim and perform a set of queries.  After a few tests I found that:

    SELECT * FROM blacklist_hosts                    // worked
    SELECT * FROM blacklist_hosts WHERE active='1'            // worked
    SELECT address FROM blacklist_hosts WHERE active='1' // failed with
"Unknown MySQL error"

ah, so now my C program behaved the same as Exim!


So off to the Maria-DB knowledge base in case 'address' is now a
reserved word, but alas, it is not documented as such, but as a hunch
that it might be - I tried this:

    SELECT address AS a FROM blacklist_hosts WHERE active='1'

and it worked in my test harness, so updated my exim.conf:

    #
    # blacklist_hosts -> hosts we refuse connections from (IPv4 or IPv6
address blocks)
    #
    # We use this to block certain Chinese, Russian, North Korean etc.
hosts/networks  from reaching us directly.
    # Inbound mail should be coming via our MX chain anyway!
    #
    hostlist blacklist_hosts = <; ${lookup mysql{SELECT address AS a
FROM blacklist_hosts WHERE active='1'}{${sg{$value}{\\n}{ ; }} }}


Restarted Exim, now accepts connections and works as expected.

I consider this a peculiar bug/issue/problem because config and code
that used to work fine stopped working... my hunch is that "address" has
become a reserved word - if not on the CLI then on the C API and that
this broke something that used to work.

Can anyone suggest a reason for this, other than the documented use of
'address' as some sort of reserved word?



Mike