Useful SQL Commands

I recently needed to take a database with separate date and time columns, convert that combined date-time data into Unix time format (i.e. seconds since the epoch [1st Jan 1970]), and add a new column to the database with that data. It took me a fair bit of googling to get it all working, so here’s my notes.

Sorry this isn’t really in an article format – but I need this written down and available for the future… Also, I wrote a small Java app to read the separate date and time text files and spit out a timezone-correct unixtime text file, which I then added to a spreadsheet containing just the the primary key rows and exported it as tab-separated values suitable for MySQL (or really, MariaDB in this case) import.

Show the table structure for the ‘accidents’ table

Count unique values in the UNIX_TIME column in the accidents table

Find duplicate values in a column (cannot make column a primary key if there are dupes!)

Make a column be the primary key for a table

Drop the UNIX_TIME column

Add a new column UNIX_TIME to the accidents table after the last column (STAT_DIV_NAME)

Add an empty column with no default value

Create a temporary table in memory with a single column called UNIX_TIME which is of type BIGINT

Import from local CSV file

I created a tab-separated CSV file which had just the ACCIDENT_NO as primary key and the UNIX_TIME field. We can then import that data into the temptable with:

Load all the single-line data values fromthe UNIX_TIME.txt file into the UNIX_TIME column of our temp table

Merge the temptable with just the ACCIDENT_NO and UNIX_TIME into the accidents table using ACCIDENT_NO as the primary key

So in this particular instance where I had a partial update, I was missing 13,375 UNIX_TIME values!

Make sure this worked by setting all the UNIX_TIME values in the accidents table to zero, and then re-importing

Now finally, we can drop the in-memory temptable!

Export the database

To export the database we can use the mysqldump utility (this is NOT within the MySQL command line!):

Then to import this dump, let’s say we don’t have a govhack2015 database

Followed by (in the MySQL command line):

It all works! =D

How to stop a MySQL action which is taking forever… (like when I bodge a merge) – in a different MySQL commandline interface

How To: Shrink/Compress a VirtualBox Windows Guest Machine

When you create a VirtualBox virtual-machine, you get the option to choose either a fixed sized hard drive (in which case the entire amount of space is allocated and fixed immediately), or a dynamically expanding hard drive for the virtual machines OS and file storage (i.e. you specify a maximum size for the hard drive, it starts at 0 bytes, and increases as necessary up to the maximum you gave it on creation – at which point the drive is full).

Linux and Windows via VirtualBox

This is all well and good, but the problem with dynamic storage is that although it’s more than happy to increase in size, it doesn’t come down in size again when data is removed. So, to give an example, if you created a 10GB dynamic disk for a virtual machine it starts off at 0 bytes, you install a 1GB operating system and the drive is now 1GB in size (and hence taking up 1GB of space on your actual hard drive), you rip a 4.7GB DVD to the virtual drive which makes its size now 5.7GB, you delete the DVD rip so only the OS remains – and you might think that the “dynamic” drive will automatically shrink back down to 1GB, only it doesn’t. You’re holding on to 4.7GB of unrecoverable* bloat. Lucky you… =P

You could rip another DVD and re-use that space without the drive expanding any further, but really, it’s just going to increase and increase, and you’ll know in your heart of hearts that when you’re running low on disk space you could really do with that space back to your real hard drive. In VMWare you can compact the drive image as a menu option, but in VB we have to do a three step process… So, shall we?

* = unrecoverable unless you jump through the below 3 hoops, or create a new dynamic drive image, expand it to a size just over the size of your data on your original drive, then raw-copy the data across, that is… IMHO the steps below are easier!

A-B-C, Easy As…

  1. Defrag your Windows guest machine
  2. Now we need to replace all the “blank” (but still taking up space!) areas of our drive with zeros so we can recognise them to be stripped out later. Thankfully, this is really easy. Just download free (and tiny – 47KB) command-line utility SDelete from Microsoft and run it from within your virtual Windows guest machine with the following command (the -c switch is important!):
  3. Once that’s finished running shut down your virtual machine, navigate to the folder where your virtual machine hard drive is (such as ~/.VirtualBox/HardDisks) then from your host system run the following command to compress the hard drive down to a more reasonable size:

    So if your virtual machine name (and thus by default the hard drive name) was “XP_Client_1”, then you’d use:

With that done I trimmed down an excessively bloated 25GB .VDI of Windows 7 into a still excessively bloated 15GB – but that’s just in the nature of Microsoft OS’s… =P

Update: If you get an error stating things along the lines of:

then you can fix it like this:

  1. Detach the drive from your virtual machine,
  2. Edit the file ~/.VirtualBox/VirtualBox.xml and remove all lines with the drive you want to compact mentioned in the HardDrives section (Note: be careful you don’t delete the virtual machine entry itself from the MachineRegistry section! Only remove the drive from the HardDrives section.),
  3. Now you’ll be able to compact the drive, and when it’s done you can re-attach the drive to your virtual machine. Good as old! =D

Snap Happy

Another thing you can do to decrease the disk usage of VirtualBox machines is get rid of all your snapshots if you don’t need them anymore. Each snapshot is basically an entire disk image which you can roll back to, so if you have Windows 7 installed it’s about 7GB or so after a fresh install, if you then put on 500MB of patches and take another snapshot you’re storing another 7.5GB. If you then install Office or something and that takes up 2GB and take yet another snapshot you’re burning through yet another 9.5GB, so we’re up to 23.5GB already for a single 9GB drive!

You should definitely be careful when merging snapshots into the main image (basically getting rid of the snapshots), as it has the potential to break, but more likely it has the potential to confuse and cause you to throw away data you didn’t mean to. This is because of some particularly ambiguous and misleading phrasing used in VirtualBox circles – the crux of the matter being:

  • When you restore a snapshot, it will throw the current state and/or any subsequent snapshots away and leave the machine in the state defined by the snapshot you’re restoring. This can mean a lot of changes which currently exist in the image being undone, and lot of files disappear, for example – the only copy of important documents created since the snapshot you’re restoring was made. Use with care.
  • When you delete a snapshot, it will actually merge the current state of the machine into the snapshot before removing that snapshot and leaving the machine at it’s current merged state but without the snapshot existing…

Yeah, I know it’s confusing, so just be careful, okay? If you’ve got the space available just take a copy of the .VDI file from the HardDisks folder AS WELL AS a copy of the snapshots by copying the folder with the name of your VM from the Machines folder, and then merge in the snapshots – this way if it all goes nuts you can’t throw away the knackered copy and replace it with your pristine pre-merge copies.


Credits: Thanks to Damien for his article at MakeTechEasier for the initial information (you can also find out how to compress Linux guests there too, but just be aware that the technique he outlines involves cloning the drives then shrinking and re-importing them) and to Alphatek’s article for the simplification!