Edgewall Software

Changes between Version 65 and Version 66 of MySqlDb


Ignore:
Timestamp:
Jan 25, 2015, 3:13:47 PM (9 years ago)
Author:
figaro
Comment:

Cosmetic changes

Legend:

Unmodified
Added
Removed
Modified

  • MySqlDb

    v65 v66  
    11[[PageOutline(2-3)]]
    2 = MySQL and its MySQLdb Python bindings =
     2= MySQL and its MySQLdb Python bindings
    33
    4 Trac supports the [http://mysql.com MySQL] database backend, provided you comply with the basic requirements exposed below.
    5 There are also a couple of [#KnownIssues] and supporting this database has proven
    6 to be painful at times.
    7 We recommend that you use it only if you don't have other choice.
    8 Some users are nevertheless using MySQL successfully, and this backend is fully
    9 integrated in our test suite, so it ''might'' work for you.''
     4Trac supports the [http://mysql.com MySQL] database backend, although there are a couple of [#KnownIssues]. MySQL is fully
     5integrated in our test suite.
    106
    11 == Requirements ==
    12 === MySQL ===
     7== Requirements
     8=== MySQL
    139
    1410 '' '''Warning:'''
    … …  
    1612 For earlier versions of Trac, please see MySqlDb@22#MySQL. ''
    1713
    18 The recommended versions of MySQL are those from 5.0.x series.
    19 It might work with 4.1.x or 6.0.x, but no guarantees.
    20 The [http://askmonty.org/wiki/index.php/MariaDB MariaDB] variant of MySQL works as well (tested MariaDB 5.3.3, MySQLdb 1.2.3 and Trac 0.12.3).
     14The recommended versions of MySQL are those from 5.0.x series. The [http://askmonty.org/wiki/index.php/MariaDB MariaDB] variant of MySQL works as well, tested MariaDB 5.3.3, MySQLdb 1.2.3 and Trac 0.12.3.
    2115
    22 In order to avoid Unicode and international characters problems in Trac (e.g., 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.
     16In 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.
    2317
    24 Besides, all tables MUST be created as  '''InnoDB''' 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 NDB introduces some annoying limits for Trac (#8567).
     18Also, all tables MUST be created as  '''InnoDB''' 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).
    2519
    2620A proper database can be created with any MySQL client, like this:
    … …  
    5044}}}
    5145
    52 If this is not the case (e.g. you have `latin1`/`latin1_swedish_ci` or even `utf8` but `utf8_ci` collation), then read on to the [#Collationfromwhatevertoutf8_bin] section below to see how to change it.
     46If you have for example `latin1` or `utf8` or `utf8_ci` collation, then read on to the [#Conversion] section below on how to change it.
    5347
    54 If you are setting new collation on old database, make sure that column collations are converted too:
     48If you are setting a new collation on an existing database, make sure that column collations are converted too:
    5549{{{
    5650mysql> SHOW FULL COLUMNS FROM `node_change`;
    … …  
    7064See #3884 for info about collations.
    7165
    72 
    7366Usually, you also want to create a user and give this user access to the database created above:
    7467{{{
    … …  
    8174}}}
    8275
     76=== MySQLdb
    8377
    84 === MySQLdb ===
    85 The recommended version is MySQL-python 1.2.3, which must be compiled in ''thread-safe client'' mode. The older version 1.2.2 also works but please don't use earlier versions which are known to be problematic.
     78The recommended version is [https://pypi.python.org/pypi/MySQL-python/ MySQL-python 1.2.2+], which must be compiled in ''thread-safe client'' mode. Earlier versions are known to be problematic.
    8679
    87 See http://cheeseshop.python.org/pypi/MySQL-python/1.2.2, which contains links to the .tar.gz source distribution as well as pre-built Windows eggs, but for Python 2.5 only as of this writing. Python 2.6 binaries for windows can be found [http://www.codegood.com/archives/4 here] (unofficial site).
     80See http://cheeseshop.python.org/pypi/MySQL-python/1.2.2, which contains links to the .tar.gz source distribution as well as pre-built Windows eggs, but for Python 2.5 only as of this writing. Python 2.6 binaries for windows can be found [http://www.codegood.com/archives/4 here], unofficial site.
    8881
    8982Older versions can be found at the [https://sourceforge.net/projects/mysql-python/ mysql-python] Sourceforge project:
    … …  
    9386|| `MySQL-python-1.2.2b2` ||  Was [googlegroups:trac-users:e291b3ecb4a990a1 reported] to solve an `AttributeError: 'array.array' object has no attribute 'startswith'` error seen when viewing a changeset. These problems ought to be fixed in 1.2.2b3; if they are not, or if you have other issues, please file a bug in the [https://sourceforge.net/tracker/?group_id=22307&atid=374932 MySQL-python Bug Tracker] ||
    9487
    95 On FreeBSD this package is called {{{py-MySQLdb}}} (in ports).
     88On FreeBSD this package is called {{{py-MySQLdb}}} in [http://www.freshports.org/databases/py-MySQLdb55/ the ports collection].
    9689
    9790If you are using ActiveState Python 2.6 on Windows, you can install with {{{pypm install mysql-python}}}.
    9891
    99 ==== thread-safety is important ====
     92==== Thread-safety is important
     93
    10094You should make sure that you're using a thread-safe version of the bindings.
    10195To verify this, you can check the ''System Information'' section in the ''About Trac'' page, when you are logged with a user having the `CONFIG_VIEW` or the `TRAC_ADMIN` permission.
    … …  
    10599 - [http://mysql-python.svn.sourceforge.net/viewvc/mysql-python/tags/MySQLdb-1.2.3/MySQLdb/site.cfg?revision=646&content-type=text%2Fplain MySQLdb-1.2.3/MySQLdb/site.cfg]
    106100
    107 Note that even if you build MySQLdb to use the thread-safe libraries, ''About Trac'' may still report that it is not thread-safe. If that happens, verify that it is thread-safe by calling `MySQLdb.thread_safe()` from a standalone Python script (i.e., not under Apache). If the stand-alone test reports that MySQLdb is indeed thread-safe, then the problem may lie with the PHP module. For example, the Red Hat rpm for PHP is built to use the non-thread-safe MySQL libraries, and the Apache PHP module apparently gets loaded first. So when Python and MySQLdb load, they end up using the already loaded MySQL libraries, which are not thread-safe. The circumvention for this problem is to replace the non-thread-safe libs in `/usr/lib/mysql` with soft links, so as to have the non-thread-safe lib names point to the thread-safe libs; restart Apache, and now Trac should be running in thread-safe mode.
     101Note that even if you build MySQLdb to use the thread-safe libraries, ''About Trac'' may still report that it is not thread-safe. If that happens, verify that it is thread-safe by calling `MySQLdb.thread_safe()` from a standalone Python script, ie not under Apache. If the stand-alone test reports that MySQLdb is indeed thread-safe, then the problem may lie with the PHP module. For example, the Red Hat rpm for PHP is built to use the non-thread-safe MySQL libraries, and the Apache PHP module apparently gets loaded first. So when Python and MySQLdb load, they end up using the already loaded MySQL libraries, which are not thread-safe. The circumvention for this problem is to replace the non-thread-safe libs in `/usr/lib/mysql` with soft links, so as to have the non-thread-safe lib names point to the thread-safe libs; restart Apache, and now Trac should be running in thread-safe mode.
    108102
    109103== Conversion Procedures == #Conversion
    110104
    111 ==== MyISAM to InnoDB ====
    112 Check that tables are not already converted:
     105==== Convert from MyISAM to InnoDB storage engine
     106
     107InnoDB is the default storage engine for MySQL since v5.5, but older databases may be defined using MyISAM.
     108Check which tables are not already converted:
    113109{{{
    114 SELECT table_name, engine FROM information_schema.tables WHERE table_schema=DATABASE();
     110SELECT table_name, engine FROM information_schema.tables WHERE table_schema = DATABASE();
    115111}}}
    116112
    117 For every table in Trac database issue:
     113For every table in the Trac database which is not InnoDB run the following:
    118114{{{
    119115ALTER TABLE `table_name` ENGINE = InnoDB;
    120116}}}
    121117
    122 ==== Collation from "whatever" to "utf8_bin" ====
     118==== Convert to "utf8_bin" collation
     119
    123120First modify the DB collation to ensure new tables will be created properly:
    124121{{{
    … …  
    126123}}}
    127124
    128 Then modify the collation for tables. The command below changes the default collation for new columns and converts old columns as well. Issue the command for every table:
     125Then 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:
    129126{{{
    130127ALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
    131128}}}
    132129
    133 ==== From SQLite ====
    134 Users have reported success with the [wiki:SqLiteToMySql following method].
     130==== Convert from SQLite to MySQL
    135131
     132To convert from the default database SQLite to MySQL, please follow [wiki:SqLiteToMySql this comprehensive guide].
    136133
    137 == Known Issues ==
    138 === Still Opened ===
     134== Known Issues
     135=== Still Opened
     136
    139137[[TicketQuery(keywords=~mysql&status=!closed&group=milestone)]]
    140138
    141 === Typical Problems ===
    142 If you have some strange issues (typically `OperationalError: (2006, 'MySQL server has gone away')`, as in #3645, and your web frontend is Apache, you might have conflicting MySQL libraries. Be sure to read: [mod-wsgi:ApplicationIssues#MySQL_Shared_Library_Conflicts]. Another possibility is that you've hit MySQL bug 36639, as explained in ticket:3645#comment:48.
     139=== Troubleshooting
    143140
    144 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 [milestone:0.10.4]. As a workaround it has been [comment:ticket:3659:5 recommended] to use InnoDB tables instead of MyISAM where this limitation is absent.
     141If you have an issue, such as `OperationalError: (2006, 'MySQL server has gone away')`, as in #3645, and your web frontend is Apache, you may have conflicting MySQL libraries. Read [mod-wsgi:ApplicationIssues#MySQL_Shared_Library_Conflicts]. Another possibility is that you've hit MySQL bug 36639, as explained in ticket:3645#comment:48.
     142
     143Changing collation to ''utf8'' may fail in MySQL 5.0.x on the MyISAM table type and display the "too big" indexes in some Trac tables. Upgrade to a higher version of MySQL and use InnoDB tables, see [comment:ticket:3659:5].
    145144
    146145[[TicketQuery(keywords=~mysql&resolution=worksforme)]]
    … …  
    149148[[TicketQuery(id=4378)]]
    150149
    151 === Troubleshooting ===
    152150If you get an error from Python when using `trac-admin` like this:
    153151{{{
    154152OperationalError: (1045, "Access denied for user 'tracuser'@'localhost' (using password: YES)")
    155153}}}
     154
    156155There are a few possibilities:
    157  1. Try first to login on the command-line using the user `tracuser` (`mysql -p -h localhost -u tracuser`)
    158  1. Created user is not yet used by MySQL (Login to the MySQL server (as root) with `mysql -p` and type: `FLUSH PRIVILEGES;`)
     156 1. Try first to login on the command-line using the user `tracuser`: `mysql -p -h localhost -u tracuser`
     157 1. Created user is not yet used by MySQL. Login to the MySQL server as root with `mysql -p` and type: `FLUSH PRIVILEGES;`
    159158 1. The user is added but the host does not match in the MySQL user table (occurred on a FreeBSD setup). Correct this with: `mysql -p; use mysql; UPDATE user SET Host="localhost" WHERE User="tracuser"; FLUSH PRIVILEGES;`