Bug 1718165

Created attachment 1578237 [details]
dwhd log

This maybe related to https://bugzilla.redhat.com/show_bug.cgi?id=1541924 that was fixed in version 4.2.2.


I believe you can update manually the history_id field in tables  - vm_interface_daily_history, vm_interface_hourly_history, vm_interface_samples_history
from integer to bigint and it should fix the issue, but I did not test on 4.1.

like in:
https://gerrit.ovirt.org/#/c/87139/4/packaging/dbscripts/upgrade/04_02_0020__updated_vm_interface_history_id_to_bigint.sql

(In reply to Shirly Radco from comment #2)
> This maybe related to https://bugzilla.redhat.com/show_bug.cgi?id=1541924
> that was fixed in version 4.2.2.
> 
> 
> I believe you can update manually the history_id field in tables  -
> vm_interface_daily_history, vm_interface_hourly_history,
> vm_interface_samples_history
> from integer to bigint and it should fix the issue, but I did not test on
> 4.1.
> 
> like in:
> https://gerrit.ovirt.org/#/c/87139/4/packaging/dbscripts/upgrade/
> 04_02_0020__updated_vm_interface_history_id_to_bigint.sql

I created a secondary database to test the change.

I tried doing it that way, however, the update fails because the history_id fields are referenced by the v4_1_statistics_vms_interfaces_resources_usage_daily view.

bla=# SELECT fn_db_change_column_type('vm_interface_daily_history', 'history_id', 'integer', 'bigint');
ERROR:  cannot alter type of a column used by a view or rule
DETALLE:  rule _RETURN on view v4_1_statistics_vms_interfaces_resources_usage_daily depends on column "history_id"
CONTEXTO:  SQL statement "ALTER TABLE vm_interface_daily_history ALTER COLUMN history_id TYPE bigint"
PL/pgSQL function fn_db_change_column_type(character varying,character varying,character varying,character varying) line 9 at EXECUTE statement
bla=# SELECT fn_db_change_column_type('vm_interface_hourly_history', 'history_id', 'integer', 'bigint');
ERROR:  cannot alter type of a column used by a view or rule
DETALLE:  rule _RETURN on view v4_1_statistics_vms_interfaces_resources_usage_hourly depends on column "history_id"
CONTEXTO:  SQL statement "ALTER TABLE vm_interface_hourly_history ALTER COLUMN history_id TYPE bigint"
PL/pgSQL function fn_db_change_column_type(character varying,character varying,character varying,character varying) line 9 at EXECUTE statement
bla=# SELECT fn_db_change_column_type('vm_interface_samples_history', 'history_id', 'integer', 'bigint');
ERROR:  cannot alter type of a column used by a view or rule
DETALLE:  rule _RETURN on view v4_1_statistics_vms_interfaces_resources_usage_samples depends on column "history_id"
CONTEXTO:  SQL statement "ALTER TABLE vm_interface_samples_history ALTER COLUMN history_id TYPE bigint"
PL/pgSQL function fn_db_change_column_type(character varying,character varying,character varying,character varying) line 9 at EXECUTE statement

Please try adding a file to your 
packaging/dbscripts/upgrade/

like the one in the patch:
packaging/dbscripts/upgrade/04_02_0020__updated_vm_interface_history_id_to_bigint.sql

(make sure that the file number is higher then the last upgrade script)
After that run 
schema.sh -d ovirt_engine_history -u <user> -c apply
that should drop the views make the alterations and recreate the views.

/usr/share/ovirt-engine-dwh/dbscripts/upgrade# cat 04_01_0021_bigint_instead_of_int.sql
SELECT fn_db_change_column_type('vm_interface_daily_history', 'history_id', 'integer', 'bigint');
SELECT fn_db_change_column_type('vm_interface_hourly_history', 'history_id', 'integer', 'bigint');
SELECT fn_db_change_column_type('vm_interface_samples_history', 'history_id', 'integer', 'bigint');

/usr/share/ovirt-engine-dwh/dbscripts/upgrade# PGPASSWORD=... /usr/share/ovirt-engine-dwh/dbscripts/schema.sh -d ovirt_engine_history -u ovirt_engine_history -c apply
Creating schema ovirt_engine_history@localhost:5432/ovirt_engine_history
Saving custom users permissions on database objects...
upgrade script detected a change in Config, View or Stored Procedure...
Running upgrade sql script '/usr/share/ovirt-engine-dwh/dbscripts/upgrade/pre_upgrade/add_comment_col_to_schema_version.sql'...
Running upgrade sql script '/usr/share/ovirt-engine-dwh/dbscripts/upgrade/pre_upgrade/add_function_to_update_enum_table.sql'...
Running upgrade sql script '/usr/share/ovirt-engine-dwh/dbscripts/upgrade/pre_upgrade/changed_len_of_installed_by.sql'...
Running upgrade sql script '/usr/share/ovirt-engine-dwh/dbscripts/upgrade/pre_upgrade/set_default_lang_code.sql'...
Running upgrade sql script '/usr/share/ovirt-engine-dwh/dbscripts/upgrade/pre_upgrade/set_etl_minimal_version.sql'...
Running upgrade sql script '/usr/share/ovirt-engine-dwh/dbscripts/upgrade/04_01_0021_bigint_instead_of_int.sql'...
Creating views API 3.6...
Creating views API 4.0...
Creating views API 4.1...
Creating ovirt engine reports views...
Creating stored procedures...
Creating stored procedures from /usr/share/ovirt-engine-dwh/dbscripts/common_sp.sql...
Creating stored procedures from /usr/share/ovirt-engine-dwh/dbscripts/create_sp.sql...
Running upgrade sql script '/usr/share/ovirt-engine-dwh/dbscripts/upgrade/post_upgrade/0010_change_enum_lang_code.sql'...
Applying custom users permissions on database objects...

Having a look at the Dashboard, this seems to have fixed the problem.

Just one more question: Once I upgrade to 4.2, will I have any issue because version 4.2 expects to be those fields integers and not bigints? In other words, should I undo this change before upgrading to 4.2?

Thanks for the help.

That is great! I'm very happy to hear that.
No. There shouldn't be any issue on upgrade.
Can we close this bug?

Sure. Thanks for the help.

Closing with resolution EOL since oVirt 4.1 reached end of life and no fix will be pushed to that version.