• Edit
  • Delete

ullright multilanguage / third language / non-latin character set / mysql utf-8 / charset / encoding

Example for russian:

  • app.yml
    •   i18n: 
          supported_languages: [ 'de', 'en', 'ru' ]
  • html head
    • use a font supporting cyrillic characters (e.g. default fonts, google fonts)
  • html: add language switcher
    • <?php echo link_to('Russian', 'ullUser/changeCulture?culture=ru') ?>
  • Check mysql default settings (see below)
  • Convert mysql database
    • Character set: UTF-8 Unicode
    • Collation: utf8_unicode_ci
    • mysqldump -u root --opt --quote-names --skip-character-set my_database | replace CHARSET=latin1 CHARSET=utf8 > mysql.dump
    • mysql -u root my_database < mysql.dump

MySql stuff

Find out current settings

General: (should all be utf8)

  • show variables like 'char%';
  • show variables like 'collation%';

Overview/All

  • Databases/Schemata
    • SELECT schema_name,default_character_set_name FROM information_schema.SCHEMATA;
  • Collation
    • SELECT T.table_schema, T.table_name, CCSA.character_set_name FROM information_schema.`TABLES` T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation;
  • Columns
    • SELECT table_schema, table_name, column_name, character_set_name FROM information_schema.`COLUMNS` C;

Specific

Schema:

SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "schemaname";

For Tables:

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,        information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA WHERE CCSA.collation_name = T.table_collation   AND T.table_schema = "schemaname"   AND T.table_name = "tablename";

For Columns:

SELECT character_set_name FROM information_schema.`COLUMNS` C WHERE table_schema = "schemaname"   AND table_name = "tablename"   AND column_name = "columnname";

Set mysql defaults to UTF-8

@see https://docs.moodle.org/23/en/Converting_your_MySQL_database_to_UTF8

  • sudo vi /etc/mysql/my.cnf
    • vi /etc/mysql/my.cnf
    • [mysqld]
      character-set-server=utf8
      collation-server=utf8_unicode_ci
    • [client]
      default-character-set=utf8
    • [mysql]
      default-character-set=utf8
  • sudo restart mysql

 

  • mysql
    • ALTER DATABASE schemaname DEFAULT CHARACTER SET utf8  DEFAULT COLLATE utf8_unicode_ci;

Convert data

  • cd ~
  • mysqldump -c -e --default-character-set=utf8 --single-transaction --skip-set-charset --add-drop-database -B mydb > dump.sql
  • sed 's/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci/' <horde.sql | sed 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8/' > dump-utf8-fixed.sql
  • iconv -f ISO-8859-2 dump-utf8-fixed.sql -t UTF-8 > dump-utf8-converted.sql
  • mysql mydb < dump-utf8-converted.sql
  • rm dump*.sql

More Unicode stuff

utf8mb4 / utf8mb4_unicode_ci -> more character support?

Ja: https://mathiasbynens.be/notes/mysql-utf8mb4

http://www.joelonsoftware.com/articles/Unicode.html