Migration Errors with postgresql

[gyhor]

Hi,

I am getting a migration error with postgres:

==  RevertTimeEntriesInTimesheet: migrating ===================================
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "issues"
LINE 1: ...time_entries" SET time_entries.fixed_version_id = issues.fix...
                                                             ^
: UPDATE "time_entries" SET time_entries.fixed_version_id = issues.fixed_version_id WHERE "time_entries"."id" IN (SELECT "time_entries"."id" FROM "time_entries" INNER JOIN "issues" ON "issues"."id" = "time_entries"."issue_id" WHERE "time_entries"."in_timesheet" = 't' AND (time_entries.fixed_version_id is NULL))/var/lib/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1161:in `exec'
/var/lib/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:1161:in `exec_no_cache'
/var/lib/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:671:in `block in exec_delete'
/var/lib/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
/var/lib/gems/1.9.1/gems/activesupport-3.2.13/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
/var/lib/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
/var/lib/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/postgresql_adapter.rb:670:in `exec_delete'
/var/lib/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/database_statements.rb:96:in `update'
/var/lib/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/connection_adapters/abstract/query_cache.rb:14:in `update'
/var/lib/gems/1.9.1/gems/activerecord-3.2.13/lib/active_record/relation.rb:294:in `update_all'
/opt/redmine-2.3/plugins/redmine_app_timesheets/db/migrate/005_revert_time_entries_in_timesheet.rb:5:in `up'

if I comment the update statement in "005_revert_time_entries_in_timesheet.rb" out, the migration success.

regards gyhor

[maxrossello]

Unfortunately database incompatibilities are hidden under ActiveRecord interfaces; and I do not have a Postgres installation. After the delivery of the next version over which I'm working, I'll try to search for hints over the internet

[maxrossello]

Have a look at this bug report: https://github.com/rails/rails/issues/13496

As a workaround, you could comment out the offending sentence as you did and run the query by hand. The mentioned bug report also states what would be the right query for Postgres.

Rationale is: for all timelogs in timesheet (:in_timesheet => true) copy all fixed versions stored into issue attributes into the timelog's equivalent field (formerly added to track just global orders). In the following migration, :in_timesheet is going to disappear in time entries and fixed_version_id (renamed order_id) will bring the same information if not NULL.

[maxrossello]

Closing as the bug is not ours

[mkrzempek]

I'll try this query and it has wrong syntax. Correct query:

UPDATE time_entries SET fixed_version_id = issues.fixed_version_id FROM issues WHERE time_entries.id IN (SELECT time_entries.id FROM time_entries INNER JOIN issues ON issues.id = time_entries.issue_id WHERE time_entries.in_timesheet = 't' AND (time_entries.fixed_version_id is NULL));

[maxrossello]

Thank you. It can work as a manual workaround for Postgres users

[Darksark]

For postgres's users, you can modify file in plugins/redmine_app_timesheets/db/migrate/005_revert_time_entries_in_timesheet.rb

Replace line 5 (TimeEntry.joins ....) by sql = "UPDATE time_entries SET fixed_version_id = issues.fixed_version_id FROM issues WHERE time_entries.id IN ( SELECT time_entries.id FROM time_entries INNER JOIN issues ON issues.id = time_entries.issue_id WHERE time_entries.in_timesheet = 't' AND (time_entries.fixed_version_id is NULL));" connection.update(sql)

Thanks mkrzempek for the query.

[maxrossello]

Fixed in 1.3.9.

The proposed query does not work under MySql (error is: 1093 - You can't specify target table 'time_entries' for update in FROM clause), hence I implemented a switch.

Thank you

[Darksark]

It's just for postgres's user as me ^^