I blogged here about adding SQL support to dhcpd. I’m now reasonably happy with the patch, but have made a couple of tiny updates. These concern the documentation, and a bug which prevented the (probably useless) “name:” field from working.
You can find a new patch, again against dhcp 4.2.0, here.
See the original post here for build instructions and documentation.
Hello,
i patch isc-dhcpd 4.2.0 and it’s ok but i have ” DBI error: dbi_initialize() failed ”
Is there a way to have some trace somewhere ?
my query :
dbi-query “SELECT test FROM test WHERE macaddr = ‘test’”;
Thanks
Things saying “DBI error” almost always means the config file is wrong or DBI hasn’t been properly installed. The only parameter this takes is (I think undocumented) dbi-path option which you should not have set, so we can eliminate the former.
My guess is that you have installed libdbi, but not the dbi driver for your database (e.g. MySQL, Postgres etc.)
-_- you have right :
yum install libdbi-drivers
yum install libdbi-dbd-mysql.x86_64
and no more errors 😀
so with this query the client will receive is ip based on hi mac ?
I see that there is a newer patch i will install asap
thanks
No because you need to make the query (a) use the MAC address, and (b) provide the option string. So if your table is called ‘test’ and it has two columns called ‘ipv4’ and ‘macaddr’ containing the ipv4 and MAC address, you would want something like:
SELECT ipv4 AS ‘fixed-address’ FROM test WHERE macaddr = ‘%h’
So the answer to the query goes into a dhcp.conf style option called ‘fixed-address’ which is how you specify an IP address in dhcpd.
Ok i understand i fix the query. Mistake from me.
no free leases found :/
Something bad in my conf, if you have a dhcpd.conf exemple with some dbi an query it could be cool. Thanks for all already i will try to understand where is the problem.
Here’s one from a live server. Note the subnet & netmask thing is a hack. It will work, because the fixed addresses will always fall within 0.0.0.0/0. However, you would be better using dhcp config as intended in many circumstances. Also note my SELECT statement is more complicated than it needs be (illustrating usage of other options) and from memory postgres’s string concatenation operator (||) needs to be something different on mysql.
ddns-update-style none;
ignore client-updates;
authoritative;
subnet 0.0.0.0 netmask 0.0.0.0 {
next-server 10.10.10.10;
filename "pxelinux.0";
}
dbi-host "127.0.0.1";
dbi-driver "pgsql";
dbi-username "myusername";
dbi-password "mypassword";
dbi-dbname "mydatabase";
dbi-query "SELECT ('fixed-address '|| node_ip || '; ' ||
'option ntp-servers 10.10.10.10; ' ||
'option routers 10.10.10.10; ' ||
'option subnet-mask 255.255.255.0 ;')
AS entry FROM node, node_mac WHERE node.node_id = node_mac.node_id AND node_mac.mac_address LIKE '%h' LIMIT 1";
Very good work
it ‘s ok for me in the default options but not in the subnet group.
I hope your work will be use in a future isc release i’s a good job. And thanks (again) for your help.
humm it seems i can’t do you like you with || in mysql. I tried with concat but i have a lot’s of problem with semicolon that are interpreted ; syntax seems to be the same
query “SELECT concat(‘fixed-address ‘,fixed-address,’; ‘), CONCAT(‘option routers 10.130.0.254 ;’) FROM …”;
DBI-HOST line 0: expecting a parameter or declaration
^
DBI-HOST line 1: expecting a parameter or declaration
concat(‘fixed-address’,fixed-address,’; ‘
DBI-HOST line 1: expecting a parameter or declaration
concat(‘fixed-address’,fixed-address,’; ‘) fixed-address10.130.0.210; ;
What is just work so far is :
“SELECT fixed-address FROM …”;
Yes, you have to be a bit clever about the semicolons and quoting; as far as I know there isn’t a way to put a semicolon in the string in dhcp.conf. I think I used CHR(59) instead of the semicolon, and so forth.
huhu yes we tried that 😉
For now i just make my query with the ip and i put routers in subnet group
Leave a Reply to jmCancel reply