26.2 Configuring a MySQL Database

This section provides configuration options for using a MySQL database. You can modify the options in the database configuration file:

Linux: my.cnf file

Windows: my.ini file

26.2.1 Configuring INNODB Storage Engine and Table Types

By default, a MySQL database uses the MyISAM table type. However, the User Application uses the INNODB storage engine, which requires the INNODB table types. You can either modify the configuration file or edit the SQL script for your database:

Modify the configuration file

In the database configuration file, change the default table statement to default-table-type=innodb. Also, remove any statement that contains the skip-innodb.

Edit the SQL scripts

In the SQL script for your database, append the ENGINE=InnoDB option to the Create Table statements.

26.2.2 Configuring the Character Set

Your User Application database must use UTF-8 as the character set. You can configure this setting for the whole server or just for a database:

On a server-wide basis

In the database configuration file, specify character_set_server=utf8.

When creating a database

To specify the character set for a database, complete the following steps:

  1. When creating the database, enter the following command: create database databasename character set utf8 collate utf8_bin;

  2. In the IDM-ds.xml file, specify the character set in the JDBC URL. For example, 

    connection-urljdbc:mysql://localhost:3306/databasename?useUnicode=true&characterEncoding=utf8&connectionCollation=utf8_bin/connection-url

26.2.3 Configuring Case Sensitivity

If you plan to back up and restore data across servers or platforms, case sensitivity must be consistent across those servers or platforms.

Linux

In the my.cnf files for all platforms on which you plan to back up and restore a database, specify 0 for lower_case_table_names. For example, lower_case_table_names=0.

Windows

In the my.ini files for all platforms on which you plan to back up and restore a database, specify 1 for lower_case_table_names. For example, lower_case_table_names=1.

26.2.4 Configuring the ANSI Setting

When you create RBPM tables in your database, the system checks whether it should use ANSI mode to initially load data in the tables. If the databse configuration file does not have an entry for ANSI mode, you might see a Guest Container Page definition not found error message.

Modify the configuration file

In the database configuration files, add the following statements:

# These variables are required for IDM User Application
character_set_server=utf8
default-table-type=innodb

# Put the server in ANSI SQL mode.
#See http://www.mysql.com/doc/en/ANSI_mode.html
ansi
Verify ANSI mode

To confirm that ANSI mode has taken effect, execute the following SQL command on your MySQL server:

mysql> select @@global.sql_mode;
+-------------------------------------------------------------+
| @@global.sql_mode                                           |
+-------------------------------------------------------------+
| REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI |
+-------------------------------------------------------------+
1 row in set (0.00 sec)

26.2.5 Configuring the Admin User Account

When you install the MySQL Database, the user account that you specify for the User Application must have full access to (be the owner of) the database. This account also needs access to the tables in the system.

Create a user to log on to the MySQL server and grant privileges to the user. For example, enter:

GRANT ALL PRIVILEGES ON dbname.* TO username@host IDENTIFIED BY 'password'

The minimum set of privileges should include the following:

  • CREATE

  • DELETE

  • INDEX

  • INSERT

  • LOCK TABLES

  • UPDATE

For more information about the GRANT command, see http://www.mysql.org/doc/refman/5.0/en/grant.html.

IMPORTANT:The user account must also have select rights to the mysql.user table. To grant the proper rights, use the following SQL syntax: 

USE mysql;
GRANT SELECT ON mysql.user TO username@host;