Configuring the Product Index to Use MySQL

« Back to Indexer Documentation

Navigation

• Overview
• Setup Database
• Create Table Schemas
• Creating the Indexer's Database Triggers
• Edit the Config File

Overview

This document describes the steps necessary to configure the Product Index to store product summaries in a MySQL database, instead of the default SQLite database. These instructions can be adapted to allow the Product Index to use another database, provided that a proper JDBC driver is installed.

Setup Database

Note: for this step, you must have privileges to add new MySQL users and create new databases on your server. If you do not have the required privileges, ask your database administrator to perform this step for you.

We recommend that you create a new database exclusively for the Product Index:

CREATE DATABASE product_index;

Next, create two users on the database. The first is an admin user and the second only has read/write permissions.

GRANT ALL PRIVILEGES ON `product_index`.* TO 'pi_admin'@'localhost' IDENTIFIED BY 'mySecretPassword';
GRANT SELECT, INSERT, UPDATE, DELETE ON `product_index`.* TO 'pi_write'@'localhost' IDENTIFIED BY 'myOtherSecretPassword';

Create Table Schemas

Next, you must create four tables in your newly created database. The Product Index needs tables to store events, products summaries, product summary links, and product summary. The tables must be created with the SQL commands in the following linked sql file, which can be executed as the pi_admin user we just created:

MYSQL Product Index Schema

The commands also create foreign key constraints and indexes. The Product Index will still function fully without either of these, but they are included as enhancements. The foreign key constraints ensure that the integrity of the data is maintained. The indexes can greatly improve performance retrieving products from the database.

Creating the Indexer's Database Triggers

Indexer SQL Dependencies

Beyond the creation of these four tables, the Indexer is dependent on two additional database components: the feplus system and OnEventUpdate stored procedures:

mysql_feplus

Found in the schema/mysql_feplus directory, feplus implements region-identifying functionality based on latitude and longitude. It uses the definitions in the etc/config/regions.xml file to associate a region-name with a particular latitude/longitude location of an event or product. OnEventUpdate stored procedures uses this functionality for origin and geoserve products, which ultimately determine properties such as event significance.

onEventTrigger Stored Procedures

Found in the schema/productIndexOnEventUpdateMysql.sql file, these procedures summarize products and events for efficient retrieval. The trigger is evoked when the Indexer's Java classes use time/latitude/longitude information in products to create or modify events.

Trigger Installation

mysql_feplus

1. Determine the database user, password and database name.
2. In your linux terminal, cd to the PDL etc/schema/mysql_feplus directory.
3. Following the directions in that directory's README.txt file, use your editor of choice to update the username, password and database values in the setup.sh shell-script file to match your database.
4. Run the setup by entering ./setup.sh at the terminal.
5. This will create your feplus database and SQL functions.

onEventTrigger Stored Procedures

1. Continuing from the feplus install above, cd .. to rise one level in your directory structure.
2. From your terminal, key in:
   mysql5 -u username -p databasename < productIndexOnEventUpdateMysql.sql
3. This will create your eventSummary table and Indexer onEventTrigger procedures.

Your MySQL database is now set up properly, so it's time to configure the Product Index.

Edit the Config File

As described on the configuration page, the Product Index can accept 3 configuration parameters: indexfile, driver, and url. indexfile only applies to SQLite databases; you should not set this parameter for MySQL databases. Modify your global configuration file (config.ini) to include the following:

...

listeners = listener_indexer

[listener_indexer]
type = gov.usgs.earthquake.indexer.Indexer
...
index = indexer_index

[indexer_index]
type = gov.usgs.earthquake.indexer.JDBCProductIndex
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://127.0.0.1/product_index?user=pi_write&password=myOtherSecretPassword

In this example, the [indexer_index] section sets the parameters for the Product Index. The driver parameter must be set to "com.mysql.jdbc.Driver" to use MySQL. Only the MySQL Java driver is provided by default, so if to use another database (PostgreSQL, Oracle, etc) you need to install the relevent JDBC driver.

The url parameter should be set to a valid JDBC URL. For MySQL, the format is jdbc:mysql://[host][:port]/[database]?user=[username]&password=[password]