.. index:: mysql; deployment notes

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