Renaming A MySQL Column With A Foreign Key Constraint

This is more of a note to myself, but blogged in the hope it will help someone else using MySQL.

I had to rename a column in a MySQL database earlier. However the column had a foreign key on it, so I was getting the dreaded error 150, warning me of a foreign key constraint.

The soltuion is to first drop the foreign key, then change the column, finally adding the constraint back in.

The SQL will look something like this…

ALTER TABLE `mytable`
DROP FOREIGN KEY `mytable_oldname_fk`,
CHANGE COLUMN oldname newname INT(11) DEFAULT NULL,
ADD CONSTRAINT `mytable_newname_fk` FOREIGN KEY (`newname`) REFERENCES `anothertable` (`id`)

Utf8 And MySQL Command Line

I’ve been having to use a lot of utf8 recently, and being old school I still use the command line a lot.

One project has been importing a lot of international data into a mysql database.

The database is in utf8, but when I used the command line, non latin1 data was coming back corrupted.

It turned out that the command line doesn’t automatically detect the character set, so it was printing as if it was latin1. There is a flag that can be passed in called –default-character-set and this can be set to utf8. Once set, utf8 data is correctly displayed on my terminal.

mysql --default-character-set=utf8 testdatabase

This is also useful when piping in utf8 data.

Parsing JSON Boolean Values With Perl

A project I’ve been working on recently has meant me using both the JSON and JSON::Streaming::Reader Perl modules.

The JSON::Streaming::Reader is great for processing large files, and produces results that are compatible with the JSON module. Compatible… well almost. They both handle booleans in a slightly different way, and that caused me a few problems as I was mainly testing against the JSON module.

In JSON::Streaming::Reader boolean values become references to either 1 or 0.

The JSON module returns a JSON::Boolean (or if using XS – JSON::XS::Boolean) object that is either JSON::true, or JSON::False. These are overloaded to act as 1 or 0.

If you look at a Data::Dumper output, here are how they compare for a “true” value.

JSON::Streaming::Reader

$VAR1 = {
'OnSale' = 1
};

JSON

$VAR1 = {
'OnSale' => bless( do{(my $o = 1)}, 'JSON::XS::Boolean' )
};

For “false” the 1’s become 0’s.

When using the JSON module, because the JSON::Boolean object is overloaded, you can test for truthfullness by simply doing…

if ($decodedjson->{'OnSale'}) {
## This is true.
}

However using JSON::Streaming::Reader this won’t work, as the reference will always evaluate to true. In this case the value must be dereferenced first before testing.

if (${$decodedjson->{'OnSale'}}) {
## This is true.
}

The good news is that this same block of code will work when also using the JSON module, so in future, when testing decoded boolean values from JSON data, always dereference first!

Using AppleScript To Send Key Presses

I’ve taken a break from my regular JavaScript, PHP and Perl scripting, and taken a look at AppleScript instead.

I’ve been playing a Flash game in a browser, and in this game you have to run by continuously and rapidly pressing the ‘a’ key to boost your speed. Readers of a certain age will know this is very similar to Daley Thompson’s Decathalon. The first few goes went OK, but weren’t spectacular as my arm started to get tired. There must be a way of automating this… right?

Yes there is, AppleScript on my Mac to the rescue. I can create a script to send key press events to Safari, which very kindly passes it over to the Flash game if that is in focus.

Firstly you need to tell AppleScript to target Safari.

tell application "Safari"
activate
end tell

Now we just need to send events claiming that the ‘a’ key has been pressed, after the activate command.

tell application "System Events"
repeat 50 times
keystroke "a"
delay 0.05
end repeat
end tell

That code will press the “a” key every 0.05 seconds 50 times. That’s 20 “a”‘s a second, for 2 and a half seconds.

Save the code under a suitable filename and call it using the osascript command.

Using Data::Dumper With Log4perl

I’m currently working on some Perl code for a change, and as part of a refactor I’ve added log4perl in to support my debugging.

I tend to overuse Data::Dumper as it’s so useful for seeing what is going on inside an object, and I had been passing the results directly to log4perl. This is actually very inefficient if I changed my logging level to turn off the dumps, as Dumper would still run, but the results would just be ignored.

Thankfully log4perl supports filters, so I can use this to defer the Dumper operation until it actually needs to be output.

get_logger(ref $self)->trace({
  filter => &Data::Dumper::Dumper,
  value  => $data
});

In the above example, I’m getting the singleton instance of log4perl for my current class, then calling trace, and saying I’d like the $data passed through Data::Dumper::Dumper. This will only be filtered if I have trace level monitoring enabled, if not it will be silently ignored and not executed, saving me valuable resources.

Disappearing Hosts On A Mac

I’ve been doing some local development work on my Macbook Pro and I noticed that my /private/etc/hosts file kept getting wiped.

I use Cisco’s AnyConnect Secure Mobility Client to connect to the office VPN and this was causing difficulties.

It turns out that the file to edit if you use a Cisco VPN product is /private/etc/hosts.ac as this file is used to overwrite /private/etc/hosts each time you connect in.

Updating A List In JQuery Mobile

Are you using jQuery Mobile and trying to manipulate a listview?

I was trying to do this earlier, adding new <li> elements to a <ul data-role="listview">. However, none of the new elements were looking correct, or having the right styles applied.

The solution is to tell jQuery Mobile to refresh the list and apply the styles.

If your <ul> list has an id of wibble you could use the following…

$('#wibble').listview('refresh');

Your list will now have the correct styling applied.

Centering A Header Image In jQuery Mobile

I needed to replace the header text in a jQuery Mobile application with an image.

I first tried wrapping the image in the <h1> tag, but that adds quite a large margin and cuts off some of my image.

The solution I used was to instead add a wrapper div that centers everything in it. My header ended up looking like this…

Header

center-wrapper is a custom style defined like this…

.center-wrapper {
  text-align: center;
}
.center-wrapper * {
  margin: 0 auto;
}

There may be a better way to do this, but this works for now.

SSH To An Ubuntu VirtualBox From A Mac

I have been setting up an Ubuntu Server instance using VirtualBox on my Macbook Pro for some development work. I thought I’d share how to get ssh working on the virtual server once you have it installed.

Firstly you need to make sure you have openssh-server on your Ubuntu instance.

On the Ubuntu Server…

sudo apt-get install openssh-server

If you change networks a lot, you’ll probably want to install a static IP you can always connect to safely to access your Ubuntu Server. To do this…

On the Ubuntu Server, edit /etc/network/interfaces, and add the following…

auto eth1
iface eth1 inet static
address 192.168.56.10
netmask 255.255.255.0

Now on your Mac…

Power down the VM

Go to network settings

Add adapter 2
– Attached to Host-only Adapter
– Name vboxnet0

Restart the VM

You should be able to connect to your Ubuntu Server now on 192.168.56.10 from your Mac.

ssh username@192.168.56.10