I have a big issue ... I need to confirm something. This is my understanding of the relations between base and ipaddr tables in mysql:
1. base has a primary key of baseindex (auto-increment)
2. Created subnets get added to the base and get assigned the next available baseindex number.
3. individual hosts (ips) configured will get added to the ipaddr table and will have a baseindex of the base subnet's baseindex in the base table.
4. If no info is added for an ip, it still gets put in the ipaddr table, but with a baseindex of 0 (likely when you create a subnet initially)
5. If an ipaddr entry has no baseindex it will NOT show up in the modifyipform.php
so ... if this is correct ... this is the problem. I have a lot of ips that are orphaned due to a database migration issue. I'd like to repair this ... I'm assuming I need to create a script that will look at each IP and find the associated subnet in the base and alter the ipaddr table entry, inputting the correct baseindex for the ip.
This seems like a complicated message ... I hope I'm being clear ... does anyone have any advice on what I can do? There's no point re-importing the data as the original data is orphaned too ...
Jim
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Point 4 is incorrect - if no ip is assigned, no record must get added. baseindex in the ipaddr table must NEVER be 0! The record will then be orphaned. The baseindex in ipaddr MUST always also exist in the base table, but the baseindex column in table base does not need to exist in table ipaddr. So the relationship is a LEFT JOIN with table base on the left and table ipaddr on the right. Thus for blank ipaddr records, you do not need a blank record in the ipaddr table. This is to save space in the ipaddr table - thus for a class B network (65536 addresses) you do not need 65536 records in the ipaddr table - only records that actually have data.
Point 5 is correct, but this should not happen!
What I suggest is dumping all the orphaned records in CSV format, delete them (delete all records with baseindex=0) and then import then using the import functions. You will then be certain that the releationship will be correct. Look at the code in the import scripts for a better understanding of the relationship. Also look at the INTERNALS doc.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Here is a query that will return all the records in table ipaddr that do not have a corresponding entry in the base table - if any records show up then they are orphaned (will find records with 0 as baseindex too):
select inet_ntoa(ipaddr.ipaddr), ipaddr.baseindex, ipaddr.userinf, ipaddr.location, ipaddr.lastmod, ipaddr.userid
from ipaddr left join base on base.baseindex=ipaddr.baseindex where base.baseindex is null;
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have a big issue ... I need to confirm something. This is my understanding of the relations between base and ipaddr tables in mysql:
1. base has a primary key of baseindex (auto-increment)
2. Created subnets get added to the base and get assigned the next available baseindex number.
3. individual hosts (ips) configured will get added to the ipaddr table and will have a baseindex of the base subnet's baseindex in the base table.
4. If no info is added for an ip, it still gets put in the ipaddr table, but with a baseindex of 0 (likely when you create a subnet initially)
5. If an ipaddr entry has no baseindex it will NOT show up in the modifyipform.php
so ... if this is correct ... this is the problem. I have a lot of ips that are orphaned due to a database migration issue. I'd like to repair this ... I'm assuming I need to create a script that will look at each IP and find the associated subnet in the base and alter the ipaddr table entry, inputting the correct baseindex for the ip.
This seems like a complicated message ... I hope I'm being clear ... does anyone have any advice on what I can do? There's no point re-importing the data as the original data is orphaned too ...
Jim
Point 1, 2, 3 are correct
Point 4 is incorrect - if no ip is assigned, no record must get added. baseindex in the ipaddr table must NEVER be 0! The record will then be orphaned. The baseindex in ipaddr MUST always also exist in the base table, but the baseindex column in table base does not need to exist in table ipaddr. So the relationship is a LEFT JOIN with table base on the left and table ipaddr on the right. Thus for blank ipaddr records, you do not need a blank record in the ipaddr table. This is to save space in the ipaddr table - thus for a class B network (65536 addresses) you do not need 65536 records in the ipaddr table - only records that actually have data.
Point 5 is correct, but this should not happen!
What I suggest is dumping all the orphaned records in CSV format, delete them (delete all records with baseindex=0) and then import then using the import functions. You will then be certain that the releationship will be correct. Look at the code in the import scripts for a better understanding of the relationship. Also look at the INTERNALS doc.
Brilliant ... I forgot about the import function no including the baseindex ... this makes sense.
Thanks ... there are over 13000 orphaned ip's so this will save a lot of time.
Here is a query that will return all the records in table ipaddr that do not have a corresponding entry in the base table - if any records show up then they are orphaned (will find records with 0 as baseindex too):
select inet_ntoa(ipaddr.ipaddr), ipaddr.baseindex, ipaddr.userinf, ipaddr.location, ipaddr.lastmod, ipaddr.userid
from ipaddr left join base on base.baseindex=ipaddr.baseindex where base.baseindex is null;