Change Image ID Number

[Wayne Workman]

@RobTitian16 @Tom-Elliott I wrote a BASH script that is working. I’ll make a github project for it soon but here it is:

#!/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 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"

lowestID=$(mysql $options "$selectLowestImageID")

#If the lowest image ID is greater than 1, we can renumber all images sequentially.
if [[ "$lowestID" -gt "1" ]]; then
    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
fi

Sample output:

[root@fog-server ~]# ./renumberFogImages.sh 
-------------------
Attempting to change Image ID 2 to 1
Attempt completed
-------------------
Attempting to change Image ID 3 to 2
Attempt completed
-------------------
Attempting to change Image ID 4 to 3
Attempt completed
-------------------
Attempting to change Image ID 5 to 4
Attempt completed
[root@fog-server ~]#

[Tom Elliott]

@Wayne-Workman you most certainly could use a count so long as the iteration of the count matches the new item. You adjust the statement to update all items matching a particular I’d. For example if I’d is 4 and is now set to 1. You would do an update like:
update imageGroupAssoc set igaImageID=1 where igaImageID=4

[Wayne Workman]

@Tom-Elliott That’s what I did in the bash script below.

[RobTitian16]

@Wayne-Workman Thanks for this, although when running the script I receive the following errors:

The blanked out part of line 23 is the password. Yet when running:

mysql -h localhost -u root -p"password" -D fog

I’m able to log-in perfectly fine.

[Wayne Workman]

@RobTitian16 Edit the script, at the top is username and password and host. Set those to what is needed. If root works, use root and “password” and set the host to “localhost”

[RobTitian16]

@Wayne-Workman Indeed, I set those as root and “password” with the host being “localhost”. It gave me those errors in the previous screenshot.

[RobTitian16]

@Wayne-Workman

[Wayne Workman]

@RobTitian16 line 17 is actually the if argument. Try to put a space between the [[ and ]] brackets and the inner line between them. Like:
if [[ $snmysqlhost != "" ]]; then

[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