InstallMariaDB

Install MariaDB/MySQL

Internally JPA using Eclipse Link and Gemini JPA is used. This requires the use of the OSGi DataSourceFactory specification, which is provided by Gemini DBAccess and easily allows the use of MySQL using its JDBC connector.

However the actual server for testing and running Package Drone is in our primary use case MariaDB. This works, since right now the MySQL JDBC driver still can access the version of Maria DB which is distributed by Ubuntu 14.04. True, this is our specific setup and we don't want to add any specifics to Maria DB, since neither Eclipse Link nor Gemini DBAccess are aware of Maria DB right now.

So as long as you go with MySQL, everything should be fine.

Global Settings

There are a few global settings which you have to check.

Most settings will go into the file my.cnf which, depending in your distribution, can be located at /etc/mysql/my.cnf or /etc/my.cnf. Or maybe even at a different place.

Also see:

• https://dev.mysql.com/doc/refman/5.5/en/option-files.html

Note: Be sure to restart the MySQL server after making any changes. Note: The configuration file my.cnf also holds configurations for the local MySQL client. However the client is configured under a different section ([client]). Be sure not to mix this up with the MySQL server section ([mysqld] in most cases).

Allow TCP connections

The JDBC MySQL driver does require access to the database via a TCP connection. So your MySQL database server has to be configured to allow access using TCP and to also actually listen to incoming TCP connections.

This is mainly controlled by the property bind-address in the section [mysqld] (for MySQL 5.6.1+) and the command line argument (--bind-address).

The default port of the JDBC driver is 3306, which is also the default MySQL/MariaDB port. It can be set in the section [mysqld] using port = 3306.

If you are unsure how to configure MySQL, it might be best to search the net ...

In short:

• If you have MySQL 5.6.1+ or MariaDB set the parameter bind-address in the section [mysqld] to 127.0.0.1 in order to accept connections from localhost
• If you have MySQL 5.1.x on RHEL6/CentOS 6, the setting bind-address seems to work, although this is an older MySQL version.

Also see:

• https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_bind_address
• https://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_bind-address

Maximum Allowed Packet Size

The MySQL server limits the size of packets which are send from the client to the server. The MySQL communication protocol assumes that a single SQL statement has to fit into one such packet. However, when you store BLOBs, and since the protocol does not stream that data, but encapsulates it into a packet, the packet size has to be increased to the biggest BLOB you want to store.

In the file my.cnf ensure that you have set:

[mysqld]
max_allowed_packet=1073741824

Also see:

• https://dev.mysql.com/doc/refman/5.5/en/packet-too-large.html
• https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_packet

Default to "InnoDB"

Older versions of MySQL default to "MyISAM" as their internal storage engine. MySQL 5.1 and CentOS6/RHEL6 has this default for example.

However, Package Drone requires foreign keys, with the CASCADE ON DELETE option. Which is not supported by "MyISAM" but works with "InnoDB" as a storage engine.

To check which storage engine is the default you can execute the following SQL command (using the command line tool mysql):

mysql> show engines;
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                    | Transactions | XA   | Savepoints |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| MyISAM     | YES     | Default engine as of MySQL 3.23 with great performance     | NO           | NO   | NO         |
| InnoDB     | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
+------------+---------+------------------------------------------------------------+--------------+------+------------+
5 rows in set (0.00 sec)

Depending of your database setup this result might look different. Important is, that you have a storage engine called InnoDB and that its support is set to DEFAULT.

If it is not set to DEFAULT you can put the following line in you my.cnf configuration file:

[mysqld]
default-storage-engine=INNODB

Some older MySQL versions might require the following option name:

[mysqld]
storage-engine=INNODB

Also see:

• https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_default_storage_engine
• https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_storage_engine

Create a new database

$ mysqladmin create pd

Create a new user

Log on, as an administrator, to you MySQL database using the command line tool mysql and execute the following commands:

CREATE USER 'pd'@'localhost' IDENTIFIED BY 'pdpass';
GRANT ALL ON pd.* TO 'pd'@'localhost';

Or when using Docker (with the default bridge on 172.17.0.0):

CREATE USER 'pd'@'172.17.0.0/255.255.0.0' IDENTIFIED BY 'pdpass';
GRANT ALL ON pd.* TO 'pd'@'172.17.0.0/255.255.0.0';

Note: When you are using docker or a database instance on a remote host, you might need to set 'bind-address = 0.0.0.0in the file/etc/mysql/my.cnf` and restart mysql.

Note: In most cases you start the mysql command line tool with mysql -u root -p

Please continue with the [Setup]