Moving from sqlite to mysql: plugin migration fails

We were using Redmine 1.3 with sqlite for a year with no problems.
After upgrading to 2.2, it frequently locks up (need to restart webserver), also the official doc now discourages usage of sqlite in multiuser environment. Thus I need to migrate the DB.

I found a simple solution:
Add

gem 'yaml_db'

to Gemfile, then

bundle install
RAILS_ENV=production rake db:dump

now switch config in database.yml to MySQL, then

RAILS_ENV=production rake db:load

Done and works.

Looks too easy, doesn't it?
In fact an issue popped up: Plugin migration is broken.
While I can do

rake db:migrate RAILS_ENV=production

(finishes without output, thus I assume without error), running

rake db:plugins:migrate RAILS_ENV=production

fails:

# rake redmine:plugins:migrate RAILS_ENV=production
Migrating redmine_contacts (Redmine CRM plugin)...
==  CreateContacts: migrating =================================================
-- create_table(:contacts)
rake aborted!
An error has occurred, all later migrations canceled:

Mysql::Error: Table 'contacts' already exists: ...

That's not pretty. While it's not an instant issue (Redmine works), it blocks future updates.

Since I don't have any Ruby/Rails knowledge apart from what I learned from Redmine administration, I don't know the inner workings of the migration scripts and hereby kindly ask for help.

Replies (4)

RE: Moving from sqlite to mysql: plugin migration fails - Added by Etienne Massip almost 12 years ago

Compare both DB schema_migrations table contents?

RE: Moving from sqlite to mysql: plugin migration fails - Added by Jan Niggemann (redmine.org team member) almost 12 years ago

First of all let me say that the use of sqlite in production environments was never a good idea, although it might not have been explicitly mentioned. This is not a limitation of redmine, but inherent to sqlite itself, like arealy mentioned in #13210.
FTR: SQLite will lock the entire file for writing, i.e. during the time it takes for data to be written to disk, nothing else can access the DB ==> no concurrent writes.

As to your error: It's related to the CRM plugin, what does the plugin creator say about it?

RE: Moving from sqlite to mysql: plugin migration fails - Added by Dietmar H almost 12 years ago

Etienne Massip wrote:

Compare both DB schema_migrations table contents?

They differ, the MySQL version misses the plugin related entries. I checked the yaml file created by db:dump task, it doesn't contain the table schema_migrations at all.
How can I restore migration information without affecting existing data (this without dropping / recreating the tables)?

RE: Moving from sqlite to mysql: plugin migration fails - Added by Dietmar H almost 12 years ago

Found a way:
http://stackoverflow.com/questions/1670154/convert-a-ruby-on-rails-app-from-sqlite-to-mysql

Need to first create an empty DB and run the migrations, then load the data, because yaml_db doesn't dump schema_migrations.

(1-4/4)

Project

General

Profile

Redmine