23.4. MySQL Connector/MXJ

A feature of the MySQL Connector/J JDBC driver is the ability to specify a connection to an embedded Connector/MXJ instance through the use of the mxj keyword in the JDBC connection string.

In the following example, we have a program which creates a connection, executes a query, and prints the result to the System.out. The MySQL database will be deployed and started as part of the connection process, and shutdown as part of the finally block.

You can find this file in the Connector/MXJ package as src/ConnectorMXJUrlTestExample.java.

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import com.mysql.management.driverlaunched.ServerLauncherSocketFactory;

public class ConnectorMXJUrlTestExample {
    public static String DRIVER = "com.mysql.jdbc.Driver";

    public static String JAVA_IO_TMPDIR = "java.io.tmpdir";

    public static void main(String[] args) throws Exception {
        File ourAppDir = new File(System.getProperty(JAVA_IO_TMPDIR));
        File databaseDir = new File(ourAppDir, "test-mxj");
        int port = 3336;

        String url = "jdbc:mysql:mxj://localhost:" + port + "/test" + "?"
                + "server.basedir=" + databaseDir;

        System.out.println(url);

        String userName = "root";
        String password = "";

        Class.forName(DRIVER);
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(url, userName, password);
            printQueryResults(conn, "SELECT VERSION()");
        } finally {
            try {
                if (conn != null)
                    conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

            ServerLauncherSocketFactory.shutdown(databaseDir, null);
        }
    }

    public static void printQueryResults(Connection conn, String SQLquery)
            throws Exception {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(SQLquery);
        int columns = rs.getMetaData().getColumnCount();
        System.out.println("------------------------");
        System.out.println();
        while (rs.next()) {
            for (int i = 1; i <= columns; i++) {
                System.out.println(rs.getString(i));
            }
            System.out.println();
        }
        rs.close();
        stmt.close();
        System.out.println("------------------------");
        System.out.flush();
        Thread.sleep(100); // wait for System.out to finish flush
    }
}

To run the above program, be sure to have connector-mxj.jar and Connector/J in the CLASSPATH. Then type:

java ConnectorMXJTestExample
  

If you have a java application and wish to “embed” a MySQL database, make use of the com.mysql.management.MysqldResource class directly. This class may be instantiated with the default (no argument) constructor, or by passing in a java.io.File object representing the directory you wish the server to be "unzipped" into. It may also be instantiated with printstreams for "stdout" and "stderr" for logging.

Once instantiated, a java.util.Map, the object will be able to provide a java.util.Map of server options appropriate for the platform and version of MySQL which you will be using.

The MysqldResource enables you to "start" MySQL with a java.util.Map of server options which you provide, as well as "shutdown" the database. The following example shows a simplistic way to embed MySQL in an application using plain java objects.

You can find this file in the Connector/MXJ package as src/ConnectorMXJObjectTestExample.java.

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import com.mysql.management.MysqldResource;

public class ConnectorMXJObjectTestExample {
    public static String DRIVER = "com.mysql.jdbc.Driver";

    public static String JAVA_IO_TMPDIR = "java.io.tmpdir";

    public static void main(String[] args) throws Exception {
        File ourAppDir = new File(System.getProperty(JAVA_IO_TMPDIR));
        File databaseDir = new File(ourAppDir, "mysql-mxj");
        int port = 3336;

        MysqldResource mysqldResource = startDatabase(databaseDir, port);

        String userName = "root";
        String password = "";

        Class.forName(DRIVER);
        Connection conn = null;
        try {
            String url = "jdbc:mysql://localhost:" + port + "/test";
            conn = DriverManager.getConnection(url, userName, password);
            printQueryResults(conn, "SELECT VERSION()");
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                mysqldResource.shutdown();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    public static MysqldResource startDatabase(File databaseDir, int port) {
        MysqldResource mysqldResource = new MysqldResource(databaseDir);

        Map database_options = new HashMap();
        database_options.put("port", Integer.toString(port));
        mysqldResource.start("test-mysqld-thread", database_options);

        if (!mysqldResource.isRunning()) {
            throw new RuntimeException("MySQL did not start.");
        }

        System.out.println("MySQL is running.");

        return mysqldResource;
    }

    public static void printQueryResults(Connection conn, String SQLquery)
            throws Exception {
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(SQLquery);
        int columns = rs.getMetaData().getColumnCount();
        System.out.println("------------------------");
        System.out.println();
        while (rs.next()) {
            for (int i = 1; i <= columns; i++) {
                System.out.println(rs.getString(i));
            }
            System.out.println();
        }
        rs.close();
        stmt.close();
        System.out.println("------------------------");
        System.out.flush();
        Thread.sleep(100); // wait for System.out to finish flush
    }
}
  

Of course there are many options we may wish to set for a MySQL database. These options may be specified as part of the JDBC connection string simply by prefixing each server option with ''server.''. In the following example we set two driver parameters and two server parameters:

        String url = "jdbc:mysql://" + hostColonPort + "/" 
                + "?"
                + "cacheServerConfiguration=true"
                + "&"
                + "useLocalSessionState=true"
                + "&"
                + "server.basedir=/opt/myapp/db"
                + "&"
                + "server.datadir=/mnt/bigdisk/myapp/data";
  

If you want to create a custom Connector/MXJ package that includes a specific mysqld version or platform then you must extract and rebuild the connector-mxj.jar file.

First, you should create a new directory into which you can extract the current connector-mxj.jar:

shell> mkdir custom-mxj
shell> cd custom-mxj
shell> jar -xf connector-mxj.jar
shell> ls
5-0-22/
ConnectorMXJObjectTestExample.class
ConnectorMXJUrlTestExample.class
META-INF/
TestDb.class
com/
kill.exe

The MySQL version directory, 5-0-22 in the above example, contains all of the files used to create an instance of MySQL when Connector/MXJ is executed. All of the files in this directory are required for each version of MySQL that you want to embed. Note as well the format of the version number, which uses hyphens instead of periods to separate the version number components.

Within the version specific directory are the platform specific directories, and archives of the data and share directory required by MySQL for the various platforms. For example, here is the listing for the default Connector/MXJ package:

shell>> ls
Linux-i386/
META-INF/
Mac_OS_X-ppc/
SunOS-sparc/
Win-x86/
com/
data_dir.jar
share_dir.jar
win_share_dir.jar

Platform specific directories are listed by their OS and platform - for example the mysqld for Mac OS X PowerPC is located within the Mac_OS_X-ppc directory. You can delete directories from this location that you do not require, and add new directories for additional platforms that you want to support.

To add a platform specific mysqld, create a new directory with the corresponding name for your operating system/platform. For example, you could add a directory for Mac OS X/Intel using the directory Mac_OS_X-i386.

On Unix systems, you can determine the platform using uname:

shell> uname -p
i386

Now you need to download or compile mysqld for the MySQL version and platform you want to include in your custom connector-mxj.jar package into the new directory.

Create a file called version.txt in the OS/platform directory you have just created that contains the version string/path of the mysqld binary. For example:

mysql-5.0.22-osx10.3-i386/bin/mysqld

You can now recreate the connector-mxj.jar file with the added mysqld:

shell> cd custom-mxj
shell> jar -cf ../connector-mxj.jar *

You should test this package using the steps outlined in Section 23.4.2.3, “Connector/MXJ Quick Start Guide”.

To include a pre-configured/populated database within your Connector/MXJ JAR file you must create a custom data_dir.jar file, as included within the main connector-mxj.jar file:

Note that if you are create databases using the InnoDB engine, you must include the ibdata.* and ib_logfile* files within the data_dir.jar archive.

As a JMX MBean, MySQL Connector/MXJ requires a JMX v1.2 compliant MBean container, such as JBoss version 4. The MBean will uses the standard JMX management APIs to present (and allow the setting of) parameters which are appropriate for that platform.

If you are not using the SUN Reference implementation of the JMX libraries, you should skip this section. Or, if you are deploying to JBoss, you also may wish to skip to the next section.

We want to see the MysqldDynamicMBean in action inside of a JMX agent. In the com.mysql.management.jmx.sunri package is a custom JMX agent with two MBeans:

When this very simple agent is started, it will allow a MySQL database to be started and stopped with a web browser.

Once there is confidence that the MBean will function on the platform, deploying the MBean inside of a standard JMX Agent is the next step. Included are instructions for deploying to JBoss.

As of 1.0.6-beta version is the ability to have the MBean start the MySQL database upon start up. Also, we've taken advantage of the JBoss life-cycle extension methods so that the database will gracefully shut down when JBoss is shutdown.

MySQL AB provides assistance to the user community by means of a number of mailing lists and web based forums.

You can find help and support through the MySQL and Java mailing list.

For information about subscribing to MySQL mailing lists or to browse list archives, visit http://lists.mysql.com/. See Section 1.7.1, “MySQL Mailing Lists”.

Community support from experienced users is also available through the MyODBC Forum. You may also find help from other users in the other MySQL Forums, located at http://forums.mysql.com. See Section 1.7.2, “MySQL Community Support at the MySQL Forums”.

If you encounter difficulties or problems with Connector/MXJ, contact the Connector/MXJ community Section 23.4.6.1, “Connector/MXJ Community Support”.

If reporting a problem, you should ideally include the following information with the email:

Remember that the more information you can supply to us, the more likely it is that we can fix the problem.

If you believe the problem to be a bug, then you must report the bug through http://bugs.mysql.com/.