Resetting FOG Snap In IDs

[Tom Elliott]

The principle used for the image id’s should also be able to be used for snapin id’s.

[THEMCV]

@Tom-Elliott I’ll have to go through the script and see if I can figure it out by changing the variables in it. Guess I can’t break too much.

[Tom Elliott]

@THEMCV If you post it here in a snippet I can probably help. I could find the script myself I suppose, but I’m just being lazy lol.

[THEMCV]

@Tom-Elliott

#!/bin/bash

#----- MySQL Credentials -----#
snmysqluser="root"
snmysqlpass=""
snmysqlhost="10.4.200.150"
# If user and pass is blank, leave just a set of double quotes like ""
# if the db is local, set the host to just double quotes "" or "127.0.0.1" or "localhost"


#----- Begin Program -----#

selectAllImageIDs="SELECT imageID FROM images ORDER BY imageID"
selectLowestImageID="SELECT imageID FROM images ORDER BY imageID ASC LIMIT 1"
selectHighestImageID="SELECT imageID FROM images ORDER BY imageID DESC LIMIT 1"

options="-sN"
if [[ $snmysqlhost != "" ]]; then
       options="$options -h$snmysqlhost"
fi
if [[ $snmysqluser != "" ]]; then
       options="$options -u$snmysqluser"
fi
if [[ $snmysqlpass != "" ]]; then
       options="$options -p$snmysqlpass"
fi
options="$options -D fog -e"


#Information gathering.
lowestID=$(mysql $options "$selectLowestImageID")
highestID=$(mysql $options "$selectHighestImageID")
newAutoIncrement=$((highestID + 1))


####### Basic logic flow ########

#If there is an image id of 1, move it to the new auto increment number.
#After re-numbering is complete, the new auto increment number will not be taken.
#Then reset the auto-increment to the new auto increment number, which is the first free number.



#Move any images that have an ID of 1 to the next free number.
if [[ "$lowestID" -eq "1" ]]; then
   echo "-------------------"
   echo "Attempting to change Image ID $lowestID to $newAutoIncrement"
   mysql $options "UPDATE images SET imageID = $newAutoIncrement WHERE imageID = $lowestID"
   mysql $options "UPDATE imageGroupAssoc SET igaImageID = $newAutoIncrement WHERE igaImageID = $lowestID"
   mysql $options "UPDATE hosts SET hostImage = $newAutoIncrement WHERE hostImage = $lowestID"
   echo "Attempt completed"
fi


#Re-number all images sequentially.
count=1
mysql $options "$selectAllImageIDs" | while read imageID; do
   echo "-------------------"
   echo "Attempting to change Image ID $imageID to $count"
   mysql $options "UPDATE images SET imageID = $count WHERE imageID = $imageID"
   mysql $options "UPDATE imageGroupAssoc SET igaImageID = $count WHERE igaImageID = $imageID"
   mysql $options "UPDATE hosts SET hostImage = $count WHERE hostImage = $imageID"
   echo "Attempt completed"
   count=$((count + 1))
done


#set new auto-increment.
echo "-------------------"
highestID=$(mysql $options "$selectHighestImageID")
newAutoIncrement=$((highestID + 1))
echo "Attempting to change the auto_increment for the images table to $newAutoIncrement"
mysql $options "ALTER TABLE images AUTO_INCREMENT = $newAutoIncrement"
echo "Attempt completed"

Here it is. My guess would be to change the SELECT imageID from images to SELECT snapinID from snap ins… but I’m taking a wild guess.

[Tom Elliott]

@THEMCV

#!/bin/bash

#----- MySQL Credentials -----#
snmysqluser="root"
snmysqlpass=""
snmysqlhost="10.4.200.150"
# If user and pass is blank, leave just a set of double quotes like ""
# if the db is local, set the host to just double quotes "" or "127.0.0.1" or "localhost"


#----- Begin Program -----#

selectAllSnapinIDs="SELECT sID FROM snapins ORDER BY sID"
selectLowestSnapinID="SELECT sID FROM snapins ORDER BY sID ASC LIMIT 1"
selectHighestSnapinID="SELECT sID FROM snapins ORDER BY sID DESC LIMIT 1"

options="-sN"
if [[ $snmysqlhost != "" ]]; then
       options="$options -h$snmysqlhost"
fi
if [[ $snmysqluser != "" ]]; then
       options="$options -u$snmysqluser"
fi
if [[ $snmysqlpass != "" ]]; then
       options="$options -p$snmysqlpass"
fi
options="$options -D fog -e"


#Information gathering.
lowestID=$(mysql $options "$selectLowestSnapinID")
highestID=$(mysql $options "$selectHighestSnapinID")
newAutoIncrement=$((highestID + 1))


####### Basic logic flow ########

#If there is a snapin id of 1, move it to the new auto increment number.
#After re-numbering is complete, the new auto increment number will not be taken.
#Then reset the auto-increment to the new auto increment number, which is the first free number.



#Move any snapins that have an ID of 1 to the next free number.
if [[ "$lowestID" -eq "1" ]]; then
   echo "-------------------"
   echo "Attempting to change Snapin ID $lowestID to $newAutoIncrement"
   mysql $options "UPDATE snapins SET sID = $newAutoIncrement WHERE snapinID = $lowestID"
   mysql $options "UPDATE snapinGroupAssoc SET sgaSnapinID = $newAutoIncrement WHERE sgaSnapinID = $lowestID"
   #mysql $options "UPDATE hosts SET hostImage = $newAutoIncrement WHERE hostImage = $lowestID"
   echo "Attempt completed"
fi


#Re-number all snapins sequentially.
count=1
mysql $options "$selectAllSnapinIDs" | while read snapinID; do
   echo "-------------------"
   echo "Attempting to change Snapin ID $snapinID to $count"
   mysql $options "UPDATE snapins SET sID = $count WHERE sID = $snapinID"
   mysql $options "UPDATE snapinGroupAssoc SET sgaSnapinID = $count WHERE sgaSnapinID = $snapinID"
   #mysql $options "UPDATE hosts SET hostImage = $count WHERE hostImage = $imageID"
   echo "Attempt completed"
   count=$((count + 1))
done


#set new auto-increment.
echo "-------------------"
highestID=$(mysql $options "$selectHighestSnapinID")
newAutoIncrement=$((highestID + 1))
echo "Attempting to change the auto_increment for the snapins table to $newAutoIncrement"
mysql $options "ALTER TABLE snapins AUTO_INCREMENT = $newAutoIncrement"
echo "Attempt completed"

[Wayne Workman]

@Tom-Elliott Good work. I’ll get this tested and into the fog community scripts repository.

[Tom Elliott]

@Wayne-Workman I’ll see about editing this script to take the more common elements that might need the id’s re-adjusted and take the table argument (or array of tables if you wanted to.

This way we have a more of a “generic” script that can be used for the whole purpose of re-id’ing tables.

The idea, ultimately, would be something like:

./updateTableIDs "snapins" or ./updateTableIDs "images snapins"

This way we have a script that could work on any table to renumber/factor. Initially it would probably only operate upon single tables. Once that’s been hashed out, work on incorporating multiple table adjustments.

[Wayne Workman]

@Tom-Elliott Instead of arguments, how about a script that just re-numbers all the IDs by default. Locations too.

[mtanigawa]

@Tom-Elliott @Wayne-Workman @THEMCV

Thanks for this. Worked successfully for me. Will be trying the image ID one once next years image(s) are done. Thank you again!

[Wayne Workman]

@Tom-Elliott The script you posted had a few issues, I’ve fixed them up. A mistyped variable in the first loop, and I added code to update the snapinAssoc IDs too so those aren’t lost. I’ve submitted a pull request.

All, the improved script will be available here:
https://github.com/FOGProject/fog-community-scripts

This is just a hold-over honestly. We need a big and comprehensive script to just cleanup and re-order everything. Locations, images, snapins, groups, everything - as well as running cleanup commands that we know fixes stuff and can’t hurt but can only help.