Changes between Version 76 and Version 77 of MySqlDb
- Timestamp:
- Sep 7, 2018, 12:16:04 AM (6 years ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
MySqlDb
v76 v77 18 18 - tested Percona 5.6 with Trac 1.1.4 19 19 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. 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 one of the following character set and collation type pairs: 21 * `utf8` / `utf8_bin` 22 * `utf8mb4` / `utf8mb4_bin` 23 The 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. 21 24 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). 25 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). 26 27 The `default_storage_engine` and `default_tmp_storage_engine` variables in `my.cnf` must be set to one of the storage engines supported by Trac. 23 28 24 29 A proper database can be created with any MySQL client, like this: 25 {{{ 30 {{{#!sql 26 31 CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; 27 32 }}} 28 33 29 34 With 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 31 36 CREATE DATABASE trac DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; 32 37 }}} 33 38 34 39 To check character set settings for your database, issue the following commands: 35 {{{ 40 {{{#!sql 36 41 mysql> USE trac; 37 42 Database changed … … 51 56 52 57 If you are setting a new collation on an existing database, make sure that column collations are converted too: 53 {{{ 58 {{{#!sql 54 59 mysql> SHOW FULL COLUMNS FROM `node_change`; 55 60 +-------------+-------------+-----------+- … … 69 74 70 75 Usually, you also want to create a user and give this user access to the database created above: 71 {{{ 76 {{{#!sql 72 77 GRANT ALL ON trac.* TO tracuser@localhost IDENTIFIED BY 'password'; 73 78 }}} 74 79 75 80 The connection string will then be: 76 {{{ 81 {{{#!ini 77 82 mysql://tracuser:password@localhost/trac 78 83 }}} … … 117 122 InnoDB is the default storage engine for MySQL since v5.5, but older databases may be defined using MyISAM. 118 123 Check which tables are not already converted: 119 {{{ 124 {{{#!sql 120 125 SELECT table_name, engine FROM information_schema.tables WHERE table_schema = DATABASE(); 121 126 }}} 122 127 123 128 For every table in the Trac database which is not InnoDB run the following: 124 {{{ 129 {{{#!sql 125 130 ALTER TABLE `table_name` ENGINE = InnoDB; 126 131 }}} … … 129 134 130 135 First modify the DB collation to ensure new tables will be created properly: 131 {{{ 136 {{{#!sql 132 137 ALTER DATABASE `trac_database` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; 133 138 }}} 134 139 135 140 Then 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 137 142 ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; 138 143 }}}
