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

Converting videos to xvid on Linux

I wanted to convert some video files on my Linux system to the xvid codec so that I could see them on my PS3. The solution I found was using the mencoder package.

#!/bin/bash
 
# this script converts videos given to it to the xvid codec, IN PLACE,
# this means it replaces the original files...
 
for x in "$@"; do
    echo "$x"
    y="$x.tmp"
    mencoder "$x" -ovc xvid -oac copy -xvidencopts fixed_quant=4 -o "$y"
    ret=$?
    if [[ $ret -eq 0 ]]; then
        mv "$y" "$x"
        ret=$?
        if [[ $ret -ne 0 ]]; then
            echo "problem moving file $x"
            break
        fi
    else
        echo "problem converting file $x"
        break
    fi
done
Posted on

Finding thread info on Solaris

As part of a crusade to find bugs in a C++ program running on a Solaris system I needed to find out information about all the threads belonging to a certain process.

The program is written the way it is because you rarely need thread info about all threads running in a system but rather threads limited to a certain process. A different way to get this info is through the /proc file system but unfortunately (or fortunately ?!?) files in this Solaris file system usually have binary content as opposed to the textual content that one usually finds on a Linux system.

#!/usr/bin/perl -w
 
# Give this script the name of a process and it will show you thread
# infomation about your process...
 
 
use strict;
use diagnostics;
 
 
if(@ARGV<1) {
    die("usage myps.pl [process names...]");
}
 
 
for(my($p)=0;$p<@ARGV;$p++) {
    my($pname)=$ARGV[$p];
    print "showing diagnostics information for process $pname\n";
 
 
    # first lets find out the pid of the process
    my($pid)=`pgrep $pname`;
    chop($pid);
    print "The process id of the process is $pid\n";
 
 
    # now lets print all the thread info for that process...
    my(@lines)=`ps -eL`;
    for(my($i)=0;$i<@lines;$i++) {
        my($line)=$lines[$i];
        my(@fields)=split(" ",$line);
        if($fields[0] eq $pid) {
            print $line;
            #print(join('-',@fields));
        }
    }
}