Archive

Posts Tagged ‘mysql’

Mule 3 Deployment Gotchas / Workarounds

June 10, 2011 1 comment

Mule is an open source enterprise service bus written in Java. I’ve worked with Mule 2.2 quite a bit but only recently have started to work with Mule 3. This post details some of the pains involved with the transition, none of which are well documented or hinted at in the Migration guide.

Gotchas/Workarounds

Mule IDE specific

The Mule IDE is really a misnomer – it’s not a standalone product, but instead an Eclipse plugin. See the installation guide for more information.

XML validation warnings

By default, Eclipse 3.5 will flag all sorts of spurious errors in your XML configuration file. See the blog post for more details, but here’s the short version on how to solve it:

General

These issues exist whether you use the IDE to deploy the app or deploy the app via the command line.

Failure to launch / Timeouts

Mule is configured via XML. You must declare the namespaces and schema locations in order to make use of the built-in Mule constructs. For instance, here’s a snippet of one of my Mule configurations:

<mule xmlns="http://www.mulesoft.org/schema/mule/core"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:spring="http://www.springframework.org/schema/beans"
      xmlns:vm="http://www.mulesoft.org/schema/mule/vm"
      xmlns:script="http://www.mulesoft.org/schema/mule/scripting"
      xmlns:http="http://www.mulesoft.org/schema/mule/http"
      xmlns:cxf="http://www.mulesoft.org/schema/mule/cxf"
      xmlns:xm="http://www.mulesoft.org/schema/mule/xml"
      xmlns:pattern="http://www.mulesoft.org/schema/mule/pattern"
      xmlns:servlet="http://www.mulesoft.org/schema/mule/servlet"
      xmlns:jetty="http://www.mulesoft.org/schema/mule/jetty"
      xmlns:test="http://www.mulesoft.org/schema/mule/test"
      xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/3.1/mule.xsd
        http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/3.1/mule-http.xsd
        http://www.mulesoft.org/schema/mule/cxf http://www.mulesoft.org/schema/mule/cxf/3.1/mule-cxf.xsd
        http://www.mulesoft.org/schema/mule/scripting http://www.mulesoft.org/schema/mule/scripting/3.1/mule-scripting.xsd
        http://www.mulesoft.org/schema/mule/pattern http://www.mulesoft.org/schema/mule/pattern/3.1/mule-pattern.xsd
        http://www.mulesoft.org/schema/mule/xml http://www.mulesoft.org/schema/mule/xml/3.1/mule-xml.xsd
        http://www.mulesoft.org/schema/mule/vm http://www.mulesoft.org/schema/mule/vm/3.1/mule-vm.xsd
        http://www.mulesoft.org/schema/mule/servlet http://www.mulesoft.org/schema/mule/servlet/3.1/mule-servlet.xsd
        http://www.mulesoft.org/schema/mule/test http://www.mulesoft.org/schema/mule/test/3.1/mule-test.xsd
        http://www.mulesoft.org/schema/mule/jetty http://www.mulesoft.org/schema/mule/jetty/3.1/mule-jetty.xsd"
        >

Make absolutely sure that the version of the xsd that you include matches the major version of mule that you’re using! If you accidentally place a 3.0 instead of a 3.1 in any of those entries, your app will mysteriously fail to launch and you’ll get a stack trace like the following:

INFO  2011-06-09 17:21:20,015 [main] org.mule.MuleServer: Mule Server initializing...
INFO  2011-06-09 17:21:20,298 [main] org.mule.lifecycle.AbstractLifecycleManager: Initialising RegistryBroker
INFO  2011-06-09 17:21:20,355 [main] org.mule.config.spring.MuleApplicationContext: Refreshing org.mule.config.spring.MuleApplicationContext@19bb5c09: startup date [Thu Jun 09 17:21:20 EDT 2011]; root of context hierarchy
WARN  2011-06-09 17:22:36,265 [main] org.springframework.beans.factory.xml.XmlBeanDefinitionReader: Ignored XML validation warning
java.net.ConnectException: Operation timed out
    at org.apache.xerces.util.ErrorHandlerWrapper.createSAXParseException(Unknown Source)
    at org.apache.xerces.util.ErrorHandlerWrapper.warning(Unknown Source)
    at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)
    at org.apache.xerces.impl.XMLErrorReporter.reportError(Unknown Source)
    at org.apache.xerces.impl.xs.traversers.XSDHandler.reportSchemaWarning(Unknown Source)
    at org.apache.xerces.impl.xs.traversers.XSDHandler.getSchemaDocument1(Unknown Source)
    at org.apache.xerces.impl.xs.traversers.XSDHandler.getSchemaDocument(Unknown Source)
    at org.apache.xerces.impl.xs.traversers.XSDHandler.parseSchema(Unknown Source)
    at org.apache.xerces.impl.xs.XMLSchemaLoader.loadSchema(Unknown Source)

Deploying via command line

While it’s nice to be able to use an IDE to develop Mule applications, I prefer to deploy from the command line. This allows me to script the launch of the applications. Furthermore, this approach works in a headless (screenless) remote server, whereas the IDE approach will not. The basic way to deploy an app has changed from Mule 2.2 to Mule 3. It used to be that you would call mule -config /path/to/your/config.xml. Now you move your application to the $MULE_HOME/apps folder and run mule, which in turn will deploy all the apps in the apps folder. This can be very handy, especially when coupled with the Hot Deployment features of Mule; you no longer need to have one terminal per mule app you’re running. From the article, “Mule 3: A New Deployment Model”, here are the ostensible steps you must take to deploy your application in this manner:

  • Create a directory under: $MULE_HOME/apps/foo
  • Jar custom classes (if any), and put them under: $MULE_HOME/apps/foo/lib
  • Put the master Mule config file at: $MULE_HOME/apps/foo/mule-config.xml (note that it has to be named: mule-config.xml
  • Start your app with: mule -app foo

While these instructions are correct, there are a lot of gotchas involved. Let me detail them below.

Relative paths

There is often a need to make reference to resources within your configuration file. For instance, you might need to configure an embedded Jetty webserver and tell Jetty where its configuration file is located. When you do this, it’s crucial that you prepend relative paths in the XML configuration file with ${app.home}.

The reason for this is that the current working directory in which you launch the mule process becomes the current working directory for all of your application configuration files. So if you have mule-config.xml in the root of your folder, and conf/jetty.xml in that same folder, then your reference to the jetty.xml should be ${app.home}/conf/jetty.xml. Otherwise, if you just use conf/jetty.xml and launch mule from a folder that’s not the same as the root folder of your application, all of your paths will break.

Property files / Resources

As the step #2 above says, you must jar up all of your compiled classes and include them in the lib folder of your project. If you don’t do this, you’ll get an exception when your component / custom classes are attempted to be instantiated.

What should be emphasized is that all resources that you reference from within your code must end up in the jar as well. By default, that won’t happen. You can use something like the solution presented in Ant Build: copy properties file to jar file to get this to happen.

Unintentional Application Deletion

When you deploy an app by copying a zip or folder into the apps directory and then running mule, Mule will launch it and then create a text file called ‘$APP_NAME-anchor.text’. If you delete this file, Mule will “undeploy this app in a clean way”. What isn’t noted by this is that it will delete the corresponding zip/folder. So be careful not to accidentally delete your whole project. (Not that I did that or anything).

JDBC drivers problems

One nice feature of the hot deploy process is that Mule will automatically load all of the jars in the lib folder and ensure that they’re on the classpath. Unfortunately there is an extremely annoying problem with JDBC drivers, in which they corresponding jar will be loaded correctly, but then will fail to be found at runtime.

At startup:

Loading the following jars:
=============================
file:/opt/local/Mule/mule-standalone-3.1.1/apps/XMLPlayer/lib/mysql-connector-java-5.1.13-bin.jar
=============================
<!-- snip -->
WARN 2011-06-09 15:56:12,130 [http://XMLPlayer].connector.http.mule.default.receiver.2 org.hibernate.cfg.SettingsFactory: Could not obtain connection to query metadata
java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/db

The exact same project works perfectly in the Mule IDE. The only solution I’ve found is to copy the mysql-connector-java-5.1.13-bin.jar into $MULE_HOME/lib/endorsed. There is a similar bug report but it was closed for some reason. It most certainly does not work the way you would intuitively expect.

Conclusion

Mule 3 has many improvements over Mule 2, particular with the introduction of Flows. Unfortunately, deployment is a much tricker problem than it was in Mule 2, and the resources online are woefully inadequate for the task at hand. I hope this blog post helps some poor soul going through the same frustration I went through to get a Mule 3 application deployed.

Hibernate + MySQL + Mac = Foreign Key Nightmares. A painless solution to a painful problem

May 23, 2011 4 comments

tl;dr summary: Avoid using mixed case table names when using MySQL on a Mac.  Use lowercase underscore separated table names instead.

I was using Hibernate to map my Java classes to MySQL tables and columns.  For most classes, inserts worked perfectly.  For other classes, I’d consistently get errors like

- SQL Error: 1452, SQLState: 23000
- Cannot add or update a child row: a foreign key constraint fails

By running the command

show engine innodb status

in my mysql window, I found following clue:

110520 14:26:09 Transaction:
TRANSACTION 85B76, ACTIVE 0 sec, OS thread id 4530606080 inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s)
MySQL thread id 3, query id 2175 localhost root update
insert into TableName (pk_Pdu) values (10)
Foreign key constraint fails for table `myproj`.`tablename`:
,
  CONSTRAINT `FKEC7DE11817B41BEB` FOREIGN KEY (`pk_Pdu`) REFERENCES `ParentClass` (`pk_Pdu`)
Trying to add to index `PRIMARY` tuple:
DATA TUPLE: 3 fields;
 0: len 8; hex 800000000000000a; asc         ;;
 1: len 6; hex 000000085b76; asc     [v;;
 2: len 7; hex 00000000000000; asc        ;;

But the parent table `myproj`.`ParentClass`
or its .ibd file does not currently exist!

I knew for a fact the table existed; I was able to query it and it showed up fine. Something else must be going on.

I finally stumbled onto the answer by way of a StackOverflow post:

However, I did rename the tables all to lowercase and that did make a difference. A quick search indicates I should maybe setting lower_case_table_names = 1 since I am using InnoDB. On Mac OS/X it is 2 by default (and I failed to mention I’m using a new box which may be why it isn’t working locally).

Sure enough, as soon as I renamed the table names to be all lowercase underscore separated, things worked perfectly. The default naming strategy in Hibernate names the tables in exactly the same way as the class names (e.g. in CamelCase as opposed to lower_case_underscore_separated). Fortunately the designers saw fit to make this naming convention overridable. All I had to do was add one line of code to fix my entire problem:


Configuration config = new Configuration();
// Name tables with lowercase_underscore_separated
config.setNamingStrategy(new ImprovedNamingStrategy());

Thanks to this blog post on ImprovedNamingStrategy for pointing the way. This post also helped me find the problem.

Conclusion

If you’re using Hibernate and a MySQL database running on MacOSX, make sure that your table names are all in lowercase.  This can be accomplished by using the ImprovedNamingStrategy class when configuring Hibernate.

This experience taught me a valuable lesson.  The first is, sometimes a problem can be caused by something that’s not directly your fault per se (i.e. I hadn’t incorrectly structured my Hibernate annotations, as I initially suspected), but rather due some quirk in the operating system or external tools you’re using.  The second is it’s crucial for cross platform libraries like Hibernate to provide the hooks for you to be able to swap out default behavior, precisely to be able to work around problems like these.  Thankfully Hibernate had built in just the hooks I needed to solve the problem.

Mono, NHibernate, MySQL – A complete example

May 2, 2011 6 comments

This article aims to show you how to use Mono, MonoDevelop 2.4.2 and NHibernate (3.2.0.Alpha2) to connect to a MySQL database on Mac OSX 10.6, Snow Leopard. (I’m assuming the solution can be adapted to other *NIX variants, but I’ve only tried it on Snow Leopard). The examples I found online were frequently Windows only. I ran into a lot of problems along the way, which I am documenting here to prevent others from having the same issues.

Step 1 – MonoDevelop:

Download and install MonoDevelop. I am using version 2.4.2.

Step 2 – NHibernate:

Download NHibernate zip (I am using version 3.2.0.Alpha2), extract to a location you will remember later (I put it on my Desktop)

Step 3 – Install MySQL

3a.) Download MySQL, and install it. I used the 64 bit DMG Archive for Mac. I opened the package, and ran all of the installers. Make sure you install the PrefPane support too. When you go into the System Preferences, you should see an entry for MySQL.

Pref pane

Pref pane

Go into the MySQL settings and choose Start MySQL Server. After this is done, launch a terminal window. Type mysql -u root in the window; if everything is configured correctly, you should get a command prompt that looks like the following:

$ mysql -u root Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1553 Server version: 5.5.11 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> 

Type the following at the command line prompt to create a new database and user we will use later: (Note that in a production system you should not have a dev account with such a simple password, and certainly not with all privileges.)

mysql> CREATE DATABASE products; Query OK, 1 row affected (0.00 sec) mysql> CREATE USER 'dev'@'localhost' IDENTIFIED BY 'test_password'; Query OK, 1 row affected (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'dev'@'localhost' WITH GRANT OPTION; Query OK, 1 row affected (0.00 sec) 

At this point, we now have an account named ‘dev’ with password ‘test_password’, and a database named ‘products’. We will use that information when connecting to the database using NHibernate.

Step 4 – Download MySQL Driver

In order to use MySQL with .NET, you need a database driver. From the MySQL Connectors page, choose Connector/Net, and then choose .NET & Mono from the dropdown. Download the file; it should be entitled mysql-connector-net–6.3.6-noinstall.zip.

When the file finishes downloading, unzip it and place the folder somewhere easy to get to; I put it on my Desktop.

Step 5 – Create Domain Objects / Hibernate configs

Follow the instructions on Your first NHibernate based application, up until the section “Test the Setup” to create all of the domain classes. We need to modify the hibernate.cfg.xml file to match what’s in this gist.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
 <!-- an ISessionFactory instance -->
 <session-factory>
  <!-- properties -->
  <property name="connection.provider">
    NHibernate.Connection.DriverConnectionProvider
  </property> 
  <property name="connection.driver_class">
    NHibernate.Driver.MySqlDataDriver
  </property> 
  <property name="connection.connection_string">
    <!-- Found from http://www.connectionstrings.com/mysql; note that the 'dev' and 'test_password' 
    match the user we created, and 'products' matches the Database -->
Server=localhost;Database=products;Uid=dev;Pwd=test_password;
  </property>

  <!-- Will automatically create a fresh table each time we create an ISessionFactory object -->
  <property name="hbm2ddl.auto">create-drop</property>

  <property name="dialect">
    NHibernate.Dialect.MySQLDialect
  </property>
  <property name="show_sql">true</property>
 </session-factory>
</hibernate-configuration>

Note that the dialect, connection.driver_class, and connection.connection_string properties have all been changed; I also added an hbm2ddl.auto property. You can read more about that here.

Fix missing references

At this point, we need to ensure that all of the dlls we need are referenced correctly. Right click on the References node, and choose Edit references. Choose the .Net Assembly tab, and browse to where you saved the mysql-connector-net-6.3.6-noinstall folder; I placed mine on the desktop. Choose the v2 folder, select all of the dlls, and choose add.

At this point your screen should look something like the following:

Edit references

Edit references

edit references

If you don’t have an NHibernate.dll listed in your references, you will need to add this as well. Follow the same steps as previously listed, and add the NHibernate.dll found in NHibernate-3.2.0.Alpha2-src/lib/net/3.5/. I added all of the dlls in that folder, but that’s overkill.

Add main entry point

At this point, you’re almost ready to get started. Create a file named Main.cs at the same level as your hibernate.cfg.xml file. Use the following code:

using System;
using System.Collections.Generic;
using NHibernate;
using NHibernateTest.Domain;
using NHibernate.Tool.hbm2ddl;

namespace NHibernateTest
{
    class MainClass
    {
        public static void Main (string[] args)
        {
            IList products;

            // Don't need to use schema export because of the hbm2dll property.
            var cfg = new NHibernate.Cfg.Configuration();
            cfg.Configure();
            // ensure that mapping hbm.xml file is loaded
            cfg.AddAssembly(typeof(MainClass).Assembly);

            Product p = new Product() {Name="Captains of Crush Gripper #1", Category="fitness" };

            ISessionFactory factory =
                cfg.BuildSessionFactory();

            using (ISession session = factory.OpenSession())
            {
                session.Save(p);
                session.Flush();

                ICriteria sc = session.CreateCriteria();
                products = sc.List();
                Console.WriteLine(products[0].Name);
                session.Close();
            }
            factory.Close();

            Console.WriteLine( products.Count );

            Console.WriteLine ("Hello World!");
        }
    }
}

My namespace might be slightly different from yours; modify accordingly.

Build and run the project. You should get output like the following:

NHibernate: INSERT INTO Product (Name, Category, Discontinued, Id) VALUES (?p0, ?p1, ?p2, ?p3);?p0 = 'Captains of Crush Gripper #1' [Type: String (28)], ?p1 = 'fitness' [Type: String (7)], ?p2 = False [Type: Boolean (0)], ?p3 = a9f8e586-727f-49b9-98da-59f534163ae7 [Type: Guid (0)] NHibernate: SELECT this_.Id as Id0_0_, this_.Name as Name0_0_, this_.Category as Category0_0_, this_.Discontinued as Disconti4_0_0_ FROM Product this_ Captains of Crush Gripper #1 1 Hello World! Abort trap Press any key to continue... 

Conclusion/Troubleshooting

I hope this helps newbies like myself get up and running in a Mono environment with NHibernate and MySQL. The complete code can be found on Github.

MappingException

Unhandled Exception: NHibernate.MappingException: No persister for: NHibernateTest.Domain.Product 

If you get an exception like this, the Product.hbm.xml file is not being loaded correctly. Make sure that you have the following line:

cfg.AddAssembly(typeof(MainClass).Assembly); 

Conversely, make sure that your Product.hbm.xml file is set to be ‘embed as resource’. (Right click, choose Build Action, Embed as resource).

embed as resource

embed as resource

Could not compile the mapping document

Unhandled Exception: NHibernate.MappingException: Could not compile the mapping document: NHibernateTest.Mappings.Product.hbm.xml ---> System.InvalidOperationException: Could not find the dialect in the configuration 

If you get an error like this, you have inadvertently switched the order of your AddAssembly and Configure calls. Make sure you call Configure before you call AddAssembly.