Change Image ID Number



  • Server
    • FOG Version: 5962
    • OS: Ubuntu 14.04
    Client
    • Service Version:
    • OS:
    Description

    Hi all,

    Is there a way to change the image ID numbers? I’ve spent some time taking images down and uploading new ones that I’m now on image 30, even though I only have about 14. It’s not a major issue, but it’s just a nice one to check off the list.



  • @Wayne-Workman Perfect - thanks for this!



  • New script that works no matter what, plus resets the auto-number too.

    Keep in mind the nodeBB bug that removes spaces after [[ and before ]].

    Where you see double left brackets, a space must come after them. Where you see double right brackets, a space must come before them. You’ll need to add these spaces to the script if they are not there.

    #!/bin/bash
    
    #----- MySQL Credentials -----#
    snmysqluser=""
    snmysqlpass=""
    snmysqlhost=""
    # 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"
    
    


  • @Tom-Elliott Ah that makes sense. Thanks for clarifying!



  • Tom’s right. I have several ideas on how to make it better but I’ve just not worked on it.


  • Senior Developer

    @RobTitian16 The scripts limitations as they currently stand will only operate if ID 1 is available to be used.



  • @Wayne-Workman Just an update on this: I tried running the script again after adding an image (the FOG web gui said it was image 31), but the script didn’t appear to run at all - there was no output as before. As a workaround, I had to set the image ID manually, but is this a known issue with the script?



  • #wiki worthy



  • @Wayne-Workman Thanks for the assistance with this - it’s much appreciated!
    It might be an idea to put this on the wiki once all is done as it would be quite helpful to others, I’m sure :)



  • @Tom-Elliott said in Change Image ID Number:

    @RobTitian16, @Wayne-Workman is correct.

    On all the of the “if lines” you must have a space after [[ and before ]]

    Tom, you’re correct. And this whole mis-understanding and errors with the script are due to a nodeBB bug. It was stripping out the spaces between the brackets. I’ve checked the script’s original text that I posted by clicking the “edit” button on the post, there are spaces there but the forums isn’t displaying the spaces, so when @RobTitian16 copy/pasted he got a copy without the needed spaces.


  • Senior Developer

    https://github.com/NodeBB/NodeBB/issues/5126

    Created issue about the spaces being stripped out.



  • @Wayne-Workman It seemed to work without double quoting the variables - I just had to put spaces, like:

    if [[ $snmysqlhost != “” ]] then



  • @RobTitian16 Try to double quote the variables in there like this:

    if [[ "$snmysqlhost" != "" ]]; then



  • @Tom-Elliott BAM - that seems to have worked! Thanks!

    0_1476716967015_Capture.PNG


  • Senior Developer

    @Tom-Elliott Never mind apparently it’s removing excess spaces for some reason.


  • Senior Developer

    @Wayne-Workman I’d recommend updating this, the if statements are missing spaces.


  • Senior Developer

    @RobTitian16, @Wayne-Workman is correct.

    On all the of the “if lines” you must have a space after [[ and before ]]



  • @Wayne-Workman Thanks, although that’s actually a result of me blocking out the password. I can confirm there are double quotes on all 3 lines. I copied and pasted the code below, so it’s all the same as far as I can tell.
    I can also confirm there is a space between the [[ and ]] brackets and the inner line between them, as suggested.



  • @Tom-Elliott Yup. I didn’t look close enough at it lol.

    Looks like a simple typo. In the original script I posted, it’s good.


  • Senior Developer

    @RobTitian16 Looks to my like your snmysqlpass and snmysqlhost fields are using missmatching quotes.

    The snmysqlpass looks to start with a single quote but end with a double quote.

    THe snmysqlhost field looks like it’s much the same.


 

501
Online

41.6k
Users

12.1k
Topics

114.4k
Posts