Used Zammad version: 6.1
Used Zammad installation type: apt package
Operating system: debian 11
Browser + version: Edge
The update process throws a few errors, alle seem to be related to the table smime_certificates.
Comment: We’re still on mariaDB; I tried to migrate to pgSQL, but the issue with the mentioned table prevented a successful migration
re-run update from the latest 6.1 version to the first available 6.2 version
Extract of the protocol:
2024-04-23T19:43:20.433000Z NOTICE ALTER TABLE public.user_two_factor_preferences ADD CONSTRAINT fk_rails_486740ea12 FOREIGN KEY(updated_by_id) REFERENCES public.users(id) ON UPDATE RESTRICT ON DELETE RESTRICT
2024-04-23T19:43:20.443000Z NOTICE Executing SQL block for after load
KABOOM!
FATAL error: Database error 42703: Spalte email_addresses existiert nicht
QUERY: alter table smime_certificates alter column email_addresses type text
An unhandled error condition has been signalled:
Database error 42703: Spalte email_addresses existiert nicht
QUERY: alter table smime_certificates alter column email_addresses type text
2024-04-23T19:43:20.449000Z ERROR Database error 42703: Spalte email_addresses existiert nicht
QUERY: alter table smime_certificates alter column email_addresses type text
Sorry I can’t follow. Are you having issues upgrading Zammad or are you trying to migrate MariaDB to PostgreSQL?
Hi, sorry for the confusion. The database thing was just an added information.
Make sure that your migrations are up to date before upgrading.zammad run rake db:migrate should do that (if there’s open ones).
Hi there!
** Invoke db:migrate (first_time)
No indexes found on smime_certificates with the options provided.index_name_for_remove' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/connection_adapters/abstract/schema_statements.rb:881:in remove_index’remove_index' /opt/zammad/db/migrate/20230726082734_smime_meta_information_table.rb:24:in remove_columns’block in migrate_table' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/connection_adapters/abstract/schema_statements.rb:490:in change_table’block in method_missing' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:897:in block in say_with_time’measure' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:897:in say_with_time’method_missing' /opt/zammad/db/migrate/20230726082734_smime_meta_information_table.rb:14:in migrate_table’change' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:867:in exec_migration’block (2 levels) in migrate' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/3.1.0/benchmark.rb:296:in measure’block in migrate' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/connection_adapters/abstract/connection_pool.rb:462:in with_connection’migrate' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1037:in migrate’block in execute_migration_in_transaction' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1382:in ddl_transaction’execute_migration_in_transaction' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1302:in each’migrate_without_lock' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1251:in block in migrate’block in with_advisory_lock' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1416:in block in with_advisory_lock_connection’with_connection' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1416:in with_advisory_lock_connection’with_advisory_lock' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1251:in migrate’up' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1061:in migrate’migrate' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/railties/databases.rake:92:in block (3 levels) in ’each' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/railties/databases.rake:90:in block (2 levels) in ’block in execute' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/task.rb:281:in each’execute' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/task.rb:219:in block in invoke_with_call_chain’synchronize' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/task.rb:199:in invoke_with_call_chain’invoke' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/application.rb:160:in invoke_task’block (2 levels) in top_level' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/application.rb:116:in each’block in top_level' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/application.rb:125:in run_with_threads’top_level' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/application.rb:83:in block in run’standard_exception_handling' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/application.rb:80:in run’<top (required)>' /opt/zammad/vendor/bundle/ruby/3.1.0/bin/rake:25:in load’<top (required)>' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/3.1.0/bundler/cli/exec.rb:58:in load’kernel_load' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/3.1.0/bundler/cli/exec.rb:23:in run’exec' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/3.1.0/bundler/vendor/thor/lib/thor/command.rb:27:in run’invoke_command' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/3.1.0/bundler/vendor/thor/lib/thor.rb:392:in dispatch’dispatch' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/3.1.0/bundler/vendor/thor/lib/thor/base.rb:485:in start’start' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/gems/3.1.0/gems/bundler-2.3.26/libexec/bundle:48:in block in <top (required)>’with_friendly_errors' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/gems/3.1.0/gems/bundler-2.3.26/libexec/bundle:36:in <top (required)>’load' /opt/zammad/bin/bundle:121:in ’
Caused by:index_name_for_remove' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/connection_adapters/abstract/schema_statements.rb:881:in remove_index’remove_index' /opt/zammad/db/migrate/20230726082734_smime_meta_information_table.rb:24:in remove_columns’block in migrate_table' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/connection_adapters/abstract/schema_statements.rb:490:in change_table’block in method_missing' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:897:in block in say_with_time’measure' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:897:in say_with_time’method_missing' /opt/zammad/db/migrate/20230726082734_smime_meta_information_table.rb:14:in migrate_table’change' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:867:in exec_migration’block (2 levels) in migrate' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/3.1.0/benchmark.rb:296:in measure’block in migrate' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/connection_adapters/abstract/connection_pool.rb:462:in with_connection’migrate' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1037:in migrate’block in execute_migration_in_transaction' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1382:in ddl_transaction’execute_migration_in_transaction' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1302:in each’migrate_without_lock' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1251:in block in migrate’block in with_advisory_lock' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1416:in block in with_advisory_lock_connection’with_connection' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1416:in with_advisory_lock_connection’with_advisory_lock' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1251:in migrate’up' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/migration.rb:1061:in migrate’migrate' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/railties/databases.rake:92:in block (3 levels) in ’each' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/activerecord-6.1.7.6/lib/active_record/railties/databases.rake:90:in block (2 levels) in ’block in execute' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/task.rb:281:in each’execute' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/task.rb:219:in block in invoke_with_call_chain’synchronize' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/task.rb:199:in invoke_with_call_chain’invoke' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/application.rb:160:in invoke_task’block (2 levels) in top_level' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/application.rb:116:in each’block in top_level' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/application.rb:125:in run_with_threads’top_level' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/application.rb:83:in block in run’standard_exception_handling' /opt/zammad/vendor/bundle/ruby/3.1.0/gems/rake-13.0.6/lib/rake/application.rb:80:in run’<top (required)>' /opt/zammad/vendor/bundle/ruby/3.1.0/bin/rake:25:in load’<top (required)>' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/3.1.0/bundler/cli/exec.rb:58:in load’kernel_load' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/3.1.0/bundler/cli/exec.rb:23:in run’exec' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/3.1.0/bundler/vendor/thor/lib/thor/command.rb:27:in run’invoke_command' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/3.1.0/bundler/vendor/thor/lib/thor.rb:392:in dispatch’dispatch' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/3.1.0/bundler/vendor/thor/lib/thor/base.rb:485:in start’start' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/gems/3.1.0/gems/bundler-2.3.26/libexec/bundle:48:in block in <top (required)>’with_friendly_errors' /opt/zammad/vendor/ruby-3.1.3/lib/ruby/gems/3.1.0/gems/bundler-2.3.26/libexec/bundle:36:in <top (required)>’load' /opt/zammad/bin/bundle:121:in ’
Can you please verify that the content of the affected migration is the same as upstream?
# Copyright (C) 2012-2024 Zammad Foundation, https://zammad-foundation.org/
class SMIMEMetaInformationTable < ActiveRecord::Migration[6.1]
def change
# return if it's a new setup
return if !Setting.exists?(name: 'system_init_done')
migrate_table
end
private
def migrate_table
change_table :smime_certificates do |t|
remove_columns(t)
rename_columns(t)
add_columns(t)
end
SMIMECertificate.reset_column_information
show original
Hm - checked this file and it was, indeed, slightly different to the one on GITHUB - I do have the previous version on my server.
Hi There,
unfortunately this is not solved, yet.
I’m afraid we by now found the issue. It was caused by a not entirely correct pgloader script. You can learn more here:
opened 03:51PM - 12 Nov 24 UTC
closed 02:50PM - 14 Nov 24 UTC
bug
verified
prioritised by payment
migration / update
### Used Zammad Version
6.3
### Environment
- Installation method: possible a… ny
- Operating system: possible any
- Database + version: psql (PostgreSQL) 13.16 (Debian 13.16-0+deb11u1)
### Actual behaviour
When migrating a MariaDB database to Postgresql, the email_address column in the smime_certificates table is saved with an incorrect type.
**The following type is set for the column email_addresses in the table smime_certificates in a clean Zammad posgresql system.**
```
email_addresses | character varying[]
```
**but when I do a migration from a MariaDB to postgresql according to our documentation, the following type is set**
https://docs.zammad.org/en/pre-release/appendix/migrate-to-postgresql.html
```
email_addresses | text[]
```
**The pgloader program used to migrate the database contains the following entry**
```
$ rake zammad:db:pgloader > /tmp/pgloader-command
```
```
AFTER LOAD DO
$$ alter table smime_certificates alter column email_addresses type text[] using translate(email_addresses::text, '[]', '{}')::text[] $$,
$$ alter table pgp_keys alter column email_addresses type text[] using translate(email_addresses::text, '[]', '{}')::text[] $$,
$$ alter table public_links alter column screen type text[] using translate(screen::text, '[]', '{}')::text[] $$
```
**However, if the type of the email_addresses field is text, the following error is displayed when verifying an S/MIME certificate. This means the S/MIME integration does not work and must be deactivated.**
```
[1] pry(main)> SMIMECertificate.find_by_email_address('foobar').first
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: operator does not exist: text[] && character varying[]
LINE 1: ..." WHERE ((("smime_certificates"."email_addresses" && ARRAY['...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
from /opt/zammad/vendor/bundle/ruby/3.2.0/gems/activerecord-7.1.4.1/lib/active_record/connection_adapters/postgresql_adapter.rb:894:in `exec_params'
Caused by PG::UndefinedFunction: ERROR: operator does not exist: text[] && character varying[]
LINE 1: ..." WHERE ((("smime_certificates"."email_addresses" && ARRAY['...
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
from /opt/zammad/vendor/bundle/ruby/3.2.0/gems/activerecord-7.1.4.1/lib/active_record/connection_adapters/postgresql_adapter.rb:894:in `exec_params'
```
### Expected behaviour
After a migration from MariaDB to Postgresql the S/Mime integration still works in Zammad.
### Steps to reproduce the behaviour
1. Install a system with MySQL/MariaDB backend.
2. Migrate to PostgreSQL by following [the official instructions](https://docs.zammad.org/en/latest/appendix/migrate-to-postgresql.html) (make sure to use `pgloader` utility for DB migration).
3. Run `SMIMECertificate.find_by_email_address('foobar').first` in your Rails console to trigger the error.
### Support Ticket
Ticket#10164132
### I'm sure this is a bug and no feature request or a general question.
yes
```[tasklist]
### Tasks
- [x] Change column array type to `varchar[]` in `pgloader` script
- [x] Add DB migration to update wrong column types on affected systems
- [x] Extend migration to include multi select and multi treeselect dynamic array columns in addition to static ones
- [x] Tests, tests, tests (DB migration, MySQL/MariaDB simulated migration, `pgloader` script)
```
You will basically have to correct the column type within the smime table manually. This is a fairly dangerous operation. Have a backup at hand.
