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

[MRob]

Ok, INET_ATON() thank you Jason.


On 2019-02-07 19:54, Jason Guy wrote:
> 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