MySQL tables setup and upgrade

[anatoly techtonik <techtonik@…>]

Currently, we have a lot of requirements on MySqlDb page. Namely requirement for tables to be in utf8_bin collation to handle Unicode and InnoDB/NDB format for tables needed for transactions.

These requirements can enforced/automated by upgrade scripts.

[Christian Boos]

Did you forget about #4990? ;-)

But yes, I think we ought to do something about this, if only to refuse to complete an initenv if the requirements are not met.

[anatoly techtonik <techtonik@…>]

It is not that I've completely forgot, but yes, I didn't remember it. =)

There is a related comment in second paragraph on MySqlDb page. I wonder if it is still actual after 0.10.4 is release.

To quote: "When changing collation to utf8 MySQL may fail in 4.1.x and 5.0.x versions on MyISAM table type. It will complain about "too big" indexes in some Trac tables. This will be (hopefully) addressed in 0.10.4. As a workaround it has been recommended to use InnoDB tables instead of MyISAM where this limitation is absent."

[Dale Miller <dale.miller@…>]

Altering the tables from MyISAM to InnoDB also has an impact on anyone that is using mysqlhotcopy. The following is taken from the ​MySQL 5.0 Reference Manual section about backups. We are using 5.0.67-community (I did not check the other manuals.)

"You can also create a binary backup simply by copying all table files (*.frm, *.MYD, and *.MYI files), as long as the server isn't updating anything. The mysqlhotcopy script uses this method. (But note that these methods do not work if your database contains InnoDB tables. InnoDB does not necessarily store table contents in database directories, and mysqlhotcopy works only for MyISAM and ISAM tables.)"

[anatoly techtonik <techtonik@…>]

MySQL never was an officially supported engine, and the lack of transaction support in MyISAM tables is essential flaw. I bet the most of us agree that switching to mysqldump from mysqlhotcopy is a minor tradeoff comparing to the possibility to leave Trac environment in inconsistent state or in other words broken. If DB engine doesn't support transactions then there is a good chance that it will shoot a hole in your Trac's foot, so it won't be able to run anymore without a help of professional DB surgeon. See #8067

Some issues may arise from InnoDB index limitation when converting from MyISAM, but they are not actual for Trac tables.

People should use mysqldump for backing up the database and the proper place for this warning is MySqlDb wiki page, but frankly speaking this task of backing up DB should be included in trac-admin.

[Christian Boos]

Patch welcome for issuing a warning (or even an error) at startup - this could be done in the trac.db.mysql_backend.MySQLConnector component, in the environment_created and environment_needs_upgrade methods implementing the IEnvironmentSetupParticipant interface.

Failing a patch in time, the MySqlDb instructions should be enough (I'll take care of wiki documentation sync later today).

[anonymous]

The ​MySqlDb instructions include a step to alter the default character set and collate for each table:

ALTER TABLE `table_name` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;

Is the above DEFAULT correct or should it be a CONVERT command instead?

ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

[anatoly techtonik <techtonik@…>]

anonymous, there should be CONVERT command. I've just run into the same problem and corrected instructions.

MySqlDb page now contains all necessary information for automated MySQL support and troubleshooting. See also #6554.

[dale.miller@…]

Replying to anatoly techtonik <techtonik@…>:

anonymous, there should be CONVERT command. I've just run into the same problem and corrected instructions.

I (dale.miller@…) was the anonymous that asked if it should be a CONVERT command instead of DEFAULT.

Since no one ever responded I did what it said and used the DEFAULT instead of the CONVERT. Should I run the

ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

command now against all of the tables? What is the risk involved in doing this or not doing this?

You updated the ​MySQLdb page but is there any Trac installation programs that also need to be modified?

Thank you.

[dale.miller@…]

When I do the

show full columns from `node_change`;

I get

+-------------+------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| Field       | Type | Collation       | Null | Key | Default | Extra | Privileges                      | Comment |
+-------------+------+-----------------+------+-----+---------+-------+---------------------------------+---------+
| rev         | text | utf8_general_ci | NO   | PRI | NULL    |       | select,insert,update,references |         |
| path        | text | utf8_general_ci | NO   | PRI | NULL    |       | select,insert,update,references |         |
| node_type   | text | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| change_type | text | utf8_general_ci | NO   | PRI | NULL    |       | select,insert,update,references |         |
| base_path   | text | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
| base_rev    | text | utf8_general_ci | YES  |     | NULL    |       | select,insert,update,references |         |
+-------------+------+-----------------+------+-----+---------+-------+---------------------------------+---------+

Note that all of "Type" fields say "text" in my database.

In the updated ​MySQLdb page it shows:

mysql> SHOW FULL COLUMNS FROM `node_change`;
+-------------+-------------+-----------+-
| Field       | Type        | Collation | 
+-------------+-------------+-----------+-
| rev         | varchar(64) | utf8_bin  | 
| path        | text        | utf8_bin  | 
| node_type   | varchar(32) | utf8_bin  | 
| change_type | varchar(32) | utf8_bin  | 
| base_path   | text        | utf8_bin  | 
| base_rev    | varchar(64) | utf8_bin  | 
+-------------+-------------+-----------+-
6 rows in set (0.00 sec)

I assume you are truncating the output on the wiki page. Is my assumption correct?

It might be helpful to say "Other columns (Null, Key, Default, Extra, Privileges, Comment) truncated from output.

Is it ok that my "Type" all show "text"? Looking at all the other tables, all my "Type" values are text or int(11). Fields with int(11) include ticket, time, authenticated, last_visit, changetime, size, version, readonly, due, and completed.

In the ticket and report tables the 'id' type is "int(10) unsigned"

[anatoly techtonik <techtonik@…>]

Replying to dale.miller@…:

Since no one ever responded I did what it said and used the DEFAULT instead of the CONVERT. Should I run the

ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

command now against all of the tables? What is the risk involved in doing this or not doing this?

If you don't do this then there is a chances that your repository browser will be locked like it happened to me in #9031. There is no risk in doing this, but spare backup never hurts.

is there any Trac installation programs that also need to be modified?

No changes to existing installations are required, but it would be nice if Trac could warn users automatically (or even fix). That's why this ticket is still opened.

And yes, your text columns are ok. The difference may be explained by many factors - versions of Trac or MySQL used to create DB, to update DB, whatever optimizations were made to this DB. So it's probably doesn't worth thinking about it as long as your Trac works.

[Dale Miller <dale.miller@…>]

Thank you for replying. For the record this is our current Trac "System Information"

      Trac: 0.11.5
    Python: 2.5.1 (r251:54863, Sep 21 2007, 22:46:31) [GCC 4.2.1 (SUSE Linux)]
setuptools: 0.6c8
     MySQL: server: "5.0.77-community", client: "5.0.77", thread-safe: 1
   MySQLdb: 1.2.2
    Genshi: 0.5.1
mod_python: 3.3.1
Subversion: 1.6.6 (r40053)
    jQuery: 1.2.6

[Christian Boos]

PatchWelcome.

[Thijs Triemstra]

Replying to cboos:

PatchWelcome.

What should this patch (not) do?

[Thijs Triemstra]

Also see #6554.

[Peter Suter]

Check charset and collation are supported when initializing a MySQL DB

[Peter Suter]

The above attached patch adds a check to the MySQL connector when a new DB is initialized. If the character set and collation are not set to one of the currently supported combinations it aborts with a TracError.

Of course this does not help with upgrades.

Seems to work with Trac trunk, Python 2.5, MySQL 5.5, and MySQLdb Python bindings 1.2.2.

[anatoly techtonik <techtonik@…>]

Why doesn't it help with upgrades?

[anatoly techtonik <techtonik@…>]

Do you mean it won't help if existing tables are not yet in utf8? Chances are that MySQL settings are wrong too in this case. Perhaps it worths to note that tables needs to be upgraded too if MySQL is configured correctly.

It should also be possible to modify CREATE TABLE command to enforce engine type and collation.

[Peter Suter]

I mean the patch only checks the character_set_database and collation_database system variables when a new Trac environment is created. So when an old Trac environment is upgraded this patch does not help in any way.

However further testing suggests these variables are apparently not really all that meaningful here:

mysql> SHOW VARIABLES LIKE 'collation_database';
| collation_database | utf8_general_ci |
mysql> CREATE DATABASE trac;
mysql> USE trac;
mysql> SET collation_database = utf8_bin;
mysql> SHOW VARIABLES LIKE 'collation_database';
| collation_database | utf8_bin |
mysql> CREATE TABLE test (a TEXT);
mysql> SHOW FULL COLUMNS FROM test;
+-------+------+-----------------+...
| Field | Type | Collation       |...
+-------+------+-----------------+...
| a     | text | utf8_general_ci |...
+-------+------+-----------------+...

(Edit: The above was flawed and can be ignored. ​Documentation states: You should not set the value of this variable manually. As described on MySqlDb#Collationfromwhatevertoutf8_bin use ALTER DATABASE (and ALTER TABLE) instead:

mysql> CREATE DATABASE trac;
mysql> USE trac;
mysql> SHOW VARIABLES LIKE 'collation_database';
| collation_database | utf8_general_ci |
mysql> ALTER DATABASE `trac` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
mysql> SHOW VARIABLES LIKE 'collation_database';
| collation_database | utf8_bin |
mysql> CREATE TABLE test (a TEXT);
mysql> SHOW FULL COLUMNS FROM test;
+-------+------+-----------+....
| Field | Type | Collation |....
+-------+------+-----------+....
| a     | text | utf8_bin  |....
+-------+------+-----------+....

So checking the variables could still be useful.)

[Peter Suter]

Superseded by #10993.