Change Image ID Number

[Wayne Workman]

@RobTitian16 What is the output of this? Just so we know what we’re working with here.
SELECT imageID FROM images ORDER BY imageID;

[RobTitian16]

@Wayne-Workman

I also restored the FOG VM from a checkpoint before attempting this, so we’re essentially working with a FOG server before I started dabbling into this SQL side of things.

[Wayne Workman]

@RobTitian16 Here’s what I think is happening.

When I did this at home and moved all IDs back by seven, I think I only had two or three images. When I moved the image IDs down at work by 80, we had 15 or 20 images.

So, if my image IDs were 7 8 9 10 and I subtract 7 from them all, you see that the result for each isn’t a duplicate of any existing.

If my image IDs were 80 81 82 83 84 85 86 87 88 89 90 ... to 100 and I subtracted 80 from each, the answer for each would not be a duplicate of any existing.

And SQL processes one row at a time.

Because your image IDs start at 4, and end at 30, if you subtract 4 from each, the answers are duplicates of existing.

However, you can still do what you are wanting to do, you’re just going to have to do more work, but this is fine. It’s just repetitive.

Let’s test re-numbering the first image in all the correct spots and see how that goes. If it works, you can re-number all images individually using the same technique.

UPDATE images SET  imageID  = 1 WHERE imageID = 4;
UPDATE imageGroupAssoc SET  igaImageID  = 1 WHERE igaImageID = 4;
UPDATE hosts SET  hostImage  = 1 WHERE hostImage = 4;

The above SQL changes the image ID 4 to 1 in all the needed spots. If this works fine, you can repeat this on the next image. The next image ID is 6, you would change it to 2. See?

[Tom Elliott]

@Wayne-Workman You might be better doing this:

SET @count = 0;
UPDATE `images` SET `images`.`imageID` = @count:= @count + 1;
ALTER TABLE `images` AUTO_INCREMENT = 1;

This will reorder without trying to guess numeric values.

The last statement will reset the auto_increment value to the next highest in the list (essentially it will put it at whatever the max id is + 1).

[Tom Elliott]

Additionally, this could use some refinement to auto adjust the image group assosiacations and host ids, but for quick and dirty reordering this will work.

[Wayne Workman]

@Tom-Elliott That’s nice, but there are other areas that need re-ordering. imageGroupAssoc and hostImage

[Tom Elliott]

@Wayne-Workman right but you can follow the same procedure to reorder the items on image group association.

For Host image id associations it may be a bit rougher.

[Tom Elliott]

@Tom-Elliott I’ll try to come up with a more intuitive approach that isn’t relying on a mathematical scale to increment things. Of course testing will be needed.

[Wayne Workman]

@Tom-Elliott Right because there are duplicate image IDs in the hosts table, you can’t use a count whatsoever there. I’m not liking this method at all because of imageGroupAssoc and hostImage not being taken care of in this answer.

[Wayne Workman]

@Tom-Elliott Of course BASH could handle this easily.

[Wayne Workman]

@RobTitian16 said in Change Image ID Number:

@Wayne-Workman Sorry, it was a typo when I was writing it up on here. Here’s the screenshot to confirm what I’ve typed in:

The query for the hosts table probably succeeded for you in the above attempt. If you’ve not tried anything else since that, It can be undone with
UPDATE hosts SET hostImage = hostImage + 4;
But if you’ve started doing other stuff, you shouldn’t do this. Communicate with us, tell us what’s going on.

Also, I’m working on a BASH script to re-order Image IDs.

Good thing you have snapshots - you might just want to revert to the snapshot and sit tight for an hour or so.

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