(From a post at http://fogproject.org/forum/threads/modifying-image-ids.408/)
Kill all tasks that are still in the queue. You do not want to do this procedure with active or scheduled tasks.
Backup your database in case you mess it up.
from a terminal:
note the use of backticks, 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>mysqldump:~$ mysqldump --user=root fog > fog.`date +%Y%m%d`.sqlLet's get startedCode:user@machine:~$ sudo apt-get install mysqldump
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;
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.
Note: 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:
You want to change this to be:Code:+---------+---------------------+ | imageID | imageName | +---------+---------------------+ | 8 | StudentSpare-HL91 | | 4 | BSELAB | | 11 | MS16372Teacher2 | | 10 | MS163KTeacher2 | +---------+---------------------+
You'll execute the following lines at the mysql> prompt.Code:+---------+---------------------+ | imageID | imageName | +---------+---------------------+ | 1 | StudentSpare-HL91 | | 2 | BSELAB | | 3 | MS16372Teacher2 | | 4 | MS163KTeacher2 | +---------+---------------------+
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.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;
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.
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.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;
Keeping your database clean and sane by fixing host records that point to non-existent images
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.
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:update hosts set hostImage=0 where hostImage NOT IN (Select imageID from images);
Code:delete from hosts where hostImage NOT IN (Select imageID from images);