Modifying Image IDs
-
I know there used to be a tutorial, i’m assuming on the old forums, where they had directions how to modify Image ID’s. I remember having to go in with a MySQL command, loading a table and selecting the Image ID where you could then run a command to change the numbers around etc. As we retire old images it would be nice to all the images be in order so you don’t have to remember active images like 1, 3, 6, 7 etc when registering hosts. Does anyone have this, or could write up another quick tutorial on this?
-
There are many ways to do this. Here is One:
[B]sudo apt-get install mysql-admin[/B]
[B]mysql-query-browser[/B]
Login then select Fog and then the images table, Change whatever you want -
For the life of me I can’t get this to work, always get some sort of error regardless of what I research online.
-
it’s pretty basic sql.
You want to connect to mysql, switch to the fog database, and updated the records in the images table. A few questions first.
Did you set a password on your mysql installation?
How do you want to handle the active/inactive images definitions? i.e. active are 1 through X, and inactive are 1000+?
-
no, the default password was never changed during the mysql installation. So i’m guessing I have to sign into the mysql browser using the default fog account with the random mysql password it generated? Also the goal is to only keep active images on the server, as we keep the most recent backup of each image on a separate server.
Now, I could be doing this wrong, but i’m actually on the FOG server itself running the mysql query browser. For the host name I put in the IP address, username i’ve tried both the fog auto-created account and my admin account with the passwords that I know. Each time I get the
"Could not connect to host ‘x.x.x.x’/
MySQL Error Nr. 2003
Can’t connect to MySQL server on ‘x.x.x.x’ (111)Click the ‘Ping’ button to see if there is a networking problem"
Which the pings work correctly
-
This post is deleted! -
Ok, so I got this all figured out and i’m able to sign into the browser, however, i’m still not sure how to edit the image ID data. I went to FOG > Images under the Schemata on the side, however, I don’t see where I can edit any data that relates to the Image ID or descriptions.
-
[B]Kill all tasks that are still in the queue. You do not want to do this procedure with active or scheduled tasks.[/B]
[B]Backup your database in case you mess it up.[/B]
from a terminal:
[CODE]user@machine>mysqldump:~$ mysqldump --user=root fog > fog.date +%Y%m%d.sql[/CODE]note the use of [B]backticks[/B], not apostrophes or single quotations. If you don’t have mysqldump, get it using the package manager of your choice. On Ubuntu:[CODE]user@machine:~$ sudo apt-get install mysqldump[/CODE]
[B]Let’s get started[/B]
So I’m assuming you are deleting the image definitions for old images you no longer use and you want to consolidate the existing images into the 1 to X id range.I’ll show you how to do it from the command line, because even through the webui, you still have to write the SQL code out OR do each update individually.
from a terminal:
[CODE]user@machine:~$ mysql -u root
mysql>use fog;
mysql>select imageid, imagename from images;[/CODE]
At this point, you should get a listing of all the image definitions showing the ID and the Name. I’m assuming you can tell the images apart based on the name. Make note of the existing image ID’s and names, we’ll need them later. I use putty, so a simple copy and paste to Notepad on my workstation is easy, but if you just have a few, write them down on paper.
[B]Note:[/B] If you deleted the old image definitions for outdated images, you either need to delete the host records associated with those entries if the hosts are no longer valid, or change their image value to a zero(0) value so they are not associated with the wrong image after you update the image ids. Just a bit of house-keeping so your FOG data stays sane. If you want to keep your database clean, skip to the end and run that script before you proceed here.
So now we have a list of the “good” images and their ID’s, it’s time to change them. Let’s say our existing data looked like:
[CODE]±--------±--------------------+
| imageID | imageName |
±--------±--------------------+
| 8 | StudentSpare-HL91 |
| 4 | BSELAB |
| 11 | MS16372Teacher2 |
| 10 | MS163KTeacher2 |
±--------±--------------------+[/CODE]You want to change this to be:
[CODE]±--------±--------------------+
| imageID | imageName |
±--------±--------------------+
| 1 | StudentSpare-HL91 |
| 2 | BSELAB |
| 3 | MS16372Teacher2 |
| 4 | MS163KTeacher2 |
±--------±--------------------+[/CODE]You’ll execute the following lines at the mysql> prompt.
[CODE]mysql>update images set imageID=1 where imageID=8 limit 1;
mysql>update images set imageID=2 where imageID=4 limit 1;
mysql>update images set imageID=3 where imageID=11 limit 1;
mysql>update images set imageID=4 where imageID=10 limit 1;[/CODE]This changes the imageID value for each image definition. You can reorder them however you want, just adjust the imageID values in each statement. the “limit 1” on the end of each statement makes sure you change just 1 record, because logically there can be only 1.
What this has done is broken the link between the host records and the image records. At this point, the hosts no longer have a valid image associated with them, because they know the image by it’s previous ID, which is no longer valid.
Now you have to update the hosts table so that any hosts which used the old image ID, now uses it’s new image ID.
[CODE]mysql>update hosts set hostImage=1 where hostImage=8;
mysql>update hosts set hostImage=2 where hostImage=4;
mysql>update hosts set hostImage=3 where hostImage=11;
mysql>update hosts set hostImage=4 where hostImage=10;[/CODE]The host records should now know the new image id for each image. If you’ve got host records that still reference images that are no longer valid, they may associate with the new ID’s you’ve given the images. You can clean them out individually through the webUI, or just leave them if you don’t care about your database being clean and sane.
[B]Keeping your database clean and sane by fixing host records that point to non-existent images[/B]
If you have removed old image definitions from FOG using the webUI, can you run the following statement from the mysql> prompt to disassocated any hosts records from those image ID’s that no longer exist. You want to do this before you change the “good” image id’s.[CODE] update hosts set hostImage=0 where hostImage NOT IN (Select imageID from images);[/CODE]
Alternatively, you can delete all hosts from FOG which no longer have a valid image associated with them. Again, run this before you change the image ID’s. This is a bit more risky as there is no way to recover if you mistype or decide you want something back later.
[CODE] delete from hosts where hostImage NOT IN (Select imageID from images);[/CODE]
-
I use phpmyadmin as my web based mysql admin tool. Be careful of installing any web accessible admin tool if you did not set a root password for your mysql instance, as it leaves you open to “curious” people who find the website.
-
perfect, that’s exactly what I was looking for. One more question I have then, I made the back of the database using the following command, mysqldump --user=root fog > fog.
date +%Y%m%d.sql. This made the file, and I was able to move that to a backup server. My question being, how would you restore this in the case of a crash, or database corruption? Sorry for all the questions, but I truly have 0 knowledge when it comes to SQL. -
There are two ways: complete, and partial.
To do a complete restore back to the point you took the database backup, you simply:
[CODE]user@computer:~$ mysql -u root fog < [backupfile].sql[/CODE] where [backupfile].sql is the name of your .sql file that was made by mysqldump. It should have all the commands in it to restore all tables and records in the FOG database.To do a partial restore, say of just the images and hosts tables, make a copy of the .sql file for backup purposes. Then you’ll need to edit the .sql file and remove all the statements that change the other tables.
Leave the statements at the top that set the parameters and all statements related to the hosts and images tables.
Usually there is a statement block to drop and rebuild the table structure, then a block below it that inserts all the data back into the table, so make sure you delete both blocks for all tables you do NOT want to restore, and leave both blocks for all tables you DO want to restore.
Once you have the file like you want, save it, and run it with the same way you would for the complete database backup, just with the modified file.
-
For those that find themselves here:
https://forums.fogproject.org/topic/8762/change-image-id-number
