[Kea-users] DHCP4 MySQL Host Reservation Troubles

[Bryan Perry]

Thomas, Marcin,

With your help I have been able to get this working exactly as I needed 
it to. The subnet4_select callout checks my hosts table for a static 
reservation and, if found, updates the assigned subnet ID. Kea then 
picks up processing with the new subnet ID and can successfully assign 
the correct static IP address to the client.

Although it's surely not the most elegant, I will paste in my code below 
in case it is helpful for anyone else dealing with shared subnets and 
static reservations. If you see anything wrong with the code or problems 
I may cause, I'm always open to feedback, but it seems to be working 
great right now.

I also had the requirements to write some basic info out to a more human 
readable log as well as writing all IP address assignments out to a 
completely different database for required lease history tracking. I 
used the lease4_select callout for the lease history storage. I'll paste 
that code as well in case it's of any use to anyone else.

Hopefully later versions of Kea will have some of this functionality 
built in.

Thanks again for all your help. I couldn't have got this working without 
you guys.

-Bryan







// subnet4_select.cc

#include <hooks/hooks.h>
#include <dhcp/pkt4.h>
#include <dhcpsrv/subnet.h>
#include <dhcpsrv/mysql_connection.h>
#include <algorithm/string.hpp>
#include "library_common.h"
#include <string>

using namespace isc::dhcp;
using namespace isc::hooks;
using namespace std;

     // Define a structure for the mysql connection instance
     struct connection_details {
       const char *server;
       const char *user;
       const char *password;
       const char *database;
     };

     MYSQL_RES *Result;     // The mysql query results set variable

     // A function to establish the mysql connection
     MYSQL* mysql_connection_setup2(struct connection_details 
mysql_details) {
       // Create a mysql instance and initialize the variables
       MYSQL *connection = mysql_init(NULL);

       // Connect to the database with the details in the connection 
instance
       if (!mysql_real_connect(connection,mysql_details.server, 
mysql_details.user, mysql_details.password, mysql_details.database, 0, 
NULL, 0)) {
         printf("Conection error : %s\n", mysql_error(connection));
         //exit(1);
       }
       return connection;
     }

     // A function to run the query and return the results set
     MYSQL_RES* mysql_perform_query2(MYSQL *connection, const char 
*sql_query) {
       // send the query to the database
       // cout << sql_query << " in function mysql_perform_query2" << endl;
       if (mysql_query(connection, sql_query))
       {
          printf("MySQL query error : %s\n", mysql_error(connection));
          // exit(1);
       }
       Result = mysql_store_result(connection);
       int RowsReturned = mysql_num_rows( Result );
       // cout << "NumRows in the function: " << RowsReturned << endl;
       // return mysql_use_result(connection);
       return Result;
     }

extern "C" {

// This callout is called at the "subnet4_select" hook.
// We will intercept the request, check the MySQL hosts table
// for reservations, and if found, set the correct subnet ID

int subnet4_select(CalloutHandle& handle) {

     // A pointer to the packet is passed to the callout via a "boost" smart
     // pointer. The include file "pkt4.h" typedefs a pointer to the Pkt4
     // object as Pkt4Ptr.  Retrieve a pointer to the object.
     Pkt4Ptr query4_ptr;
     handle.getArgument("query4", query4_ptr);

     // Get a pointer to the subnet4 object
     Subnet4Ptr subnet4_ptr;
     handle.getArgument("subnet4", subnet4_ptr);

     // Get the collection of subnets from the callout argument set
     const isc::dhcp::Subnet4Collection* subnets;
     handle.getArgument("subnet4collection", subnets);

     // Get a pointer to the hardware address.
     HWAddrPtr hwaddr_ptr = query4_ptr->getHWAddr();

     // Get the subnet ID from the initial request
     SubnetID subnetId = subnet4_ptr->getID();

     // Exclude hardware type from hardware address string
     bool include_htype=false;
     string colonized_hwaddr = hwaddr_ptr->toText(include_htype);

     // Strip colons from colonized_hwaddr to get hwaddr
     string hwaddr = colonized_hwaddr;
     boost::erase_all(hwaddr, ":");

     // Define some variables we will use
     int newSubnetId = 0;
     string ipaddr = "BLANK";
     long RowsReturned;

     // cout << hwaddr << " wants subnet "  <<  subnetId << "\n";
     // Use a try...catch here to help prevent MySQL errors from killing 
the server.
try {
     // Here is where we query the database and set new subnet ID if 
necessary
     MYSQL *conn2;        // the connection
     MYSQL_RES *res2;     // the results
     MYSQL_ROW row2;      // the results row (line by line)

     struct connection_details mysqlD2;
       mysqlD2.server = "localhost";   // where the mysql database is
       mysqlD2.user = "********";     // the root user of mysql
       mysqlD2.password = "********"; // the password of the root user 
in mysql
       mysqlD2.database = "********";          // the databse to pick

     // Build the query string
     ostringstream ss2;
     ss2 << "select hex(dhcp_identifier), dhcp4_subnet_id, 
INET_NTOA(ipv4_address), host_id from hosts where hex(dhcp_identifier) = 
'" << hwaddr << "' limit 1";
     string ss2_str = ss2.str();
     const char *full_query2 = ss2_str.c_str();

     // Connect to the mysql database
     conn2 = mysql_connection_setup2(mysqlD2);

     // Assign the results returned to res2 if valid
     if (res2 = mysql_perform_query2(conn2, full_query2)) {
       // Get the number of rows in the results set. Should be 0 or 1
       int rCount = mysql_num_rows(res2);
       // If we got more than 0 rows in the results then we found a 
reservation for that client
       if (rCount > 0) {
         while ((row2 = mysql_fetch_row(res2)) !=NULL) {
             // Convert the subnet ID to an integer for use further on
             string input(row2[1]); stringstream SS(input); SS >> 
newSubnetId;
             // Handle a NULL ipv4_address field
             if (row2[2]) {
               ipaddr = row2[2];
             } // end if
             // Log that we found a reservation
             leaselog << "Static reservation " << ipaddr << " found for 
" << row2[0] << " on row " << row2[3] << ", subnet " << row2[1] << endl;
         }
       } // end if
       // cout << "New subnet ID: " << newSubnetId << endl;

       // Clean up the database result set
       mysql_free_result(res2);

       // Clean up the database connectio
       mysql_close(conn2);
     } // end if
     else {
       // Throw an error if something went wrong in the mysql lookup
       leaselog << "MySQL query failed in lookup for " << hwaddr << " -- 
Results processing skipped." << endl;
     } // end else

} catch (...) {
   // Throw an error if something more serious happened
   leaselog << "# ERR: Caught an error in subnet4_select.cc" << endl;
}



     // Next, if we have to change the subnet ID, we iterate through the 
collection
     // of subnets, looking for the ID we want and then set it.
     if ((newSubnetId != 0) && (subnetId != newSubnetId)) {
       for (int i = 0; i < subnets->size();  ++i) {
           Subnet4Ptr new_subnet = (*subnets)[i];
           if (new_subnet->getID() == newSubnetId) {
               // id matched so replace the selected subnet by
               // setting the "subnet4" callout argument with
               // the new subnet
               handle.setArgument("subnet4", new_subnet);
               break;
           }
       }
     }

     flush(leaselog);
     return (0);

};

}








// lease4_select.cc

#include <hooks/hooks.h>
#include <dhcp/pkt4.h>
#include <dhcpsrv/lease.h>
#include <dhcpsrv/mysql_connection.h>
#include "library_common.h"
#include <string>

using namespace isc::dhcp;
using namespace isc::hooks;
using namespace std;

     // Define a structure for the mysql connection instance
     struct connection_details {
       const char *server;
       const char *user;
       const char *password;
       const char *database;
     };

     // A function to establish the mysql connection
     MYSQL* mysql_connection_setup(struct connection_details 
mysql_details) {
       // first of all create a mysql instance and initialize the 
variables within
       MYSQL *connection = mysql_init(NULL);

       // connect to the database with the details attached.
       if (!mysql_real_connect(connection,mysql_details.server, 
mysql_details.user, mysql_details.password, mysql_details.database, 0, 
NULL, 0)) {
       printf("Conection error : %s\n", mysql_error(connection));
       // exit(1);
       }
       return connection;
     }

     // A function to run the query and return the results set
     MYSQL_RES* mysql_perform_query(MYSQL *connection, const char 
*sql_query) {
       // send the query to the database
       // cout << sql_query << " in function mysql_perform_query" << endl;
       if (mysql_query(connection, sql_query))
       {
          printf("MySQL query error : %s\n", mysql_error(connection));
          // exit(1);
       }
       return mysql_use_result(connection);
     }


extern "C" {

// This callout is called at the "lease4_select" hook.
int lease4_select(CalloutHandle& handle) {

     // A pointer to the object is passed to the callout via a "boost" smart
     // pointer. The include file "lease.h" typedefs a pointer to the Lease4
     // object as Lease4Ptr.  Retrieve a pointer to the object.
     Lease4Ptr lease4_ptr;
     handle.getArgument("lease4", lease4_ptr);
     bool isFake;
     handle.getArgument("fake_allocation", isFake);

     // Get a pointer to the hardware address.
     HWAddrPtr hwaddr_ptr = lease4_ptr->hwaddr_;

     string ipaddr = lease4_ptr->addr_.toText();
     int subnetId = lease4_ptr->subnet_id_;

     bool include_htype=false;
     string hwaddr = hwaddr_ptr->toText(include_htype);

    // If it is DHCPACK and not just DHCPOFFER, write to log and database
    if (!isFake) {

     leaselog << "ASSIGNED: " << ipaddr << " to " << hwaddr << " 
subnetID " << subnetId << "\n";

     // Use a try...catch here to help prevent MySQL errors from killing 
the server.
try {
     // Here is where we query the database and set new subnet ID if 
necessary
     MYSQL *conn;        // the connection
     MYSQL_RES *res;     // the results
     MYSQL_ROW row;      // the results row (line by line)

     struct connection_details mysqlD;
       mysqlD.server = "localhost";   // where the mysql database is
       mysqlD.user = "********";     // the root user of mysql
       mysqlD.password = "********"; // the password of the root user in 
mysql
       mysqlD.database = "********";          // the databse to pick

     // connect to the mysql database
     conn = mysql_connection_setup(mysqlD);

     // Build the query
     std::stringstream ss;
     ss << "INSERT INTO lease_history VALUES ('" << ipaddr << "', '" << 
hwaddr << "', '" << subnetId << "')";
     const char *full_query = ss.str().c_str();
     // leaselog << full_query << endl;

     // assign the results return to the MYSQL_RES pointer
     res = mysql_perform_query(conn, full_query);

//    printf("MySQL Tables in mysql database:\n");
//    while ((row = mysql_fetch_row(res)) !=NULL)
//        printf("%s\n", row[0]);

     /* clean up the database result set */
     mysql_free_result(res);
     /* clean up the database link */
     mysql_close(conn);
} catch (...) {
   // Throw an error if something bad happened
   leaselog << "# ERR: Caught an error in lease4_select.cc" << endl;
}


    } //End if (!isFake)

     flush(leaselog);
     return (0);
};
}