Defect #15564

MySQL Errors updating from 2.3.2 to 2.4.1

Added by Torben Hansen about 9 years ago. Updated almost 8 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:-
Target version:-
Resolution:Cant reproduce Affected version:2.4.1

Description

Today I've updated from Redmine 2.3.2 to 2.4.1

During the DB Migration the following errors occured (shortened summary).

Mysql2::Error: Table 'queries_roles' already exists: CREATE TABLE `queries_roles`
Mysql2::Error: Table 'custom_fields_roles' already exists: CREATE TABLE `custom_fields_roles` 

I had a look at the two database tables and found out they were empty so I just deleted them. After that, the DB Migration ran smoothly.


Related issues

Related to Redmine - Defect #19905: Problem in migrate from 2.3 to 3.0.3 Closed

History

#1 Updated by Jean-Philippe Lang about 9 years ago

  • Status changed from New to Closed
  • Resolution set to Cant reproduce

There's no way I can reproduce this, tables are not created twice for sure. I guess these tables were created during a first "attempt" to upgrade, please reopen if you can provide a way to reproduce.

#2 Updated by Eugene B about 9 years ago

I was able to reproduce it few times while migrating from 2.3.0 to 2.4.1

I am using Bitnami Redmine installers for both versions mentioned above. I had 2 problems:

1st was gem rake version: after running command to generate secret token, system returned the following error:
"Rake error “You have already activated rake 0.9.2.2, but your Gemfile requires rake 10.1.0.”
after checking what rake version is installed by running command: gem list rake system returns 0.9.2.2 version
is installed. After running command "gem install rake" a new 10.1.0 version is installed and secret token generation
is running without problems.

2nd problem is same as Torben Hansen wrote above. To reproduce I need to do the following:
1. Clean install for Latest 2.4.1 Bitnami Redmine (no other redmine databases or else components are present at the system)
2. Checking that clean redmine installation is working (I am able to login and browse all pages, but base is clean)
3. Importing old MySQL base backup from 2.3.0 to 2.4.1 - no problems.
4. Running command rake db:migrate RAILS_ENV=production - system returns error: Mysql2::Error: Table 'queries_roles' already exists: CREATE TABLE `queries_roles`
at first run. After logging in to PhpMyadmin and deleting 'queries_roles' table on second run of command "rake db:migrate RAILS_ENV=production
system returns error: Mysql2::Error: Table 'custom_fields_roles' already exists: CREATE TABLE `custom_fields_roles` after removing this table too
command runs fine.

I need to check if everything is good, but so far, all pages are available and seems to be ok.

#3 Updated by Toshi MARUYAMA about 9 years ago

Eugene B wrote:

1. Clean install for Latest 2.4.1 Bitnami Redmine (no other redmine databases or else components are present at the system)
3. Importing old MySQL base backup from 2.3.0 to 2.4.1 - no problems.

You need to import 2.3.0 database to Redmine 2.3.0.

#4 Updated by Torben Hansen about 9 years ago

Well, today my Redmine installation throwed some 500 errors. A look into the log showed, that the two tables I deleted were missing. I expected, that they were created by the DB migration script but actually they were note. I had a backup from the old database (before updating from v2.3 to v2.4) and extracted both tables from there and created the table structure in the v2.4 database. Now everything runs smoothly.

#5 Updated by Thanasis Karapatis almost 9 years ago

I also had the same problem. I tried to update from 2.3.4 to 2.4.1.
I am not using Bitnami, just an ubuntu repository.
Eugene B solution worked.
But what happens if you have custom roles? Will they disappear?

#6 Updated by Hossam Hammady over 8 years ago

I could reproduce the issue. However, I believe there is no defect in redmine.
When you create a redmine installation using any 3rd part tool like BitNami or Ubuntu redmine, it does create the database in the corresponding version.
When you attempt to import the older database it will override the tables that are common in both (it drops then creates tables).
However, it won't touch the tables that only exist in the newer database. Consequently, when you try to do db:migrate, those untouched tables will raise this error.
The solution is to first drop the database then recreate it manually from mysql shell then finally do the migration.

#7 Updated by florent thiery almost 8 years ago

Hi

Just saw the same; i was running 2.2, did a backup, transferred it to a new installation of 2.2 (based on git), then did upgrade from branch to branch (2.2 > 2.3 > 2.4); at each step, did

bundle update
rake db:migrate RAILS_ENV=production

I had to fiddle with rake which was complaining (had to uninstall the latest version

gem uninstall rake

Then i got the exact same errors:

Mysql2::Error: Table 'queries_roles' already exists: CREATE TABLE `queries_roles` (`query_id` int(11) NOT NULL, `role_id` int(11) NOT NULL) ENGINE=InnoDB/var/lib/gems/1.9.1/gems/activerecord-3.2.19/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `query'

I dropped the table; then again:
Mysql2::Error: Table 'custom_fields_roles' already exists: CREATE TABLE `custom_fields_roles` (`custom_field_id` int(11) NOT NULL, `role_id` int(11) NOT NULL) ENGINE=InnoDB/var/lib/gems/1.9.1/gems/activerecord-3.2.19/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:245:in `query'

After dropping the table, the migration seems to pass.

#8 Updated by Toshi MARUYAMA over 7 years ago

  • Related to Defect #19905: Problem in migrate from 2.3 to 3.0.3 added

Also available in: Atom PDF