Optimal collation for openQRM database with MySQL

Thomas
2013-12-27
2014-01-25
  • Thomas
    Thomas
    2013-12-27

    Hello!

    After completing installation of openQRM 5.1 on MySQL, the default collation is "latin1_swedish_ci".


    mysql> select * from information_schema.SCHEMATA S where schema_name = "openqrm";+--------------+-------------+----------------------------+------------------------+----------+
    | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
    +--------------+-------------+----------------------------+------------------------+----------+
    | def | openqrm | latin1 | latin1_swedish_ci | NULL |
    +--------------+-------------+----------------------------+------------------------+----------+
    1 row in set (0.00 sec)

    Is this a recommended collation?
    Or shall this be modified?

    THX

     
  • Hi Thomas,

    latin1_swedish_ci should be no problem, I just discovered that my mysql db uses same settings. We don't use any chars not covered by latin1 and utf8 equally. But I'll keep an eye on it.

    Thanks for your report
    Alexander

     
  • Thomas
    Thomas
    2014-01-08

    Hi Alexander,

    thanks for your reply.

    This means no immediate action is required.
    However it could make sense to apply a different default collation for MySQL (either latin1 or utf8) after I figured out where this modification must be applied.

    THX

     
  • Hi Thomas,
    as it seems we have forgotten to add DEFAULT CHARACTER SET to /ect/db/mysql/openqrm-mysql-init.sql - But using it for years now without complains it seems to be a term of no danger.

    CU
    Alexander

     
  • Thomas
    Thomas
    2014-01-18

    OK.
    This means I could modify /ect/db/mysql/openqrm-mysql-init.sql and enter the default character set.
    What would that be?

     
  • Hi Thomas,

    replace create database OPENQRM_DB'; by create databaseOPENQRM_DB' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; in /ect/db/mysql/openqrm-mysql-init.sql. But this needs to reinstall openQRM.

    Hope it helps
    Alexander

     
  • Thomas
    Thomas
    2014-01-21

    Hm... what if I modify the collation with Adminer (http://www.adminer.org/de/)?
    I mean, will the database still be usable after "conversion"?
    Or should I better reinstall openQRM?

    THX

     
  • Thomas
    Thomas
    2014-01-25

    Hi!

    I managed to create openQRM database with the modification in /usr/share/openqrm/etc/db/mysql/openqrm-mysql-init.sql.
    All objects how have collation "utf8_general_ci".

    THX for you great support!
    Thomas