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



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



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



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


 

321
Online

41.4k
Users

11.8k
Topics

112.2k
Posts