CORAL Component documentation: MySQLAccess

Radovan Chytracek

CERN

December 2005


1. Introduction

1.1. Purpose of the component

The MySQLAccess package is a plugin library providing an implementation of the IRelationalDomain interface for accessing an MySQL database. The package is based on the MySQL C API version 4.0.24 which is still based on ASCII protocol and old password scheme.

1.2. Known problem and restrictions

The package is for the moment tested against MySQL 4.0.26 server version. Earlier server versions might work as of versions 4.0.13 and later. The plan is to add incrementally support for 4.1 and 5.0 versions of MySQL server in the short time after 4.0 is proven to work well.

Bulk inserts are emulated as there is no API which allows that.

1.3. Repository of the component

:pserver:anonymoys@coral.cvs.cern.ch:/cvs/coral/coral/MySQLAccess

2. MySQLAccess Semantics

2.1. The logical data hiererchy

In order to connect to an MySQL database using CORAL one should specify a connection string which typically has a format such as

 
   mysql://databaseServerName:port/schemaOrDbName
  
where the port number is optional (default is port number 3306).

The schema name is in practice the same as MySQL user account name. A user has access to the tables defined in the schema of another user for which all the relevant privileges have been granted.

2.2. Controlling MySQL-specific parameters

2.2.1. Table spaces

New tables, indices and LOBs are created in the default table space as set in the MySQL server configuration file. This can't be changed yet in the 4.0.x version. In 4.1.x version one can enable tablespace per table feature but it's not controllable parameter on the client side so this can't be in sync with OracleAccess plug-in.

2.2.2. (C)LOB handling

When reading back a LOB object from the database server we rely entirely on MySQL machinery. We might change it in the future to perform retrieval in chunks with configurable size if we see a gain in performance. There is an important client & server side parameter max_alowed_packet_size which specifies the maximum size for the transfer of CLOB/BLOB data. Our server setting in /etc/my.cnf is:

 
     set-variable=max_allowed_packet=21000000
   
which means roughly 21MB maximum size of CLOB/BLOB data per one transfer. If the size is exceeded MySQL will issue an error. We do not set anything yet in CORAL for the client side.

2.2.3. Silent type conversions

When a column is created and its size is more then 255 characters the silent (VAR)CHAR conversion into CLOB type happens. CORAL does it already for 4.0, MySQL itself does it from version 4.1.6 onwards. Since we will provide 4.1 & later support in CORAL soon we consider it a safe behavior.

If user defines a column of type std::string without specifying the size and whether it is fixed or not the SQL type becomes VARCHAR(255) for this column defintion. It is better to be more explicit when defining table column description.

2.2.4. Silent index creation

The silent creation of indices for foreign key columns happens in the referencing table to avoid the MySQL error:

   ERROR 1005 (00000): Can't create table './DbName/TableName.frm' (errno: 150)
   
The reason is that MySQL performs renaming of the altered table and if there is no index created it does not know anymore about the column and issues an error. Beware, this is valid so far for the InnoDB tables (the CORAL default engine). This error might not appear for MyISAM tables as for this engine MySQL apparently parses the SQL for FK constraints but ignores it completely. Note that MySQL does not maintain referential integrity for MyISAM tables.

2.2.5. CLOB/BLOB unique constraints, indexes & implications

The implication for unique constraints on CLOB/BLOB columns is that the mandatory prefix must be used for index creation otherwise there is MySQL error:

   ERROR 1170 (00000): BLOB column 'ColumnName' used in key specification without a key length
   
For configuring this prefix see MySQLAccess ReferenceManual.

Foreign key constraints of the CLOB/BLOB types are not supported, it's just pure craziness! The primary keys are allowed to be of a CLOB type which is crazy as well but building a foreign key in addition is a complete madness. Doing it just doubles the total storage requirements at least not saying that updating such tables for many rows will take the whole life-time.

The index prefix is not supported for foreign key constraints. There is simply too much restrictions being applied and is very complex to build DDL for it. The golden rule is to make sure the referenced key & the foreign key are the same type and size to avoid surprises.

2.2.6. Enabling SQL TRACE

There are some ways to enable debugging of MySQL server to see what a particular session is doing but it does not seem yet as a systematic activity performed by a standard user and is more targeted to MySQL developers and testers. This has not been investigated yet in detail as there was no strong interest from our user community to do it.

3. Implementation specifics

The package is based on the the MySQL C API. The choice was favoured with respect to mysql++, the C++ client library, in order to avoid potential problems which will arise with the non-availability of the mysql++ library for a given C++ compiler, especially for the Linux platforms. The MySQL API allows the implementation of the POOL Relational Abstraction Layer (RAL) interfaces in the most efficient way.

The shared library is linked at the moment against the MySQL client libraries version 4.0.24.

It is necessary that during runtime the relevant environment path variables are setup such the the MySQL client runtime libraries can be found.

4. Related components

  • RelationalAccess, is the package where the CORAL abstract interfaces are defined.

  • CoralCommon, is the package where some developer-level interfaces and some common implementation classes are defined.

5. MySQLAccess Reference

5.1. MySQLAccess environment variables

  • CORAL_MYSQL_CLOB_PREFIX_LEN : if set to a numeric value in the range from 0 to 255, it affects the orefix size of the index prefix of all the index column of the CLOB MySQL types like TINYTEXT, TEXT, MEDIUMTEXT & LONGTEXT. If not set the default built-in prefix size is 32 bytes/chars.