MySQL Backend

This notes detail the MySQL backend for the Roundup issue tracker.

Prerequisites

To use MySQL as the backend for storing roundup data, you also need to install:

1. MySQL RDBMS 4.0.18 or higher - https://www.mysql.com/. Your MySQL installation MUST support InnoDB tables (or Berkeley DB (BDB) tables if you have no other choice). If you’re running < 4.0.18 (but not <4.0) then you’ll need to use BDB to pass all unit tests. Edit the roundup/backends/back_mysql.py file to enable DBD instead of InnoDB.

2. Python MySQL interface - https://pypi.org/project/mysqlclient/

Preparing the Database

The Roundup user expects to be able to create and drop its database when using roundup_admin init.

In the examples below, replace roundupuser, rounduppw and roundupdb with suitable values.

This assumes you are running MySQL on the same host as you are running Roundup. If this is not the case, setting up remote credentials, SSL/TLS etc. is beyond the scope of this documentation. However examples are welcome on the wiki or mailing list.

These references may be helpful: https://dev.mysql.com/doc/refman/8.0/en/create-user.html and https://dev.mysql.com/doc/refman/8.0/en/grant.html.

Creating a Role/User

The following command will create a roundupuser with the ability to create the database:

mysql -u root -e 'CREATE USER "roundupuser"@"localhost" IDENTIFIED WITH mysql_native_password BY "rounduppw"; GRANT ALL on roundupuser.* TO "roundupuser"@"localhost";'

Other Configuration

If you are indexing large documents (e.g attached file contents) using MySQL, you may need to increase the max_allowed_packet size. If you don’t you can see the error:

'MySql Server has gone away (2006)'

To do this edit /etc/my.conf and change:

[mysqld]
max_allowed_packet = 1M

the ‘max_allowed_packet’ value from ‘1M’ to ‘64M’ or larger.

Alternatively you can install an alternate indexer (whoosh, xapian etc.) and force the tracker to use it by setting the indexer setting in the tracker’s config.ini.

This fix was supplied by telsch. See issue https://issues.roundup-tracker.org/issue2550743 for further info or if you are interested in developing a patch to roundup to help work around this issue.

Running the MySQL tests

Roundup tests expect an empty MySQL database. Two alternate ways to provide this:

1. If you have root permissions on the MySQL server, you can create the necessary database entries using the following SQL sequence. Use mysql on the command line to enter:

   CREATE DATABASE rounduptest;
   USE rounduptest;
   GRANT ALL PRIVILEGES ON rounduptest.* TO rounduptest@localhost
        IDENTIFIED BY 'rounduptest';
   FLUSH PRIVILEGES;
   

2. If your administrator has provided you with database connection info, see the config values in ‘test/db_test_base.py’ about which database connection, name and user will be used.

The MySQL database should not contain any tables. Tests will not drop the database with existing data.

Note that rounduptest is a well known account. You should delete it and the rounduptest database and create a new user/database for production use.

Showing MySQL who’s boss

If things ever get to the point where that test database is totally hosed, just:

$ su -
# /etc/init.d/mysql stop
# rm -rf /var/lib/mysql/rounduptest
# /etc/init.d/mysql start

and all will be better (note that on some systems, mysql is spelt mysqld).