12.2 Using the MySQL Enterprise Plugin for Connector/J

The MySQL Enterprise Plugin for Connector/J, enables query analysis for your applications without requiring any modification to the application code.

Important

The Connector/J does not require the MySQL Enterprise Monitor Aggregator for query aggregation.

Figure 12.2 Connector Plugin Architecture

Connector Plugin Architecture

Prerequisites

  1. MySQL Connector/J version 5.1.12 or later.

  2. JDK-1.7.0 or later.

  3. MySQL Enterprise Service Manager version 3.0 or later.

  4. The MySQL client application user must have SELECT privileges on the mysql.inventory table. This table contains the server UUID which is required to report the Query Analyzer data to the MySQL Enterprise Service Manager. Use the GRANT statement. For example:

    mysql> GRANT SELECT on mysql.inventory to 'user'@'localhost' IDENTIFIED BY 'password';
    
  5. Apache Commons logging in the CLASSPATH of the application being analyzed. If you are not already using Commons Logging, modify the application's CLASSPATH as described in the following section to point to the JAR file bundled with the MySQL Enterprise Monitor product.

Installation

Place the JAR file lib/c-java-mysql-enterprise-plugin-version.jar in the application's CLASSPATH where it is visible to the version of MySQL Connector/J in use. Ideally, use the same location as MySQL Connector/J's JAR file, or in a parent classloader to that JAR file's location.

If the application being analyzed does not have Apache Commons Logging in the CLASSPATH, install the file lib/required/commons-logging-1.1.1.jar in the application's CLASSPATH as well. If no other component in your application uses Apache Commons Logging, install it in the same place where the Query Analyzer plugin was installed.

There is static shutdown() method on com.mysql.etools.jdbc.StatementPerformanceCounters, which can be used to cleanly shutdown the query analysis plugin when the application is going to be shutdown.

If the application is deployed in a J(2)EE application server, there is a ContextListener distributed with the plugin which calls this method when the application's context is shutdown (or reloaded). Application Servers which support @WebListener (such as JEE6 and above) do not need to do any extra configuration, but users with older Application Servers need to add the following line to their application's web.xml file:


<listener> 
    <listener-class> 
        com.mysql.etools.jdbc.ContextListener 
    </listener-class> 
</listener> 

Using the MySQL Enterprise Plugin for Connector/J

This section describes how to configure the MySQL Plugin for Connector/J.

Table 12.2 MySQL Plugin for Connector/J Properties

Property Name Description
statementInterceptors

Enables the plugin. Set this property as follows:

statementInterceptors = com.mysql.etools.jdbc.StatementPerformanceCounters
disableSourceLocation

Defines whether to send stack traces with example queries to MySQL Enterprise Service Manager.

  • Property type: boolean

  • Values: true or false (default).

serviceManagerUrl

Defines the URL of the MySQL Enterprise Service Manager. Include the full URL and port number.

  • Property type: string

  • Value: URL and port number of MySQL Enterprise Service Manager.

serviceManagerUser

Defines the Agent username to use when connecting to MySQL Enterprise Service Manager.

  • Property type: string

serviceManagerPassword

Defines the Agent password to use when connecting to MySQL Enterprise Service Manager.

  • Property type: string

serviceManagerConnectTimeout

Defines the number of seconds to wait for a connection to MySQL Enterprise Service Manager.

  • Property type: numeric

  • Default value: 0

serviceManagerResponseTimeout

Defines the number of seconds to wait for a response from MySQL Enterprise Service Manager.

  • Property type: numeric

  • Default value: 0

mysqlServerUUID

If you are unable to retrieve the server's UUID, define it with this property.

  • Property type: string

To retrieve the UUID, the plugin requires SELECT privileges on mysql.inventory.


You can also configure MySQL Enterprise Plugin for Connector/J to use SSL for all communication with MySQL Enterprise Service Manager. To enable SSL, add the following properties to your connection string:

Table 12.3 MySQL Plugin for Connector/J SSL Properties

Property Name Description
verifySslHostnames

If set to true, it enables verification of the host names in the SSL Server certificate. Host names are verified using the scheme described at BrowserCompatHostnameVerifier which is the same as used by Firefox, and Curl, and specified by RFC 2818.

  • Property type: boolean

  • Default value: false

verifySslCerts

Defines whether the plugin verifies the certificate presented by the server was signed by a CA in the trustCertificateKeystore.

  • Property type: boolean

  • Default: false, verification is disabled.

trustCertificateKeystoreUrl

Defines the URL of the trusted root certificate KeyStore. If none is specified, the Java defaults are used.

  • Property type: string

trustCertificateKeystorePassword

Defines the password for the KeyStore.

  • Property type: string

trustCertificateKeystoreType=[type]

Defines the KeyStore type for trusted root certificates. If type is set to NULL or empty, JKS is used by default. The standard keystore types supported by the JVM are JKS and PKCS12. Your environment may have more available depending on what security products are installed and available to the JVM.

clientCertificateKeystoreUrl Defines the URL of the client KeyStore. If none specified, Java defaults are used.
clientCertificateKeystorePassword=[password]

Defines the password to use for the client certificate store.

clientCertificateKeystoreType

Defines the KeyStore type for client certificates. If type is set to NULL or empty, JKS is used by default.


The following example configures a Connector/J to communicate with the MySQL Enterprise Service Manager localhost, on port 18443, using the agent username agent, and password PASSWORD. Add the properties to your connection string on a single line:

statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters
  &serviceManagerUrl=https://localhost:18443/
  &serviceManagerUser=agent
  &serviceManagerPassword=PASSWORD

You must also add the application-specific properties to the JDBC URL. For example, the following fragment connects to the MySQL database test on localhost, using the user and password of mysqltest, while also collecting query data and sending it to the MySQL Enterprise Service Manager on localhost:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

Connection conn = null;
...
try {
    conn =
       DriverManager.getConnection("jdbc:mysql://localhost/test?" +
           "?user=mysqltest" + 
           "&password=mysqltest" +
           "&statementInterceptors=com.mysql.etools.jdbc.StatementPerformanceCounters" +
           "&serviceManagerUrl=https://localhost:18443/" + 
           "&serviceManagerUser=agent" + 
           "&serviceManagerPassword=PASSWORD"
       );

    // Do something with the Connection

   ...
} catch (SQLException ex) {
    // handle any errors
}
Note

If a DataSource is in use (typically when using Glassfish, Weblogic, or Websphere), these properties must be passed as part of the URL property, they cannot be added to the DataSource configuration itself.

If an alternate logging system has not been selected for Connector/J, it is recommended that Connector/J's log factory is configured to use something other than the standard logger by adding the following property to the URL or DataSource:

  • logger=Log4JLogger (for applications using Log4J)

  • logger=CommonsLogger (for applications using Apache Commons Logging)

  • logger=Jdk14Logger (for applications using Java 1.4 or later logging)

Further configuration of the plugin is done via the Enterprise Service Manager's user interface, using the Query Analyzer tab. From here, the capture of query performance data for a given MySQL instance that the plugin is being with can be enabled or disabled. Further, the level of detail that is captured; summary, examples (with source code locations) and EXPLAIN plans can be configured as well.