Edgewall Software

Changes between Version 76 and Version 77 of MySqlDb


Ignore:
Timestamp:
Sep 7, 2018, 12:16:04 AM (12 months ago)
Author:
Ryan J Ollos
Comment:

Document #10993.

Legend:

Unmodified
Added
Removed
Modified
  • MySqlDb

    v76 v77  
    1818    - tested Percona 5.6 with Trac 1.1.4
    1919
    20 In order to avoid Unicode and international characters problems in Trac, eg international characters turning into question marks with mysterious **"Incorrect string value"** errors logged, the Trac database '''must''' be configured  with the `utf8` character set and the `utf8_bin` collation type.
     20In order to avoid Unicode and international characters problems in Trac, eg international characters turning into question marks with mysterious **"Incorrect string value"** errors logged, the Trac database '''must''' be configured  with one of the following character set and collation type pairs:
     21* `utf8` / `utf8_bin`
     22* `utf8mb4` / `utf8mb4_bin`
     23The configuration is specified through the `character_set_database` and `collation_database` variables of `my.cnf`. Additionally, all tables must be of type `utf8_bin` or `utf8mb4_bin` collation.
    2124
    22 Also, all tables '''must''' be created as **InnoDB** or **TokuDB** type tables, because Trac uses a transaction mechanism that is not supported by MyISAM tables, see [comment:ticket:8067:5] and [http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html MySQL docs]. Be aware that the cluster storage engine NDB introduces some limits for Trac (#8567).
     25Tables '''must''' be created as **InnoDB** or **TokuDB** type tables, because Trac uses a transaction mechanism that is not supported by MyISAM tables, see [comment:ticket:8067:5] and [http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html MySQL docs]. Be aware that the cluster storage engine NDB introduces some limits for Trac (#8567).
     26
     27The `default_storage_engine` and `default_tmp_storage_engine` variables in `my.cnf` must be set to one of the storage engines supported by Trac.
    2328
    2429A proper database can be created with any MySQL client, like this:
    25 {{{
     30{{{#!sql
    2631CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
    2732}}}
    2833
    2934With MySQL version 5.5.3 and up, you can also select the [http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html mb4] variant of utf8, which enables you to store any Unicode character, not only those in the [WikiPedia:Basic_Multilingual_Plane Basic Multilingual Plane]. Note that you'll need at least Trac 0.12.3 for this (#9766).
    30 {{{
     35{{{#!sql
    3136CREATE DATABASE trac DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
    3237}}}
    3338
    3439To check character set settings for your database, issue the following commands:
    35 {{{
     40{{{#!sql
    3641mysql> USE trac;
    3742Database changed
     
    5156
    5257If you are setting a new collation on an existing database, make sure that column collations are converted too:
    53 {{{
     58{{{#!sql
    5459mysql> SHOW FULL COLUMNS FROM `node_change`;
    5560+-------------+-------------+-----------+-
     
    6974
    7075Usually, you also want to create a user and give this user access to the database created above:
    71 {{{
     76{{{#!sql
    7277GRANT ALL ON trac.* TO tracuser@localhost IDENTIFIED BY 'password';
    7378}}}
    7479
    7580The connection string will then be:
    76 {{{
     81{{{#!ini
    7782mysql://tracuser:password@localhost/trac
    7883}}}
     
    117122InnoDB is the default storage engine for MySQL since v5.5, but older databases may be defined using MyISAM.
    118123Check which tables are not already converted:
    119 {{{
     124{{{#!sql
    120125SELECT table_name, engine FROM information_schema.tables WHERE table_schema = DATABASE();
    121126}}}
    122127
    123128For every table in the Trac database which is not InnoDB run the following:
    124 {{{
     129{{{#!sql
    125130ALTER TABLE `table_name` ENGINE = InnoDB;
    126131}}}
     
    129134
    130135First modify the DB collation to ensure new tables will be created properly:
    131 {{{
     136{{{#!sql
    132137ALTER DATABASE `trac_database` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
    133138}}}
    134139
    135140Then modify the collation for tables. The command below changes the default collation for new columns and converts old columns as well. Run the following for every table:
    136 {{{
     141{{{#!sql
    137142ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
    138143}}}