Server environment - configure from scratch

The setup requires root access privileges. You need to open the command line and enter the listed commands. After each command press ENTER.

Set locale

Ensure that the system (and after installation apache too) is using a UTF8 capable locale. The current system locale is set either with the tool "update-locale" or directly in the file /etc/default/locale.

The available locales can be listed with "locale -a". For apache this may be configured separately in the apache config files. For Ubuntu in /etc/apache2/envvars.

For details see: https://www.thomas-krenn.com/de/wiki/Locales_unter_Ubuntu_konfigurieren.

Install Apache

sudo apt-get install apache2

Enable Additional Modules for Apache

Apache web server needs to be fine tuned for translate5 to work properly. Switch to Apache directory with

cd /etc/apache2

and enter these commands to enable required Apache submodules

sudo a2enmod rewrite filter deflate headers expires

sudo apache2ctl restart

Last command is mandatory for changes to take effect. If you see, upon issuing sudo apache2ctl restart, the following warning:

Could not reliably determine the server's fully qualified domain name, using 127.0.1.1 for ServerName

do not worry, it’s not important.

Install MySQL

sudo apt-get install mysql-server mysql-client

The installation process will prompt a few times for a new root password access for MySQL. Please provide a password, when prompted. Please ensure you type the same password every time. If you omit a password you could get problems with a MySQL version greater 5.7, in this case follow the instructions in: http://askubuntu.com/a/801950/428011
You can test the reachability of the database by issuing at a terminal:

mysql -u root -p

If you specified a password during MySQL setup, type it when prompted; otherwise, just hit Return. It is possible that you have to use "sudo" or be root to connect to mysql as root with an empty password on newer systems.
If everything went fine, you should see the greeting message and the shell prompt:

mysql>

Leave mysql shell prompt with

exit;

MySQL configuration

In general you will find the mysql configuration in "/etc/mysql/mysql.conf.d/mysqld.cnf" or in "/etc/mysql/my.cnf" or a similar file.

Charset and Collation

The charset is set on creation of the concrete DB in the next step.

Full text index optimization

For proper functionality of the termportal search the minimum index token length must be set to 1:

innodb_ft_min_token_size=1
After changing this values the indexes must be rebuild:

Rebuild the index if you just changed some of the above settings:

set GLOBAL innodb_optimize_fulltext_only=ON;
OPTIMIZE TABLE terms_term;

On MySQL 5.7 the innodb_optimize_fulltext_only flag should be kept disabled, since there the rebuild of the index does not properly work!

Timezone

Ensure that the timezone of MySQL is the same as the timezone configured in apache php and CLI php.

To get the MySQLs timezone call:

SELECT @@global.time_zone, @@session.time_zone;
-- or more easier to read:
SELECT TIME_FORMAT(TIMEDIFF(utc_timestamp(), NOW()), '%H:%i') gmtshift;

Not allowed SQL Modes

The following SQL Mode must not be set: ONLY_FULL_GROUP_BY, NO_ZERO_IN_DATE, NO_ZERO_DATE, STRICT_TRANS_TABLES

Executing the following command in mysql lists the current SQL modes:

SHOW VARIABLES WHERE Variable_name = 'sql_mode';

To achieve this, you can add / change the following setting to your mysql configuration file:

sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

For MySQL 8 this would be: 
sql_mode = "ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

See http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html for further information.

Binary Logging and unprivileged DB users

If you are connecting translate5 with an unprivileged (non root) user to the Database, please ensure that no binary logging or the less safe option "log_bin_trust_function_creators" is set.
To set it just add

log_bin_trust_function_creators = 1 

to your mysql config file.
See https://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html for further information.

InnoDB files per table

Also it is recommended to enable the mysql config innodb_file_per_table so that each Table is stored in an own file. This is very useful if you plan to import many huge projects.
You find Instructions for that in the MySQL documentation and in the cpanel forum.

Increase innodb_buffer_pool_size

Since some tables contain a lot of data, the innodb_buffer_pool_size should be increased to 64MB. Background are multiple issues. One is described below with the import of huge files.

Another issue could be migration scripts which affects tables containing a lot of data, like LEK_segment_data. Migration scripts working with that table can run into the following or similar error:
exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1206 The total number of locks exceeds the lock table size' in /webs/in cludes/ZendFramework-1.12.9-minimal/library/Zend/Db/Statement/Pdo.php:228

Solution is here:

• edit /etc/my.cnf
• add / change the line
  innodb_buffer_pool_size=512MB    # the suitable value depends on several points, see here.
• Restart the MySQL server

See also here and https://stackoverflow.com/questions/6901108/the-total-number-of-locks-exceeds-the-lock-table-size

MySQL gone away errors on import

The following changes should not be needed anymore. We keep the advice as hint, if we receive similar problems again.

In an older version of translate5 the imported packages were store in the DB. This led to MySQL gone away errors on the import process. It was recommended to increase the config value max_allowed_packet to a higher value (about 32 - 64M).

The same was for the innodb_log_file_size, which should be increased to the same value. (At least 10x the size of the biggest sdlxliff/xlf compressed file). Also the size of innodb_buffer_pool_size was advised to be checked.

Restart MySQL

If you made some changes to the MySQL configuration, please restart the database server:

/etc/init.d/mysql restart

Install PHP 8.0 core

Please refresh your package list, the package names may be different, but it must be at least PHP 8.0.X at the moment.

sudo apt-get update

and install php 8.0

sudo apt-get install php8.0

Check if you have the correct version of php installed

php -v

Change PHP configuration

If you plan to upload project files bigger than 2MB, you have to change the following settings in the php.ini file.

Please edit therefore the php.ini file which should be located in /etc/php5/apache2/php.ini.

Search for the below settings and change their values to 600M to allow file uploads until 600 Megabytes.

post_max_size=600M
upload_max_filesize=600M
memory_limit=1024M #or higher, if possible for production instances and large files in imports to be on the save side
max_execution_time=3600 #for production instances and large files in imports to be on the save side

Also consider to set the error_log configuration to get application properly logged.

Install php extensions

Install the extension dom, fileinfo, iconv, mbstring, zip, pdo_mysql,gd and curl

sudo apt-get install libapache2-mod-php8.0 php8.0-mysql php8.0-gd php8.0-dom php8.0-mbstring php8.0-zip php8.0-curl php8.0-intl php8.0-json

• dom
• fileinfo
• gd with Freetype-Support
• iconv
• mbstring
• pdo_mysql
• zip
• curl
• xml
• intl
• json
• opcache
• soap
• sqlite3

Install openjdk-8-jdk

(needed by openTMStermTagger, which is used by translate5; a newer java version does not work; financial or work support to make OpenTMSTermTagger run under a newer java version is welcome )

sudo apt install openjdk-8-jdk

Install unzip

sudo apt-get install unzip