Posted on

Altering the character set of a MySQL database

It happens often that I forget to change the default character set of a database to utf8 and so find out late in the development cycle that many of my fields are based on non utf8 character sets (mostly latin1). Then I go in and modify each field in turn using ALTER TABLE [table] MODIFY [field name] [field type] CHARACTER SET [charset]. After some digging I found the ALTER TABLE $TABLE CONVERT TO CHARSET [charset] syntax which converted all fields in a table to a certain character set. I looked for a similar syntax to convert the entire database and found ALTER DATABASE which, unfortunately, only changes the default character set and collation but does not affect the existing tables, fields or data.

So here is a script that repeats ALTER TABLE / CONVERT TO on each table in your database:

#!/bin/bash
 
# parameters...
USER='[your db user name]'
PASS='[your db password]'
DB='[your db]'
CHARSET='[character set (utf8?)]'
COLLATION='[collation (utf8_unicode_ci?)]'
 
# here we go...
QUERY="SELECT table_name FROM information_schema.TABLES WHERE table_schema = '$DB';"
TABLES=$(mysql -u $USER --password=$PASS $DB --batch --skip-column-names --execute="$QUERY")
for TABLE in $TABLES; do
        echo "ALTER TABLE $TABLE ......"
        mysql -u $USER --password=$PASS $DB -e "ALTER TABLE $TABLE CONVERT TO CHARSET $CHARSET"
        #mysql -u $USER --password=$PASS $DB -e "ALTER TABLE $TABLE CONVERT TO CHARSET $CHARSET COLLATE $COLLATION"
done
Posted on

Switching Java versions on a Debian/Ubuntu system

I recently found some issues with the openjdk Ubuntu/Debian default Java implementation. Specifically I had issues with their web start support (javaws). I found that the Sun implementation of Java did not have such a deficiency and the Sun implementation is available through the regular Ubuntu/Debian package sources. I installed the Sun implementation and wanted to switch the default Java to that version.

So what have I found out ?

When you want to switch to the Sun implementation:

sudo update-java-alternatives --set java-6-sun

When you want to go back to the openjdk implementation:

sudo update-java-alternatives --set java-6-openjdk

Notice that once you do any of the above you leave “auto” mode which means that new installation of Java implementation will not switch your default one. If that is what you want then ok. If not you can return to “auto” mode with:

sudo update-java-alternatives --auto

Posted on

Using “noatime” on a running Linux system

When upgrading my Ubuntu system my /etc/fstab got overwritten by the upgrade process. It seems that the new /etc/fstab file did not keep my old preferences for file systems. I didn’t notice this for some time but what I did notice was that my system was sluggish. After some time I recalled that I had previously used noatime as a mount option for all of my hard drives which gave me some more speed and treated my hard drives with a softer touch.

First lets explain what noatime means. atime or Access Time is an attribute stored by all well behaved UNIX file systems for each and every file. It is one of 3 dates stored: meta info modification time – ctime, last modification time – mtime and last acces time – atime. Out of the 3 atime is the most controversial since it means that for every read from the disk there is a write operation. This is one of the worst defaults in your UNIX system.

The solution is just to disable atime altogether. Warning – this may cause some weird applications that rely on atime to break. If you want your system to be as “default” as possible don’t do what I suggest. If you want better performance and hard disk lifetime and on the other hand don’t mind parting ways with one or two misfit applications then this trick is for you.

How do you do it? Just edit /etc/fstab and add noatime at the 4′th column where file system mount options are for any file system you want to avoid access time updating. Reboot your system. Run mount(1) to see that all your file systems are mounted correctly. Enjoy.

What applications break? Actually – I have yet to see an application break because of this change. I have been running with “noatime” for 2 years now and all the applications seem to behave well. If you know of an application that breaks please let me know…

Posted on

Producing MySQL dates on the command line

I often find myself in need of inserting data manually into a MySQL database using some kind of database editor. In a database editor you find yourself manually inserting values into cells of a table and in most of them you are not allowed to enter an SQL expression (the MySQL Query Browser is a prime example). In this case I need some way to generate the current date and time as per the MySQL format quickly on the command line. I will usually use:

alias date_mysql="date +'%F %T'"
Posted on

WordPress and UNIX security (part 2)

In an effort to secure my blog I once again did battle with the mighty Word press. It seems that you can run a perfectly healthy blog with no write permissions by the HTTP server (usually www-data) to your service directory.

What do I suggest? Change owner ship to root on your blog area. When you know that you need to upload stuff to Word press then open the permissions on the relevant folders. This happens when you want to add or remove plug-ins, upload media, themes etc. After the relevant operation clamp down on security again. There are plugins (like xLanguage) that write all kinds of junk log files into the upload folder as part of their operation. Obviously you cannot use these if you want better security.

Advantages: better security.
Disadvantages: A little discomfort and the need to write very simple short script to do the chmod for you. The Inability to use certain brain dead plug-ins.

Posted on

Git and signing

I’m now using git heavily for configuration management and wanted to sign my objects. There were no complete guides out there that I found so here is the list of instructions that I finally arrived at:

  1. If you have a key that you are already using to sign things (email, code, whatever) then you can skip to item 4. If not, then decide on an name, email and pass phrase that you will use to sign your code.
  2. Create a key pair based on the name, email and pass phrase that you chose,. You can do this using gpg2 --gen-key. The program is interactive and very easy to use. The program comes with the gnupg2 package on Ubuntu or Debian. The keys are generated in ~/.gnupg. If you want to see that everything went well the you can list all keys using gpg2 --list-public-keys.
  3. Configure git to use your email. This usually involves editing your git configuration file at ~/.gitconfig and setting the email config option under the user section to your email.
  4. Sign your change when you commit or tag it. If you commit then use “git -s commit”. If you tag then use “git tag -s -m ‘commit message’ [tagname]“.
Posted on

Grep is broken – use perl instead

In the course of running various grep(1) combination to find various defects in source files I ran into inherent grep(1) difficulties. It seems that the regular expression syntax in grep(1) is very limited and so it’s better to use perl to solve these issues as it’s regular expression support is fantastic. A small script can solve all your grepping needs. So here it is. Please comment with fixes and I’ll incorporate them if you want to add features.

#!/usr/bin/perl -w
 
# This is a general script to grep using perl to overcome some of the deficiencies
# of grep(1) grepping...
 
use strict;
use diagnostics;
 
my($pattern)=$ARGV[0];
my($debug)=0;
my($print_filename)=1;
 
for(my($i)=1;$i<@ARGV;$i++) {
        my($filename)=$ARGV[$i];
        if($debug) {
                print "filename is [$filename]\n";
        }
        open(FILE,$filename) || die "Can't open file [$filename]: $!";
        my($line);
        while($line=<FILE>) {
                if($line=~$pattern) {
                        if($print_filename) {
                                print $filename.": ";
                        }
                        print $line;
                }
        }
        close(FILE) || die("unable to close file [$filename]: $!");
}
Posted on

WordPress and UNIX security

Here is what I found the hard way. Sometime you want WordPress to install plugins, themes and all and do not want to pass through an ssh or ftp connection in order to achieve this. Maybe you have your own machine and do not want to run or configure an ssh or ftp server (which is my situation). In that case you can choose to either install plugins and themes by hand (just unzip them to $WORDPRESS/wp-content/[plugins|themes]) or you can give wordpress permissions so it can do it for you. The disadvantage of giving WordPress permissions is ofcourse security since any one hacking into your server could have write access to the wordpress files themselves. In order to avoid this you can keep all your wordpress files owned as root.root (maximum security) and only change permissions for the duration of the installation of the plugin or theme.

Here is how to do this for a completely safe install:


    Turn off your world access to your web server. This can be done by bringing down your external network link by ifdown eth0. This step is only necessary if you are a security freak.
    chown -R [webuser].[webgroup] [wordpress]/wp-content/{plugins,themes}. Substitute webuser and webgroup for your web servers user and group. These are usually www-data on Debian based systems or could be gotten from ps -ef.
    Now perform your installation of plugins or themes from the local machine or from a remote machine if you have not followed the security step above.
    chown -R root.root [wordpress]/wp-content/{plugins,themes}. This will clamp down on security once again.

Please note that some weird WordPress plugins write to the web folder due to their regular operation. If you have such plugins and are worried about security then I urge you to dump them and find substitute plugins. If you cannot dump them then you probably cannot use any sane security practice for your blog.

Posted on

Firefox favicon cache is over caching

I’ve recently tried setting some sites icon to appear as the small icon you see at the tab you are browsing it. This icon is called favicon.ico and is located in your servers root directory which resides in /var/www on standard systems. When changing this icon and reloading the page in Chrome the icon got updated promptly. No such luck with Firefox. The only way I found to do it is to go to the Firefox cache which is at ~/.mozilla/firefox/[some instance of firefox]/Cache and remove the icon. The problem is that the cache folder shows files whose names are hash keys of the cache which means that you need to find the file. Usually something like file * | grep icon can help. If you know the exact size of the icon you are looking for this could help also or if you have the actual icon file you are trying to erase from the cache you can just explicitly run a search for it using cmp(1).

Addendum: A much easier way is just to point your browser at the favicon URL which should update it’s cache just for this URL. In Firefox this worked even without browser restart.
In addition to all of the above in Firefox the bookmarks tool bar could show a different favicon than the tab. For this you can install “Bookmark Favicon Changer” as an extension and set the icon yourself.

Posted on

Purging unneeded packages on a debian system

If you want to remove all packages which are in the “rc” state (means that the package was already removed but only it’s configuration remained) you can use the following command as administrator:

dpkg --purge `dpkg --list | grep "^rc" | tr -s " " | cut -d " " -f 2`

Take care to save configuration files that you need before issuing it.