Content

Added by Will Griffin almost 4 years ago

Please would you be able to help?

I have openproject version 7 installed on ubuntu 16.04 from the package manager (7.4.7-1532077196.a556554d.xenial)

My goal is to upgrade to version 11 on ubuntu 20.04. I have tried a couple of approaches without success.

Any advice would be appreciated

Attempt 1: I upgraded the OS first. Then I attempted to follow the openproject upgrade process at https://docs.openproject.org/installation-and-operations/operation/upgrading/

1 a) I updated the package sources to point to the newer release channel:

wget -qO- https://dl.packager.io/srv/opf/openproject/key | sudo apt-key add -

sudo wget -O /etc/apt/sources.list.d/openproject.list https://dl.packager.io/srv/opf/openproject/stable/11/installer/ubuntu/20.04.repo

sudo apt-get update

1 b) then I ran the upgrade install:

sudo apt-get install --only-upgrade openproject sudo openproject reconfigure

...but this stopped due to the lack of the postgresql database, with the following error:

======= INCOMPATIBLE DATABASE DETECTED =======

Your database is set up for use with a MySQL or MySQL-compatible variant.

This installation of OpenProject no longer supports these variants.

The following guides provide extensive documentation for migrating your installation to a PostgreSQL database:

https://www.openproject.org/migration-guides/

This process is mostly automated so you can continue using your OpenProject installation within a few minutes! ==============================================

1 c) so then I tried to migrate the mysql database to postgresql as described in the migration guide at https://docs.openproject.org/installation-and-operations/misc/packaged-postgresql-migration/

wget -qO- https://dl.packager.io/srv/opf/pgloader-ccl/key | sudo apt-key add -

sudo wget -O /etc/apt/sources.list.d/pgloader-ccl.list https://dl.packager.io/srv/opf/pgloader-ccl/master/installer/ubuntu/20.04.repo

sudo apt-get update sudo apt-get install pgloader

[pgloader-ccl was not compatible with ubuntu 20.04 library versions, so I had to use the standard pgloader packeaged with the OS]

sudo apt install postgresql postgresql-contrib libpq-dev

sudo su - postgres

createuser -P -d openproject

sudo openproject config:set MYSQL_DATABASE_URL="localmysqlconnectionstring"

sudo openproject config:set DATABASE_URL="localpostgresqlconnectionstring"

sudo openproject run /opt/openproject/docker/prod/mysql-to-postgres/bin/migrate-mysql-to-postgres

...but this failed with the error:

Traceback (most recent call last):

2: from /opt/openproject/docker/prod/mysql-to-postgres/bin/migrate-mysql-to-postgres:120:in `<main>'

1: from /opt/openproject/docker/prod/mysql-to-postgres/bin/migrate-mysql-to-postgres:71:in `run'

/opt/openproject/docker/prod/mysql-to-postgres/bin/migrate-mysql-to-postgres:71:in `spawn': No such file or directory - fork failed (Errno::ENOENT)

...then I realised I need to edit this line of the script to use pgloader, not pgloader-ccl:

gloader_path = ENV.fetch('PGLOADER_PATH', 'pgloader-ccl')

..but then pgloader gave the error:

KABOOM!

FATAL error: Failed to connect to mysql at "localhost" (port 3306) as user "openproject":

Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.

An unhandled error condition has been signalled: Failed to connect to mysql at "localhost" (port 3306) as user "openproject": Condition QMYND:MYSQL-UNSUPPORTED-AUTHENTICATION was signalled.

...after some investigation this turned out to be due to mysql 8 (which is the version packaged in ubuntu 20.04) no longer uses mysql_native_password as the default authentication method, so I edited my.cnf to use this and changed the user permissions for the openproect mysql user:

sudo nano /etc/mysql/my.cnf

[mysqld]

default-authentication-plugin=mysql_native_password

...then when I reran the migration script it got further, but errored at:

An unhandled error condition has been signalled:
76 fell through ECASE expression.
Wanted one of (2 3 4 5 6 8 9 10 11 14 15 17 20 21 23 27 28 30 31 32 33 35 41
42 45 46 47 48 49 50 51 52 54 55 56 60 61 62 63 64 65 69 72
77 78 79 82 83 87 90 92 93 94 95 96 97 98 101 102 103 104 105
106 107 108 109 110 111 112 113 114 115 116 117 118 119 120
121 122 123 124 128 129 130 131 132 133 134 135 136 137 138
139 140 141 142 143 144 145 146 147 148 149 150 151 159 160
161 162 163 164 165 166 167 168 169 170 171 172 173 174 175
176 177 178 179 180 181 182 183 192 193 194 195 196 197 198
199 200 201 202 203 204 205 206 207 208 209 210 211 212 213
214 215 223 224 225 226 227 228 229 230 231 232 233 234 235
236 237 238 239 240 241 242 243 244 245 246 247 254).

...after some investigation it turned out that the default character collation in mysql 8 is utf8mb4_0900_ai_ci which it identifies with the number 76, but the packaged version of pgloader 3.6.1 uses a qmynd version that does not cater for this. I didn't want to get into compiling a new version of pgloader from source, so instead I span up a snapshot I took of the server during the OS upgrade process when it was on ubuntu 18.04, which has a mysql 5.7 as the packaged version, then changed the database connection string the migration script was using to point at that instead.

...this time the script completed successfully, so I continued with the next step to reconfigure openproject

sudo openproject reconfigure

...but it gave the following error:

PG::UndefinedTable: ERROR: relation "planning_element_type_colors_pkey" does not exist

...when I checked manually, this table does not exist in the database. So it looks like the migration script does not create additional database objects needed by version 11, but just migrates the database objects already present in version 7.

I attempted to do a fresh install of version 11 in order to create an empty version 11 database, then perform the migration script on that empty database, thinking that the migration would just move the version 7 data, and the version 11 install would make sure that all the necessary database objects would be present.

Unfortunately the migration script will not run if there is data already present in postgresql, and openproject configure will not create a new database if postgresql is already present.

Attempt 2:

So I abandoned the above approach, and instead tried to use the dockerised method on a snapshot of the 18.04 version of the server in order to avoid the mysql 8.0 issues, following the approach described at https://docs.openproject.org/installation-and-operations/misc/upgrading-older-openproject-versions/

2 a) install docker and backup the openproject installation to get the mysql dump:

sudo apt install docker.io

sudo openproject run backup

2 b) update the package sources

wget -qO- https://dl.packager.io/srv/opf/openproject/key | sudo apt-key add -

sudo wget -O /etc/apt/sources.list.d/openproject.list https://dl.packager.io/srv/opf/openproject/stable/11/installer/ubuntu/18.04.repo

2 c) upgrade openproject

sudo apt-get update

sudo apt-get install --only-upgrade openproject

2 d) extract the mysql dump

sudo cp /var/db/openproject/backup/mysql-dump-20210204143720.sql.gz /tmp/openproject-mysql.dump.gz

sudo gunzip /tmp/openproject-mysql.dump.gz

2 e) run the script

sudo bash /opt/openproject/script/migration/migrate-from-pre-8.sh 172.17.0.1 /tmp/openproject-mysql.dump

...but that errors:

1.6) Migrating database from 6 to 7 ... (NOOP if already on 7)

rake aborted!

Mysql2::Error: Can't connect to MySQL server on '172.17.0.1' (110 "Connection timed out")

...where 172.17.0.1 is the IP of the host machine on the docker virtual network:

ip a

4: docker0: <BROADCAST,MULTICAST,UP,LOWER_UP>

mtu 1500 qdisc noqueue state UP group default l

ink/ether 02:42:74:34:16:3c brd ff:ff:ff:ff:ff:ff

inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0

valid_lft forever preferred_lft forever

inet6 fe80::42:74ff:fe34:163c/64 scope link

...so it looks like it cannot connect to the mysql instance, but I don't understand why it is trying to connect to a mysql instance on the host anyway. The script runs up 3 containers, one of which is running mysql 5.6 - surely it should be trying to connect to this instance, not the instance on the host machine anyway?

sudo docker ps

CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES

a86315941e28 openproject/community:8-mysql "./docker/entrypoint…" 2 hours ago Up 2 hours 80/tcp, 5432/tcp op8

aaf60becc5e4 openproject/community:7 "/usr/src/app/docker…" 2 hours ago Up 2 hours 80/tcp, 5432/tcp op7

b027d3191ab6 mysql:5.6 "docker-entrypoint.s…" 2 hours ago Up 2 hours

sudo docker network ls

NETWORK ID NAME DRIVER SCOPE

1f830b5c94cb bridge bridge local

1626897c3a22 host host local

682331a8ca0d none null local

...so I pointed the script at that docker container mysql instance:

sudo docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' b027d3191ab6

172.17.0.2

sudo bash /opt/openproject/script/migration/migrate-from-pre-8.sh 172.17.0.2 /tmp/openproject-mysql.dump

...but that errors too, though it seems to be due to permissions:

Mysql2::Error: Can't connect to MySQL server on '172.17.0.2' (111 "Connection refused")

Please could you advise a way which I can successfully migrate from openproject 7 on mysql on ubuntu 16.04, to openproject 11 on postgresql on ubuntu 20.04?

Many thanks for your help, it is much appreciated.

Will

Replies (6)

RE: Upgrade from version 7 to version 11 - Added by Max Mutzge almost 4 years ago

Dear Will,

It makes sense to upgrade OpenProject step by step and not directly from 7 to 11.
I would go 7->8->9->11, while changing from MySQL to PostgreSQL in-between (e.g. between 8 and 9).

Have you found these upgrade notes, yet?:
https://docs.openproject.org/installation-and-operations/operation/upgrading/#upgrade-notes-for-openproject-7x-to-8x
https://docs.openproject.org/installation-and-operations/operation/upgrading/#upgrade-notes-for-8x-to-9x

You can go either step by step by hand or use a script to go directly from 8 to 10: https://github.com/opf/openproject/blob/dev/script/migration/migrate-from-pre-8.sh (or you can just use it as inspiration).
For the script to work you will need docker. However, you can run it somewhere other than the final server and then use the result in a package-based installation. The script expects a MySQL backup as input and will give you a PostgreSQL backup as output.

On packager.io there seem to be only packages for 9 or newer. This means: If you don't have the package for 9 lying around you will have to use the docker script either way as far as I see it.

Regarding Ubuntu: If you want to do the migration by hand, and not via script, you should first do the migration on the old 16.04 system, because the old packages will not be transferable to Ubuntu 20.04 probably.

I hope that helps.

Kind regards

Matthias

RE: Upgrade from version 7 to version 11 - Added by Will Griffin almost 4 years ago

Hi Matthias

Many thanks for your help, it is much appreciated.

This is the process I used for upgrading from version 7 on ubuntu 16.04 to version 11 on ubuntu 20.04 in case others may find it useful. Please note this was a package based install.

Step 1: use the docker migration script to create an openproject version 10 postgresql database dump, from inputting an openproject version 7 mysql database dump.

I recommend running the docker migration script on a separate VM from your original openproject version 7 VM.

[When I tried to run it on the same VM I got a variety of errors to do with the dockerised processes being unable to connect to the mysql instance on the host machine. I suspect this was to do with there already being an openproject database on the host VM with the same database name and username as the database in the msqldump file that the docker script was trying to process.]

Dump your database on your original VM running version 7 of openproject:

mysqldump -uroot -p --databases openproject --result-file=/opt/openproject/openproject7.sql

Create a new VM just for running the docker script.

Make sure it has at least 4GB RAM.

[I got an out of memory error when I ran the docker script on a VM with 2GB RAM, but succeeded with 4GB. I used the same OS as the original openproject version 7 VM which was Ubuntu 16.04.]

Copy your database dump from the original openproject version 7 VM to the new VM:

scp /opt/openproject/openproject7.sql root@xxx.yyy.ppp.qqq:openproject7.sql

Grab the docker script onto your new VM:

cd /opt/openproject/

wget https://raw.githubusercontent.com/opf/openproject/dev/script/migration/migrate-from-pre-8.sh

Make it executable:

chmod +x /opt/openproject/migrate-from-pre-8.sh

install docker onto your new VM:

curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
sudo apt-get update
sudo apt-get install -y docker-ce
sudo systemctl status docker

install mysql onto your new VM:

apt-get install mysql-server

Edit the mysql config to bind to all interfaces, so the docker containers will be able to access it:

nano /etc/mysql/my.cnf

[mysqld]
bind-address = 0.0.0.0

sudo systemctl restart mysql

run the migration script (xxx.yyy.ppp.qqq is the IP address):

migrate-from-pre-8.sh xxx.yyy.ppp.qqq openproject7.sql

[You can see from reading the script the steps it will go through, and the kind of output to expect. It took about 5 minutes to run for me, but I have a small database.]

It will output a few files including:

openproject-migrated.dump
openproject-dump-8.sql
migration.log

openproject-migrated.dump is the final oepnproject version 10 postresql database. Save this for later. Now you can destroy the VM that you created just for running the docker script.

Step 2: upgrade the OS on the original openproject version 7 VM

Upgrade Ubuntu from 16.04 to 18.04. I won't go into all the details as this is well documented elsewhere, but the gist of it is:

Allow access to a second instance of sshd obn port 1022 in case your current ssh session gets killed during the upgrade:

sudo ufw allow 1022/tcp

Comment out or delete the openproject package sources file references:

/etc/apt/sources.list.d/openproject-ce.list

Make sure all the packages are up to date then do the upgrade:

sudo apt update
sudo apt dist-upgrade
sudo do-release-upgrade

As part of the upgrade you will probably need to choose to retain various config files such as sshd, webserver, grub, etc then reboot.

Once the upgrade is complete, make sure that openproject version 7 is still functional. You might need to tweak webserver configuration if stuff has changed.

Step 3: upgrade openproject from version 7 to version 10

Make sure the correct repo is in your package sources files:

Either just edit the files directly e.g. sudo nano /etc/apt/sources.list.d/openproject.list

wget -qO- https://dl.packager.io/srv/opf/openproject/key | sudo apt-key add -

sudo wget -O /etc/apt/sources.list.d/openproject.list https://dl.packager.io/srv/opf/openproject/stable/10/installer/ubuntu/18.04.repo

Install the new version of openproject:

sudo apt-get update
sudo apt-get install --only-upgrade openproject

Install postgresql:

sudo apt install postgresql postgresql-contrib libpq-dev

Create a database and user for openproject:

sudo su - postgres

createuser -P -d openproject

createdb -O openproject openproject

exit

Stop open project:

sudo service openproject stop

Set your database connection string to use the details of the database and user you just created:

sudo openproject config:set DATABASE_URL="postgresql://openproject:password@localhost/openproject"

copy over your openproject version 10 postgresql database dump (produced from the docker script) and restore it:

sudo pg_restore --host=localhost --username=openproject --password --dbname=openproject --clean --create /home/will/openproject-migrated.dump

configure openproject:

cd /opt/openproject

sudo openproject configure

...now visit the openproject site and make sure it is working okay

Step 4: Upgrade Ubuntu from 18.04 to 20.04

Again, make sure all the packages are up to date then do the upgrade:

sudo apt update
sudo apt dist-upgrade
sudo do-release-upgrade

As part of the upgrade you will probably again need to choose to retain various config files such as sshd, webserver, grub, etc.

Check the openproject site is working okay

Step 5: upgrade openproject from version 10 to version 11

Edit package sources

sudo nano /etc/apt/sources.list.d/openproject.list
sudo nano /etc/apt/sources.list.d/openproject.list.distUpgrade

change:
deb https://dl.packager.io/srv/deb/opf/openproject/stable/10/ubuntu 18.04 main
to:
deb https://dl.packager.io/srv/deb/opf/openproject/stable/11/ubuntu 20.04 main

Install the new version of openproject:

sudo apt-get update
sudo apt-get install --only-upgrade openproject

cd /opt/openproject

sudo openproject configure

[At this point I got various errors about missing indexes. It seems like the docker script migration process produces a postgres database with indexes which are named differently to those resulting from a clean install of postgresql, and so differently to what is expected by the version 11 upgrade process. The solution is to identify the indexes and rename them to what the version 11 upgrade process expects.]

The first run of sudo openproject configure produced this error:

rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:
PG::UndefinedTable: ERROR: relation "cost_objects_pkey" does not exist]

To fix the error, rename the index.

Get your database connection string:

sudo su openproject
openproject config:get DATABASE_URL
postgresql://openproject:password@localhost/openproject

run psql with this connection string:

psql postgresql://openproject:password@localhost/openproject

Show the information about the table:

\d+ cost_objects

This will show you the table and it's indexes. Find the primary key and rename it, for example:

ALTER INDEX "idx_16546_primary" RENAME TO "cost_objects_pkey";

Then re-run sudo openproject configure

I had to do this repeatedly for multiple misnamed indices, so you may need to repeat this process until all the indexes are renamed and the configure runs to completion.

Then check the site is working okay. Go to https://yoururl/admin/info and you should see it says:

Core version
OpenProject 11.1.3 (PostgreSQL)

...so this produced a working instance of version 11 of openproject running on Ubuntu 20.04, with the same data as the original version 7 instance which was on Ubuntu 16.04.

Kind regards

Will

RE: Upgrade from version 7 to version 11 - Added by Max Mutzge almost 4 years ago

Hi Will,

I'm glad it worked. Thanks a lot for your extensive description of the process! I'm sure this will help other users with the same situation a lot.

Kind regards

Matthias

RE: [Solved] Upgrade from version 7 to version 11 - Added by Markus Kahl almost 4 years ago

Hello Will,

thanks for the very detailed notes about your upgrade process. About the broken indices: There is a step in the script that is supposed to fix this automatically. Evidently it didn't. Can you please confirm that the indices still had the wrong names in openproject-migrated.dump? Maybe we should just add an extra step for good measure just before the dump to fix the indices again.

Can you please also confirm that all tables are in the same schema? This seems to be an issue that keeps popping up as well.
Again there is a step in the script that ought to fix it but I just want to make sure it worked properly in your case.

RE: [Solved] Upgrade from version 7 to version 11 - Added by Will Griffin over 3 years ago

Hi Markus

Please find attached the output of pg_restore -l openproject-migrated.dump

This shows that all the tables seem to be in the public schema. Also that the indices cost_objects_pkey and cost_object_journals_pkey are not present. So maybe the extra step before the dump could be useful.

Hope that helps

Best wishes

Will

RE: [Solved] Upgrade from version 7 to version 11 - Added by Markus Kahl over 3 years ago

FYI the script now includes another step to make extra sure all primary keys are named correctly. I've done some more test runs and it did indeed still have to rename a couple of indices. I'm not quite sure why this isn't covered during the initial MySQL-to-Postgres migration in the OpenProject 10 container, but anyway using this script it will be corrected.

Thanks again for all your help, Will.

Top Menu

Side Menu

Content

Replies (6)

RE: Upgrade from version 7 to version 11 - Added by Max Mutzge almost 4 years ago

RE: Upgrade from version 7 to version 11 - Added by Will Griffin almost 4 years ago

RE: Upgrade from version 7 to version 11 - Added by Max Mutzge almost 4 years ago

RE: [Solved] Upgrade from version 7 to version 11 - Added by Markus Kahl almost 4 years ago

RE: [Solved] Upgrade from version 7 to version 11 - Added by Will Griffin over 3 years ago

RE: [Solved] Upgrade from version 7 to version 11 - Added by Markus Kahl over 3 years ago