Support galera cluster for MySQL

1 Requirements

Currently it´s only possible to run ILIAS on a single MySQL server or a master/slave system. ILIAS needs its own database system with appropriate Hardware, in most cases. For master/slave systems we need a second one, which does not do anything useful most of its time.
 
For large ILIAS installations and institutions with central database solutions ILIAS should be able to run on multi master database systems like “galera cluster for MySQL” (galeracluster.com). Galera Cluster for MySQL is a true Multimaster Cluster based on synchronous replication.
 
More about the technology behind Galera Cluster: http://galeracluster.com/products/technology/
 
Limitations
These systems come up with some limitations, which ILIAS does not comply all at the moment. For Galera this are (copied from galera readme):

1. Currently replication works only with InnoDB storage engine. Any writes to tables of other types, including system (mysql.*) tables are not replicated. However, DDL statements are replicated in statement level, and changes to mysql.* tables will get replicated that way. So, you can safely issue: CREATE USER..., but issuing: INSERT INTO mysql.user..., will not be replicated.
2. DELETE operation is unsupported on tables without primary key. Also rows in tables without primary key may appear in different order on different nodes. As a result SELECT...LIMIT... may return slightly different sets.
3. Unsupported queries:- LOCK/UNLOCK TABLES cannot be supported in multi-master setups.- lock functions (GET_LOCK(), RELEASE_LOCK()... )
4. Query log cannot be directed to table. If you enable query logging, you must forward the log to a file:     log_output = FILE Use general_log and general_log_file to choose query logging and the log file name
5. Maximum allowed transaction size is defined by wsrep_max_ws_rows and wsrep_max_ws_size. Anything bigger (e.g. huge LOAD DATA) will be rejected.
6. Due to cluster level optimistic concurrency control, transaction issuing COMMIT may still be aborted at that stage. There can be two transactions. writing to same rows and committing in separate cluster nodes, and only one of the them can successfully commit. The failing one will be aborted. For cluster level aborts, MySQL/galera cluster gives back deadlock error. code (Error: 1213 SQLSTATE: 40001  (ER_LOCK_DEADLOCK)).
7. XA transactions can not be supported due to possible rollback on commit.

1. All tables need to get a primary key. For those who haven’t one yet, a random id should be enough. (according to limitation #2)
2. Get rid of MySQL LOCK functions. (according to limitation #3)
3. MySQL deadlock errors should be caught and the statement should tried to be executed again a severeal times. (according to limitation #6)
4. Any other steps suggested by developers.

• Maybe there is a way do combine implementation with FR: Replace ilDB/MDB2 with PDO.
• For new galra cluster installations, it make sense to start with an up-to-date MySQL Version. Currently ILIAS does not support MySQL 5.6 (http://www.ilias.de/mantis/view.php?id=13196).

First step
The SIG performance recommends to do an evaluation about the possibilities to solve the mentioned problems in a first step. Please make an offer about the costs of this evaluation. The University of Bern and Aachen are willing to finance this evaluation.

Code analysis ILIAS 5.1 pre alpha, 18. June 2015

• Missing Primary Keys: 64 tables of ILIAS release 5.1 do not have a primary or unique key. Three of them are not used anymore and can be deleted. A complete list of tables, components and responsible is given in the following excel sheet:

• Unsupported queries: Table locking in ILIAS is mainly used for data consistency and not for performance reasons. ILIAS 5.1 uses around 120 table locks. Each of them must be rewritten using transactions respectively specific transaction isolation levels. Since the MyISAM storage engine does not support transactions, a wrapper has to be introduced, which performs table locking for MyISAM-table engines and transactions for all other engines and DB-types. In case of database errors inside transactions the ILIAS code must not rely on the existance of a transaction rollbacks.
  

include_once './Services/Database/classes/class.ilDBQueryIsolation.php';
$isolation_handler = ilDBQueryIsolation::getInstance();
$isolation_handler->setLockOptions(
		array(
			0 => array(
				'name' => 'tree', 
				'type' => ilDB::LOCK_WRITE))
);
// optionally set isolation level
$isolation_handler->setIsolationLevel(ilDBQueryIsolation::READ_UNCOMMITED);
// begin transaction or lock tables
$isolation_handler->begin();
try {
        // SQL queries ....
 
	// unlock tables, commit transaction
	$isolation_handler->commit();
}
catch(ilDBException $e)	{
	// rollback; unlock tables
	$isolation_handler->rollback();
if(!$isolation_handler->supportsRollback)  {
        // error handling...
}
}

Code analysis ILIAS 5.2 pre alpha, 30. June 2016

There are currently 14 classes containing table locks, which must be rewitten for supporting transactions:

• Optimistic Locking: Galera uses "optimistic locking", which means that other transactions are able to concurrently access the same tables and rows and the possibility of conflicting table changes is given. The DBMS checks for conflicting changes at commit time. Only if no conflicts happended the changes are made persistant. Otherwise a deadlock error is thrown.
  ILIAS could automatically handle these deadlock errors, by repeating the queries and/or transactions.
  
  ilDBQueryIsolation with deadlock-error handling:

...
// optionally set isolation level
$isolation_handler->setIsolationLevel(ilDBQueryIsolation::READ_UNCOMMITED);
 
// pass transaction code inside anonymous function 
$transaction_code = function() {
    // sql queries, transaction code
}
$isolation_handler->setTransactionsCode($transaction_code);
 
try {
// begin transaction or lock tables
$isolation_handler->begin();
// unlock tables, commit transaction
$isolation_handler->commit();
}
catch(ilDBException $e)	{
	// rollback; unlock tables
	$isolation_handler->rollback();
if(!$isolation_handler->supportsRollback)  {
        // error handling...
}
}

2 Additional Information

• Idea / conept: Jochen Erkens (j.erkens@fh-aachen.de)
• Funding: Funded by FH Aachen, CaT Concepts and Training GmbH
• Maintainer: Schmid, Fabian [fschmid], Meyer, Stefan [smeyer]
• Implementation of the feature is done by sr.solutions (basics), Databay AG, LEIFOS
• Testcases by / status: (name, e-mail)

3 Discussion

AM 2. March 2015: We are highly interested in this feature and would certainly help funding. This would be a great enhencement for big installations.
 

SIG Performance, 26. March 2015: The performance group supports this feature and is willing to look for financing an evaluation of the possibilities to solve the mentioned issues.
 

JE 26.3.2015: On first glance we thought, that one possibility might be, to use the “Filesystem Locks” mecanisem from Test&Assesment and move the option (Table/Filesystem Locks) to the setup pages. But this only seems to prevent race conditions for a single user but not cases where different users would produce inconsistent data.

JF 30 Mar 2015: We support the idea to start with an evaluation about the possibilities to solve the technical issues. Depending on the results of the evaluation this might be a DB infrastructure ILIAS supports in the future.

Simon Moor: We are also very interested in a solution. Is there any time schedule available?

JourFixe, ILIAS [jourfixe], 22 June 2015: Stefan presented the current state of the feasibility study today. We discussed the issues and what would be needed to support galera cluster. Issue (2) could be solved, but would need some work, since each table needs to be adressed individually and developers may need to take care of duplicates (for the new primary keys). (3) (5) and (6) could be adressed by a transaction based rewrite of existing table locking code. (5) could be difficult, since we have no experience with appropriate values, additionally (5) is risky since no exceptions are thrown in these cases. Stefan will look if file based locking is a better alternative than the transaction based approached.

JourFixe, ILIAS [jourfixe], 13 July 2015: We discussed the introduction of "primary keys" for all tables today. We support the idea to make primary keys for all tables mandatory. This can be done with 5.1 separately (without the remaining galera cluster support). This should documented on a separate feature wiki page (with info why this is and advantage for the use with innodb, too). We schedule this (mandatory primary keys) for 5.1.

Erkens, Jochen [j.erkens], 2015-07-13: Guidline for mandatory primary keys

Jansen, Michael [mjansen], 21 Jan. 2016: There is an open issue with the MySQL STRICT_MODE which could be also relevant for the Galera support as well ( although this is not a req. or for a Galera Cluster). Silent Truncation by MySQL <= 5.5, Problem handling il_meta_description.description and object_data.description

JourFixe, ILIAS [jourfixe], May 23, 2016: Stefan gave a short overview of the current implementation:

• Primary keys have been introduced
• He got an ILIAS running on Galera.
• About 100 transactions need to be re-written.
• Stefan suggest to offer experimental support status for Galera for 5.2. A final decision if Galera is fully supported has to be taken after additional tests and first experiences with Galera support in practise.
• We highly appreciate this feature and schedule Galera support for 5.2.

4 Implementation

The following part only describes the abstraction of Transactions wich has been implemented to have a general interface for atom queries. 

ilAtomQuery is called via iDB:

$['DIC']->database()->buildAtomQuery();

It allows to add a callable which contains the atom query. Based on the capabilities of the database, the stom query will be run as transactions or using lock-tables. 
All developers changed their table locks to atom queries, e.g.:

Test Cases

Test cases completed at {date} by {user}

• {Test case number linked to Testrail} : {test case title}

Approval

Approved at {date} by {user}.