Feature Wiki

Information about planned and released features

Tabs

Abandon support for MyISAM engine and MySQL fulltext search

Page Overview

[Hide]

1 Initial Problem

Currently, ILIAS allows the use of the MyISAM database engine when using MySQL or MariaDB as the database management system (DBMS). MyISAM doesn't support Foreign Keys inside or between tables - the very essence of Referential Constraints, Referential Integrity or RI.

2 Conceptual Summary

This feature request is part of FR Establish Referential Integrity.

One prerequisite for the superordinate FR is to drop the support for MyISAM by ILIAS, including a mandatory migration of existing MySQL/MariaDB-installations to enable them to establish foreign keys in later steps. Theses changes MAY be distributed later on both: update- and bugfix-level.

  • The dedicated database engine for the migration MUST be InnoDB, as this engine supports the demanded functionality for RI.
  • The migration from MyISAM to InnoDB MUST be considered a non-reversible decision per installation. If an installation is decided and updated to -lets say- ILIAS 8, then the migration of all related client-databases using MyISAM MUST be an automatic part of the database update per client.

3 User Interface Modifications

3.1 List of Affected Views

None

3.2 User Interface Details

None

3.3 New User Interface Concepts

None

4 Technical Information

4.1 Fulltext Indexes

Currently the concrete `\ilDBPdoMySQLInnoDB` imlementation of the `\ilDBInterface` does not support fulltext indexes.

class ilDBPdoMySQLInnoDB extends ilDBPdoMySQL implements ilDBInterface {
    // [...]
    public function supportsFulltext() {
        return false;
    }
    // [...]
}

Fulltext indizes are support for the InnoDB engine since:

So with the decision of abandoning the MyIsam engine we should also decide upon the fulltext indexes.

If we decided to drop the fulltext search support this could be done via (an ilDBInterface/MySQL equivalent of):

1
2
3
4
5
6
(mysql -u$DBUSER -p$DBPASS << ENDSQL
SELECT DISTINCT CONCAT('ALTER TABLE ', table_schema, '.', table_name, ' DROP INDEX ', index_name, ';') AS ddl
FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema='$1' and index_type='FULLTEXT';
ENDSQL
) | tail -n +2 | mysql -u$DBUSER -p$DBPASS

4.2 Migrating the Engine

We suggest to migrate to InnoDB via (an ilDBInterface/MySQL equivalent of):

1
2
3
4
5
6
7
(mysql -u$DBUSER -p$DBPASS << ENDSQL
SELECT CONCAT('ALTER TABLE ', table_schema,'.',table_name, ' ENGINE=InnoDB;') 
FROM information_schema.tables
WHERE TABLE_SCHEMA like '$1' AND engine = 'MyISAM' 
ORDER BY table_name DESC;
ENDSQL
) | tail -n +2 | mysql -u$DBUSER -p$DBPASS

Converting the engine to InnoDB will take a while, so executing the database steps via CLI should be the preferred approach when updating.

4.3 Setup

Changes have to be applied to the database objectives relevant for the ILIAS CLI setup. Therefore we'll have to change `ilDatabasePopulatedObjective` to enforce `InnoDB` as default engine (and raise an error if it is not supported for any reason).

5 Privacy Information

None

6 Security Implications

No security relevant changes known.

7 Contact

8 Funding

If you are interest in funding this feature, please add your name and institution to this list.

9 Discussion

Klees, Richard [rklees], 2020-03-05 - I like!

JourFixe, ILIAS [jourfixe], 09 MAR 2020 : We highly appreciate this suggestion and abandon the support of MyISAM with ILIAS 7.

JourFixe, ILIAS [jourfixe], 22 MAR 2021 : We highly appreciate this suggestion and abandon MyISAM engine support and MySQL fulltext search with ILIAS 8.

10 Implementation

MyISAM has been completely removed from the source code. The conversion for the database was implemented as DB setup migration ("ilMysqlMyIsamToInnoDbMigration").

Test Cases

Test cases completed at {date} by {user}

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

Approval

Approved at 26.05.2022 by Tesche, Uwe [utesche].

Last edited: 16. May 2022, 12:20, Tesche, Uwe [utesche]