ERROR 1005 (HY000): Can't create table : How to Tackle

It's bit hard to identify the referential integrity issues in MySQL InnoDB engine  by looking at the error. It just throw the error in abstract, "Can't create table". But when we altering a table or adding a table with several foreign key constraints we are more prone to do such errors. So here I am going to explain a method which can be use to identify the error without going trough all the table syntax.

   

It's very simple. When you executing a bad foreign key sql query InnoDB engine will automatically saved it in its activities. So what you need to do is, asking the InnoDB engine to show its activities. Here is the sql query to dump out the InnoDB engine activities. 

mysql> SHOW engine InnoDB STATUS; 

When you execute it you will get a big output. You can go through it and find a topic "LATEST FOREIGN KEY ERROR" which has a clear description for your the error. Here is a sample output, I extracted from a error I was faced.

----------------------------------------------
LATEST FOREIGN KEY ERROR
----------------------------------------------
130529  3:55:45 Error in foreign key constraint of table mydatabase/mytable:

   FOREIGN KEY (`CountryName` )
   REFERENCES `mydatabase`.`country` (`countryName` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8:
Cannot resolve table name close to:
 (`Country` )
   ON DELETE NO ACTION
   ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8

As in the above description say, the fault is, the InnoDB engine cannot find a table. Basically the country table which has a column countryName which reference by one of the columns in mytable table, does not exists in the database. Like wise you can get an idea about the error using the output of that command. Then you can go for a appropriate solution. 

Hope you use this for tackle your referential integrity issues. 

Exposing JMS queue via JAX-RS Service

Today I am going to show you how to expose a JMS queue as a JAX-RS Service. Here I am using few tools for this.

1. WSO2 Developer Studio 3.0.0
2. WSO2 Application Server 
3. Apache ActiveMQ

Here I am assuming that the Apache ActiveMQ up and running. You can start ActiveMQ with its management console (using the command ./activemq console) and view the queue details using from URL http://localhost:8161/admin/queues.jsp. Here is an screenshot the list of queue in my ActiveMQ server. Here I have one queue, SampleStockQuoteProvider with three messages in it.

You can view the content and other metadata of those messages by clicking the queue name. Following figure shows an example.

Now let's start with creating the JAX-RS service. Here I am using WSO2 Developer Studio where you can download from here.

Unzip the pack and start it as it is normal Eclipse IDE.
Then click on Developer Studio ---> Open Dashboard to get the Developer Studio dashboard. You can see a dashboard like below.

Now click on JAX-RS Service Project and create a project by filling the details as below.

Developer Studio will create a template project for you. Since we are going to access ActiveMQ within this service we need to add some dependencies.
To do that open the pom.xml file and add the following to the dependencies.

        <dependency>
            <groupId>org.apache.activemq</groupId>
            <artifactId>activemq-core</artifactId>
            <version>5.7.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.geronimo.specs</groupId>
            <artifactId>geronimo-jms_1.1_spec</artifactId>
            <version>1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.ws.commons.axiom</groupId>
            <artifactId>axiom-api</artifactId>
            <version>1.2.14</version>
        </dependency>

Now we can edit the DequeueService to consume the JMS queue in Apache ActiveMQ. Following is sample java code which I wrote.

package org.wso2.carbon.sample.dequeu.rest.service;

import org.apache.activemq.ActiveMQConnectionFactory;

import javax.jms.*;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.PathParam;
import javax.ws.rs.Produces;
import javax.ws.rs.core.MediaType;
import javax.xml.stream.XMLStreamException;

@Path("/dequeue/")
public class DequeueService {

    @GET
    @Path("{queueName}")
    @Produces(MediaType.APPLICATION_XML)
    public String dequeue(@PathParam("queueName") String queueName) throws Exception {
        System.out.println(queueName);
        Connection conn = null;
        Session session = null;
        MessageConsumer consumer = null;
        try {
            ConnectionFactory factory = new ActiveMQConnectionFactory("tcp://localhost:61616");
            conn = factory.createConnection();
            conn.start();
            session = conn.createSession(false, Session.CLIENT_ACKNOWLEDGE);
            Destination queue = session.createQueue(queueName);
            consumer = session.createConsumer(queue);
            Message message = consumer.receive(1000);

            if(message == null) {
                throw new Exception("No message available in the queue");
            }

            message.acknowledge();
            return ((TextMessage)message).getText();
        } catch (JMSException e) {
            throw new Exception("Dequeuing not succeeded" ,e);
        } catch (XMLStreamException e){
            throw new Exception("Unexpected message found in the queue", e);
        } finally {
            if (consumer != null){
                consumer.close();
            }
            if (session != null) {
                session.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }
}

[NOTE] Here I use the return mediatype as xml since messages in the queue I am going to pull has xml content. Instead you can use any mediatype listed in javax.ws.rs.core.MediaType.

Now you can go to the project home and build it using Maven using the command mvn clean install. It will create a web archive (WAR file) in the target folder. Now we need to deploy it in a Application Server, here I am going to use the WSO2 Application Server. Before deploying it, we need to add the dependency jars. So add the following list of jars (you can find them inside the ActiveMQ library) to the $CARBON_HOME/repository/components/lib. No need to restart the Application Server, these jars will be hot deployed.

• activemq-core-5.7.0.jar  
• geronimo-j2ee-management_1.1_spec-1.0.1.jar  
• geronimo-jms_1.1_spec-1.1.1.jar  
• hawtbuf-1.9.jar

Now log into WSO2 Application Management console on default URL https://localhost:9443/carbon and using the default username and passwords admin, admin. Then add the web application we just created. It will take 2-5 seconds and then it will appear on the deployed web application list as below.

Now the dequeuing JAX-RS service is ready to use. When you send a http GET request  to http://localhost:9768/JMSDequeueService/services/dequeue_service/dequeue/SampleStockQuoteProvider , you will get the top message of the queue, SampleStockQuoteProvider.

Dequeue from a JMS queue and send as a SOAP message using WSO2 ESB

When you providing a solution for a enterprise you may need to dequeue from a JMS queue and send it to a SOAP endpoint. For this solution the service operation we going to invoke should be one way. So here I am explaining how you can do that easily using WSO2 ESB. Its all about a matter of creating a proxy service. You can download WSO2 ESB from here.

Before starting the WSO2 ESB you need to enable the JMS listener by setting the configuration details of JMSListener in $WSO2ESB_HOME/repository/conf/axis2/axis2.xml. WSO2 ESB supports most of the JMS message brokers and its a just a matter of simple configuration. Here are some configuration document links.

• WSO2 MB
• ActiveMQ
• IBM WebSphere MQ
• IBM WebSphere Application Server
• MSMQ
• Tibco EMS
• SwiftMQ

Start the WSO2 ESB using executing the following command using command line inside $WSO2ESB_HOME/bin.

• Windows - wso2server.bat 
• Linux - sh wso2server.sh

Now lest see a short diagram of what we are going to do.

Here red lines show the message flow. 

Now lets see how to create the proxy service to achieve this functionality. First go to WSO2 ESB Management Console which you can access using the URL https://localhost:9443/carbon which is the default URL.  

Now you need to login to the Management Console and the default username and password are admin and admin.

Now select the Main ==> Manage ==> Services ==> Add Proxy Service ==> Custom Proxy as shown in the figure below.

In the first page of the proxy configuration add a Proxy name, set the transport to jms and add the following Service Parameters categorize under General Settings.

  transport.jms.ContentType : 

      <rules>         

         <jmsProperty>contentType</jmsProperty>         

         <default>application/xml</default>      

      </rules>

   transport.jms.ConcurrentConsumers : 1

   transport.jms.ConnectionFactory : myTopicConnectionFactory

   transport.jms.SessionTransacted : false

   transport.jms.Destination : myQueue

   transport.jms.MaxConcurrentConsumers : 1

You can find more details for these parameters on here.

After filling the page will look like as following figure.

Now you are done with the first page. Click Next to go to the next page.

Here you need to configure the inSequence to send the SOAP message to your service endpoint. Here we are going to add it in line. Click Edit in Define Inline in Define In Sequence.

And add a Log mediator. You can do it by click on Add Child ==> Core ==> Log and set to log level to full. Which will log the messages comes to this Proxy endpoint.

Then add the Send mediator where we invoke the endpoint of our service. This can be done by clicking  Add Child ==> Core ==> Send. Then edit the Send mediator configuration by defining the In Line endpoint and selecting address endpoint and add your service endpoint there.

Now it will be look like as following figure.

Save and Close the In sequence, then click Next.

Then click Finish and we are done with the configuration.

So if we add a message to the myQueue, then this proxy will dequeue it from the queue and send it to your endpoint. 

Here is my full configuration in xml format and my service endpoint is http://localhost:7611/sample/ep1

<proxy xmlns="http://ws.apache.org/ns/synapse" name="jmsReaderProxy" transports="jms" statistics="disable" trace="disable" startOnLoad="true">
   <target>
      <inSequence>
         <log level="full"/>
         <send>
            <endpoint>
               <address uri="http://localhost:7611/sample/ep1"/>
            </endpoint>
         </send>
      </inSequence>
   </target>
   <parameter name="transport.jms.ContentType">
      <rules>         
         <jmsProperty>contentType</jmsProperty>         
         <default>application/xml</default>      
      </rules>
   </parameter>
   <parameter name="transport.jms.ConcurrentConsumers">1</parameter>
   <parameter name="transport.jms.ConnectionFactory">myTopicConnectionFactory</parameter>
   <parameter name="transport.jms.SessionTransacted">false</parameter>
   <parameter name="transport.jms.Destination">myQueue</parameter>
   <parameter name="transport.jms.MaxConcurrentConsumers">1</parameter>
   <description></description>
</proxy>

Exception sending context initialized event to listener instance of class org.springframework.web.util.Log4jConfigListener

There can be special cases where you may not able to deploy a web application in your application server because of the $subject exception but it may worked in Apache Tomcat. So you may misunderstand that its a bug in your web app or bug in the application server. Fortunately its not, but its a problem of your configuration. Will explain the solution later, but lets see how I get to the solution.

So in the first case I have no idea about this and I was digging in to bit further for a solution. As in the exception say it should be a problem of initialization of log4j. Hmmm.... what could be the course of this? In my case the web application was successfully deployed in Apache Tomcat, but not in WSO2 Application Server. Knowing the fact that WSO2 Application Server build on top of Apache Tomcat it should be something done to the web application archive by WSO2 Application Server before deploying it in the Apache Tomcat. So what is could be? Oh.. it is exploded....

Now I found the difference between deploy in the Apache Tomcat and Deploy in the WSO2 Server, So I need to find the solution how the exploding causing the above problem. Then I search on it and found the following.

In a web archive (.war), Tomcat expect everything to be at the same place. If it it exploded then all the files have their own paths. Tomcat itself explode the web archive, but before that it reads some of the configurations from the war file like log4j listeners. 

When the war file have the following configuration in its web.xml,

    <context-param>
        <param-name>log4jConfigLocation</param-name>
        <param-value>/WEB-INF/log4j.xml</param-value>
    </context-param>

Apache Tomcat expect log4.xml in its a war file in the given location. But since the war exploded it could not find the log4j.xml. So it fails.

Now what we need to be done is we need to change the configuration so that it will read from a file system. For that you need to change the above configuration to the following.

    <context-param>
        <param-name>log4jExposeWebAppRoot</param-name>
        <param-value>false</param-value>
    </context-param>

My problem Solved... Hope you too solve yours...