ILIAS-7-Install-Tutorial for Ubuntu 20.04

Move to InnoDB

  • If you use a database that uses the storage engine MyISAM, you should move to InnoDB for several reasons:
    • Performance
    • Solve Lucene search problems
  • If your Database uses MyISAM can be found out as followes: 

When you are done, do not forget to change your client.ini.php (value mysql to innodb)

Show database status

  • mysql
  • mysql> Show Databases;
  • mysql> use youriliasdb;
  • mysql> SHOW TABLE STATUS\G;
  • Look for this line: Engine: MyISAM
  • mysql> exit

Prepare the two convert scripts

  • cd /opt/scripts
  • touch drop_db_fulltext.sh
  • chmod +x drop_db_fulltext.sh
  • touch convert_db_to_innodb.sh
  • chmod +x convert_db_to_innodb.sh
  • Code for drop_db_fulltext.sh:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#!/bin/bash
DBUSER="root"
DBPASS=""
read -s -p "Bitte geben Sie das SQL Passwort an " DBPASS && echo
 
 # Used parenthesis for the initial 'mysql' command so we can have a heredoc and still
 # pipe stdout back into mysql.
 # The tail command skips the first line of output, so we have only the ALTER TABLE commands.
 # After all that, pipe the output back into the mysql command.
  
(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
  • Code for convert_db_to_innodb.sh:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#!/bin/bash
DBUSER="root"
DBPASS=""
read -s -p "Bitte geben Sie das SQL Passwort an " DBPASS && echo
 
 
 # Used parenthesis for the initial 'mysql' command so we can have a heredoc and still
 # pipe stdout back into mysql.
 # The tail command skips the first line of output, so we have only the ALTER TABLE commands.
 # After all that, pipe the output back into the mysql command.
 
(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

Convert to InnoDB

Backup Database

  • Set the client offline in ilias/setup/setup.php
  • Backup the database to a file:
    • mysqldump corona2 > corona3.sql
    • If credentials are requiered (you can find them in ilias/data/clientname/client.ini.php)
      • mysqldump -u databaseuser -p somepasswort corona2 > corona3.sql
    • If you get the error "(Errcode: 24 - Too many open files)", add this parameter:
      • --skip-lock-tables

Convert Database

  • Begin with drop_db_fulltext.sh
  • Run the scripts like this: scriptname.sh databasename
  • Give the mysql password that is stored in client.ini.php

Change value in client.ini.php

  • cd ilias/data/clientname
  • nano client.ini.php
  • Change type = "mysql" to type = "innodb"

Troubleshooting

  • If you get an error like this: ERROR 1046 (3D000) at line 1: No database selected
  • Try this scripts:
  • The first drop-script:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
#!/bin/bash
DBNAME=somedb
DBUSER=someuser
DBPASS=somepassword
 
 
 # Used parenthesis for the initial 'mysql' command so we can have a heredoc and still
 # pipe stdout back into mysql.
 # The tail command skips the first line of output, so we have only the ALTER TABLE commands.
 # After all that, pipe the output back into the mysql command.
  
(mysql -u$DBUSER -p$DBPASS $DBNAME << 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 $DBNAME
  • For the second convert script:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DBNAME=somedb
DBUSER=someuser
DBPASS=somepassword
 
# Used parenthesis for the initial 'mysql' command so we can have a heredoc and still
# pipe stdout back into mysql.
# The tail command skips the first line of output, so we have only the ALTER TABLE commands.
# After all that, pipe the output back into the mysql command.
 
(mysql -u$DBUSER -p$DBPASS $DBNAME << ENDSQL 
SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;')  
FROM information_schema.tables 
WHERE table_schema = '$DBNAME'
AND engine = 'MyISAM'
ORDER BY table_name DESC;
ENDSQL
) | tail -n +2 | mysql -u$DBUSER -p$DBPASS $DBNAME


Information Message
No comment has been posted yet.