How to Install Plone with RelStorage and MySQL

These step by step instructions describe how to install Plone on Ubuntu with RelStorage connected to MySQL as the main database. Familiarity with Linux systems administration is expected. Update: These instructions were revised in August 2009 for Plone 3.2.3 and RelStorage 1.2.0.

Server Preparation

Start with a minimal installation of Ubuntu 8.04 LTS. It will be easiest if you start with a server (or virtual server) that provides no public services yet. Otherwise, make a backup of everything important on the server, especially any MySQL databases, before proceeding.

Most of these commands should be performed with root privileges. You might need to type sudo su - to get root privileges. In these instructions, commands you should type are preceded with the character $ to represent the prompt, but you should not type the $.

Ensure the main and universe repositories are configured and that the system is fully up to date. See below. Use a mirror appropriate for your location.

$ cd /etc/apt
$ cat sources.list
deb http://us.archive.ubuntu.com/ubuntu hardy main universe
deb http://us.archive.ubuntu.com/ubuntu hardy-updates main universe
deb http://us.archive.ubuntu.com/ubuntu hardy-security main universe
$ apt-get update
$ apt-get upgrade

Restart the server if major packages were upgraded.

Install Ubuntu Packages

Install all of the following packages. During the installation process, the system will prompt you for a MySQL administration password. Make up a good but memorable password.

$ apt-get install build-essential mysql-client mysql-server libmysqlclient15-dev
$ apt-get install wget libjpeg62-dev

Don’t forget to install your favorite text editor. One good choice is nano.

Configure MySQL

It is important to configure MySQL before starting Plone or creating any database, because these MySQL configuration changes will affect the layout of the database.

With root privileges, edit /etc/mysql/my.cnf. Change it as follows:

  1. In the [mysqld] section, find the line that configures max_allowed_packet. Change the line to read:
    max_allowed_packet = 32M

    This changes the maximum size of a ZODB object. 32 megabytes is probably sufficient for most sites.

  2. Find the commented line that contains skip-innodb. Remove that line or leave it commented. Add the following lines below it:
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_buffer_pool_size=64M
    innodb_log_file_size=16M
    innodb_log_buffer_size=8M
    innodb_flush_log_at_trx_commit=1
    innodb_file_per_table

    These lines improve the performance of RelStorage over the default configuration. The values are intended for a server with about 256 MB of RAM, running both MySQL and some Zope processes. Feel free to adjust these values now, but avoid touching them once your database has been created.

Since we changed the innodb_log_file_size parameter, we need to stop MySQL, delete the log files, and start MySQL again:

$ /etc/init.d/mysql stop
$ rm /var/lib/mysql/ib_logfile*
$ /etc/init.d/mysql start

Create the Databases and Grant Access

Start the MySQL command line client. The client will prompt you for the administration password you typed earlier. (If you chose not to set an administration password, leave out the -p option.)

$ mysql -p

At the MySQL prompt, type (copy and paste) the following commands to create two databases in MySQL: one for testing RelStorage, the other for storing Plone data; then grant access to these databases. Replace PASSWORD with a new password of your choosing. This password does not need to be memorized by humans, so you can make it ugly and strong, but write it down somewhere.

mysql> create database relstoragetest;
mysql> create database plone;
mysql> grant all on relstoragetest.* to 'relstoragetest'@'localhost' identified by 'relstoragetest';
mysql> grant all on plone.* to 'plone'@'localhost' identified by 'PASSWORD';
mysql> flush privileges;
mysql> quit

Install Plone

Type the commands below to download and unpack Plone.  Version 3.2.3 was the most current release when these instructions were written.

$ cd /root
$ wget 'http://launchpad.net/plone/3.2/3.2.3/+download/Plone-3.2.3-UnifiedInstaller.tgz'
$ tar xvzf Plone-3.2.3-UnifiedInstaller.tgz

Type the commands below to install Plone in standalone mode, not cluster mode. Plone’s cluster installation mode installs a ZEO server, but RelStorage does not need a ZEO server. The standalone installation mode provides everything we need to build an installation of Plone that supports clustering with RelStorage.

$ cd Plone-3.2.3-UnifiedInstaller
$ ./install.sh standalone

The install script compiles Plone and its dependencies, then installs at /usr/local/Plone .  Note that the Plone installer will generate a password for you to use later. The generated password is stored in the file /usr/local/Plone/zinstance/adminPassword.txt.

Patch ZODB

After Plone is installed, you need to patch its installed copy of ZODB to support RelStorage. This is all you need to do:

$ cd /usr/local/Plone/Zope-2.10.7-final-py2.4/lib/python/ZODB
$ wget 'http://svn.zope.org/*checkout*/relstorage/trunk/poll-invalidation-1-zodb-3-7-1.patch'
$ patch -p0 < poll-invalidation-1-zodb-3-7-1.patch

The patch command should generate the following output to confirm that the patch was successful:

patching file Connection.py
patching file DB.py

Modify the Buildout

We are going to install RelStorage by enhancing the buildout configuration in Plone. Edit the file /usr/local/Plone/zinstance/buildout.cfg, changing it as follows:

  1. Add RelStorage and MySQL-python to the list of eggs to install. Do this by finding the eggs option in the [buildout] section and appending RelStorage and MySQL-python to it. Note that each egg must be listed on a separate line, so make the option look like this:
    eggs =
        Plone
        RelStorage
        MySQL-python
  2. Add ZODB3 to the list of “additional fake eggs”. Plone is using a concept called fake eggs to aid in the transition from the old monolithic Zope installation method to Python egg installation. Find the additional-fake-eggs option in the [zope2] section and add ZODB3 = 3.7.1 to the end. After your edit, the option should look like this:
    additional-fake-eggs =
        ZConfig
        pytz
        ZODB3 = 3.7.1
  3. Add zodbconvert to the list of scripts to generate in the [zopepy] section.
    scripts = zopepy zodbconvert
  4. Configure Plone to use RelStorage by adding the following to the [instance] section.  Replace PASSWORD with the strong password you set for the MySQL user named plone.
    rel-storage =
        type mysql
        db plone
        user plone
        passwd PASSWORD

We also need to add a line to versions.cfg to solve a version dependency issue. Type the following command (be sure to type “>>”, which means to append to the file, rather than just “>”, which means to overwrite the file):

$ echo zc.recipe.testrunner = 1.1.0 >> /usr/local/Plone/zinstance/versions.cfg

Run Buildout

Execute the buildout command to install RelStorage:

$ cd /usr/local/Plone/zinstance
$ bin/buildout

You should get output similar to the following.

Uninstalling zopepy.
Uninstalling instance.
Uninstalling zope2.
Installing zope2.
Creating fake eggs
Updating productdistros.
Installing instance.
Getting distribution for 'RelStorage'.
Got RelStorage 1.2.0b2.
Getting distribution for 'MySQL-python'.
zip_safe flag not set; analyzing archive contents...
Got MySQL-python 1.2.3c1.
Generated script '/usr/local/Plone/zinstance/bin/instance'.
Installing zopepy.
Generated script '/usr/local/Plone/zinstance/bin/zodbconvert'.
Generated interpreter '/usr/local/Plone/zinstance/bin/zopepy'.
Updating zopeskel.
Updating chown.
chown: Running
chmod 600 .installed.cfg
find /usr/local/Plone/zinstance -type d -name var -exec chown -R plone \{\} \;
find /usr/local/Plone/buildout-cache/eggs -type d -name LC_MESSAGES -exec chown -R plone \{\} \;
find /usr/local/Plone/zinstance -name runzope -exec chown plone \{\} \;
Updating unifiedinstaller.
Updating precompile.
  precompiling python scripts in /usr/local/Plone/zinstance/products
  precompiling python scripts in /usr/local/Plone/zinstance/parts/productdistros

If the Buildout command did not work, please feel free to ask questions. Buildout is an excellent tool, but when something goes wrong, its error messages are often cryptic.

Test RelStorage on MySQL

Now it’s time to test RelStorage and MySQL together. It only takes a moment and could prevent a lot of frustration. The tests will detect various bad configurations of MySQL.

Type the following to run the tests:

$ cd /usr/local/Plone/zinstance
$ bin/instance test -m mysql -p

If everything is configured properly, there should be no test failures or errors. The output should look similar to this:

Running unit tests:
  Running:

  Ran 113 tests with 0 failures and 0 errors in 24.016 seconds.

Some troubleshooting tips:

  • If you set up MySQL on a different server (a procedure not covered by these instructions), synchronize the clocks of the server and client using ntp. The MySQL adapter for RelStorage requires the clocks on the server and client to be within 60 seconds of each other.
  • If the connection isolation test fails, it most likely means that MySQL created MyISAM tables instead of InnoDB tables, which can happen if the InnoDB engine failed to initialize when MySQL started. InnoDB is required for RelStorage.

Run Plone

Type this to start Plone without putting the process in the background:

$ /usr/local/Plone/zinstance/bin/instance fg

The first time Plone runs, RelStorage should create the database tables automatically. If everything goes well, at this point you can point your web browser at http://localhost:8080/manage, log in (see /usr/local/Plone/zinstance/adminPassword.txt for the password), and add a Plone Site to the root folder. The new Plone site will be stored entirely in MySQL.

Clustering

RelStorage supports clustering with no additional software. ZEO is not needed. Just set up more Plone instances with the same software (use rsync, nfs, or some other method to keep the software in sync) and point all of the clients at the same database. Furthermore, RelStorage has several options designed to improve cluster performance, such as memcached integration. If you are setting up a cluster, you are welcome to contact the author about the best way to set it up for your site.

11 thoughts on “How to Install Plone with RelStorage and MySQL

  1. Great article. I’ve recently created a Plone / Relstorage / Mysql instance. Our plan is to get our sites production ready and then clone it over to our production server. I didn’t set any of the following settings – is it too late to introduce them now? What about on the production database when we clone? What’s the impact of not using them?

    max_allowed_packet = 32M
    innodb_data_file_path = ibdata1:10M:autoextend
    innodb_buffer_pool_size=64M
    innodb_log_file_size=16M
    innodb_log_buffer_size=8M
    innodb_flush_log_at_trx_commit=1
    innodb_file_per_table

  2. Hi Anthony. Here are my thoughts on these settings:

    – I think you can change max_allowed_packet, innodb_buffer_pool_size, and innodb_flush_log_at_trx_commit anytime.

    – innodb_data_file_path can change. It simply has to point at the database file.

    – Either innodb_log_buffer_size or innodb_log_file_size (maybe both) reacts badly to a change. It disables InnoDB sometimes. Be careful.

    – You have to dump and re-create the database in order to activate changes of innodb_file_per_table.

    I would also like to quote two emails from Russell Quick. When I have some time, I’ll update this howto to use Russell’s method instead of mine.

    “””
    We have one small issue and were wondering if you knew what to do with this: Each time we change and run buildout it wipes out the zope.conf file and we have to copy over the database configuration section. Is there a way to set up buildout so that it will set the relstorage zodb_main section automatically or not rewrite the file?
    “””

    “””
    We’ve discovered how to set the zope.conf file from the buidout so it doesn’t have to be changed manually. You can add the following section to the [instance] section of the buildout.cfg, adjusting your parameters for your specific database settings (we used postgresql):

    rel-storage =
       type postgresql
       dbname yourdatabasename
       host yourhost
       user yourpostgresusername
       password yourpostgrespassword
    

    “””

    Thanks for your help, Russell!

  3. Anthony, to answer the rest of your question, the default configuration of MySQL:

    – will not pass the tests (max_allowed_packet must be larger than 16M),

    – bottlenecks RelStorage write performance, and

    – puts all the important data in one big file without providing tools to manage that big file.

  4. We’ve managed to get buildout working using a different mount point and database for temp and the plone folder. No editing of zope.conf is required after running the buildout. We adopted this configuration with the idea we’d be running multiple plone instances – we’d have one database for each Plone instance.


    rel-storage =
    type mysql
    db zope
    host 127.0.0.1
    user zope
    passwd xxxxx
    zodb-temporary-storage =

    mount-point /temp_folder

    db zope_temp
    host 127.0.0.1
    user zope
    passwd xxxxx

    zope-conf-additional =

    mount-point /plone

    db plone
    host 127.0.0.1
    user zope
    passwd xxxxx

  5. Well I am at a bit of a bump in the road. Following these above instructions I run bin/buildout and it appears to run correctly, however when I run the bin/instance test -m mysql -p command I get the following error message:

    Running tests at level 1
    Test-module import failures:

    Module: relstorage.tests.testmysql

    Traceback (most recent call last):
    File “/usr/local/Plone/buildout-cache/eggs/RelStorage-1.2.0b2-py2.4.egg/relstorage/tests/testmysql.py”, line 20, in ?
    from relstorage.adapters.mysql import MySQLAdapter
    File “/usr/local/Plone/buildout-cache/eggs/RelStorage-1.2.0b2-py2.4.egg/relstorage/adapters/mysql.py”, line 52, in ?
    import MySQLdb
    ImportError: No module named MySQLdb

    Total: 0 tests, 0 failures, 0 errors

    Test-modules with import problems:
    relstorage.tests.testmysql

    When I do dpkg -l python-mysqldb I get:
    Desired=Unknown/Install/Remove/Purge/Hold
    | Status=Not/Inst/Cfg-files/Unpacked/Failed-cfg/Half-inst/trig-aWait/Trig-pend
    |/ Err?=(none)/Hold/Reinst-required/X=both-problems (Status,Err: uppercase=bad)
    ||/ Name Version Description
    +++-===========================-===========================-======================================================================
    ii python-mysqldb 1.2.2-7ubuntu1 A Python interface to MySQL

    Does anyone have any ideas of what I need to do to get this test to work?
    PFiero

  6. I’ve got things running. A couple things to note:
    a) Where the document says “at least version 8.04 LTS” for the Ubuntu version, it appears to me (not being REALLY all that Ubuntu literate) that it should read “only use version 8.04 LTS”.

    b) When putting in the mysql statements instead of:
    grant all on relstoragetest.* to ”@’localhost';
    it should read:
    grant all on relstoragetest.* to ‘relstoragetest’@’localhost’ identified by ‘relstoragetest';

    My instance test now passes. Yay.

  7. I try to build Plone 4 with RelStorage.
    I added RelStorage and psycopg2 to the eggs section and the rel-storage configuration in base.cfg. buildout works fine and i am able to add a plone site in my postgresql db.

    The only issue i have is that zodbconvert is not built. I added
    zodbconvert to the zopepy section in my base.cfg:

    [zopepy]
    # installs a zopepy python interpreter that runs with your
    # full Zope environment
    recipe = zc.recipe.egg
    eggs = ${buildout:eggs}
    interpreter = zopepy
    scripts = zopepy zodbconvert

    Any pointers what might cause the zodbconvert script not to be built?

  8. Shane,

    Thanks for this excellent write up. I realize it is a few years old, just curious how RelStorage has worked out for you in that time?

  9. I’ve been very pleased with RelStorage. I have used it for small sites with no problems at all. I’ve helped people set it up for large sites and while they have had some problems, none of them have been showstoppers.

Comments are closed.