<html><head><meta http-equiv="Content-Type" content="text/html charset=utf-8"></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;" class=""><div><blockquote type="cite" class=""><div class="">On Sep 15, 2017, at 2:12 PM, Jason Lixfeld <<a href="mailto:jason-kea@lixfeld.ca" class="">jason-kea@lixfeld.ca</a>> wrote:</div><br class="Apple-interchange-newline"><div class=""><div class="">Hello,<br class=""><br class="">I was troubleshooting an issue with host reservations via flex-id and postgresql.<br class=""><br class="">I tracked it down to a difference in how the database was storing identifier_value, vs. how the identifier_expression is configured in the kea config file.<br class=""><br class="">The kea debug shows the generated flex-id:<br class=""><br class="">2017-09-15 13:32:01.182 DEBUG [kea-dhcp4.packets/631] DHCP4_FLEX_ID flexible identifier generated for incoming packet: flex-id=010972677730312E6C616200040C370201<br class=""><br class="">The kea config identifier_expression is like so:<br class=""><br class=""> "hooks-libraries": [<br class=""> {<br class=""> "library": "/usr/local/lib/hooks/libdhcp_flex_id.so",<br class=""> "parameters":<br class=""> {<br class=""> "identifier-expression": "concat(relay4[2].hex,relay4[1].hex)"<br class=""> }<br class=""> }<br class=""> ],<br class=""><br class="">I set the identifier_value per the how-to (<a href="https://kea.isc.org/wiki/HostReservationsHowTo" class="">https://kea.isc.org/wiki/HostReservationsHowTo</a>) to match what the kea debug generated for the flex-id:<br class=""><br class="">…<br class="">…<br class="">kea=# \set identifier_value ‘010972677730312e6c616200040c370201'<br class="">…<br class="">…<br class=""><br class="">When I noticed that the reservation was not working, I looked at the postgres query that Kea was making, and it appears as though $3 is prepended with \x :<br class=""><br class="">2017-09-15 13:32:01.189 EDT [726] kea@kea LOG: execute get_host_subid4_dhcpid: SELECT h.host_id, h.dhcp_identifier, h.dhcp_identifier_type, h.dhcp4_subnet_id, h.dhcp6_subnet_id, h.ipv4_address, h.hostname, h.dhcp4_client_classes, h.dhcp6_client_classes, h.dhcp4_next_server, h.dhcp4_server_hostname, h.dhcp4_boot_file_name, o.option_id, o.code, o.value, o.formatted_value, o.space, o.persistent FROM hosts AS h LEFT JOIN dhcp4_options AS o ON h.host_id = o.host_id WHERE h.dhcp4_subnet_id = $1 AND h.dhcp_identifier_type = $2 AND h.dhcp_identifier = $3 ORDER BY h.host_id, o.option_id<br class="">2017-09-15 13:32:01.189 EDT [726] kea@kea DETAIL: parameters: $1 = '1', $2 = '4', $3 = '\x010972677730312e6c616200040c370201’<br class=""><br class="">Making that query directly of course fails, which is where the issue lies:<br class=""><br class="">SELECT h.host_id, h.dhcp_identifier, h.dhcp_identifier_type, h.dhcp4_subnet_id, h.dhcp6_subnet_id, h.ipv4_address, h.hostname, h.dhcp4_client_classes, h.dhcp6_client_classes, h.dhcp4_next_server, h.dhcp4_server_hostname, h.dhcp4_boot_file_name, o.option_id, o.code, o.value, o.formatted_value, o.space, o.persistent FROM hosts AS h LEFT JOIN dhcp4_options AS o ON h.host_id = o.host_id WHERE h.dhcp4_subnet_id = '1' AND h.dhcp_identifier_type = '4' AND h.dhcp_identifier = '\x010972677730312e6c616200040c370201' ORDER BY h.host_id, o.option_id;<br class=""><br class=""> host_id | dhcp_identifier | dhcp_identifier_type | dhcp4_subnet_id | dhcp6_subnet_id | ipv4_address | hostname | dhcp4_client_classes | dhcp6_client_classes | dhcp4_next_server | dhcp4_server_hostname | dhcp4_boot_file_name | option_id | code | value | formatted_value | space | persistent<br class="">---------+-----------------+----------------------+-----------------+-----------------+--------------+----------+----------------------+----------------------+-------------------+-----------------------+----------------------+-----------+------+-------+-----------------+-------+------------<br class="">(0 rows)<br class=""><br class="">If I remove the leading \x from the query, the expected results are presented:<br class=""><br class="">SELECT h.host_id, h.dhcp_identifier, h.dhcp_identifier_type, h.dhcp4_subnet_id, h.dhcp6_subnet_id, h.ipv4_address, h.hostname, h.dhcp4_client_classes, h.dhcp6_client_classes, h.dhcp4_next_server, h.dhcp4_server_hostname, h.dhcp4_boot_file_name, o.option_id, o.code, o.value, o.formatted_value, o.space, o.persistent FROM hosts AS h LEFT JOIN dhcp4_options AS o ON h.host_id = o.host_id WHERE h.dhcp4_subnet_id = '1' AND h.dhcp_identifier_type = '4' AND h.dhcp_identifier = '010972677730312e6c616200040c370201' ORDER BY h.host_id, o.option_id;<br class=""><br class=""> host_id | dhcp_identifier | dhcp_identifier_type | dhcp4_subnet_id | dhcp6_subnet_id | ipv4_address | hostname | dhcp4_client_classes | dhcp6_client_classes | dhcp4_next_server | dhcp4_server_hostname | dhcp4_boot_file_name | option_id | code | value | formatted_value | space | persistent<br class="">---------+------------------------------------------------------------------------+----------------------+-----------------+-----------------+--------------+----------+----------------------+----------------------+-------------------+-----------------------+----------------------+-----------+------+-------+-----------------+-------+------------<br class=""> 6 | \x30313039373236373737333033313265366336313632303030343063333730323031 | 4 | 1 | | 171966277 | | | | | | bootfile.efi | | | | | |<br class="">(1 row)<br class=""><br class="">Manually decoding the dhcp_identifier shows the non-prepended value:<br class=""><br class="">kea=# select convert_from('\x30313039373236373737333033313265366336313632303030343063333730323031', 'UTF-8');<br class=""> convert_from<br class="">------------------------------------<br class=""> 010972677730312e6c616200040c370201<br class="">(1 row)<br class=""><br class="">kea=#<br class=""><br class="">So my question is did I misinterpret the instructions (either by cluelessness or oversight) when setting this up, or is this a bug?<br class=""><br class="">Thanks!</div></div></blockquote></div><br class=""><div class="">For the archives, the issue was that the source value is a hex value, but it was being stored as ASCII, instead of binary. The fix was to modify the insert query accordingly.</div><div class=""><br class=""></div><div class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=# START TRANSACTION;</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">START TRANSACTION</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=# \set ipv4_reservation '10.63.255.69'</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=# \set identifier_type 'flex-id'</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=# \set identifier_value '010972677730312e6c616200040c370201'</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=# \set dhcp4_subnet_id 1</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=# \set boot_file_name 'bootfile.efi'</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=#</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=# INSERT INTO hosts (dhcp_identifier,</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea(# dhcp_identifier_type,</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea(# dhcp4_subnet_id,</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea(# ipv4_address,</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea(# dhcp4_boot_file_name)</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea-# VALUES (DECODE(REPLACE(:'identifier_value', ':', ''), 'hex'),</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea(# (SELECT type FROM host_identifier_type WHERE name=:'identifier_type'),</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea(# :dhcp4_subnet_id,</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea(# (SELECT (:'ipv4_reservation'::inet - '0.0.0.0'::inet)),</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea(# :'boot_file_name');</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">INSERT 0 1</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=#</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=# COMMIT;</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">COMMIT</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=# select * from hosts;</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">host_id | dhcp_identifier | dhcp_identifier_type | dhcp4_subnet_id | dhcp6_subnet_id | ipv4_address | hostname | dhcp4_client_classes | dhcp6_client_classes | dhcp4_next_server | dhcp4_server_hostname | dhcp4_boot_file_name</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">---------+--------------------------------------+----------------------+-----------------+-----------------+--------------+----------+----------------------+----------------------+-------------------+-----------------------+----------------------</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class=""> 9 | \x010972677730312e6c616200040c370201 | 4 | 1 | | 171966277 | | | | | | bootfile.efi</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">(1 row)</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=#</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">2017-09-18 10:29:45.609 DEBUG [kea-dhcp4.hosts/516] HOSTS_CFG_GET_ONE_SUBNET_ID_IDENTIFIER get one host with IPv4 reservation for subnet id 1, identified by flex-id=010972677730312E6C616200040C370201</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">2017-09-18 10:29:45.610 DEBUG [kea-dhcp4.hosts/516] HOSTS_CFG_GET_ALL_IDENTIFIER get all hosts with reservations using identifier: flex-id=010972677730312E6C616200040C370201</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">2017-09-18 10:29:45.610 DEBUG [kea-dhcp4.hosts/516] HOSTS_CFG_GET_ALL_IDENTIFIER_COUNT using identifier flex-id=010972677730312E6C616200040C370201, found 0 host(s)</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">2017-09-18 10:29:45.610 DEBUG [kea-dhcp4.hosts/516] HOSTS_CFG_GET_ONE_SUBNET_ID_IDENTIFIER_NULL host not found using subnet id 1 and identifier flex-id=010972677730312E6C616200040C370201</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">2017-09-18 10:29:45.610 DEBUG [kea-dhcp4.hosts/516] HOSTS_MGR_ALTERNATE_GET4_SUBNET_ID_IDENTIFIER get one host with IPv4 reservation for subnet id 1, identified by flex-id=010972677730312E6C616200040C370201</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">2017-09-18 10:29:45.611 DEBUG [kea-dhcp4.hosts/516] HOSTS_MGR_ALTERNATE_GET4_SUBNET_ID_IDENTIFIER_HOST using subnet id 1 and identifier flex-id=010972677730312E6C616200040C370201, found host: flex-id=010972677730312E6C616200040C370201 ipv4_subnet_id=1 hostname=(empty) ipv4_reservation=10.63.255.69 siaddr=(no) sname=(empty) file=bootfile.efi ipv6_reservations=(none)</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">2017-09-18 10:29:45.610 EDT [506] kea@kea LOG: execute get_host_subid4_dhcpid: SELECT h.host_id, h.dhcp_identifier, h.dhcp_identifier_type, h.dhcp4_subnet_id, h.dhcp6_subnet_id, h.ipv4_address, h.hostname,</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class=""> h.dhcp4_client_classes, h.dhcp6_client_classes, h.dhcp4_next_server, h.dhcp4_server_hostname, h.dhcp4_boot_file_name, o.option_id, o.code, o.value, o.formatted_value, o.space, o.persistent FROM hosts A</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">S h LEFT JOIN dhcp4_options AS o ON h.host_id = o.host_id WHERE h.dhcp4_subnet_id = $1 AND h.dhcp_identifier_type = $2 AND h.dhcp_identifier = $3 ORDER BY h.host_id, o.option_id</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">2017-09-18 10:29:45.610 EDT [506] kea@kea DETAIL: parameters: $1 = '1', $2 = '4', $3 = '\x010972677730312e6c616200040c370201’</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=# SELECT h.host_id, h.dhcp_identifier, h.dhcp_identifier_type, h.dhcp4_subnet_id, h.dhcp6_subnet_id, h.ipv4_address, h.hostname, h.dhcp4_client_classes, h.dhcp6_client_classes, h.dhcp4_next_server, h.dhcp4_server_hostname, h.dhcp4_boot_file_name, o.option_id, o.code, o.value, o.formatted_value, o.space, o.persistent FROM hosts AS h LEFT JOIN dhcp4_options AS o ON h.host_id = o.host_id WHERE h.dhcp4_subnet_id = '1' AND h.dhcp_identifier_type = '4' AND h.dhcp_identifier = '\x010972677730312e6c616200040c370201' ORDER BY h.host_id, o.option_id;</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">kea=#</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">host_id | dhcp_identifier | dhcp_identifier_type | dhcp4_subnet_id | dhcp6_subnet_id | ipv4_address | hostname | dhcp4_client_classes | dhcp6_client_classes | dhcp4_next_server | dhcp4_server_hostname | dhcp4_boot_file_name | option_id | code | value | formatted_value | space | persistent</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">---------+--------------------------------------+----------------------+-----------------+-----------------+--------------+----------+----------------------+----------------------+-------------------+-----------------------+----------------------+-----------+------+-------+-----------------+-------+------------</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class=""> 9 | \x010972677730312e6c616200040c370201 | 4 | 1 | | 171966277 | | | | | | bootfile.efi | | | | | |</span><br style="font-family: Menlo-Regular; font-size: 11px;" class=""><span style="font-family: Menlo-Regular; font-size: 11px;" class="">(1 row)</span></div></body></html>