Db update fails on upgrade to 6.0.0

Technical assistance
1

Infos:

  • Used Zammad version: 6.0.0-1687159499.569749e9.bionic
  • Used Zammad installation type: deb package
  • Operating system: Ubuntu
  • Browser + version: Firefox

Expected behavior:

  • upgrade without errors

Actual behavior:

  • Noticed this error on upgrade, and not able to create new ticket then:
Unpacking zammad (6.0.0-1687159499.569749e9.bionic) over (6.0.0-1686655247.7bb53cc4.bionic) ...
Setting up zammad (6.0.0-1687159499.569749e9.bionic) ...
# (Re)creating init scripts
Nothing to do.
Nothing to do.
Nothing to do.
# Enabling Zammad on boot
# Stopping Zammad
# Enforcing Redis...
# Creating Redis bootstart
Synchronizing state of redis-server.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install enable redis-server
# Starting Redis server
# Clear cache...
# database.yml found. Updating db...
== 20230129220648 TaskbarUpdatePreferenceTasks: migrating =====================
rake aborted!
StandardError: An error has occurred, this and all later migrations canceled:

Validation failed: User must exist
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/validations.rb:80:in `raise_validation_error'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/validations.rb:53:in `save!'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/transactions.rb:302:in `block in save!'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/transactions.rb:354:in `block in with_transaction_returning_statu
s'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/database_statements.rb:318:in `trans
action'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/transactions.rb:350:in `with_transaction_returning_status'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/transactions.rb:302:in `save!'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/suppressor.rb:48:in `save!'
/opt/zammad/db/migrate/20230129220648_taskbar_update_preference_tasks.rb:11:in `block in up'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches/batch_enumerator.rb:40:in `block (2 levels) in e
ach_record'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches/batch_enumerator.rb:40:in `each'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches/batch_enumerator.rb:40:in `block in each_record'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches.rb:245:in `block in in_batches'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches.rb:229:in `loop'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches.rb:229:in `in_batches'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches/batch_enumerator.rb:39:in `each'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches/batch_enumerator.rb:39:in `each_record'
/opt/zammad/db/migrate/20230129220648_taskbar_update_preference_tasks.rb:8:in `up'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:870:in `public_send'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:870:in `exec_migration'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:851:in `block (2 levels) in migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:850:in `block in migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:462:in `with_conn
ection'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:849:in `migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1037:in `migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1329:in `block in execute_migration_in_transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1380:in `block in ddl_transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `block
 in transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/transaction.rb:319:in `block in with
in_new_transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activesupport-6.1.7.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 lev
els) in synchronize'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activesupport-6.1.7.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_inter
rupt'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activesupport-6.1.7.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in syn
chronize'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activesupport-6.1.7.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_inter
rupt'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activesupport-6.1.7.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/transaction.rb:317:in `within_new_tr
ansaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `trans
action'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/transactions.rb:209:in `transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1380:in `ddl_transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1328:in `execute_migration_in_transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1302:in `each'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1302:in `migrate_without_lock'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1251:in `block in migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1401:in `block in with_advisory_lock'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1416:in `block in with_advisory_lock_connection'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:462:in `with_conn
ection'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1416:in `with_advisory_lock_connection'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1397:in `with_advisory_lock'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1251:in `migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1086:in `up'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1061:in `migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/tasks/database_tasks.rb:237:in `migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/railties/databases.rake:92:in `block (3 levels) in <main>'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/railties/databases.rake:90:in `each'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/railties/databases.rake:90:in `block (2 levels) in <main>'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/exe/rake:27:in `<top (required)>'
/opt/zammad/bin/bundle:121:in `load'
/opt/zammad/bin/bundle:121:in `<main>'

Caused by:
ActiveRecord::RecordInvalid: Validation failed: User must exist
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/validations.rb:80:in `raise_validation_error'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/validations.rb:53:in `save!'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/transactions.rb:302:in `block in save!'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/transactions.rb:354:in `block in with_transaction_returning_statu
s'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/database_statements.rb:318:in `trans
action'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/transactions.rb:350:in `with_transaction_returning_status'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/transactions.rb:302:in `save!'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/suppressor.rb:48:in `save!'
/opt/zammad/db/migrate/20230129220648_taskbar_update_preference_tasks.rb:11:in `block in up'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches/batch_enumerator.rb:40:in `block (2 levels) in e
ach_record'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches/batch_enumerator.rb:40:in `each'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches/batch_enumerator.rb:40:in `block in each_record'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches.rb:245:in `block in in_batches'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches.rb:229:in `loop'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches.rb:229:in `in_batches'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches/batch_enumerator.rb:39:in `each'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/relation/batches/batch_enumerator.rb:39:in `each_record'
/opt/zammad/db/migrate/20230129220648_taskbar_update_preference_tasks.rb:8:in `up'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:870:in `public_send'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:870:in `exec_migration'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:851:in `block (2 levels) in migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:850:in `block in migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:462:in `with_conn
ection'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:849:in `migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1037:in `migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1329:in `block in execute_migration_in_transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1380:in `block in ddl_transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `block
 in transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/transaction.rb:319:in `block in with
in_new_transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activesupport-6.1.7.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:26:in `block (2 lev
els) in synchronize'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activesupport-6.1.7.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `handle_inter
rupt'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activesupport-6.1.7.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:25:in `block in syn
chronize'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activesupport-6.1.7.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `handle_inter
rupt'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activesupport-6.1.7.3/lib/active_support/concurrency/load_interlock_aware_monitor.rb:21:in `synchronize'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/transaction.rb:317:in `within_new_tr
ansaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/database_statements.rb:320:in `trans
action'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/transactions.rb:209:in `transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1380:in `ddl_transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1328:in `execute_migration_in_transaction'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1302:in `each'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1302:in `migrate_without_lock'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1251:in `block in migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1401:in `block in with_advisory_lock'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1416:in `block in with_advisory_lock_connection'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/connection_adapters/abstract/connection_pool.rb:462:in `with_conn
ection'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1416:in `with_advisory_lock_connection'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1397:in `with_advisory_lock'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1251:in `migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1086:in `up'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/migration.rb:1061:in `migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/tasks/database_tasks.rb:237:in `migrate'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/railties/databases.rake:92:in `block (3 levels) in <main>'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/railties/databases.rake:90:in `each'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.3/lib/active_record/railties/databases.rake:90:in `block (2 levels) in <main>'
/opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/exe/rake:27:in `<top (required)>'
/opt/zammad/bin/bundle:121:in `load'
/opt/zammad/bin/bundle:121:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)
# Updating translations...
# No custom packages detected...
# Configuring Elasticsearch...

Steps to reproduce the behavior:

  • Perform the upgrade, or retry to run the /opt/zammad/contrib/packager.io/postinstall.sh. re-run didn’t help and ended with the same error.

After that, I’m not able to create a new ticket. But note that this seems to be smth different from After upgrade 5.4 to 6.0 unable to open ticket
as redis is running, and re-run of the postinst script does not help here…

2

And the error I get when trying to create new ticket says: "PG::InFailedSqlTransaction: ERROR: current transaction is aborted, commands ignored until end of transaction block "

3

Hi @paka, sounds like this one: Failed upgrade from 5.3 to 5.4 by "missing DB constraint" · Issue #4669 · zammad/zammad · GitHub

4

Yes looks similar. But in my case it was working fine on 5.4.1, and appeared after upgrade to 6.0.0…

5

This is somehow strange. The mentioned migration TaskbarUpdatePreferenceTasks was new in Zammad 5.4, I don’t understand why it started to fail right now and not before.

6

I had to revert using backup back to that working 5.4.1 meanwhile, so if there would be anything I could check there to help investigate that, please let me know.

7

Maybe you can check if there are taskbar entries related to deleted users.

8

I’m not sure, but if you mean by taskbar the list of issues on left screen side (below Dashboard, Overviews…) then I see one suspicious issue there, which says “Not Found” as name and clicking on it says “Oops… I’m sorry, but I can’t find this Ticket.” in the right screen part.

9

You can use the query in the mentioned GitHub issue to verify if you have taskbar entries with a deleted user. :slight_smile:

10

If I try sql like “select * from taskbars where user_id not in (select id from users where id in (select distinct user_id from taskbars));” it seems to return one row.

11

After doing the “delete from taskbars where user_id not in (select id from users where id in (select distinct user_id from taskbars));” and then upgrading to 6.0.0, it seems ok - I can create a new ticket.

2 Likes