This page should include design documentation for the integration of MySQL as a supported backend.

This can also include User documentation.

Code Change

• MySQL doesn't support sequence
  Use column properties AUTO_INCREMENT instead. Since RHQ supported SQLServer before, which use IDENTITY to mock sequence, RHQ code can support this solution without much effort.

• MySQL is table name case sensitive
  Change the table name in RHQ code to upper case.

• MySQL has reversed word "interval", "maxvalue", "key".
  Change the column "interval"  to "interval_value", "maxvalue" to "max_value", "minvalue" to "min_value", and change alias "key" to "key_table".

• MySQL cannot update the table which is in the subquery. 
  The solution to solve this problem is case by case. 

  1. If it is native SQL, use equivalent SQL instead. For more details, refer to http://dev.mysql.com/doc/refman/5.5/en/subquery-restrictions.html

  2. If the SQL is generated by JPQL, use equivalent JPQL instead.

  3. Retrieve the subquery result set first, and then apply the result to the original SQL.

• MySQL doesn't support alias in delete clause.
  Remove the table alias in delete clause and table name instead.

• MySQL native SQL is missing
  Add native SQL for mysql by demand.

• MySQL datatype mapping is missing
  Add mysql datatype mapping.

How to build RHQ

• It is the same as before.

How to install RHQ 

• Prepare MySQL databse.

• Comment PostgreSQL database setting and uncomment MySQL database settings in configuration file rhq-server.properties.

• Others are the same as before.

MySQL Version

• It supports version 5.x. (It is tested on 5.5.28)

MySQL Engine

• It currently supports engine InnoDB. (It is only test on InnoDB, and some InnoDB feature is used.)

MySQL Installation

1. Download release of MySQLfrom http://dev.mysql.com/downloads/mysql/#downloads

2. Install it as described here: http://dev.mysql.com/doc/refman/5.5/en/binary-installation.html

MySQL Preparation

1. Modify MySQL configuration file /etc/my.cnf

   1. modify max_allowed_packet property in "[mysqld]" section
      max_allowed_packet = 16M # default is 1M

   2. add character_set_server property in "[mysqld]" section (character_set_server is formerly 
      character_set_server = utf8

   3. use case sensitive collation in "[mysqld] section
      collation_server = utf8_bin

   4. add sql_mode property in "[mysqld] section
      sql_mode = NO_AUTO_VALUE_ON_ZERO

2. Restart MySQL

   1. service mysql restart

3. Create MySQL database rhq

   1. go to mysql command line

   2. create database rhq;

4. Create MySQL database user rhqadmin and grant it privileges

   1. CREATE USER 'rhqadmin'

   2. GRANT ALL PRIVILEGES ON *.* TO 'rhqadmin'@'%' IDENTIFIED BY 'rhqadmin' WITH GRANT OPTION;

   3. GRANT ALL PRIVILEGES ON *.* TO 'rhqadmin'@'localhost' IDENTIFIED BY 'rhqadmin' WITH GRANT OPTION;

5. Restart MySQL

   1. service mysql restart