Dear community,

As of yesterday opening the ‘work_packages’ for a project results in an error. Openproject was updated yesterday to 7.4.0.

Info:
Installed Packages
Name : openproject
Arch : x86_64
Version : 7.4.0
Release : 1517669388.b866a6df.centos7

Error:
“Your query is erroneous and could not be processed. An internal error has occured.”

I think that in the production log it becomes clear why the error occurs (full production log attached):

Partial production log error:
#<ActiveRecord::StatementInvalid: Mysql2::Error: Table ‘admin_op.hierarchy_paths’ doesn’t exist: SELECT DISTINCT `work_packages`.`id`, CONCAT_WS(‘,’, hierarchy_paths.path, work_packages.id) AS alias_0 FROM `work_packages` LEFT OUTER JOIN `statuses` ON `statuses`.`id` = `work_packages`.`status_id` LEFT OUTER JOIN `projects` ON `projects`.`id` = `work_packages`.`project_id` LEFT OUTER JOIN `relations` ON `relations`.`to_id` = `work_packages`.`id` AND (`relations`.`hierarchy` != 0) AND `relations`.`relates` = 0 AND `relations`.`duplicates` = 0 AND `relations`.`follows` = 0 AND `relations`.`blocks` = 0 AND `relations`.`includes` = 0 AND `relations`.`requires` = 0 LEFT OUTER JOIN
hierarchy_paths
ON
hierarchy_paths.work_package_id = work_packages.id WHERE `work_packages`.`project_id` IN (SELECT DISTINCT `projects`.`id` FROM `projects` INNER JOIN `enabled_modules` ON `projects`.`id` = `enabled_modules`.`project_id` AND `enabled_modules`.`name` IN (‘work_package_tracking’) AND `projects`.`status` = 1 WHERE `projects`.`status` = 1) AND ((projects.id IN (6,69,70,137,75,49,72,20,139,140,71,76,138)) AND (statuses.is_closed=0)) ORDER BY CONCAT_WS(‘,’, hierarchy_paths.path, work_packages.id) LIMIT 20 OFFSET 0>

Question:
I don’t see a table called hierarchy_paths in the database. ( table structure attached ). Has anyone encountered this problem before, and if so, how did you manage to solve it?

Any help will be appreciated!

As is output during the actual package update (from your distributions package manager), you need to run openproject configure on every package update to ensure migrations and new assets are built and the application is upgraded.

Please follow the upgrade guide for more information.
https://www.openproject.org/operations/upgrading/upgrade-guide-packaged-installation/

Best,
Oliver

Hi Oliver,

Thanks for your response.

The configure script did run, ( re ran it just now so I could generate the log ). The log file is attached.

Do you have any other suggestions?

Weird, the migration db/migrate/20180116065518_add_hierarchy_paths.rb should have run when you migrated to 7.4.0, but judging from your table structure, it did not.

Can you append the output of the SQL:

SELECT * FROM schema_migrations;

Thanks!
Oliver

Hi Oliver,

Thanks again!

Attached the migrations table!

EDIT: Ops result was paginated, please open version_2.txt

The last file in /db/migrate is “20180116065518_add_hierarchy_paths.rb
” but the last line in the migrations table is “20180125082205”.

Do you reckon this might have got something to do with the problem?

According to the schema_migrations the migration was indeed executed and succeeded. I can’t fathom why you don’t have the table, however you can still force the migration to run again:

Run the following SQL first:

DELETE FROM schema_migrations WHERE version = '20180116065518';

And than run on shell

openproject run bundle exec rake db:migrate

It should then execute that migration again and create the table.

Best,
Oliver

Hi Oliver,

Thanks for your advice, awesome.

I ran the migration and the problem is solved!

The rake was aborted with an error however. ( log attached )

I’m not sure if this is something we need to worry about. It seems to fail on “Index name ‘index_hierarchy_paths_on_work_package_id’ on table ‘hierarchy_paths’ already exists”. I don’t understand why though, since this migration just created the table right?

The migration does not show up in the schema_migrations so I guess it did not run fully right?

Could you take a look at the error if you have some time?

Update:
After backing up the DB I removed the (new) hierarchy_paths table and re-migrated again.

20180116065518 AddHierarchyPaths: migrating
-- create_table(:hierarchy_paths)
-> 0.0288s
20180116065518 AddHierarchyPaths: migrated (0.0329s) ===

:)

Thanks for your assistance!

Oliver Günther wrote:

openproject run bundle exec rake db:migrate

solved my issue

According to the schema_migrations the migration was indeed executed and succeeded. I can’t fathom why you don’t have the table, however you can still force the migration to run again:

Run the following SQL first:

DELETE FROM schema_migrations WHERE version = '20180116065518';

And than run on shell

openproject run bundle exec rake db:migrate

It should then execute that migration again and create the table.

Best,
Oliver