Defect #19721

Upgrade from 2.6.4 to 3.0.2 fails with Mysql2::Error: Table 'email_addresses' already exists:

Added by Deoren Moor about 2 years ago. Updated about 2 years ago.

Status:Needs feedbackStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Database
Target version:-
Resolution: Affected version:3.0.2

Description

I started with a working Redmine 2.6.4 installation on an Ubuntu 12.04 LTS box with Ruby 1.8.7. These are the steps I took in my attempt to upgrade to Redmine 3.0.2:

  1. apt-get install ruby1.9.3 -y
  2. apt-get remove ruby1.8 ruby1.8-dev -y
  3. cd /opt/redmine
  4. svn sw http://svn.redmine.org/redmine/tags/3.0.2
  5. sed -i 's/adapter: mysql/adapter: mysql2/g' /opt/redmine/config/database.yml
  6. gem install bundle
  7. bundle update
  8. rm -f config/initializers/secret_token.rb
  9. bundle exec rake generate_secret_token
  10. bundle exec rake db:migrate RAILS_ENV=production --trace
  11. bundle exec rake tmp:cache:clear
  12. bundle exec rake tmp:sessions:clear
  13. chown -R www-data /opt/redmine
  14. chmod +x /opt/redmine/extra/mail_handler/rdm-mailhandler.rb

I didn't make it past step 10:

bundle exec rake db:migrate RAILS_ENV=production --trace

** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Invoke db:load_config (first_time)
** Execute db:load_config
** Execute db:migrate
== 20141029181752 AddTrackersDefaultStatusId: migrating =======================
-- add_column(:trackers, :default_status_id, :integer)
   -> 0.0211s
== 20141029181752 AddTrackersDefaultStatusId: migrated (0.0843s) ==============

== 20141029181824 RemoveIssueStatusesIsDefault: migrating =====================
-- remove_column(:issue_statuses, :is_default)
   -> 0.0170s
== 20141029181824 RemoveIssueStatusesIsDefault: migrated (0.0172s) ============

== 20141109112308 AddRolesUsersVisibility: migrating ==========================
-- add_column(:roles, :users_visibility, :string, {:limit=>30, :default=>"all", :null=>false})
   -> 0.0176s
== 20141109112308 AddRolesUsersVisibility: migrated (0.0179s) =================

== 20141122124142 AddWikiRedirectsRedirectsToWikiId: migrating ================
-- add_column(:wiki_redirects, :redirects_to_wiki_id, :integer)
   -> 0.0183s
-- change_column(:wiki_redirects, :redirects_to_wiki_id, :integer, {:null=>false})
   -> 0.0195s
== 20141122124142 AddWikiRedirectsRedirectsToWikiId: migrated (0.0467s) =======

== 20150113194759 CreateEmailAddresses: migrating =============================
-- create_table(:email_addresses)
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Table 'email_addresses' already exists: CREATE TABLE `email_addresses` (`id` int(11) auto_increment PRIMARY KEY, `user_id` int(11) NOT NULL, `address` varchar(255) NOT NULL, `is_default` tinyint(1) DEFAULT 0 NOT NULL, `notify` tinyint(1) DEFAULT 1 NOT NULL, `created_on` datetime NOT NULL, `updated_on` datetime NOT NULL) ENGINE=InnoDB/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `query'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `block in execute'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:473:in `block in log'
/var/lib/gems/1.9.1/gems/activesupport-4.2.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:467:in `log'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `execute'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/connection_adapters/mysql2_adapter.rb:231:in `execute'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract/schema_statements.rb:208:in `create_table'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:462:in `create_table'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:662:in `block in method_missing'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:632:in `block in say_with_time'
/usr/lib/ruby/1.9.1/benchmark.rb:280:in `measure'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:632:in `say_with_time'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:652:in `method_missing'
/opt/redmine/db/migrate/20150113194759_create_email_addresses.rb:3:in `change'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:606:in `exec_migration'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:590:in `block (2 levels) in migrate'
/usr/lib/ruby/1.9.1/benchmark.rb:280:in `measure'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:589:in `block in migrate'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract/connection_pool.rb:292:in `with_connection'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:588:in `migrate'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:765:in `migrate'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:995:in `block in execute_migration_in_transaction'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:1043:in `ddl_transaction'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:994:in `execute_migration_in_transaction'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:956:in `block in migrate'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:952:in `each'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:952:in `migrate'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:820:in `up'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/migration.rb:798:in `migrate'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/tasks/database_tasks.rb:137:in `migrate'
/var/lib/gems/1.9.1/gems/activerecord-4.2.1/lib/active_record/railties/databases.rake:44:in `block (2 levels) in <top (required)>'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/task.rb:240:in `call'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/task.rb:240:in `block in execute'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/task.rb:235:in `each'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/task.rb:235:in `execute'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/task.rb:179:in `block in invoke_with_call_chain'
/usr/lib/ruby/1.9.1/monitor.rb:211:in `mon_synchronize'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/task.rb:172:in `invoke_with_call_chain'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/task.rb:165:in `invoke'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/application.rb:150:in `invoke_task'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/application.rb:106:in `block (2 levels) in top_level'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/application.rb:106:in `each'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/application.rb:106:in `block in top_level'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/application.rb:115:in `run_with_threads'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/application.rb:100:in `top_level'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/application.rb:78:in `block in run'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/application.rb:176:in `standard_exception_handling'
/var/lib/gems/1.9.1/gems/rake-10.4.2/lib/rake/application.rb:75:in `run'
/var/lib/gems/1.9.1/gems/rake-10.4.2/bin/rake:33:in `<top (required)>'
/usr/local/bin/rake:19:in `load'
/usr/local/bin/rake:19:in `<main>'
Tasks: TOP => db:migrate

bundle list

Gems included by the bundle:
  * actionmailer (4.2.1)
  * actionpack (4.2.1)
  * actionpack-action_caching (1.1.1)
  * actionpack-xml_parser (1.0.2)
  * actionview (4.2.1)
  * activejob (4.2.1)
  * activemodel (4.2.1)
  * activerecord (4.2.1)
  * activesupport (4.2.1)
  * arel (6.0.0)
  * builder (3.2.2)
  * bundler (1.9.4)
  * coderay (1.1.0)
  * daemon_controller (1.2.0)
  * erubis (2.7.0)
  * globalid (0.3.5)
  * i18n (0.7.0)
  * jquery-rails (3.1.2)
  * json (1.8.2)
  * loofah (2.0.1)
  * mail (2.6.3)
  * mime-types (2.5)
  * mini_portile (0.6.2)
  * minitest (5.6.1)
  * mysql2 (0.3.18)
  * net-ldap (0.3.1)
  * nokogiri (1.6.6.2)
  * passenger (4.0.57)
  * protected_attributes (1.0.9)
  * rack (1.6.0)
  * rack-openid (1.4.2)
  * rack-test (0.6.3)
  * rails (4.2.1)
  * rails-deprecated_sanitizer (1.0.3)
  * rails-dom-testing (1.0.6)
  * rails-html-sanitizer (1.0.2)
  * railties (4.2.1)
  * rake (10.4.2)
  * rbpdf (1.18.5)
  * rdoc (4.2.0)
  * redcarpet (3.1.2)
  * request_store (1.0.5)
  * rmagick (2.13.4)
  * ruby-openid (2.3.0)
  * sprockets (3.0.3)
  * sprockets-rails (2.2.4)
  * thor (0.19.1)
  * thread_safe (0.3.5)
  * tzinfo (1.2.2)
  * yard (0.8.7.6)

History

#1 Updated by Toshi MARUYAMA about 2 years ago

  • Status changed from New to Needs feedback

I think your plugin created "email_addresses" table.

#2 Updated by Deoren Moor about 2 years ago

Toshi MARUYAMA wrote:

I think your plugin created "email_addresses" table.

Thank you for your feedback. We are running a vanilla installation with no plugins installed.

From the info page:

Redmine 2.6.4.stable.14227

Default administrator account changed                       True
Attachments directory writable                              True
Plugin assets directory writable (./public/plugin_assets)   True
RMagick available (optional)                                True
ImageMagick convert available (optional)                    True
Environment:
  Redmine version                2.6.4.stable.14227
  Ruby version                   1.8.7-p352 (2011-06-30) [i686-linux]
  Rails version                  3.2.21
  Environment                    production
  Database adapter               MySQL
SCM:
  Subversion                     1.6.17
  Git                            1.7.9.5
  Filesystem                     
Redmine plugins:
  no plugin installed

Please let me know if I can provide further information.

#3 Updated by Toshi MARUYAMA about 2 years ago

Thank you for your feedback.
If you have no idea who created "email_addresses" table,
you can drop or rename this table.
Ensure you have backup and table has no data.

#4 Updated by Toshi MARUYAMA about 2 years ago

There is same post: Redmine 3.0.1 from 2.6.0.

#5 Updated by Deoren Moor about 2 years ago

Toshi MARUYAMA wrote:

Thank you for your feedback.
If you have no idea who created "email_addresses" table,
You can drop or rename this table.
Ensure you have backup and table has no data.

I checked and the table has 205 entries.

mysql> describe email_addresses;

+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id    | int(11)      | NO   | MUL | NULL    |                |
| address    | varchar(255) | NO   |     | NULL    |                |
| is_default | tinyint(1)   | NO   |     | 0       |                |
| notify     | tinyint(1)   | NO   |     | 1       |                |
| created_on | datetime     | NO   |     | NULL    |                |
| updated_on | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

It appears to contain valid email addresses for our users and is mapped to their user_id values.

I checked three other installations and didn't find the table listed. Could it have been created as part of a failed 2.6.3 to 3.0.1 migration? I thought I was careful to roll back all changes from that previous attempt, but maybe I missed something. I'll check the previous backups to see if I can pinpoint when the table was first created.

#6 Updated by Toshi MARUYAMA about 2 years ago

It seems your entries were created by Redmine.
source:tags/3.0.2/db/migrate/20150113211532_populate_email_addresses.rb

I think there is inconsistency in "schema_migrations".
http://www.redmine.org/boards/2/topics/46173?r=46179#message-46179

#7 Updated by Deoren Moor about 2 years ago

Thanks for the follow-up. I went back and compared a database backup taken the day before a previous failed 3.0.1 backup and the day after and found that is when the table first appeared. I was operating under a bad assumption that the drop database statement was present in the backup sql file. Thus I expected that when I imported the pre-upgrade backup file that it reverted the database to the former state.

Instead, the database was left with the old data and whatever new tables that were created prior to the failure. I'm going to restore a previous backup, list the tables and then restore to a recent backup and list the tables again. I'll diff them to see if there are any other tables still hanging around from the failed 3.0.1 upgrade.

#8 Updated by Deoren Moor about 2 years ago

Deoren Moor wrote:

I'll diff them to see if there are any other tables still hanging around from the failed 3.0.1 upgrade.

I just found the one table when I compared them.

#9 Updated by Deoren Moor about 2 years ago

I cloned the production box I tried to upgrade earlier and manually removed the email_addresses table. I then went back through the process from the beginning and am now running the 3.0.2 version of Redmine on the clone. I'm going to keep a copy of the clone around for a bit, so if you or any of the other devs would like me to run any tests against it let me know and I'll be happy to do so.

#10 Updated by Antoine Kolpacheque about 2 years ago

- Restore from dumpfile

- drop table email_addresses;

- bundle exec rake db:migrate RAILS_ENV=production

Also available in: Atom PDF