Migrating to MySQL in Instiki

[[!include contents]]
:category: help
So you've been running Instiki, for a while, with the default [SQLite](http://www.sqlite.org/) database engine. Now you've decided to migrate to a beefier alternative, like [MySQL](http://mysql.com/). Here's how to migrate your data (based on an article by [Al Hoang](http://blogs.law.harvard.edu/hoanga/2007/02/07/migrating-instiki-from-one-database-type-to-another/)).
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.
1. Add the line
 gem "mysql"
 to the end of the Gemfile and re-run
 % ruby bundle
 which will install the `mysql` gem for you.
1. 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
2. Export your database to `dump/fixtures/*.yml`
 % ruby bundle exec rake db:fixtures:export_all
3. Edit `config/database.yml`, replacing
 production:
 adapter: sqlite3
 database: db/production.db.sqlite3
 with something along the lines of
 <pre><code>production:<br /> adapter: mysql<br /> database: <i style="color:red">your_db_name</i><br /> username: <i style="color:red">your_db_username</i><br /> password: <i style="color:red">your_db_password</i><br /> host: 127.0.0.1<br /> port: 3306</code></pre>
 (The precise details will depend on your setup.)
4. Create a database in MySQL, with the same name as the one you gave in `database.yml`, using something like
 <pre><code>% echo "create database <i style="color:red">your_db_name</i>" | mysql -u <i style="color:red">your_db_username</i> -p <i style="color:red">your_db_password</i></code></pre>
5. Initialize the database tables and reimport your data
 % ruby bundle exec rake db:migrate
 % ruby bundle exec rake db:fixtures:import_all
## Case Sensitivity## {#Case}
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](http://dev.mysql.com/doc/refman/5.1/en/case-sensitivity.html).
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
~~~~~ {: lang=sqlite}
ALTER TABLE `pages` MODIFY `name` varchar(255) COLLATE latin1_bin DEFAULT NULL;
ALTER TABLE `wiki_references` MODIFY `referenced_name` varchar(255) COLLATE latin1_bin NOT NULL DEFAULT '';
ALTER TABLE `wiki_files` MODIFY `file_name` varchar(255) COLLATE latin1_bin NOT NULL;
~~~~~~~~~~~~~~~~~~~~~
or
~~~~~ {: lang=sqlite}
ALTER TABLE `pages` MODIFY `name` varchar(255) COLLATE utf8_bin DEFAULT NULL;
ALTER TABLE `wiki_references` MODIFY `referenced_name` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE `wiki_files` MODIFY `file_name` varchar(255) COLLATE utf8_bin NOT NULL;
~~~~~~~~~~~~~~~~~~~~~
depending on what encoding your database was created with. (MySQL defaults to `latin1` so, even though Instiki uses UTF-8, the database may or may not see it that way.)