Skip to content

GitLab

Kea
Kea
Last edited by Tomek Mrugalski
Page history

editing host reservations

Tips about Host Reservations in Kea

Introduction

Host Reservations is one of the most desired features in Kea and we continuously receive questions pertaining to Host Reservations over mailing lists. This document is intended to explain how to manage reservations in the SQL database and what SQL queries the server is using to retrieve reservations. NB: the Kea host_cmds hook library provides the recommended REST API interface for managing host reservations in Kea (such as getting a single reservation, all reservations page by page or all reservations from a subnet, adding and deleting reservations). It provides additional sanity checks, including DHCP configuration logic, that simply cannot be verified by the SQL schema alone.

This document describes a 'back-door' editing process that may be useful, but also risky. This information is no longer maintained. Use with caution!

Host Reservations in MySQL and PostgreSQL Databases

Host Reservations Data Structure in MySQL and PostgreSQL Databases

The host reservation information is held in multiple tables within a database:

  • hosts - includes client identification information, subnet identifiers for both DHCPv4 and DHCPv6 client, hostname reserved for a client, IPv4 address reservation, client class names and fixed fields carried within DHCPv4 messages (siaddr, sname and file).
  • ipv6_reservations - holds IPv6 address and prefix reservations for a client. It provides one-to-many relation with hosts table
  • dhcp4_options - holds DHCPv4 options reserved for a client (one-to-many with hosts table)
  • dhcp6_options - holds DHCPv6 options reserved for a client (one-to-many with hosts table)

In addition, the schema contains two tables mapping the numeric identifiers to the user friendly text representations:

  • host_identifier_type - contains the mapping of host identifiers to the host identifiers names,
  • dhcp_option_scope - contains the mapping of DHCP option scopes (e.g. global, subnet etc), used in dhcp4_options and dhcp6_options tables, into the scope names.

For any host reservation there must be an entry added into the '''hosts''' table. Adding entries into other tables is optional, e.g. if only IPv4 reservation is required for a client there is no entry added into the ''ipv6_reservations'' table. Similarly, if there are no client specific options assigned, there are no entries added into the ''dhcp4_options'' or ''dhcp6_options'' tables.

IPv4 only Reservation

In order to add an IPv4 address and hostname reservation for a client, identified by its MAC address the following MySQL INSERT statement can be used:

START TRANSACTION;
SET @ipv4_reservation='192.0.2.4';
SET @hostname = 'myhost.example.org';
SET @identifier_type='hw-address';
SET @identifier_value='10:20:30:40:50:60';
SET @dhcp4_subnet_id=1;
SET @next_server='10.0.0.1';
SET @server_hostname='server-name.example.org';
SET @boot_file_name='bootfile.efi';

INSERT INTO hosts (dhcp_identifier,
                   dhcp_identifier_type,
		   dhcp4_subnet_id,
                   ipv4_address,
                   hostname,
                   dhcp4_next_server,
                   dhcp4_server_hostname,
                   dhcp4_boot_file_name)
VALUES (UNHEX(REPLACE(@identifier_value, ':', '')),
	(SELECT type FROM host_identifier_type WHERE name=@identifier_type),
        @dhcp4_subnet_id,
        INET_ATON(@ipv4_reservation),
        @hostname,
        INET_ATON(@next_server),
        @server_hostname,
        @boot_file_name);

COMMIT;

And this is the corresponding PostgreSQL statement:

START TRANSACTION;
\set ipv4_reservation '192.0.2.4'
\set hostname   'myhost.example.org'
\set identifier_type 'hw-address'
\set identifier_value '10:20:30:40:50:60'
\set dhcp4_subnet_id 1
\set next_server '10.0.0.1'
\set server_hostname 'server-name.example.org'
\set boot_file_name 'bootfile.efi'

INSERT INTO hosts (dhcp_identifier,
                   dhcp_identifier_type,
                   dhcp4_subnet_id,
                   ipv4_address,
                   hostname,
                   dhcp4_next_server,
                   dhcp4_server_hostname,
                   dhcp4_boot_file_name)
VALUES (DECODE(REPLACE(:'identifier_value', ':', ''), 'hex'),
	(SELECT type FROM host_identifier_type WHERE name=:'identifier_type'),
        :dhcp4_subnet_id,
        (SELECT (:'ipv4_reservation'::inet - '0.0.0.0'::inet)),
        :'hostname',
        (SELECT (:'next_server'::inet - '0.0.0.0'::inet)),
        :'server_hostname',
        :'boot_file_name');

COMMIT;

The reservation will be made for an address of ''192.0.2.4'' and hostname of ''myhost.example.org''. In addition, host specific values of "sadder", "sname" and "file" will be assigned. The identifier type ''hw-address'' indicates that the client is identified by its MAC address. The ''identifier_value'' specifies the MAC address of the client. The ''dhcp4_subnet_id'' must match the identifier of the subnet to which the client is connected. The subnet identifier can be explicitly specified as a parameter of a subnet declaration within the Kea configuration file. This identifier should be used here.

Different identifier types can also be used to create reservations. All supported identifier names can be retrieved from the ''dhcp_identifier_type'' table. Note however, that some of them may be specific to DHCPv4 or DHCPv6 only. For example: ''circuit-id'' and ''client-id'' are specific to DHCPv4 and must not be used for DHCPv6 reservations.

IMPORTANT NOTE: If Flexible Identifier Library is loaded to gain ability to create your own reservation identifiers and ''flex-id'' can be used as ''identifier_type''. In this case statement will be slightly different. In MySQL VALUES (UNHEX(REPLACE(@identifier_value, ':', '')) will be replaced by VALUES (@identifier_value full example:

START TRANSACTION;
SET @ipv4_reservation='192.0.2.4';
SET @hostname = 'myhost.example.org';
SET @identifier_type='flex-id';
SET @identifier_value='value-of-your-decision';
SET @dhcp4_subnet_id=1;
SET @next_server='10.0.0.1';
SET @server_hostname='server-name.example.org';
SET @boot_file_name='bootfile.efi';

INSERT INTO hosts (dhcp_identifier,
                   dhcp_identifier_type,
		   dhcp4_subnet_id,
                   ipv4_address,
                   hostname,
                   dhcp4_next_server,
                   dhcp4_server_hostname,
                   dhcp4_boot_file_name)
VALUES (@identifier_value,
	(SELECT type FROM host_identifier_type WHERE name=@identifier_type),
        @dhcp4_subnet_id,
        INET_ATON(@ipv4_reservation),
        @hostname,
        INET_ATON(@next_server),
        @server_hostname,
        @boot_file_name);

COMMIT;

And this is the change in the corresponding PostgreSQL statement: VALUES (DECODE(REPLACE(:'identifier_value', ':', ''), 'hex'), to VALUES (:'identifier_value',

START TRANSACTION;
\set ipv4_reservation '192.0.2.4'
\set hostname   'myhost.example.org'
\set identifier_type 'flex-id'
\set identifier_value 'value-of-your-decision'
\set dhcp4_subnet_id 1
\set next_server '10.0.0.1'
\set server_hostname 'server-name.example.org'
\set boot_file_name 'bootfile.efi'

INSERT INTO hosts (dhcp_identifier,
                   dhcp_identifier_type,
                   dhcp4_subnet_id,
                   ipv4_address,
                   hostname,
                   dhcp4_next_server,
                   dhcp4_server_hostname,
                   dhcp4_boot_file_name)
VALUES (:'identifier_value',
	(SELECT type FROM host_identifier_type WHERE name=:'identifier_type'),
        :dhcp4_subnet_id,
        (SELECT (:'ipv4_reservation'::inet - '0.0.0.0'::inet)),
        :'hostname',
        (SELECT (:'next_server'::inet - '0.0.0.0'::inet)),
        :'server_hostname',
        :'boot_file_name');

COMMIT;

In order to test that the reservation has been successfully added the following MySQL SELECT query can be used:

SELECT 
    HEX(h.dhcp_identifier) AS dhcp_identifier,
    i.name AS dhcp_identifier_name,
    h.dhcp4_subnet_id AS dhcp4_subnet_id,
    INET_NTOA(h.ipv4_address) AS ipv4_address,
    h.hostname AS hostname
FROM
    hosts AS h
        INNER JOIN
    host_identifier_type AS i ON h.dhcp_identifier_type = i.type;

Similar SELECT query in PostgreSQL should return the same result:

SELECT 
    ENCODE(h.dhcp_identifier, 'hex') AS dhcp_identifier,
    i.name AS dhcp_identifier_name,
    h.dhcp4_subnet_id AS dhcp4_subnet_id,
    ('0.0.0.0'::inet + h.ipv4_address) AS ipv4_address,
    h.hostname AS hostname
FROM
    hosts AS h
        INNER JOIN
    host_identifier_type AS i ON h.dhcp_identifier_type = i.type;

IPv6 only Reservations

A DHCPv6 client can have multiple IPv6 reservations assigned. In this example we'll demonstrate how to make two reservations for a particular client.

The statement will insert a DHCPv6 host and two reservations into the database:

START TRANSACTION;
SET @ipv6_address_reservation='2001:db8:1::100';
SET @ipv6_prefix_reservation='3000:1::';
SET @ipv6_prefix_len_reservation=64;
SET @hostname = 'myhost.example.org';
SET @identifier_type='duid';
-- DUID-EN with ISC enterprise id (2495) --
SET @identifier_value='00:02:00:00:09:BF:10:20:03:04:05:06:07:08';
SET @dhcp6_subnet_id=1;
INSERT INTO hosts (dhcp_identifier,
                   dhcp_identifier_type,
		   dhcp6_subnet_id,
                   hostname)
VALUES (UNHEX(REPLACE(@identifier_value, ':', '')),
		(SELECT type FROM host_identifier_type WHERE name=@identifier_type),
        @dhcp6_subnet_id,
        @hostname);

-- Obtain host_id generated for a newly added host entry. It will be used to associate --
-- IPv6 reservations within the ipv6_reservations table with this host --
SET @inserted_host_id = (SELECT LAST_INSERT_ID());

-- Insert address reservation. Note that 0 indicates address reservation type. --
INSERT INTO ipv6_reservations(address, type, host_id)
VALUES (@ipv6_address_reservation, 0, @inserted_host_id);

-- Insert prefix reservation. The value of 2 indicates prefix reservation type. --
INSERT INTO ipv6_reservations(address, prefix_len, type, host_id)
VALUES (@ipv6_prefix_reservation, @ipv6_prefix_len_reservation, 2, @inserted_host_id);

COMMIT;

and the corresponding PostgreSQL statement is:

START TRANSACTION;
\set ipv6_address_reservation '2001:db8:1::100'
\set ipv6_prefix_reservation '3000:1::'
\set ipv6_prefix_len_reservation 64
\set hostname   'myhost.example.org'
\set identifier_type 'duid'

-- DUID-EN with ISC enterprise id (2495) --
\set identifier_value '00:02:00:00:09:BF:10:20:03:04:05:06:07:08'
\set dhcp6_subnet_id 1
INSERT INTO hosts (dhcp_identifier,
                   dhcp_identifier_type,
                   dhcp6_subnet_id,
                   hostname)
VALUES (DECODE(REPLACE(:'identifier_value', ':', ''), 'hex'),
               (SELECT type FROM host_identifier_type WHERE name=:'identifier_type'),
               :dhcp6_subnet_id,
               :'hostname');

-- Obtain host_id generated for a newly added host entry. It will be used to associate --
-- IPv6 reservations within the ipv6_reservations table with this host. --
-- The value is stored in a new table lastval, which will be deleted at the end of this --
-- transaction. --
SELECT LASTVAL() INTO lastval;

-- Insert address reservation. Note that 0 indicates address reservation type. --
INSERT INTO ipv6_reservations(address, type, host_id)
VALUES (:'ipv6_address_reservation', 0, (SELECT lastval FROM lastval));

-- Insert prefix reservation. The value of 2 indicates prefix reservation type. --
INSERT INTO ipv6_reservations(address, prefix_len, type, host_id)
VALUES (:'ipv6_prefix_reservation', :ipv6_prefix_len_reservation, 2, (SELECT lastval FROM lastval));

DROP TABLE lastval;

COMMIT;

Note that it is possible to insert more than one address and prefix reservation for the client.

'''Also note that the type value of 2 (rather than 1) indicates that the reservation is for a prefix.'''

It is also important to use the correct format of the DUID. The expected format comprises the whole DUID carried in the Client Identifier option. In our example it comprises DUID type = DUID-EN (2 bytes) and the enterprise id (4 bytes), followed by a variable length value.

In order to test that reservations have been added correctly for this client, the following query can be used for MySQL:

SELECT 
    r.address, r.prefix_len, r.type
FROM
    ipv6_reservations AS r
        INNER JOIN
    hosts AS h ON r.host_id = h.host_id
WHERE
    h.dhcp_identifier = UNHEX(REPLACE(@identifier_value, ':', ''));

And, the following query can be used for PostgreSQL:

SELECT 
    r.address, r.prefix_len, r.type
FROM
    ipv6_reservations AS r
        INNER JOIN
    hosts AS h ON r.host_id = h.host_id
WHERE
    h.dhcp_identifier = DECODE(REPLACE(:'identifier_value', ':', ''), 'hex');

DHCPv4 options

The next example demonstrates how to insert a host and specify multiple DHCPv4 options which will be returned to the client.

The option values are typically held in the binary format in the database. The binary format is the same format in which the option is sent over the wire, but the value excludes option code (which is held in a separate field) and the option length (which can be determined by checking the length of the data returned by the SELECT query for the value column). In order to insert option value into the database this value must be converted into binary format. The example above demonstrates how to convert a text option value and the list of IPv4 addresses into the binary format.

Another possibility is to specify formatted option value as a comma separated list of values. This is the same convention as used in the Kea configuration file when specifying ''option-data'' with the ''csv-format'' set to ''true''. If the option definition exists for a given option, the server will convert the ''formatted_value'' into the binary format on its own.

The following example shows how to insert some options into the MySQL database.

START TRANSACTION;
SET @ipv4_reservation='192.0.2.4';
SET @hostname = 'myhost.example.org';
SET @identifier_type='hw-address';
SET @identifier_value='10:20:30:40:50:60';
SET @dhcp4_subnet_id=1;

INSERT INTO hosts (dhcp_identifier,
                   dhcp_identifier_type,
				   dhcp4_subnet_id,
                   ipv4_address,
                   hostname)
VALUES (UNHEX(REPLACE(@identifier_value, ':', '')),
		(SELECT type FROM host_identifier_type WHERE name=@identifier_type),
        @dhcp4_subnet_id,
        INET_ATON(@ipv4_reservation),
        @hostname);

-- Store generated host identifier so as we can associate --
-- inserted options with this host --
SET @inserted_host_id = (SELECT LAST_INSERT_ID());

-- Set Bootfile option values --
SET @boot_file_option_code = 67;
-- Bootfile option holds a value in textual format. --
-- We need to convert it to binary format because options --
-- are held in the binary format in the database. --
SET @boot_file_option_value = BINARY '/tmp/boot-file';
SET @scope_name = 'subnet';

INSERT INTO dhcp4_options (code, value, space, host_id, scope_id)
VALUES (@boot_file_option_code,
        @boot_file_option_value,
        'dhcp4',
		@inserted_host_id,
        (SELECT scope_id FROM dhcp_option_scope WHERE scope_name = @scope_name));

-- Specify DNS servers option value --
SET @dns_servers_option_code = 5;
-- This option comprises one or multiple IPv4 addresses. --
-- We insert option containing two IPv4 addresses: 192.0.2.1 and --
-- 192.0.2.2 by concatenating hexadecimal representations of these --
-- addresses and then converting the result into binary format. --
SET @dns_servers_option_value = UNHEX(CONCAT(LPAD(HEX(INET_ATON('192.0.2.1')), 8, 0), LPAD(HEX(INET_ATON('192.0.2.2')), 8, 0)));

INSERT INTO dhcp4_options (code, value, space, host_id, scope_id)
VALUES (@dns_servers_option_code,
        @dns_servers_option_value,
        'dhcp4',
		@inserted_host_id,
        (SELECT scope_id FROM dhcp_option_scope WHERE scope_name = @scope_name));

-- Specify Router option value --
SET @router_option_code = 3;
-- This time let's use formatted option value instead of the --
-- binary value. This is only possibly for the options for which --
-- option definitions exist. Option definitions are predefined for --
-- most of the standard options, but also it is possible to define --
-- option definitions for other options in the Kea configuration file.--
SET @router_option_value = '192.0.2.155,192.0.2.156';

INSERT INTO dhcp4_options (code, formatted_value, space, host_id, scope_id)
VALUES (@router_option_code,
        @router_option_value,
        'dhcp4',
		@inserted_host_id,
        (SELECT scope_id FROM dhcp_option_scope WHERE scope_name = @scope_name));


COMMIT;

The following example demonstrates how to insert the same reservations into the PostgreSQL database.

START TRANSACTION;
\set ipv4_reservation '192.0.2.4'
\set hostname   'myhost.example.org'
\set identifier_type 'hw-address'
\set identifier_value '10:20:30:40:50:60'
\set dhcp4_subnet_id 1

INSERT INTO hosts (dhcp_identifier,
                   dhcp_identifier_type,
				   dhcp4_subnet_id,
                   ipv4_address,
                   hostname)
VALUES (DECODE(REPLACE(:'identifier_value', ':', ''), 'hex'),
		(SELECT type FROM host_identifier_type WHERE name=:'identifier_type'),
        :dhcp4_subnet_id,
        (SELECT (:'ipv4_reservation'::inet - '0.0.0.0'::inet)),
        :'hostname');

-- Store generated host identifier so as we can associate --
-- inserted options with this host. --
-- The value is stored in a new table lastval, which will be deleted at the end of this --
-- transaction. --
SELECT LASTVAL() INTO lastval;

-- Set Bootfile option values --
\set boot_file_option_code  67
-- Bootfile option holds a value in textual format. --
-- We need to convert it to binary format because options --
-- are held in the binary format in the database. --
\set boot_file_option_value '/tmp/boot-file'
\set scope_name 'subnet'

INSERT INTO dhcp4_options (code, value, space, host_id, scope_id)
VALUES (:boot_file_option_code,
        DECODE(:'boot_file_option_value', 'escape'),
        'dhcp4',
		(SELECT lastval FROM lastval),
        (SELECT scope_id FROM dhcp_option_scope WHERE scope_name = :'scope_name'));

-- Specify DNS servers option value --
\set dns_servers_option_code 5
-- This option comprises one or multiple IPv4 addresses. --
-- We insert option containing two IPv4 addresses: 192.0.2.1 and --
-- 192.0.2.2 by concatenating hexadecimal representations of these --
-- addresses and then converting the result into binary format. --
\set dns_servers_option_value1 '192.0.2.1'
\set dns_servers_option_value2 '192.0.2.2'

INSERT INTO dhcp4_options (code, value, space, host_id, scope_id)
VALUES (:dns_servers_option_code,
        (SELECT DECODE(to_hex((:'dns_servers_option_value1'::inet - '0.0.0.0'::inet)::bigint) || to_hex((:'dns_servers_option_value2'::inet - '0.0.0.0'::inet)::bigint), 'hex')),
        'dhcp4',
		(SELECT lastval FROM lastval),
        (SELECT scope_id FROM dhcp_option_scope WHERE scope_name = :'scope_name'));

-- Specify Router option value --
\set router_option_code 3
-- This time let's use formatted option value instead of the --
-- binary value. This is only possibly for the options for which --
-- option definitions exist. Option definitions are predefined for --
-- most of the standard options, but also it is possible to define --
-- option definitions for other options in the Kea configuration file.--
\set router_option_value '192.0.2.155,192.0.2.156'

INSERT INTO dhcp4_options (code, formatted_value, space, host_id, scope_id)
VALUES (:router_option_code,
        :'router_option_value',
        'dhcp4',
		(SELECT lastval FROM lastval),
        (SELECT scope_id FROM dhcp_option_scope WHERE scope_name = :'scope_name'));

DROP TABLE lastval;

COMMIT;

DHCPv6 Options

The DHCPv6 options are inserted into the ''dhcp6_options'' table in the same way as DHCPv4 options are inserted into the ''dhcp4_options'' table.

Queries Used by the Kea Server

The Kea server uses the following queries to retrieve the information about host reservations, IPv6 reservations and DHCP options associated with the hosts.

Host Information, IPv6 reservations, DHCPv4 & DHCPv6 options

This query retrieves all information about the host and associated IPv6 reservations and options.

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, 
    o4.option_id, o4.code, o4.value, o4.formatted_value, o4.space, 
    o4.persistent, 
    o6.option_id, o6.code, o6.value, o6.formatted_value, o6.space, 
    o6.persistent, 
    r.reservation_id, r.address, r.prefix_len, r.type, 
    r.dhcp6_iaid 
FROM hosts AS h 
LEFT JOIN dhcp4_options AS o4 
    ON h.host_id = o4.host_id 
LEFT JOIN dhcp6_options AS o6 
    ON h.host_id = o6.host_id 
LEFT JOIN ipv6_reservations AS r 
    ON h.host_id = r.host_id 
WHERE dhcp_identifier = ? AND dhcp_identifier_type = ? 
ORDER BY h.host_id, o4.option_id, o6.option_id, r.reservation_id},

Retrieve Host Information with DHCPv4 options by reserved IPv4 address

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 ipv4_address = ? 
ORDER BY h.host_id, o.option_id

Retrieve host information with DHCPv4 options by subnet and host identifier

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 = ? AND h.dhcp_identifier_type = ? 
   AND h.dhcp_identifier = ? 
ORDER BY h.host_id, o.option_id

Retrieve host information with DHCPv6 reservations and DHCPv6 options

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, 
    r.reservation_id, r.address, r.prefix_len, r.type, 
    r.dhcp6_iaid 
FROM hosts AS h 
LEFT JOIN dhcp6_options AS o 
    ON h.host_id = o.host_id 
LEFT JOIN ipv6_reservations AS r 
    ON h.host_id = r.host_id 
WHERE h.dhcp6_subnet_id = ? AND h.dhcp_identifier_type = ? 
    AND h.dhcp_identifier = ? 
ORDER BY h.host_id, o.option_id, r.reservation_id},

Retrieve host information with DHCPv4 options by reserved IPv4 address

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 = ? AND h.ipv4_address = ? 
ORDER BY h.host_id, o.option_id

Retrieve host information with IPv6 reservations and DHCPv6 options by reserved IPv6 address/prefix

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, 
    r.reservation_id, r.address, r.prefix_len, r.type, 
    r.dhcp6_iaid 
FROM hosts AS h 
LEFT JOIN dhcp6_options AS o 
    ON h.host_id = o.host_id 
LEFT JOIN ipv6_reservations AS r 
    ON h.host_id = r.host_id 
WHERE h.host_id = 
    (SELECT host_id FROM ipv6_reservations 
     WHERE address = ? AND prefix_len = ?) 
ORDER BY h.host_id, o.option_id, r.reservation_id