Moving from sqlite to mysql: plugin migration fails
Added by Dietmar H over 11 years ago
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 over 11 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) over 11 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 over 11 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 over 11 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.