Multilingual support and database issues — how many languages?

Question

I am considering enabling multiple languages (in CiviCRM 4.6.12 with Drupal) and would like to hear from those of you who've done so about your experiences. Specifically, how many languages are you using, and have you observed any database problems as a result?

I read in a discussion here that enabling more than 2-3 languages can lead to database problems. This presentation from CiviCon 2013 gives the number as 3-4. Has anyone successfully used more than 4?

Are there other pitfalls of multilingual support I should be aware of, even if I stick to only 2-3 languages?

Answer 1

So the problem is that each new language is stored as a separate column for each translated field (eg title, body...), so the more fields that are translated and the more new language you add, the sooner you will hit a limit of mysql on how many columns can be set. As for the exact number, it depends as well on the type of content you store, mysql has a longer explanation, but 2 or 3 is what I've tested without reaching that limit: http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

Other than that, works pretty ok, but keep in mind that because the structure of the database is changed, it becomes impossible to revert to a single language version.

Answer 2

In addition to the answers of Xavier and Mathieu I would like to add some non-technical aspects for your consideration.

IMHO a multilanguage set up considerably raises complexity in a lot of ways for users and admins alike. Think auomated emails, online forms or extensions just to name a few. All issues arising from those can probably be dealt with but it is definitely time consuming and not fun at all (also given the lack of documentation)!

In short, unless it is absolutely necessary I would recommend to stick to a single-language set up.

PS: Even eithout multi-language you can use CiviCRM's user interface in various languages - however some elements (such as option groups, contribution status...) wont be translated...

Answer 3

I did some more testing, and managed to get it up to 8 languages, if using the Barracuda file type in MySQL. In the default mode (Antelope), I could only enable 6 languages.

The main issue is the "civicrm_event" table, which has a lot of text fields.

WARNING: this has not been extensively tested. Use at your own risk.

To try it:

In the MySQL configuration (ex: /etc/mysql/conf.d/civicrm.cnf), add:

innodb_file_per_table = 1
innodb_file_format = Barracuda

(then restart MySQL)

Then alter the civicrm_event table to use dynamic allocation for all varchar/text columns:

drush civicrm-sql-cli

alter table civicrm_event ROW_FORMAT=DYNAMIC;

I documented this in: CRM17827