SQL DLZ query are implemented diffently between BIND 9.9.7 and BIND 9.11.5 and above.
Summary
SQL DLZ query are implemented diffently between BIND 9.9.7 and BIND 9.11.5 and above : When we submit a IN A query for domain.com (@) BIND 9.9.7 DLZ do 2 queries, one for "@" and another one for "*" BIND 9.11.5 and above do only one query for "@"
BIND version used
BIND 9.9.7 and BIND 9.11.5
Steps to reproduce
Compilation options : ./configure --with-dlz-mysql
SQL Data base store zone like this
+----------------------+----------------+------+
| category | zone | host |
+----------------------+----------------+------+
| mycategory | domain.com | * |
+----------------------+----------------+------+Queries :
dig -t a domain.com @127.0.0.1
dig -t a www.domain.com @127.0.0.1What is the current bug behavior?
SQL Trace given by above queries with BIND 9.11.5 and BIND 9.12.3:
181204 9:16:12 113 Query SELECT zone FROM blacklist WHERE zone = 'domain.com' AND (category = 'None' OR category = 'mycategory')
113 Query SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '@'
113 Query SELECT 86400 as ttl, 'SOA' as type, 'localhost.' as data, 'root.localhost.' as resp_person, 1 as serial, 3600 as refresh, 200 as retry, 3600000 as expire, 3600 as minimum FROM `blacklist` WHERE zone = 'domain.com' UNION SELECT NULL as ttl, 'NS' as type, 'localhost.' as data, NULL as resp_person, NULL as serial, NULL as refresh, NULL as retry, NULL as expire, NULL as minimum FROM `blacklist` WHERE zone = 'domain.com'
113 Query SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '@'
113 Query SELECT 86400 as ttl, 'SOA' as type, 'localhost.' as data, 'root.localhost.' as resp_person, 1 as serial, 3600 as refresh, 200 as retry, 3600000 as expire, 3600 as minimum FROM `blacklist` WHERE zone = 'domain.com' UNION SELECT NULL as ttl, 'NS' as type, 'localhost.' as data, NULL as resp_person, NULL as serial, NULL as refresh, NULL as retry, NULL as expire, NULL as minimum FROM `blacklist` WHERE zone = 'domain.com'
181204 9:16:24 113 Query SELECT zone FROM blacklist WHERE zone = 'www.domain.com' AND (category = 'None' OR category = 'mycategory')
113 Query SELECT zone FROM blacklist WHERE zone = 'domain.com' AND (category = 'None' OR category = 'mycategory')
113 Query SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '@'
113 Query SELECT 86400 as ttl, 'SOA' as type, 'localhost.' as data, 'root.localhost.' as resp_person, 1 as serial, 3600 as refresh, 200 as retry, 3600000 as expire, 3600 as minimum FROM `blacklist` WHERE zone = 'domain.com' UNION SELECT NULL as ttl, 'NS' as type, 'localhost.' as data, NULL as resp_person, NULL as serial, NULL as refresh, NULL as retry, NULL as expire, NULL as minimum FROM `blacklist` WHERE zone = 'domain.com'
113 Query SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = 'www'
113 Query SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '*'What is the expected correct behavior?
SQL Trace given by above queries with BIND 9.9.7 :
181204 9:25:21 39 Connect user@localhost on my_db
39 Query SET NAMES latin1
39 Query SELECT zone FROM blacklist WHERE zone = 'domain.com' AND (category = 'None' OR category = 'mycategory')
39 Query SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '@'
39 Query SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '*' <<<-- !!! This query is missing in new BIND version !!!
39 Query SELECT 86400 as ttl, 'SOA' as type, 'localhost.' as data, 'root.localhost.' as resp_person, 1 as serial, 3600 as refresh, 200 as retry, 3600000 as expire, 3600 as minimum FROM `blacklist` WHERE zone = 'domain.com' UNION SELECT NULL as ttl, 'NS' as type, 'localhost.' as data, NULL as resp_person, NULL as serial, NULL as refresh, NULL as retry, NULL as expire, NULL as minimum FROM `blacklist` WHERE zone = 'domain.com'
181204 9:25:27 39 Query SELECT zone FROM blacklist WHERE zone = 'www.domain.com' AND (category = 'None' OR category = 'mycategory')
39 Query SELECT zone FROM blacklist WHERE zone = 'domain.com' AND (category = 'None' OR category = 'mycategory')
39 Query SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '@'
39 Query SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '*'
39 Query SELECT 86400 as ttl, 'SOA' as type, 'localhost.' as data, 'root.localhost.' as resp_person, 1 as serial, 3600 as refresh, 200 as retry, 3600000 as expire, 3600 as minimum FROM `blacklist` WHERE zone = 'domain.com' UNION SELECT NULL as ttl, 'NS' as type, 'localhost.' as data, NULL as resp_person, NULL as serial, NULL as refresh, NULL as retry, NULL as expire, NULL as minimum FROM `blacklist` WHERE zone = 'domain.com'
39 Query SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = 'www'
39 Query SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = 'domain.com' AND host = '*'Relevant configuration files
DLZ Config in a view :
view my_view {
include "/opt/myapp/bind/etc/named.conf.default-zones";
match-clients { my_acl; };
allow-recursion { any; };
zone "rootzone"{type master;file "domain.com.zone";};
dlz "blacklist zones" {
database "mysql
{host=localhost dbname=my_db user=user pass=*******}
{SELECT zone FROM blacklist WHERE zone = '$zone$' AND (category = 'None' OR category = 'mycategory')}
{SELECT NULL as ttl, 'CNAME' as type, NULL as mx_priority, 'domain.com.' as data FROM blacklist WHERE zone = '$zone$' AND host = '$record$'}
{SELECT 86400 as ttl, 'SOA' as type, 'localhost.' as data, 'root.localhost.' as resp_person, 1 as serial, 3600 as refresh, 200 as retry, 3600000 as expire, 3600 as minimum FROM `blacklist` WHERE zone = '$zone$' UNION SELECT NULL as ttl, 'NS' as type, 'localhost.' as data, NULL as resp_person, NULL as serial, NULL as refresh, NULL as retry, NULL as expire, NULL as minimum FROM `blacklist` WHERE zone = '$zone$'}
";
};
};Relevant logs and/or screenshots
See SQL trace above.