Compare Two MySQL Database Structures Using mysqldump and diff

To compare two mysql databases in linux/osx you can use a very simple script that calls the “mysqldump” and “diff” commands to output the difference in structure of two databases to an output file. In this case I was comparing my local database with another at IP address 1.2.3.4. You will be prompted for passwords but to avoid this you can add “-pYOURPASSWORD” (less secure):

mysqldump --no-data --skip-comments --skip-extended-insert --host=127.0.0.1 -u root -p DATABASENAME>file1.sql
mysqldump --no-data --skip-comments --skip-extended-insert --host=1.2.3.4 -u root -p DATABASENAME>file2.sql
diff file1.sql file2.sql > diffoutput.txt
rm -f file1.sql
rm -f file2.sql

Forward ports in Ubuntu Server 12.04 using ufw

We needed to forward port 3307 to port 3306 to get around a new company wide firewall restriction blocking access to port 3306 (our MySQL server). It was a pain to find how to get port forwarding working in Ubuntu Server 12.04, which uses “ufw” as a front end to “iptables”. I couldn’t get it working without specifically forwarding to my IP, which I shouldn’t need to do (but at least it works).

This will forward port 3307 to 3306 so you can connect to your.ip.add.ress:3307 and have it automatically connect to a server (such as MySQL) on port 3306.

To do this you need “ufw” to be enabled, which you can check with “sudo ufw status”.

Make sure the ports you need are allowed:

sudo ufw allow 3307

Now open up “/etc/ufw/before.rules”:

sudo nano /etc/ufw/before.rules

Go to the bottom of the file and put:

# nat Table rules
*nat
:PREROUTING ACCEPT [0:0]
-A PREROUTING -i eth0 -p tcp -m tcp -d your.ip.add.ress –dport 3307 -j DNAT –to-destination your.ip.add.ress:3306
COMMIT

Restart “ufw” to make sure everything worked ok:

sudo ufw disable
sudo ufw enable

Now when you connect to port 3307 it will forward to 3306.

Fix iptables error “Loading additional iptables modules: ip_conntrack_netbios_n[FAILED]” in XenServer 6 (and others)

As part of setting up and testing routing rules in XenServer 6 I used the built in “lokkit” tool to temporarily turn off the firewall. Unfortunately, just opening the tool overwrote our custom “/etc/sysconfig/iptables” rules and cleared the file. This wasn’t a huge problem as we had a backup and just recreated it (you shouldn’t really be editing iptables manually anyway). On restarting iptables using “/etc/init.d/iptables restart” we received the error:

Loading additional iptables modules: ip_conntrack_netbios_n[FAILED]

This is very easy to fix and is due to a setting in “/etc/sysconfig/iptables-config” which was set by “lokkit” by default. The issue is that iptables is trying to load the “ip_conntrack_netbios_ns” kernel module, which doesn’t exist by default in XenServer (and other linux distributions).

Find the following line at the top of “/etc/sysconfig/iptables-config”:

IPTABLES_MODULES=”ip_conntrack_netbios_ns”

And set to:

IPTABLES_MODULES=”"

A few people have said to also set “IPTABLES_MODULES_UNLOAD” to =”no”:

IPTABLES_MODULES_UNLOAD=”no”

But I found that ”/etc/init.d/iptables restart” still failed so I left it as “yes”. You may be able to set to “no” so try this first.

This will stop the missing kernel module being loaded and allow iptables to start properly.

If you get any other errors about loading modules when restarting iptables, check “/etc/sysconfig/iptables-config” isn’t trying to load something in “IPTABLES_MODULES=” that you don’t have installed.

Installing a Network Shared HP LaserJet 4050 in Windows 7 x64

This is fairly specific but was difficult to find a fix for. The HP universal print driver cannot be used when installing a LaserJet 4050 that is not attached directly to your PC. The problem is down to the plug-n-play ID for the printer not being found and the driver not installing correctly. Thanks to the guys on the HP Support Forums I found a solution.

Initially I tried just installing the universal printer driver and adding a network printer (with the correct machine and printer name) but it couldn’t find the driver. I even manually selected the download folder for the driver “C:\HP Universal Print Driver\pcl5-x64-5.5.0.12834″ but it wouldn’t have it.

Next I tried adding a local printer on LPT1 (with no physical printer present) and using the same driver. This didn’t work.

The solution is to add a local printer using LPT1 then click the “Windows Update” button, wait a few minutes then select Manufacturer “HP” and Printers “HP LaserJet 4050 Series PCL 5″. This will install a working driver, which is then used automatically when you add a network printer.

Use a Spring InitBinder to Resolve Type Mismatch and Bind Exceptions in POST from Spring Framework MVC Forms to Controller Actions

As a follow up to the previous article on binding entities and their children to form objects in Spring Framework it’s important to know how to submit the values of form objects in a standard form back to the controller. In the previous example we had a “Parent” entity, with a dropdown “select” on the form where you could choose one of the available “Children” objects. We also had a “Description” textbox that allowed the user to type in a suitable description.

The actual form, a very simple interface with both these elements and a submit button looks like:

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form" %>

<c:url var="saveParentUrl" value="/parent/save" />
<form:form modelAttribute="parent" method="POST" action="${saveParentUrl}">
Description:  <form:input path="description"/>
Child:  <form:select path="child" id="child" items="${children}" itemValue="id" itemLabel="name"/>
<input type="submit" value="Save Parent" />
</form:form>

In this example, when the user clicks on the “Save Parent” button the form will be posted to the “parent” controller action “saveParent” at “/parent/save” where we can save the updated “Parent” object.

Now the problem is that the POST header only contains text strings, not a full description of the actual “Child” object we have chosen for “Parent”. When you try to save the “Parent” object in the controller action the result is an exception,  ”org.springframework.validation.BeanPropertyBindingResult”. The error message itself tells you exactly what is happening:

default message [Failed to convert property value of type 'java.lang.String[]‘ to required type ‘models.Child’ for property ‘Child’; nested exception is java.lang.IllegalStateException: Cannot convert value of type ] to required type [models.Child] for property ‘Child’: no matching editors or conversion strategy found]

The message tells you that Spring can’t automatically convert from the POST string value of the “child” select option (actually “id” in our case) to an actual “Child” object. We need to convert this string to a “Child” object in order to save the updated “Parent” entity. This is easily achieved using an “InitBinder” method within the controller class and a “@Validated” annotation on the “save” action’s “Parent” “@ModelAttribute” argument.

The code for the controller action looks like the following. Note that in this example I am using a “parentService” to pull the protocol to be updated from a “Parent” stored in session. It is likely that you will have something different or even pass in the id as part of the POST:

// ParentController.java
@RequestMapping(value = "/save", method=RequestMethod.GET)
public ModelAndView saveParent (@Validated @ModelAttribute("parent") Parent formparent, HttpServletRequest request )
{
    ...
    // find parent to update in database using parent service
    Parent parent = parentService.findById(sessionparent.getId());

    // set parent description and Child based on user input and save
    parent.setDescription(formparent.getDescription());
    parent.setChild(formparent.getChild());
    parentService.saveParent(parent);
    ...
}

The “formparent” is the “Parent” object passed in POST and mapped by Spring using the “@ModelAttribute” annotation. The “InitBinder” required to map the “Child” id passed in as part of POST to an actual “Child” object is:

@InitBinder
protected void initBinder(HttpServletRequest request, ServletRequestDataBinder binder) throws Exception {
    binder.registerCustomEditor(Child.class, "child", new PropertyEditorSupport() {
    @Override
    public void setAsText(String text) {
        Child ch = childService.findById(Long.parseLong(text));
        setValue(ch);
    }
    });
}

What this does is register a binder for the “child” object when it is passed from the form to Spring that converts the value to an actual object. By overriding the “SetAsText” function I use a “childService” service to retrieve the correct “Child” entity based on the text value passed in POST. Spring can now understand and convert the POST values to actual objects and the “Parent” entity can be safely modified by the “saveParent” controller action mapped to “/parent/save”. More information and some alternative methods are available at Develop and Conquer and Empire5.

Quickly add a Virtual Host to Apache Tomcat to Map URLs to Java Web Applications

Adding a virtual host to Apache Tomcat is really easy and just involves pointing the address at the right directory. This means you can map web addresses pointing at your server IP to Java web applications running on Apache Tomcat. This assumes you already have a hostname such as “websiteaddress.com” pointing to your server with an IP (for example) of 1.2.3.4.

Just modify you server.xml file (in our Ubuntu Server tomcat7 setup in “/usr/share/tomcat7/conf/server.xml”) to include the following at the end of the file in the “Engine” element after the “Host” element for localhost:


      <Host name="localhost"  appBase="webapps"
            unpackWARs="true" autoDeploy="true">

        <!-- SingleSignOn valve, share authentication between web applications
             Documentation at: /docs/config/valve.html -->
        <!--
        <Valve className="org.apache.catalina.authenticator.SingleSignOn" />
        -->

        <!-- Access log processes all example.
             Documentation at: /docs/config/valve.html
             Note: The pattern used is equivalent to using pattern="common" -->
        <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs"
               prefix="localhost_access_log." suffix=".txt"
               pattern="%h %l %u %t &quot;%r&quot; %s %b" />

      </Host>

      <Host name="websiteaddress.com" appBase="/usr/share/tomcat7/webapps/myapplication">
        <Context path="" docBase="."/>
      </Host>

Note here that the web address is http://websiteaddress.com and is being resolved to the application at “/usr/share/tomcat7/webapps/myapplication”. More information about this “Host” element is available at the Apache Tomcat website.

Binding Entities and Their Children to Java JSP Form Elements in Spring Framework MVC

Binding objects to JSP form elements is actually quite easy in Spring Framework using the “modelAttribute” and “path” tags. Our scenario was the use of two Hibernate POJOs comprised of a parent entity “Parent” and a single “Child” entity which needed to be displayed on screen with appropriate form elements (“input” and “select”). The entities were annotated for use as part of a larger application but the basic structure was:

// Parent.java
public class Parent{
  private Int id
  private String description
  private Child child
  ...
}

// Child.java
public class Child{
  private Int id
  private String name
  ...
}

To display these objects on screen as part of a form in Spring Framework MVC we used the following “ParentController” controller with a “showparent” action. The action uses a “parentService.getParent(id)” method (not shown) to retrieve a single parent entity based on “id” and attach it to the form using a Map “model” and a returned “ModelAndView”. A “childService” is used to get the list of every possible child entity (also not shown) and this is also added to the “ModelAndView”:

// ParentController.java
@RequestMapping(value = "/showparent", method=RequestMethod.GET)
public ModelAndView protocolList(HttpServletRequest request)
{
	...
	Map<String, Object> model = new HashMap<String, Object>();
	model.put ( "children", childService.getAll());
	model.put ( "parent", parentService.getParent(id));
	return new ModelAndView ( "showparent", model );
}

The form itself is very simple and uses Spring binding to automatically create and populate input fields:

<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form" %>
<form:form modelAttribute="parent" >
Description:  <form:input path="description"/>
Child:  <form:select path="child" id="child" items="${children}" itemValue="id" itemLabel="name"/>
</form:form>

Spring Framework uses the “modelAttribute” and “path” tags to determine what is set on the form. In this case the forms “modelAttribute” is “parent” which means that the elements in the form are properties of the “parent” entity. The “description” is shown as the contents of a simple textbox input using “form:input”. The interesting part is the “form:select” which automatically populates the select with option objects based on the set of “child” objects in “children” and selects the correct “child” option for the “parent” based on the “itemValue”, “id” in this case.

Submitting changes to “parent” entities is covered in my more recent post.

Extracting and Copying Mail and Calendar Appointments from a Corrupted Microsoft Windows Live Mail Installation (Calendar EDB/ESE Database Files)

In order to install another one of the Live series of packages (Movie Maker) on Windows 7 a colleague updated their Window Live Mail package as part of the install process. The install process then hung and crippled the Mail program, stopping it from booting with a useless “Windows Live Mail has stopped working” message.

As they were using POP3 to download mail and not synchronising with the calendar server very often they were worried about losing everything, including all their very important calendar appointments. First step was to try System Restore, which didn’t work (all the system restore points we tried came back with the same error in Windows Live Mail. Next we had to look at manually moving and editing files.

We decided that the next best thing would be to set up Windows Live Mail on another machine before manually copying the data from the corrupted machine over to the new one. You can alternatively just make backups of your data and try uninstalling/re-installing Windows Live Mail again.

Mail retrieval from the existing install is easy as all you need to do is copy some physical files across from the USERNAME C:\Users directory. Just move the subfolders (containing mail) from “C:\Users\USENAME\AppData\Local\Microsoft\Windows Live Mail” to the other machine with a good copy of Windows Live Mail. You will need to set up your email retrieval settings again but your old mail should just appear in all the correct folders.

Retrieval of a corrupted calendar is a lot more tricky and needs some free third party tools in order to work. Despite the availability of several (very poor) tools I couldn’t find a way of extracting the calendar data in a format that could be easily imported into the working Windows Live Mail program. Most tools simply refused to open the data file. As a result I had to extract the data and my colleague had to manually enter the appointments again (which is still better than losing everything). I went a step further and wrote a little PHP script to display the data more easily so they didn’t have as hard time of it.

By far the most success I had was with NirSoft ESEDatabaseView which could open the corrupted Live Mail Calendar database file where all other programs failed. The “WLCalendarStore.edb” file containing the database of calendar appointments was found at “C:\Users\USERNAME\AppData\Local\Microsoft\Windows Live Mail\Calendars\DBStore\WLCalendarStore.edb”. I downloaded ESEDatabaseView and ran the executable from the zip. Then I opened the corrupted “WLCalendarStore.edb” and selected the “calendarItem” table from the dropdown. Now this is great, but everything is in HEX format and needs to be converted to normal text!

I first extracted the HEX encoded CalendarItem data to CSV (click on an item in the list of CalendarItem, ctrl-A, ctrl-S then Save as Type “Comma Delimited Text File (*.csv)”). It’s up to you how best to convert this HEX information from the output CSV but I used the following PHP script to convert and display the easy to understand “ServerIcal” column which was output as column 17 in the CSV. Note that I have two parts to this PHP, one part that exports nicely human readable data and the other that just outputs the raw iCal style data:


<?php

// helper function to convert hex value to string
function hex2str($hex) {
    for($i=0;$i<strlen($hex);$i+=2) $str .= chr(hexdec(substr($hex,$i,2)));
    return $str;
}

// show appointments as easy to read HTML
echo "<h1>Appointments</h1>";

if (($handle = fopen("calendaritem.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000000, ",")) !== FALSE) {

			// get the ServerIcal column
			$ical = $data[17];

			// strip out all '00' and ' ' strings from the output
			$ical = str_replace(" 00 ","",$ical);
			$ical = str_replace(" ","",$ical);

			// convert to string
			$line = hex2str($ical);

			// tidy up HTML to make it easily human readable
			$line = substr($line,strpos($line,"DTSTART"));
			$line = substr($line,0,strpos($line,"UID"));
			$line = str_replace("SUMMARY:","SUMMARY: <strong>",$line);
			$line = str_replace(PHP_EOL,"</strong>" .PHP_EOL,$line);
			$line = str_replace("DTSTART;VALUE=DATE:","DTSTART;VALUE=DATE: <strong>",$line);
			$line = str_replace(" DTEND;","</strong>  DTEND;",$line);
			$line = str_replace("DTEND;VALUE=DATE:","DTEND;VALUE=DATE: <strong>",$line);
			$line = str_replace("  SUMMARY: ","</strong>   SUMMARY: ",$line);
			$line = str_replace("DTSTART;VALUE=DATE:","Start Date:",$line);
			$line = str_replace("DTEND;VALUE=DATE:","End Date:",$line);

			// output as HTML
			echo "<br/>$line";
        }

    fclose($handle);
}

// show raw appointment data
echo "<h1>Raw Data</h1>";

if (($handle = fopen("calendaritem.csv", "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000000, ",")) !== FALSE) {

			// get the ServerIcal column
            $ical = $data[17];

			// strip out all '00' and ' ' strings from the output
			$ical = str_replace(" 00 ","",$ical);
			$ical = str_replace(" ","",$ical);

			// convert to string
			$line = hex2str($ical);

			// output as HTML
			echo "<br/><br/>$line";
        }

    fclose($handle);
}

?>

The output as HTML was easily readable enough that my colleague could manually enter all the appointments again.

Obviously I would much prefer to output from the database to something that could be directly imported but this functionality isn’t available in Windows Live Mail and the database was so corrupted we couldn’t even just copy it over to the new machine. My colleague is now looking at some of the many alternatives to POP3 and manual sync of calendar items using Windows Live Mail.

Quickly and Safely Move a Microsoft SQL Server Database (MDF and LDF Files) to a New Physical Location (Including Setting Read Write Access)

To move a Microsoft SQL Server database to a new physical location you need to detach, copy, reattach and set permissions for the database MDF and log LDF files associated with the database. I needed to do it as the drive on which each type of database required file (MDF/LDF) was located was to be separately backed up, as per the site backup regulations. I was using SQL Server 2008 R2.

Microsoft’s recommended way of doing this is to use SQL Management Studio. Create a query and detach your database “mydb” by entering and running the following:

use master
go
sp_detach_db ‘mydb’
go

Now copy the MDF and LDF files to their new location and reattach (locations and file names are for this example only):

use master
go
sp_attach_db ‘mydb’,'E:\DATA\mydb.mdf’,'F:\DATA\mydb_log.ldf’
go

You can check the basic properties of the database (and that the file locations have been correctly set) using:

use mydb
go
sp_helpfile
go

The final thing I needed to do was to set the file/folder permissions so that the database could go from read-only to read/write. I first set the folder permissions for my two new folders (“E:\DATA\” and “F:\DATA\” as above). To do this I needed to add the following user to the security settings with full control:

sqlservermssqluser$COMPUTERNAME$mssqlserver

This didn’t actually set the files (server permissions error) so I set the permissions for the “E:\DATA\mydb.mdf” and ”F:\DATA\mydb_log.ldf” files individually the same way.

Now open up another query window and type the following to enable read/write access to the database:

use master
go
alter database mydb set read_write with no_wait
go

Now your database is set up exactly as it was previously, only the associated files have moved physical location.