IP Addresses -- How to Store
I'm looking to develop a database of IP addresses for the company and have, of course, chosen mySQL as the backend. I want the app to add (remove, etc.) a host, giving its hostname and segment. The app will add the next available address to the database and, looking at the segment, provide the subnet mask and default gateway for said new host. I've got the db structure written out, but I'm having issues on how to store the data like address, subnet mask, default gateway. Our network is very diverse, covering many ranges of addresses and, more importantly, many subnet masks. Some are 24bit, some are 16bit, and some are 23bit. What is the best way to store this data in mySQL? If I do a varchar, then how do I restrict the data to between 0 and 255? Also, how would I manipulate any address with a classless mask? I'm thinking storage as binary so I can parse out a byte of binary data and present it as a decimal number; this also limits the data to numbers between 0 and 255, based on an 8-bit byte. The problem is that I have no clue how to store such. I'm running around in circles on this one. Can some point me to a resource that can shed some light on this type of data storage? -- Aaron Conaway Network Engineer III Verisign, Inc. -- Telecom Services Division http://www.verisign.com Office: 912.527.4343 Fax: 912.527.4014 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: IP Addresses -- How to Store
Thanks to Peter, Ravi, and Dan. That's exactly what I needed to know. -- Aaron Conaway Network Engineer III Verisign, Inc. -- Telecom Services Division http://www.verisign.com Office: 912.527.4343 Fax: 912.527.4014 -Original Message- From: Peter Hicks [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 11, 2003 1:30 PM To: Dan Nelson Cc: Aaron Conaway; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: IP Addresses -- How to Store On Tue, 11 Feb 2003, Dan Nelson wrote: > Store your addresses as INTs, so you would have three fields: > "address", "netmask", and "gateway". You can either encode the values > yourself, or use mysql's INET_NTOA()/INET_ATON() functions. ...and beware, INET_NTOA/ATON calls aren't compatible (as far as I can see) with PHP's equivilent calls. PHP uses signed integers, and MySQL not. Has anyone come up with a workaround to this on either the MySQL or PHP sides? Peter. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Different value from same function
Weird problem here running 3.23.55-Max. I'm (still) trying to get my IP
address DB working, but have run into a problem.
When I use the inet_aton function during an insert, I get a very
different value than if I use the function by itself on the CLI. Please
see below.
The IP address I am trying to use is 172.20.20.2. When I run select
inet_aton("172.20.20.2") on the CLI, I get 2886996994, which is the
correct value; when I run the same function during an insert, I get
2147483647, which is not correct.
Any thoughts on this one? I'm stumped.
mysql> describe host;
++-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| name | varchar(20) | | PRI | | |
| ip | int(4) | | | 0 | |
| admin | varchar(30) | | | | |
| subnet | varchar(20) | | | | |
++-+--+-+-+---+
4 rows in set (0.01 sec)
mysql> insert into host values (
-> "Test1",
-> inet_aton("172.20.20.2"),
-> "Pixies",
-> "Elive"
-> );
Query OK, 1 row affected (0.00 sec)
mysql> select * from host;
++++--+
| name | ip | admin | subnet |
++++--+
| Test1 | 2147483647 | admin1 | sub1 |
++++--+
1 row in set (0.01 sec)
mysql> select inet_ntoa(2147483647);
+---+
| inet_ntoa(2147483647) |
+---+
| 127.255.255.255 |
+---+
1 row in set (0.00 sec)
mysql> select inet_aton("172.20.20.2");
+--+
| inet_aton("172.20.20.2") |
+------+
| 2886996994 |
+--+
1 row in set (0.00 sec)
--
Aaron Conaway
Network Engineer III
Verisign, Inc. -- Telecom Services Division
http://www.verisign.com
Office: 912.527.4343
Fax: 912.527.4014
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
