Version 3.2 complains about undefined table

After upgrading to Zammad 3.2.0-1575624531.d79c85cf.buster on Debian 10, I get this:

/opt/zammad/bin/bundle:3:in `load'
/opt/zammad/bin/bundle:3:in `<main>'
Caused by:
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "active_job_locks" does not exist
LINE 8:                WHERE a.attrelid = '"active_job_locks"'::regc...
:               SELECT a.attname, format_type(a.atttypid, a.atttypmod),
                     pg_get_expr(d.adbin, d.adrelid), a.attnotnull, a.atttypid, a.atttypmod,
                     c.collname, col_description(a.attrelid, a.attnum) AS comment
                FROM pg_attribute a
                LEFT JOIN pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
                LEFT JOIN pg_type t ON a.atttypid = t.oid
                LEFT JOIN pg_collation c ON a.attcollation = c.oid AND a.attcollation <> t.typcollation
               WHERE a.attrelid = '"active_job_locks"'::regclass
                 AND a.attnum > 0 AND NOT a.attisdropped
               ORDER BY a.attnum

Any ideas how to solve this? Many thanks in advance!

you can read this post, it is looks very close to your problem

I´ve the same problem with missing table “active_job_locks”. What I´m wondering is the single and double quotes around the table name, maybe an coding or variable error?

Running “zammad run rake db:migrate --trace” solves nothing.
I´m updateing from 2.3 to 3.2, acrtual DB version is “20180327170847”

so, if you are now running zammad 3.2.x with db version 20180327170847, then db patches was not applied. if single one patch fails to apply, then all patches after failed one all not applied also. this is my personal expirience. so, my advice for you:

  1. get zammad 3.2 install archive, unpack it, look for sql patches in db folder.
  2. check, what patch with 20180327170847 name actually applied to your db (install pgadmin and explore your db)
  3. most definitely next patch after 20180327170847 failed to apply because of some problem. resolve this problem and try upgrade process again on working zammad 2.3. with your original, but fixed db.
  4. optionally try to upgrade to zammad 3.1, because 3.2 has many db updates. I do not know, how big difference between 2.3 and 3.1 in terms of db updates.
    this is my user class advice. I am not ruby or sql guru.

Same here. “Migrate” does not help at all. Therefore, I assume the problem is a different one.

you can not migrate, if your db, for example, has already some table, what some patch want to create again. this will produce error and no any migrate, or, actually, upgrade. you need to have correct db, where version number exactly match with changes, made by corresponding patch file. then you can issue “db:migrate” command, what will try to apply all missed patches. IMHO.

Unfortunately, I cannot see, which table exactly failed. Going through all the patches is something I would like to avoid. I downgraded now to 3.1.0 which gives me a more-or-less working Zammad. Interestingly, the user table is shown as completely empty. I will try now a new update process, but will directly go to 3.1.0 and not via 3.2.0. If that fails, too, I have to check all the migrations.

when you do normal “yum update zammad”, capture terminal session to file. when update finished, look at captured log. each postgres db patch are shown in this log. and you can easily see, what patch are failed.

That is not the case with apt install zammad="version", unfortunately.

Correction: It’s very well visible. Just not anymore at that stage I was. I dropped the DB and imported the old one again, and installed 3.1.0 again. Now I can see all the migration steps very well.

I have installed 3.1.0 instead of 3.2.0 now and it works. I will postpone 3.2.0 as long as possible and deal with possible problems then!

I’d suggest running the update and keeping the console output.
The issue will proberbly live there.

hi!
we migrated from 2.9 to 3.3 (Ubuntu 16.04, postgres 9.5) and also experienced the “table missing” issue. Login to Zammad was no longer possible.

A simple

zammad run rake db:migrate --trace

did not work either. By trial and error we found the following solution:

1/ get the deployed db version:

sudo zammad run rake db:version --trace

2/ change to the dir where the migration scripts are stored:

cd /opt/zammad/db/migrate

3/ figure out the first file > your current db version and run (eg.)

sudo zammad run rake db:migrate == 20190613000001 --trace

You should then see the installation of the migration files, which we did not before.

Hope this will help somebody else.

Hi
I was trying to update from 2.2 to 3.3, and it was failing every time. Migrations wouldn’t even start to run because they needed table active_jobs_locks to be present, which wasn’t. I tried @homat’s solution too, but it didn’t work, failing for the same reason. based on @Martin1’s comment, I updated first to version 3.1. Migrations ran fine. Then I updated from 3.1 to 3.3, and migrations ran fine again.
So, this might help anyone in the future save some days. I don’t know what’s the threshold but I think it’s good to be added to documentations that “updating from a version prior to XX you should first update to version YY and then to the latest version”.
As a complete side note, I found that the best way for updating elasticsearch from 5.5 to 7.6 is totally purging it, manually deleting nodes, and installing 7.6 from scratch :joy:
Cheers