Resetting FOG Snap In IDs



  • I found the script that @Wayne-Workman posted for fixing the numbering of the image IDs (which worked great!)

    Is there a script or a way to reset the ID numbers of the snap ins? We’re getting into the 20s-30s and the numbering is becoming a bit sporadic during full registration.

    Thanks. :)


  • Moderator

    @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.



  • @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!


  • Moderator

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


  • Senior Developer

    @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.


  • Moderator

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


  • Senior Developer

    @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"
    


  • @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. :p


  • Senior Developer

    @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.



  • @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. :)


  • Senior Developer

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


Log in to reply
 

459
Online

39176
Users

10821
Topics

102960
Posts

Looks like your connection to FOG Project was lost, please wait while we try to reconnect.