Problem with Database migration (mariadb => postgres)

Marian_Thieme · June 27, 2019, 9:13am

Problem:
Want to migrate my database from mariadb to postgres. I am following the steps provided in the manual: Section: Switching from SQLite to MySQL/PostgreSQL while maintaining existing data.
The migration fails at step: (move the data to the new db)

bundle exec rake db:convert:prod2dev

Expected outcome:
Migration succeeds.

Foreman and Proxy versions:
1.19.1
Foreman and Proxy plugin versions:
foreman_memcache 0.1.1
foreman_templates 6.0.3
puppetdb_foreman 4.0.0

Other relevant data:
[e.g. logs from Foreman and/or the Proxy, modified templates, commands issued, etc]
(for logs, surround with three back-ticks to get proper formatting, e.g.)

[root@foreman]sh> foreman-rake db:convert:prod2dev
/usr/share/foreman/lib/core_extensions.rb:182: warning: already initialized constant ActiveSupport::MessageEncryptor::DEFAULT_CIPHER
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activesupport-5.1.6/lib/active_support/message_encryptor.rb:22: warning: previous definition of DEFAULT_CIPHER was here
Converting ar_internal_metadata...1 records converted in 0.47762999683618546 seconds
Converting architectures...4 records converted in 0.10056199878454208 seconds
Converting architectures_operatingsystems...45 records converted in 0.1366613507270813 seconds
Converting audits...1732842 records converted in 5631.201423153281 seconds
Converting auth_sources...3 records converted in 1.0005889981985092 seconds
Converting bookmarks...6 records converted in 0.49473225325345993 seconds
Converting cached_user_roles...79 records converted in 0.46019819378852844 seconds
Converting cached_usergroup_members...0 records converted in 0.2176203727722168 seconds
Converting compute_attributes...22 records converted in 0.32057303190231323 seconds
Converting compute_profiles...20 records converted in 0.22419188916683197 seconds
Converting compute_resources...2 records converted in 0.3565940782427788 seconds
Converting config_group_classes...0 records converted in 0.18822918087244034 seconds
Converting config_groups...0 records converted in 0.036217100918293 seconds
Converting domains...15 records converted in 0.18824415653944016 seconds
Converting dynflow_actions...rake aborted!
NoMethodError: undefined method `fetch_value' for nil:NilClass
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/attribute_methods/read.rb:71:in `_read_attribute'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/attribute_methods/read.rb:36:in `__temp__36c6163737'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/core.rb:358:in `init_with'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/persistence.rb:69:in `instantiate'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/querying.rb:50:in `block (2 levels) in find_by_sql'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/result.rb:55:in `block in each'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/result.rb:55:in `each'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/result.rb:55:in `each'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/querying.rb:50:in `map'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/querying.rb:50:in `block in find_by_sql'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activesupport-5.1.6/lib/active_support/notifications/instrumenter.rb:21:in `instrument'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/querying.rb:49:in `find_by_sql'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/relation.rb:678:in `exec_queries'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/relation.rb:546:in `load'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/relation.rb:255:in `records'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/relation/delegation.rb:39:in `each'
/usr/share/foreman/lib/tasks/convert.rake:125:in `block (5 levels) in <top (required)>'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/connection_adapters/abstract/database_statements.rb:235:in `block in transaction'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/connection_adapters/abstract/transaction.rb:194:in `block in within_new_transaction'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/connection_adapters/abstract/transaction.rb:191:in `within_new_transaction'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/connection_adapters/abstract/database_statements.rb:235:in `transaction'
/opt/theforeman/tfm-ror51/root/usr/share/gems/gems/activerecord-5.1.6/lib/active_record/transactions.rb:210:in `transaction'
/usr/share/foreman/lib/tasks/convert.rake:124:in `block (4 levels) in <top (required)>'
/usr/share/foreman/lib/tasks/convert.rake:83:in `each'
/usr/share/foreman/lib/tasks/convert.rake:83:in `block (3 levels) in <top (required)>'
/opt/rh/rh-ruby24/root/usr/share/gems/gems/rake-12.0.0/exe/rake:27:in `<top (required)>'
Tasks: TOP => db:convert:prod2dev

Maybe worth to note: I created table template_inputs manually. (as suggested in similar posts)
CREATE TABLE “template_inputs” (
“id” SERIAL NOT NULL,
“name” VARCHAR(255) NOT NULL,
“required” BIT(1) NOT NULL DEFAULT E’0’,
“input_type” VARCHAR(255) NOT NULL,
“fact_name” VARCHAR(255) NULL DEFAULT NULL,
“variable_name” VARCHAR(255) NULL DEFAULT NULL,
“puppet_class_name” VARCHAR(255) NULL DEFAULT NULL,
“puppet_parameter_name” VARCHAR(255) NULL DEFAULT NULL,
“description” TEXT NULL DEFAULT NULL,
“template_id” INTEGER NULL DEFAULT NULL,
“created_at” DATE NULL DEFAULT NULL,
“updated_at” DATE NULL DEFAULT NULL,
“options” TEXT NULL DEFAULT NULL,
“advanced” BIT(1) NOT NULL DEFAULT E’0’
)

Marian_Thieme · June 27, 2019, 9:33am

Dont know if this is important: However, to make the last point more clear. I compared both databases and found only table template_inputs was missing. The mariadb schema of that table looks like:
MariaDB [foreman]> show columns from template_inputs;

MariaDB [foreman]> show columns from template_inputs;
+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| id                    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name                  | varchar(255) | NO   |     | NULL    |                |
| required              | tinyint(1)   | NO   |     | 0       |                |
| input_type            | varchar(255) | NO   |     | NULL    |                |
| fact_name             | varchar(255) | YES  |     | NULL    |                |
| variable_name         | varchar(255) | YES  |     | NULL    |                |
| puppet_class_name     | varchar(255) | YES  |     | NULL    |                |
| puppet_parameter_name | varchar(255) | YES  |     | NULL    |                |
| description           | text         | YES  |     | NULL    |                |
| template_id           | int(11)      | YES  | MUL | NULL    |                |
| created_at            | datetime     | YES  |     | NULL    |                |
| updated_at            | datetime     | YES  |     | NULL    |                |
| options               | text         | YES  |     | NULL    |                |
| advanced              | tinyint(1)   | NO   |     | 0       |                |
+-----------------------+--------------+------+-----+---------+----------------+

Anyway, seems like there is no data in the table:

MariaDB [foreman]> select * from template_inputs;
Empty set (0.01 sec)

lzap · June 28, 2019, 1:19pm

If you are missing some schemas, it’s a serious data integrity issue. What we’ve learned this week is that MySQL DDL commands are not subject of transaction rollbacks, so if your instance have ever suffered an error during database migration, schema might not be migrated correctly.

What I would recommend:

Install the very same version of Foreman from scratch on a different host.
Make sure the version is the same and also set of all plugins is exactly the same.
Perform this on both new (empty) and your current instance: foreman-rake db:schema:dump
Copy this one file from both instances: /usr/share/foreman/db/schema.rb
Compare the two files

This should give you overview of what’s exactly different in the schema. Share the result with us.

Marian_Thieme · July 1, 2019, 8:59am

I’ve installed new instance and dumped the schemas, as you said. There are more than 1000 lines. The first obvious difference I see already is CHARSET: utf8 and utf8mb4 (on new instance).
More differences as follows: (just used diff)

166,167c166,167
<     t.string "execution_plan_uuid", limit: 36, default: "", null: false
<     t.integer "id", default: 0, null: false
---
>     t.string "execution_plan_uuid", limit: 36, null: false
>     t.integer "id", null: false
183,184c183,184
<     t.string "id", limit: 100, default: "", null: false
<     t.string "class", limit: 100, default: "", null: false
---
>     t.string "id", limit: 100, null: false
>     t.string "class", limit: 100, null: false
232,233c232,233
<     t.string "execution_plan_uuid", limit: 36, default: "", null: false
<     t.integer "id", default: 0, null: false
---
>     t.string "execution_plan_uuid", limit: 36, null: false
>     t.integer "id", null: false
253c253
<   create_table "environment_classes", id: :integer, force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
---
>   create_table "environment_classes", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
333c333
<   create_table "host_classes", id: :integer, force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
---
>   create_table "host_classes", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
357c357
<   create_table "hostgroup_classes", id: :integer, force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
---
>   create_table "hostgroup_classes", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
675c675
<     t.datetime "expired_at"
---
>     t.timestamp "expired_at"
678d677
<     t.integer "subject_id"
679a679
>     t.integer "subject_id"
845,854d844
<   create_table "setup_provisioners", id: :integer, force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
<     t.integer "host_id"
<     t.integer "smart_proxy_id"
<     t.string "provision_interface"
<     t.integer "subnet_id"
<     t.string "timestamps"
<     t.integer "hostgroup_id"
<     t.integer "domain_id"
<   end
< 
960,976d949
<   create_table "template_inputs", id: :integer, force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
<     t.string "name", null: false
<     t.boolean "required", default: false, null: false
<     t.string "input_type", null: false
<     t.string "fact_name"
<     t.string "variable_name"
<     t.string "puppet_class_name"
<     t.string "puppet_parameter_name"
<     t.text "description"
<     t.integer "template_id"
<     t.datetime "created_at"
<     t.datetime "updated_at"
<     t.text "options"
<     t.boolean "advanced", default: false, null: false
<     t.index ["template_id"], name: "templates_template_id_fk"
<   end
< 
1002,1003c975
<     t.string "type", default: "Token::Build", null: false
<     t.index ["host_id"], name: "index_tokens_on_host_id"
---
>     t.index ["host_id"], name: "index_tokens_on_host_id", unique: true
1054d1025
<     t.integer "member_id"
1055a1027
>     t.integer "member_id"
1196d1167
<   add_foreign_key "template_inputs", "templates", name: "templates_template_id_fk"

Marian_Thieme · July 1, 2019, 11:16am

I used the following command to generate the diff:
$ diff schema-productive-instance.rb schema-new-instance.rb
interpretation of ouput:

< -- productive instance
> -- new/test instance

Should I upload the entire files somewhere ?

Marian_Thieme · July 1, 2019, 11:23am

obvious difference are: tables setup_provisioners and template_inputs only installed on prod instance.
I can confirm, that same versions of plugins foreman_memcache, foreman_templates, puppetdb_foreman are installed on either intstance.

lzap · July 2, 2019, 6:20am

By chance, do you have /var/log/foreman/db_migrate.log files in backups? The problem is this file is being logrotated therefore it was already removed and we are looking for a whole history of migrations done in the past.

I am afraid my concern about schema integrity is valid. Any ideas how to proceed @core? This will be an issue once we ask users to migrate in 1.23. My only idea is to modify prod2dev rake task to check schemas if they are the same and issue warnings during migration. Then build a knowledge base of cases and how to fix them.

lzap · July 2, 2019, 6:35am

All I can offer for now is small change which will make the process to skip tables which does not migrate correctly:

Try the patch and report back please.

Marian_Thieme · July 2, 2019, 7:25am

By chance, do you have /var/log/foreman/db_migrate.log files in backups?

Seems like I still have more or less all db-migration logs. What should I do with it ?

lzap · July 2, 2019, 7:27am

Good, can you investigate and find failures, errors and problems? I would like to know reasons why some tables were not migrated correctly.

Also try the patch to see how many actual problems you get.

tbrisker · July 2, 2019, 7:28am

Is it possible that you had the foreman_setup plugin installed in the past on the prod instance? setup_provisioners table is created by it. I think this table can be removed if the plugin has been uninstalled.
template_inputs table used to be part of remote execution plugin but was migrated to core in 1.20, so it should also be present on the clean dev instance.

Marian_Thieme · July 2, 2019, 7:35am

My dev/test instance is 1.19.1 (in order to have the same version as current prod). So thats why there is template_inputs table ?

tbrisker · July 2, 2019, 7:37am

In that case I expect that you had remote execution installed at some point on the prod instance and removed it but the tables are left over, similar to foreman setup.

lzap · July 18, 2019, 3:46pm

Good find, we need to do conclusion in order to find a good pre-migration step - finding out tables which should not be present. This looks like something we should be able to do, I was thinking it was worse… this sounds reasonable.