Instiki
Migrating to MySQL (Rev #20)

Skip the Navigation Links | Home Page | All Pages | Recently Revised | Authors | Feeds | Export |

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 "mysql"

   to the end of the Gemfile and re-run

   % ruby bundle

   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

   (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 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

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

sex shop sex shop sex shop sex shop sex shop lingerie sex shop atacado calcinhas uniformes profissionais uniformes dicas de sexo

Revision from November 6, 2011 07:29:15 by Anonymous Coward

Forward in time (16 more) | Back in time (19 more) | See current | See changes | History | Rollback | View: Source | Linked from: Upgrading, News, Installing on Shared Hosts, Known Bugs, Run on Port 80