[Kea-users] How to insert IPv4 address with host reservation (mysql)

[Jason Guy]

The best link used to be here:
https://kea.isc.org/wiki/HostReservationsHowTo
Alas that is not resolving anymore. Here is essentially the SQL command
from that link (was when 1.3 was new...but should be basically the same in
newer versions).
I may have added the 'ON DUPLICATE KEY UPDATE' bit, since I use ansible to
provision this, but I cannot recall.

START TRANSACTION;
SET @ipv4_address='10.50.25.254';
SET @ipv4_reservation=INET_ATON(@ipv4_address);
SET @ipv6_address_reservation='fc00:10:50:25::254';
SET @dhcp4_subnet_id=25;
SET @dhcp6_subnet_id=25;
SET @hostname = 'utility';
SET @identifier_type='hw-address';
SET @identifier_mac='00:05:1b:d0:4f:84';
SET @identifier_value=UNHEX(REPLACE(@identifier_mac, ':', ''));

INSERT INTO hosts (dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id,
dhcp6_subnet_id, ipv4_address, hostname)
VALUES (
    @identifier_value,
    (SELECT type FROM host_identifier_type WHERE name=@identifier_type),
    @dhcp4_subnet_id,
    @dhcp6_subnet_id,
    @ipv4_reservation,
    @hostname
)
ON DUPLICATE KEY UPDATE
    dhcp_identifier=@identifier_value,
    dhcp4_subnet_id=@dhcp4_subnet_id,
    dhcp6_subnet_id=@dhcp6_subnet_id,
    ipv4_address=@ipv4_reservation,
    hostname=@hostname
;
SET @inserted_host_id = (SELECT host_id FROM hosts WHERE
dhcp_identifier=@identifier_value);
INSERT INTO ipv6_reservations (address, type, host_id)
VALUES (@ipv6_address_reservation, 0, @inserted_host_id)
ON DUPLICATE KEY UPDATE
    address=@ipv6_address_reservation,
    type=0,
    host_id=@inserted_host_id
;
COMMIT;


Cheers,
Jason


On Thu, Feb 7, 2019 at 2:05 PM MRob <mrobti at insiberia.net> wrote:

> Hi, I learned how to insert into mysql host reservations for the
> dhcp_identifier column like this:
>
> INSERT INTO hosts (dhcp_identifier, hostname) VALUES
> (UNHEX(REPLACE('AA:BB:11:22:CC:33', ':', '')), 'myhost')
>
> Can anyone help me understand how to insert IPv4 address into the
> "ipv4_address" column, the data type is integer.
>
> Thank you.
> _______________________________________________
> Kea-users mailing list
> Kea-users at lists.isc.org
> https://lists.isc.org/mailman/listinfo/kea-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.isc.org/pipermail/kea-users/attachments/20190207/c9905c5a/attachment.htm>