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

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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

MRob
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
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

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

Jason Guy
The best link used to be here:
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 <[hidden email]> 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
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users

_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

Re: [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 <[hidden email]> 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
>> [hidden email]
>> https://lists.isc.org/mailman/listinfo/kea-users
_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users
Reply | Threaded
Open this post in threaded view
|

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

Thomas Markwalder
In reply to this post by Jason Guy
We moved everything to gitlab.  You can find it here:


We have migrated everything to gitlab. That article is now here:

https://gitlab.isc.org/isc-projects/kea/wikis/docs/editing-host-reservations

FYI, for the originals, you can try changing "kea.isc.org" to "oldkea.isc.org".  Can't guarantee that will always work but it does currently:

https://oldkea.isc.org/wiki/HostReservationsHowTo

Cheers,

Thomas Markwalder
ISC Software Engineering




On 2/7/19 2:54 PM, Jason Guy wrote:
The best link used to be here:
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 <[hidden email]> 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
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users

_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users


_______________________________________________
Kea-users mailing list
[hidden email]
https://lists.isc.org/mailman/listinfo/kea-users