Thursday, November 27, 2008

MySQL 5.1 goes GA

I was quite surprised when I browsed dev.mysql.com and found that 5.1 was already on GA. There was no mention of this in the mysql.com site. It seems that they're holding off on the announcement until all the mirrors have it because some of the Asian mirrors still do not have it. Most of the US mirrors though have it. We'll be transitioning to this release to take advantage of partitioning.

Friday, September 12, 2008

iReport Default Date Parameter

If you want to set a default date for a jasperreports parameter, you can enter the following in the default value expression of the parameter.

new Date()

However, if you wanted the default to be relative from today, for example, 3 days ago, you will need to do go outside of the standard java date libraries (Date, Calendar) because JR parameter does not allow you to have multiple statements. You can write your own utility package or you can use Joda, a replacement library for the JDK date and time library.

To use Joda in iReports, you need to extract the jar file and add it to Options -> Classpath. You may also drop the jar file in iReports' lib directory. You will also need to add this library to your application or report server.

For setting a default date 5 days prior from today, use this:

new org.joda.time.DateTime().minusDays(5).toDate()

or this if you want the time part set to the beginning of the day:

new org.joda.time.LocalDate().toDateTimeAtStartOfDay().minusDays(5).toDate()

or if you want the parameter to be relative to another parameter:

new org.joda.time.LocalDate($P{DateParam}).toDateTimeAtStartOfDay().minusDays(5).toDate()

Joda has other methods to get a relative date: minusWeeks, minusMonths, minusYears, plusDays, plusWeeks, plusMonths, plusYears. I think you can also use Groovy to write an expression to get a relative date but that's for another time. It would probably be as simple as new Date() - 5. Post a comment if you came up with a better way.

Saturday, August 30, 2008

selective table restore from mysqldump

I recently had to restore a corrupted table that had resulted from a full disk. I had been using automysqlbackup as my backup script. Each database had a corresponding backup file. It would have been easy to use a text editor to edit out the tables that you didn't want to restore but this database had grown to 1G.

STEP 1: Restore table structure

If the table structure is corrupted, you need to retrieve the CREATE TABLE statements from the backup. To do this you can use grep:

zcat backup.sql.gz | grep -A 20 'CREATE TABLE `tablename1`' > tablestructure.sql

This command will create tablestructure.sql containing the CREATE TABLE statement and 20 lines after that. You may have to change the parameter from 20 to whatever to get the entire statement.

Once tablestructure.sql had been edited to only contain the proper CREATE TABLE statement, you may now restore the table structure.

mysql -u user -p dbname < tablestructure.sql

STEP 2: Extract and restore the table data

This command will create a sql file for the table.

zcat backup.sql.gz | grep 'INSERT INTO `tablename1`' > tabledata.sql

If the resulting sql file is small enough, you may load the table straight back into the database.

mysql -u user -p dbname < tabledata.sql

OPTIONAL: If you have a huge table and want to partially restore or batch things, you may want to split the table data into multiple lines.

split -l 100 tabledata.sql

This will create multiple files of 100 lines each named xaa, xab, xac, etc. You may then load each file separately.

If some rows are already on the table and you just don't want the INSERT to fail on a duplicate primary key, you can use sed to add the IGNORE option to the INSERT statement. The command below simply does a search and replace on the source file and writes it to a new file.

sed 's/INSERT/INSERT IGNORE/' < tabledata.sql > newtabledata.sql

That should do the trick.

Wednesday, August 13, 2008

Setting up Corporate Ubuntu Desktops

This guide has been updated for Ubuntu Hardy 8.04.1. The following is the official installation guide:

[WWW] https://help.ubuntu.com/8.04/installation-guide/i386/index.html

The preseed method will enable a more automated install. You will want to enter the proxy a local apt-cacher server if you have one to speed up the updates and installs of new packages.

Post Installation Tasks

Updating and Installing Packages

  • In Synaptic, configure proxy as needed. If you have an apt-cacher service running on the proxy server, it will make your updates much faster if the packages have already been downloaded by a prior machine.

  • Click on Reload

  • Click on Upgrade

  • Install ubuntu-restricted-extras

  • Install ttf-liberation

  • Apply

Configure Desktop to Remove Annoying Stuff

  • In System -> Administration -> Login Window -> Accessibility, uncheck the sounds

  • In System -> Preferences -> Sounds -> Sounds, uncheck Play System Sound to remove annoying sounds

If you want to change the desktop wallpaper for everyone:

  1. In the desktop, type Alt-F2

  2. gksudo gconf-editor

  3. Enter your password if prompted

  4. Choose /desktop/gnome/background from the tree on the left

  5. Change the picture_filename key to (e.g.) usr/share/backgrounds/ubuntu-calendar.png

  6. Right click and Set As Mandatory or Set As Default. Mandatory means users cannot change the wallpaper.

You will also want to change the default fonts to the liberation fonts. It makes the menus and fonts in your applications more compact.

The scripts below will set the default fonts and settings without going through the GUI. Just change the mandatory to defaults if you want to allow users to change it.

sudo gconftool-2 --direct --config-source xml:readwrite:/etc/gconf/gconf.xml.mandatory --set /apps/nautilus/preferences/desktop_font --type string "Liberation Sans 9"
sudo gconftool-2 --direct --config-source xml:readwrite:/etc/gconf/gconf.xml.mandatory--set /desktop/gnome/interface/document_font_name --type string "Liberation Sans 9"
sudo gconftool-2 --direct --config-source xml:readwrite:/etc/gconf/gconf.xml.mandatory--set /desktop/gnome/interface/font_name --type string "Liberation Sans 9"
sudo gconftool-2 --direct --config-source xml:readwrite:/etc/gconf/gconf.xml.mandatory--set /desktop/gnome/interface/monospace_font_name --type string "Monospace 9"
sudo gconftool-2 --direct --config-source xml:readwrite:/etc/gconf/gconf.xml.defaults --set /desktop/gnome/sound/event_sounds --type bool "false"

Adding a New User

  1. System -> Administration -> Users and Groups, Unlock (You will asked for your password.)

  2. Add User as Desktop User Profile. This user will not be able to install new packages.

Adding a Printer

  1. System -> Administration -> Printing

  2. New Printer -> HP JetDirect

  3. Enter the IP of the network printer

  4. Change Printer Options to a default media size of Letter instead of A4 if you are in the Philippines.

Monday, February 04, 2008

Pentaho Quick Install

Pentaho has a preconfigured install package that can be used to evaluate the platform. The default install will only work if it is run on your own machine. There are two things that you'd want to do if you want others to test the platform.

1. Allow access from other machines on the LAN.

You need to edit web.xml file located at

%PCI%/jboss/server/default/deploy/pentaho.war/WEB-INF/web.xml

where %PCI% is the folder where you uncompressed the Pentaho preconfigured install.

Look for the string base-url. http://localhost:8080/pentaho/ Change the value into an IP address or the computer name that will resolve to it.

2. Change the default port 8080.

Sometimes port 8080 is already being used by another application, possibly Tomcat. Pentaho has a web container using port 8080 that may conflict with the existing application.

You need to edit server.xml located at:

%PCI%/jboss/server/default/deploy/jboss-web.deployer/server.xml

Look for the string 8080.



Change the port number to your desired number. You may now restart the Pentaho BI platform.