Broadcast address based on IP and MASK with mysql

Question

hello, I have the following two columns:

SELECT  b.ip_address AS IP ,b.mask AS MASK FROM interfaces b WHERE b.ip_address = 167804290;
+-----------+------------+
| IP        | MASK       |
+-----------+------------+
| 167804290 | 4294967168 | 
+-----------+------------+

Where is an actual IP address an its subnet mask

SELECT INET_NTOA(b.ip_address) AS IP,INET_NTOA(b.mask) AS MASK FROM interfaces b WHERE b.ip_address = 167804290;

+--------------+-----------------+
| IP           | MASK            |
+--------------+-----------------+
| 10.0.125.130 | 255.255.255.128 | 
+--------------+-----------------+
1 row in set (0.00 sec)

I am trying to find a way with mysql to grab the actual broadcast range which in this case is 10.0.125.255 or 167804415, but I can't see to find it. closest I gotten is

SELECT INET_NTOA(b.ip_address+(POWER(2,32)- b.mask - 1)) FROM interfaces b WHERE b.ip_address = 167804290; 
+---------------------------------------------------+
| INET_NTOA(b.ip_address+(POWER(2,32)- b.mask - 1)) |
+---------------------------------------------------+
| 10.0.126.1                                        | 
+---------------------------------------------------+

Only problem there is that is assuming that the column ip_address is the start of the subnet(10.0.125.128)

Any help will be appreciated.

Answer 1

The broadcast address is the complement of the subnet mask, OR'ed with the IP address.

SELECT INET_NTOA( ~b.mask & 0xffffffff | b.ip_address) 
FROM interfaces b 
WHERE b.ip_address = 167804290;

(You have to mask with "& 0xffffffff" because in MySQL, the bitwise complement operator returns a 64-bit value.)