I am using the process outlined in https://community.openproject.org/topics/13102

as a guide for upgrading our openProject installation

• it's presently at 7  on Ubuntu 16.04
• and we'd like to upgrade to the most recent stable on Ubuntu 20.04

I am encountering a problem when running the migration script on the dump I created from our running installation

The command I ran was:

./migrate-from-pre-8.sh localhost ../mnt/openpprojectbackup/op7.sql

it runs for a while and ends with:

1.4) Creating MySQL database for migration from OP 7 to 8
Warning: Using a password on the command line interface can be insecure.
Created database

1.5) Importing MySQL dump (../mnt/openpprojectbackup/op7.sql)
Warning: Using a password on the command line interface can be insecure.
Imported database

1.6) Migrating database from 6 to 7 ... (NOOP if already on 7)
Error response from daemon: Container fe7a496000df8771d37e51b2fe79ca5e89e481ae641526c828f3c8d9fe40e340 is not running
Could not migrate database

if i try and re-run the script it appears that it has left things in an active state

richard@richard-toolbox:~/output$ ./migrate-from-pre-8.sh localhost ../mnt/openpprojectbackup/op7.sql

1. Migrate to 8 in MySQL

1.1) Starting mysql database...
already running

1.2) Starting OpenProject 7
docker: Error response from daemon: Conflict. The container name "/op7" is already in use by container "fe7a496000df8771d37e51b2fe79ca5e89e481ae641526c828f3c8d9fe40e340". You have to remove (or rename) that container to be able to reuse that name.
See 'docker run --help'.
richard@richard-toolbox:~/output$

A couple of things I can think of:

• the IP address for the migration script: I passed localhost as everything here is on the one VM (it's been created just for this migration) is there something I am missing here?
• the machine I am trying to run the migration on is running Ubuntu 20.04 - would this cause problems?

Thanks for any suggestions

All the best

• Richard

I appear to have got beyond that part of the script by trying on an Ubuntu16 machine and passing the host IP address (as reported from ifconfig) to the script

It appear that the script completes but complains about not being able to rename primary keys

From the log file:

Full-text extraction for attachments have successfully been requested in background jobs.
Migration from MySQL to Postgres completed successfully!
-----> Starting the all-in-one OpenProject setup at /app/docker/supervisord...
-----> You're using an external database. Not initializing a local database cluster.
       Trying to contact PostgreSQL server instance or waiting for it to come online.
       Starting memcached: memcached.
[DEPRECATED] Bundler.environment has been removed in favor of Bundler.load (called at /app/lib/open_project/plugins/acts_as_op_engine.rb:178)
       hook registered
rake aborted!
ActiveRecord::ConcurrentMigrationError: 

Cannot run migrations because another migration process is currently running.

/app/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1380:in `with_advisory_lock'
/app/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1229:in `migrate'
/app/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1061:in `up'
/app/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/migration.rb:1036:in `migrate'
/app/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/tasks/database_tasks.rb:238:in `migrate'
/app/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/railties/databases.rake:85:in `block (3 levels) in <top (required)>'
/app/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/railties/databases.rake:83:in `each'
/app/vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.1/lib/active_record/railties/databases.rake:83:in `block (2 levels) in <top (required)>'
/app/vendor/bundle/ruby/2.6.0/gems/rake-13.0.1/exe/rake:27:in `<top (required)>'
/usr/local/bundle/gems/bundler-2.1.4/lib/bundler/cli/exec.rb:63:in `load'
/usr/local/bundle/gems/bundler-2.1.4/lib/bundler/cli/exec.rb:63:in `kernel_load'
/usr/local/bundle/gems/bundler-2.1.4/lib/bundler/cli/exec.rb:28:in `run'
/usr/local/bundle/gems/bundler-2.1.4/lib/bundler/cli.rb:476:in `exec'
/usr/local/bundle/gems/bundler-2.1.4/lib/bundler/vendor/thor/lib/thor/command.rb:27:in `run'
/usr/local/bundle/gems/bundler-2.1.4/lib/bundler/vendor/thor/lib/thor/invocation.rb:127:in `invoke_command'
/usr/local/bundle/gems/bundler-2.1.4/lib/bundler/vendor/thor/lib/thor.rb:399:in `dispatch'
/usr/local/bundle/gems/bundler-2.1.4/lib/bundler/cli.rb:30:in `dispatch'
/usr/local/bundle/gems/bundler-2.1.4/lib/bundler/vendor/thor/lib/thor/base.rb:476:in `start'
/usr/local/bundle/gems/bundler-2.1.4/lib/bundler/cli.rb:24:in `start'
/usr/local/bundle/gems/bundler-2.1.4/exe/bundle:46:in `block in <top (required)>'
/usr/local/bundle/gems/bundler-2.1.4/lib/bundler/friendly_errors.rb:123:in `with_friendly_errors'
/usr/local/bundle/gems/bundler-2.1.4/exe/bundle:34:in `<top (required)>'
/usr/local/bundle/bin/bundle:23:in `load'
/usr/local/bundle/bin/bundle:23:in `<main>'
Tasks: TOP => db:migrate

and from the terminal output

Migrating to EnsurePostgresIndexNames (20190502102512)
== 20190502102512 EnsurePostgresIndexNames: migrating =========================
-- execute("ALTER INDEX \"idx_18270_primary\" RENAME TO announcements_pkey;")
   -> 0.0012s
-- execute("ALTER INDEX \"idx_18278_primary\" RENAME TO ar_internal_metadata_pkey;")
   -> 0.0007s
-- execute("ALTER INDEX \"idx_18286_primary\" RENAME TO attachable_journals_pkey;")
   -> 0.0009s
-- execute("ALTER INDEX \"idx_18309_primary\" RENAME TO attachment_journals_pkey;")
   -> 0.0011s
-- execute("ALTER INDEX \"idx_18293_primary\" RENAME TO attachments_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18325_primary\" RENAME TO attribute_help_texts_pkey;")
   -> 0.0006s
-- execute("ALTER INDEX \"idx_18334_primary\" RENAME TO auth_sources_pkey;")
   -> 0.0012s
-- execute("ALTER INDEX \"idx_18361_primary\" RENAME TO categories_pkey;")
   -> 0.0005s
-- execute("ALTER INDEX \"idx_18369_primary\" RENAME TO changes_pkey;")
   -> 0.0005s
-- execute("ALTER INDEX \"idx_18391_primary\" RENAME TO changeset_journals_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18379_primary\" RENAME TO changesets_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18400_primary\" RENAME TO colors_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18409_primary\" RENAME TO comments_pkey;")
   -> 0.0008s
-- execute("ALTER INDEX \"idx_18421_primary\" RENAME TO cost_entries_pkey;")
   -> 0.0005s
-- execute("ALTER INDEX \"idx_18437_primary\" RENAME TO cost_object_journals_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18428_primary\" RENAME TO cost_objects_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18446_primary\" RENAME TO cost_queries_pkey;")
   -> 0.0005s
-- execute("ALTER INDEX \"idx_18456_primary\" RENAME TO cost_types_pkey;")
   -> 0.0040s
-- execute("ALTER INDEX \"idx_18475_primary\" RENAME TO custom_actions_pkey;")
   -> 0.0014s
-- execute("ALTER INDEX \"idx_18484_primary\" RENAME TO custom_actions_projects_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18493_primary\" RENAME TO custom_actions_roles_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18502_primary\" RENAME TO custom_actions_statuses_pkey;")
   -> 0.0005s
-- execute("ALTER INDEX \"idx_18511_primary\" RENAME TO custom_actions_types_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18520_primary\" RENAME TO custom_fields_pkey;")
   -> 0.0040s
-- execute("ALTER INDEX \"idx_18466_primary\" RENAME TO customizable_journals_pkey;")
   -> 0.0064s
-- execute("ALTER INDEX \"idx_18552_primary\" RENAME TO custom_options_pkey;")
   -> 0.0239s
-- execute("ALTER INDEX \"idx_18561_primary\" RENAME TO custom_styles_pkey;")
   -> 0.0006s
-- execute("ALTER INDEX \"idx_18570_primary\" RENAME TO custom_values_pkey;")
   -> 0.0005s
-- execute("ALTER INDEX \"idx_18582_primary\" RENAME TO delayed_jobs_pkey;")
   -> 0.0010s
-- execute("ALTER INDEX \"idx_18593_primary\" RENAME TO design_colors_pkey;")
   -> 0.0005s
-- execute("ALTER INDEX \"idx_18614_primary\" RENAME TO document_journals_pkey;")
   -> 0.0016s
-- execute("ALTER INDEX \"idx_18602_primary\" RENAME TO documents_pkey;")
   -> 0.0070s
-- execute("ALTER INDEX \"idx_18629_primary\" RENAME TO enabled_modules_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18635_primary\" RENAME TO enterprise_tokens_pkey;")
   -> 0.0010s
-- execute("ALTER INDEX \"idx_18644_primary\" RENAME TO enumerations_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18654_primary\" RENAME TO export_card_configurations_pkey;")
   -> 0.0015s
-- execute("ALTER INDEX \"idx_18664_primary\" RENAME TO grids_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18673_primary\" RENAME TO grid_widgets_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18685_primary\" RENAME TO journals_pkey;")
   -> 0.0013s
-- execute("ALTER INDEX \"idx_18696_primary\" RENAME TO labor_budget_items_pkey;")
   -> 0.0005s
-- execute("ALTER INDEX \"idx_18703_primary\" RENAME TO ldap_groups_memberships_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18709_primary\" RENAME TO ldap_groups_synchronized_groups_pkey;")
   -> 0.0009s
-- execute("ALTER INDEX \"idx_18715_primary\" RENAME TO material_budget_items_pkey;")
   -> 0.0005s
-- execute("ALTER INDEX \"idx_18741_primary\" RENAME TO meeting_content_journals_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18731_primary\" RENAME TO meeting_contents_pkey;")
   -> 0.0014s
-- execute("ALTER INDEX \"idx_18750_primary\" RENAME TO meeting_journals_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18759_primary\" RENAME TO meeting_participants_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18722_primary\" RENAME TO meetings_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18777_primary\" RENAME TO member_roles_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18768_primary\" RENAME TO members_pkey;")
   -> 0.0016s
-- execute("ALTER INDEX \"idx_18783_primary\" RENAME TO menu_items_pkey;")
   -> 0.0031s
-- execute("ALTER INDEX \"idx_18805_primary\" RENAME TO message_journals_pkey;")
   -> 0.0013s
-- execute("ALTER INDEX \"idx_18792_primary\" RENAME TO messages_pkey;")
   -> 0.0005s
-- execute("ALTER INDEX \"idx_18828_primary\" RENAME TO news_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18841_primary\" RENAME TO news_journals_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18854_primary\" RENAME TO oauth_access_grants_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18863_primary\" RENAME TO oauth_access_tokens_pkey;")
   -> 0.0005s
-- execute("ALTER INDEX \"idx_18873_primary\" RENAME TO oauth_applications_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18884_primary\" RENAME TO plaintext_tokens_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18893_primary\" RENAME TO principal_roles_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18916_primary\" RENAME TO project_associations_pkey;")
   -> 0.0011s
-- execute("ALTER INDEX \"idx_18899_primary\" RENAME TO projects_pkey;")
   -> 0.0027s
-- execute("ALTER INDEX \"idx_18925_primary\" RENAME TO queries_pkey;")
   -> 0.0005s
-- execute("ALTER INDEX \"idx_18942_primary\" RENAME TO rates_pkey;")
   -> 0.0004s
-- execute("ALTER INDEX \"idx_18948_primary\" RENAME TO relations_pkey;")
   -> 0.0006s
-- execute("ALTER INDEX \"idx_18965_primary\" RENAME TO repositories_pkey;")
   -> 0.0006s
-- execute("ALTER INDEX \"idx_18991_primary\" RENAME TO role_permissions_pkey;")
  Failed to rename primary keys. You may have to do this yourself.

This is a pre-flight test to see if the migration is possible and it certainly looks like it has got most of the way it needs; I am trying to get my head around the last few steps so that I can be confident that when I take the running server off line to upgrade it's OS & installation that I'll be able to get it running again

Any advice or suggestions would be gratefully received

Hi Richard,

the first output hints at something running in parallel trying to migrate the database. Is the migration script all you perform? You could also append a mysql dump with --no-data set so we can try to reproduce the migration error without providing any actual data to us.

Best

Oliver

Hi Oliver

There should have been nothing else going on; I was running the script on a VM running a fresh install of Ubuntu16.04 created for the purpose

I've attached the no-data dump as you suggested; hopefully there will be some clues there

All the best

Richard

Hi Richard,

could you also provide a dump of the schema_migrations table? That is needed to perform the migrations

you can do that `mysqldump <dbname> schema_migrations > schema_migrations.sql.dump`

Best

Oliver

Hi Oliver 

Hopefully the attached file should be what you need ( I zipped it as there appeared to be a content violation with the dump file)

All the best

• Richard

Hi Richard,

good news and bad news: The migration ran through fine for me with your latest dump attached to the previous one:

  migration SUCCESSFUL!

2.6) Dumping migrated database to openproject-migrated.dump
 Dumped database

Judging from your previous output, it might be that the fulltext migrations somehow fail to run through as they start a second migration run.

I do have an older MySQL dump lying around with data that I can try to perform the migration out. If you have the option to provide us a MySQL dump with (partial) data to try again, feel free to reach out to me personally at o.guenther@openproject.com 

Best

Oliver

Thanks Oliver

It's good to know that it can work!

I tried running the script again a few minutes ago

The script I am using is the one here: https://raw.githubusercontent.com/opf/openproject/dev/script/migration/migrate-from-pre-8.sh which looks to have its last commit in April - so hopefully that is up to date

Strangely it appeared to get a bit further failing with the following in the console (perhaps being a re-run makes a difference - looking at the text in the terminal it certainly had less to do towards the start of the script?)

-- execute("ALTER INDEX \"idx_21125_primary\" RENAME TO work_package_journals_pkey;")
   -> 0.0038s
-- execute("ALTER INDEX \"idx_21108_primary\" RENAME TO work_packages_pkey;")
   -> 0.0398s
== 20190502102512 EnsurePostgresIndexNames: migrated (0.9369s) ================

  Ensured correct primary key names.

2.5) Migrating from 10 to current (11)
11: Pulling from openproject/community
0bc3020d05f1: Pull complete 
a110e5871660: Pull complete 
83d3c0fa203a: Pull complete 
a8fd09c11b02: Pull complete 
14feb89c4a52: Pull complete 
958d2475f181: Pull complete 
ca06945b3a95: Pull complete 
4c67825eab9f: Pull complete 
a9d923cc0b3d: Pull complete 
0ccbc7c22f7b: Pull complete 
d5d8a8bf113c: Pull complete 
0fa344502894: Pull complete 
44a84fc3628d: Pull complete 
cc2d207d4628: Pull complete 
ac6e89587967: Pull complete 
c6813a31d8ba: Pull complete 
703ce8bc4f73: Pull complete 
1e25b96a9325: Pull complete 
0ece78423186: Pull complete 
e589295d950f: Pull complete 
Digest: sha256:3d82b6be2787f6bb6e82dd5e5c74e011e39dba071b38ea3c421fe98116058829
Status: Downloaded newer image for openproject/community:11
docker.io/openproject/community:11
  migration unsuccessful. Please check migration.log

I have attached the last portion of the migration.log

• it appears to be complaining about a missing column

PG::UndefinedColumn: ERROR:  column "updated_on" of relation "users" does not exist
LINE 1: UPDATE "users" SET "admin" = $1, "updated_on" = $2 WHERE "us...

Do you think there's an obvious fix for this?

All the best,

Richard

Richard, Oliver... I had the same problem.. I managed to get inside the PostgreSQL database being used for the migration and was able to look at the users relation. Turns out that the columns there are named created_at and updated_at rather than (as it is in the MySQL version I was upgrading from) created_on and updated_on.

Looking through the other tables of my current MySQL installation, it seems there is very inconsistent usage: Some tables use created_at... others use created_on... etc. 

PostgreSQL:

MySQL:

Richard McAleer wrote:

Thanks Oliver

...

I have attached the last portion of the migration.log

• it appears to be complaining about a missing column

PG::UndefinedColumn: ERROR:  column \"updated_on\" of relation \"users\" does not exist
LINE 1: UPDATE \"users\" SET \"admin\" = $1, \"updated_on\" = $2 WHERE \"us...

Do you think there's an obvious fix for this?

All the best,

Richard

Just for anyone else who ends up here, and the aforementioned shell script is still not working for the reasons mentioned above, I did manage to work around the problem of the script crashing at the last migration from OP10 > OP11.

What I did was to follow all of the instructions on the page https://www.openproject.org/docs/installation-and-operations/misc/upgrading-older-openproject-versions/. (Do not forget to also check the notes about the possibility of the OP7 Docker instance failing to start, and make the adjustments recommended there if needs be.) The script will run up until the point it tries to migrate from version 10 to version 11. Since as of this writing that was pretty much the last step, I decided to see if the packaged installation would migrate successfully. So basically, here's what I did, but I am not saying that this is the most correct or optimized workaround:

1. Run the migrate-from-pre-8.sh script as far as it will go. Should crash on step 2.5 as indicated by the script, migrating from 10 to 11 (latest version.)
2. Using the tools that come with Docker, log into the the PostgreSQL Docker instance, and dump the database as is to an SQL file. Should be a version 10 database.
3. Log out of the instance. Use the Docker tool docker cp to copy the dump file to your host machine.
4. (Not sure if necessary, but I stopped all the Docker instances at this point. Normally these would be stopped when the script successfully completes.)
5. With a packaged installation of PostgreSQL, use the dump file to create a fresh database.
6. Go get the packaged version of OpenProject. Be sure to select that you will be using existing Postgres installation.
7. As instructed in the packaged installation, run sudo openproject configure which should see the existing data and trigger the migration. *** NOTE: In order to get the migration to run without errors, I had to set the database user as postgres with an appropriate password. This is obviously not ideal. I also tried creating the database as an openproject user (so that openproject would be the owner) in step 4, but this still resulted in errors (possibly different errors... I wasn't paying close attention) in the migration. I'll admit I'm still a bit new to Postgres so if there's a recommendation of how to alter that step properly, I'm open to suggestions. Maybe after the migration, it's okay to change the OP database user back to a different account that has access only to the OpenProject database? What about future migrations?
8. In any case, at this point I have a working installation of OP11 with all of my OP5 data in place.

The version I upgraded to is 11.4.1. I'm not sure why the migration fails for the Docker instance (maybe not running same version 11 in Docker?)

If I need to expand on any details in these steps (Docker, Postgres commands used, etc.) let me know and I can flesh it out a little more.