Instiki
Migrating to MySQL

So you’ve been running Instiki, for a while, with the default SQLite database engine. Now you’ve decided to migrate to a beefier alternative, like MySQL. Here’s how to migrate your data (based on an article by Al Hoang).

1. Make sure you’re running Instiki 0.19 or later, and have run

    % ruby bundle exec rake upgrade_instiki

   This upgrades the database schema to the latest version. The previous schema (used in earlier versions of Instiki) could cause (data loss!) problems for some users, when migrating from SQLite3 to MySQL.

2. Add the line

   gem 'ruby-mysql', "~>2.9"

   to the end of the Gemfile and re-run

   % ruby bundle install

   which will install the mysql gem for you.

3. Set an environment variable in your shell.

   • Under sh and its cousins (bash, zsh, etc) type

     % RAILS_ENV=‘production’

   • Under csh and its cousins (tcsh, etc) type

     % setenv RAILS_ENV production

4. Export your database to dump/fixtures/*.yml

    % ruby bundle exec rake db:fixtures:export_all

5. Edit config/database.yml, replacing

   production:
       adapter: sqlite3
       database: db/production.db.sqlite3

   with something along the lines of

   production:
     adapter: mysql
     database: your_db_name
     username: your_db_username
     password: your_db_password
     host: 127.0.0.1
     port: 3306
     encoding: utf8mb4

   (The precise details will depend on your setup.)

6. Create a database in MySQL, with the same name as the one you gave in database.yml, using something like

   % echo "create database your_db_name" | mysql -u your_db_username -p your_db_password

7. Initialize the database tables and reimport your data

    % ruby bundle exec rake db:migrate
    % ruby bundle exec rake db:fixtures:import_all

Case Sensitivity

There’s one more issue that you may want to consider. Under SQLite3, string comparisons are (by default) case-sensitive. Thus, you can have a page named ‘Foo’ and a page named ‘foo’, and they will be treated as distinct by Instiki. Under MySQL, the opposite is true. String comparisons are, by default, case-insensitive.

Neither behaviour is “more correct”; they’re just different. If you want to retain the case-sensitivity of page names, when you migrate to MySQL, you can change the collation on the relevant database columns. The relevant SQL commands are

ALTER TABLE `pages` MODIFY `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL;
ALTER TABLE `wiki_references` MODIFY `referenced_name` varchar(255) COLLATE utf8mb4_bin NOT NULL DEFAULT '';
ALTER TABLE `wiki_files` MODIFY `file_name` varchar(255) COLLATE utf8mb4_bin NOT NULL;

where I have assumed that your database was created with the default utf8mb4 encoding.

Tuning MySQL/MariaDB

If you receive the error “Specified key was too long; max key length is 767 bytes.” when migrating to MySQL or MariaDB or changing the encoding from utf8 to utf8mb4, this is probably due to a configuration problem in the server.

The error can be fixed by using the following config for the MariaDB server, e.g. put it in /etc/mysql/mariadb.conf.d/60-maxkeyfix.cnf:

[mysqld]
innodb_large_prefix=on
innodb_file_format=Barracuda

Also, the tables in question need to have ROW_FORMAT=DYNAMIC set.

After that, a conversion to utf8mb4 should run without errors.