Edgewall Software
Modify

Opened 15 years ago

Closed 10 years ago

#8089 closed enhancement (duplicate)

MySQL tables setup and upgrade

Reported by: anatoly techtonik <techtonik@…> Owned by:
Priority: normal Milestone:
Component: admin/console Version: 0.11-stable
Severity: major Keywords: mysql helpwanted
Cc: dale.miller@…, Thijs Triemstra Branch:
Release Notes:
API Changes:
Internal Changes:

Description (last modified by Christian Boos)

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.

Attachments (1)

T8089 MySQL charset collation check on create DB.patch (1.1 KB ) - added by Peter Suter 12 years ago.
Check charset and collation are supported when initializing a MySQL DB

Download all attachments as: .zip

Change History (21)

comment:1 by Christian Boos, 15 years ago

Description: modified (diff)
Keywords: mysql added
Milestone: 0.11.4
Severity: normalmajor

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.

comment:2 by anatoly techtonik <techtonik@…>, 15 years ago

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."

comment:3 by Dale Miller <dale.miller@…>, 15 years ago

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.)"

comment:4 by anatoly techtonik <techtonik@…>, 15 years ago

Component: generaladmin/console

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.

comment:5 by Christian Boos, 15 years ago

Keywords: helpwanted added

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).

comment:6 by Dale Miller <dale.miller@…>, 15 years ago

Cc: dale.miller@… added

comment:7 by anonymous, 15 years ago

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;

comment:8 by anatoly techtonik <techtonik@…>, 14 years ago

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.

in reply to:  8 ; comment:9 by dale.miller@…, 14 years ago

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.

comment:10 by dale.miller@…, 14 years ago

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"

in reply to:  9 comment:11 by anatoly techtonik <techtonik@…>, 14 years ago

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.

comment:12 by Dale Miller <dale.miller@…>, 14 years ago

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

comment:13 by Christian Boos, 14 years ago

Milestone: next-minor-0.12.xunscheduled

in reply to:  13 comment:14 by Thijs Triemstra, 13 years ago

Cc: Thijs Triemstra added

Replying to cboos:

PatchWelcome.

What should this patch (not) do?

comment:15 by Thijs Triemstra, 13 years ago

Also see #6554.

by Peter Suter, 12 years ago

Check charset and collation are supported when initializing a MySQL DB

comment:16 by Peter Suter, 12 years ago

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.

comment:17 by anatoly techtonik <techtonik@…>, 12 years ago

Why doesn't it help with upgrades?

comment:18 by anatoly techtonik <techtonik@…>, 12 years ago

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.

comment:19 by Peter Suter, 12 years ago

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.)

Last edited 10 years ago by Peter Suter (previous) (diff)

comment:20 by Peter Suter, 10 years ago

Milestone: unscheduled
Resolution: duplicate
Status: newclosed

Superseded by #10993.

Modify Ticket

Change Properties
Set your email in Preferences
Action
as closed The ticket will remain with no owner.
The resolution will be deleted. Next status will be 'reopened'.
to The owner will be changed from (none) to the specified user.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.