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.

[cboos]

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.

[cboos]

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

[cboos]

PatchWelcome.

[thijstriemstra]

Replying to cboos:

PatchWelcome.

What should this patch (not) do?

[thijstriemstra]

Also see #6554.