Change Image ID Number

[Tom Elliott]

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

[Wayne Workman]

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

[RobTitian16]

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

@RobTitian16, @Wayne-Workman is correct.

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

[Tom Elliott]

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

[Tom Elliott]

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

[RobTitian16]

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

[Wayne Workman]

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

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

[RobTitian16]

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

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

[Tom Elliott]

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

Created issue about the spaces being stripped out.

[Wayne Workman]

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

[RobTitian16]

@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

[Wayne Workman]

#wiki worthy

[RobTitian16]

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

[Tom Elliott]

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

[Wayne Workman]

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

[RobTitian16]

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

[Wayne Workman]

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"

[RobTitian16]

@Wayne-Workman Perfect - thanks for this!