Upgrading to Red Hat Satellite 6.11 fails on db:migrate stage with error 'null value in column "created_at" violates not-null constraint'

Solution Verified - Updated

Environment

  • Red Hat Satelite 6.11

Issue

  • Upgrade to Red Hat Satellite 6.11, fails on the db:migrate state.

       \ Executing installer2022-07-19 10:48:00 [ERROR ] [configure] '/usr/sbin/foreman-rake db:migrate' returned 1 instead of one of [0]
2022-07-19 10:48:00 [ERROR ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns: change from 'notrun' to ['0'] failed: '/usr/sbin/foreman-rake db:migrate' returned 1 instead of one of [0]
   | Executing installer2022-07-19 10:48:42 [ERROR ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]: Failed to call refresh: '/usr/sbin/foreman-rake db:migrate' returned 1 instead of one of [0]
   2022-07-19 10:48:42 [ERROR ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]: '/usr/sbin/foreman-rake db:migrate' returned 1 instead of one of [0]

Resolution

This issue has been reported to the Red Hat Engineering team via This content is not included.Bugzilla 2108719.

Meanwhile, to fix the problem:

  • Ensure that there is a backup or VM snapshot present for the affected satellite server.

  • Fix the schema of the taxable_taxonomies table by dropping the NOT NULL set on created_at and updated_at using the following approach:

    # su - postgres -c "psql foreman"
ALTER TABLE taxable_taxonomies ALTER COLUMN created_at DROP NOT NULL;
ALTER TABLE taxable_taxonomies ALTER COLUMN updated_at DROP NOT NULL;
exit

  • Ensure that the table schema now looks like this:

    # echo "\d+ taxable_taxonomies" | su - postgres -c "psql foreman"
                                                             Table "public.taxable_taxonomies"
    Column    |            Type             | Collation | Nullable |                    Default                     | Storage  | Stats target | Description 
\--------------+-----------------------------+-----------+----------+------------------------------------------------+----------+--------------+-------------
 id           | integer                     |           | not null | nextval('taxable_taxonomies_id_seq'::regclass) | plain    |              | 
 taxonomy_id  | integer                     |           |          |                                                | plain    |              | 
 taxable_id   | integer                     |           |          |                                                | plain    |              | 
 taxable_type | character varying(255)      |           |          |                                                | extended |              | 
 created_at   | timestamp without time zone |           |          |                                                | plain    |              | 
 updated_at   | timestamp without time zone |           |          |                                                | plain    |              |

  • Re-run the db-migrate action and ensure it completes without any errors.

    # foreman-rake db:migrate --trace --verbose

  • Re-execute the upgrade of the satellite server and observe the progress.

 

Reach out to This content is not included.Red Hat Technical Support in case of any further assistance is required.

For more KB articles/solutions related to Red Hat Satellite 6.x Installation/Upgrade/Update Issues, please refer to the Red Hat Satellite Consolidated Troubleshooting Article for Red Hat Satellite 6.x Installation/Upgrade/Update Issues.

Root Cause

  • Table taxable_taxonomies contains NOT NULL for created_at and updated_at fields but that is not expected.

Diagnostic Steps

  • During the failed upgrade attempt, the following errors were captured in the /var/log/foreman-installer/satellite.log file.

    2022-07-19 10:48:00 [INFO  ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns:
2022-07-19 10:48:00 [INFO  ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns: Caused by:
2022-07-19 10:48:00 [INFO  ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns: PG::NotNullViolation: ERROR:  null value in column "created_at" violates not-null constraint
2022-07-19 10:48:00 [INFO  ] [configure] /Stage[main]/Foreman::Database/Foreman::Rake[db:migrate]/Exec[foreman-rake-db:migrate]/returns: DETAIL:  Failing row contains (321220, 4, 1, ForemanPuppet::Environment, null, null).

  • The manual execution of foreman-rake db:migrate command also reveals a similar error.

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

PG::NotNullViolation: ERROR:  null value in column "created_at" violates not-null constraint
DETAIL:  Failing row contains (321227, 4, 1, ForemanPuppet::Environment, null, null).

  • Investigation of the taxable_taxonomies table of foreman database reveals that it has the wrong schema. 

    # echo "\d+ taxable_taxonomies" | su - postgres -c "psql foreman"
                                                             Table "public.taxable_taxonomies"
    Column    |            Type             | Collation | Nullable |                    Default                     | Storage  | Stats target | Description
\--------------+-----------------------------+-----------+----------+------------------------------------------------+----------+--------------+-------------
 id           | integer                     |           | not null | nextval('taxable_taxonomies_id_seq'::regclass) | plain    |              |
 taxonomy_id  | integer                     |           |          |                                                | plain    |              |
 taxable_id   | integer                     |           |          |                                                | plain    |              |
 taxable_type | character varying(255)      |           |          |                                                | extended |              |
 created_at   | timestamp without time zone |           | not null |                                                | plain    |              |
 updated_at   | timestamp without time zone |           | not null |                                                | plain    |              |

    i.e. both created_at and updated_at are not nullable.

SBR
Product(s)
Components
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.