From: "InterNetX - Juergen Gotteswinter" <jg(a)internetx.com&gt; To: users(a)ovirt.org Sent: Monday, December 22, 2014 11:08:05 AM Subject: [ovirt-users] Problem Upgrading 3.4.4 -> 3.5 Hi, i am currently trying to upgrade an existing 3.4.4 Setup (which got upgraded several times before, starting at 3.3), but this time i run into a Error while Upgrading the DB

-- snip -- ********* QUERY ********** ALTER TABLE event_subscriber DROP CONSTRAINT fk_event_subscriber_event_notification_methods; ************************** 2014-12-20 00:16:27 DEBUG otopi.plugins.ovirt_engine_setup.ovirt_engine.db.schema plugin.executeRaw:803 execute-result: ['/usr/share/ovirt-engine/dbscripts/schema.sh', '-s', 'localhost', '-p', '5432', '-u', 'engine', '-d', 'engine', '-l', '/var/log/ovirt-engine/setup/ovirt-engine-setup-20141220001232-3xjymi.log', '-c', 'apply'], rc=1 2014-12-20 00:16:27 DEBUG otopi.plugins.ovirt_engine_setup.ovirt_engine.db.schema plugin.execute:861 execute-output: ['/usr/share/ovirt-engine/dbscripts/schema.sh', '-s', 'localhost', '-p', '5432', '-u', 'engine', '-d', 'engine', '-l', '/var/log/ovirt-engine/setup/ovirt-engine-setup-20141220001232-3xjymi.log', '-c', 'apply'] stdout: Creating schema engine@localhost:5432/engine 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/dbscripts/upgrade/pre_upgrade/0000_config.sql'... Running upgrade sql script '/usr/share/ovirt-engine/dbscripts/upgrade/pre_upgrade/0010_custom.sql'... Running upgrade sql script '/usr/share/ovirt-engine/dbscripts/upgrade/pre_upgrade/0020_add_materialized_views_table.sql'... Running upgrade sql script '/usr/share/ovirt-engine/dbscripts/upgrade/pre_upgrade/0030_materialized_views_extensions.sql'... Running upgrade sql script '/usr/share/ovirt-engine/dbscripts/upgrade/pre_upgrade/0040_extend_installed_by_column.sql'... Dropping materialized views... Running upgrade sql script '/usr/share/ovirt-engine/dbscripts/upgrade/03_05_0010_add_tables_for_gluster_volume_and_brick_details.sql'... Running upgrade sql script '/usr/share/ovirt-engine/dbscripts/upgrade/03_05_0020_gluster_refresh_gluster_volume_details-event_map.sql'... Skipping upgrade script /usr/share/ovirt-engine/dbscripts/upgrade/03_05_0030_add_ha_columns_to_vds_statistics.sql, already installed by 03040610 Skipping upgrade script /usr/share/ovirt-engine/dbscripts/upgrade/03_05_0040_add_ha_maintenance_events.sql, already installed by 03040620 Running upgrade sql script '/usr/share/ovirt-engine/dbscripts/upgrade/03_05_0050_event_notification_methods.sql'... 2014-12-20 00:16:27 DEBUG otopi.plugins.ovirt_engine_setup.ovirt_engine.db.schema plugin.execute:866 execute-output: ['/usr/share/ovirt-engine/dbscripts/schema.sh', '-s', 'localhost', '-p', '5432', '-u', 'engine', '-d', 'engine', '-l', '/var/log/ovirt-engine/setup/ovirt-engine-setup-20141220001232-3xjymi.log', '-c', 'apply'] stderr: psql:/usr/share/ovirt-engine/dbscripts/upgrade/03_05_0050_event_notification_methods.sql:2: ERROR: constraint "fk_event_subscriber_event_notification_methods" of relation "event_subscriber" does not exist FATAL: Cannot execute sql command: --file=/usr/share/ovirt-engine/dbscripts/upgrade/03_05_0050_event_notification_methods.sql 2014-12-20 00:16:27 DEBUG otopi.context context._executeMethod:152 method exception Traceback (most recent call last): File "/usr/lib/python2.6/site-packages/otopi/context.py", line 142, in _executeMethod method['method']() File "/usr/share/ovirt-engine/setup/bin/../plugins/ovirt-engine-setup/ovirt-engine/db/schema.py", line 291, in _misc oenginecons.EngineDBEnv.PGPASS_FILE File "/usr/lib/python2.6/site-packages/otopi/plugin.py", line 871, in execute command=args[0], RuntimeError: Command '/usr/share/ovirt-engine/dbscripts/schema.sh' failed to execute 2014-12-20 00:16:27 ERROR otopi.context context._executeMethod:161 Failed to execute stage 'Misc configuration': Command '/usr/share/ovirt-engine/dbscripts/schema.sh' failed to execute 2014-12-20 00:16:27 DEBUG otopi.transaction transaction.abort:131 aborting 'Yum Transaction' -- snip -- after that, engine-setup starts doing a rollback to 3.4.4 which worksflawless. Anyone got an Idea what is causing this? Thanks, Juergen _______________________________________________ Users mailing list Users(a)ovirt.org http://lists.ovirt.org/mailman/listinfo/users

From: "InterNetX - Juergen Gotteswinter" <jg(a)internetx.com&gt; To: "Eli Mesika" <emesika(a)redhat.com&gt; Cc: users(a)ovirt.org Sent: Monday, December 22, 2014 12:06:51 PM Subject: Re: [ovirt-users] Problem Upgrading 3.4.4 -> 3.5 Am 22.12.2014 um 10:41 schrieb Eli Mesika: > > > ----- Original Message ----- >> From: "InterNetX - Juergen Gotteswinter" <jg(a)internetx.com&gt; >> To: users(a)ovirt.org >> Sent: Monday, December 22, 2014 11:08:05 AM >> Subject: [ovirt-users] Problem Upgrading 3.4.4 -> 3.5 >> >> Hi, >> >> i am currently trying to upgrade an existing 3.4.4 Setup (which got >> upgraded several times before, starting at 3.3), but this time i run >> into a Error while Upgrading the DB > > Hi > Can you please attach the following data (this information does not include > any customer info, just log of installed scripts) > > pg_dump -U engine -f schema-version.sql -t schema_version engine sure :)

> >> >> -- snip -- >> >> >> ********* QUERY ********** >> ALTER TABLE event_subscriber DROP CONSTRAINT >> fk_event_subscriber_event_notification_methods; >> ************************** >> >> 2014-12-20 00:16:27 DEBUG >> otopi.plugins.ovirt_engine_setup.ovirt_engine.db.schema >> plugin.executeRaw:803 execute-result: >> ['/usr/share/ovirt-engine/dbscripts/schema.sh', '-s', 'localhost', '-p', >> '5432', '-u', 'engine', '-d', 'engine', '-l', >> '/var/log/ovirt-engine/setup/ovirt-engine-setup-20141220001232-3xjymi.log', >> '-c', 'apply'], rc=1 >> 2014-12-20 00:16:27 DEBUG >> otopi.plugins.ovirt_engine_setup.ovirt_engine.db.schema >> plugin.execute:861 execute-output: >> ['/usr/share/ovirt-engine/dbscripts/schema.sh', '-s', 'localhost', '-p', >> '5432', '-u', 'engine', '-d', 'engine', '-l', >> '/var/log/ovirt-engine/setup/ovirt-engine-setup-20141220001232-3xjymi.log', >> '-c', 'apply'] stdout: >> Creating schema engine@localhost:5432/engine >> 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/dbscripts/upgrade/pre_upgrade/0000_config.sql'... >> Running upgrade sql script >> '/usr/share/ovirt-engine/dbscripts/upgrade/pre_upgrade/0010_custom.sql'... >> Running upgrade sql script >> '/usr/share/ovirt-engine/dbscripts/upgrade/pre_upgrade/0020_add_materialized_views_table.sql'... >> Running upgrade sql script >> '/usr/share/ovirt-engine/dbscripts/upgrade/pre_upgrade/0030_materialized_views_extensions.sql'... >> Running upgrade sql script >> '/usr/share/ovirt-engine/dbscripts/upgrade/pre_upgrade/0040_extend_installed_by_column.sql'... >> Dropping materialized views... >> Running upgrade sql script >> '/usr/share/ovirt-engine/dbscripts/upgrade/03_05_0010_add_tables_for_gluster_volume_and_brick_details.sql'... >> Running upgrade sql script >> '/usr/share/ovirt-engine/dbscripts/upgrade/03_05_0020_gluster_refresh_gluster_volume_details-event_map.sql'... >> Skipping upgrade script >> /usr/share/ovirt-engine/dbscripts/upgrade/03_05_0030_add_ha_columns_to_vds_statistics.sql, >> already installed by 03040610 >> Skipping upgrade script >> /usr/share/ovirt-engine/dbscripts/upgrade/03_05_0040_add_ha_maintenance_events.sql, >> already installed by 03040620 >> Running upgrade sql script >> '/usr/share/ovirt-engine/dbscripts/upgrade/03_05_0050_event_notification_methods.sql'... >> >> 2014-12-20 00:16:27 DEBUG >> otopi.plugins.ovirt_engine_setup.ovirt_engine.db.schema >> plugin.execute:866 execute-output: >> ['/usr/share/ovirt-engine/dbscripts/schema.sh', '-s', 'localhost', '-p', >> '5432', '-u', 'engine', '-d', 'engine', '-l', >> '/var/log/ovirt-engine/setup/ovirt-engine-setup-20141220001232-3xjymi.log', >> '-c', 'apply'] stderr: >> psql:/usr/share/ovirt-engine/dbscripts/upgrade/03_05_0050_event_notification_methods.sql:2: >> ERROR: constraint "fk_event_subscriber_event_notification_methods" of >> relation "event_subscriber" does not exist >> FATAL: Cannot execute sql command: >> --file=/usr/share/ovirt-engine/dbscripts/upgrade/03_05_0050_event_notification_methods.sql >> >> 2014-12-20 00:16:27 DEBUG otopi.context context._executeMethod:152 >> method exception >> Traceback (most recent call last): >> File "/usr/lib/python2.6/site-packages/otopi/context.py", line 142, in >> _executeMethod >> method['method']() >> File >> "/usr/share/ovirt-engine/setup/bin/../plugins/ovirt-engine-setup/ovirt-engine/db/schema.py", >> line 291, in _misc >> oenginecons.EngineDBEnv.PGPASS_FILE >> File "/usr/lib/python2.6/site-packages/otopi/plugin.py", line 871, in >> execute >> command=args[0], >> RuntimeError: Command '/usr/share/ovirt-engine/dbscripts/schema.sh' >> failed to execute >> 2014-12-20 00:16:27 ERROR otopi.context context._executeMethod:161 >> Failed to execute stage 'Misc configuration': Command >> '/usr/share/ovirt-engine/dbscripts/schema.sh' failed to execute >> 2014-12-20 00:16:27 DEBUG otopi.transaction transaction.abort:131 >> aborting 'Yum Transaction' >> >> -- snip -- >> >> after that, engine-setup starts doing a rollback to 3.4.4 which >> worksflawless. >> >> Anyone got an Idea what is causing this? >> >> Thanks, >> >> Juergen >> >> _______________________________________________ >> Users mailing list >> Users(a)ovirt.org >> http://lists.ovirt.org/mailman/listinfo/users >>

From: "InterNetX - Juergen Gotteswinter" <jg(a)internetx.com&gt; To: users(a)ovirt.org Sent: Monday, December 22, 2014 2:07:55 PM Subject: Re: [ovirt-users] Problem Upgrading 3.4.4 -> 3.5 Hello again, > It seems that somebody had deleted manually the constraint > fk_event_subscriber_event_notification_methods from your database > Therefor, the first line that attempts to drop this constraint in > 03_05_0050_event_notification_methods.sql: ALTER TABLE event_subscriber > DROP CONSTRAINT fk_event_subscriber_event_notification_methods; > fails. uhm, interesting. could this be caused be deinstallation of dwh reporting? > > Please try to run the following manually and upgrade again > > psql -U engine -c "ALTER TABLE ONLY event_subscriber ADD CONSTRAINT > fk_event_subscriber_event_notification_methods FOREIGN KEY (method_id) > REFERENCES event_notification_methods(method_id) ON DELETE CASCADE;" > engine > it just drops "ERROR: relation "event_notification_methods" does not exist"

> Let me know how it is going ... > > Eli > Thank you already for your help! Juergen _______________________________________________ Users mailing list Users(a)ovirt.org http://lists.ovirt.org/mailman/listinfo/users

>>> It seems that somebody had deleted manually the constraint >>> fk_event_subscriber_event_notification_methods from your database >>> Therefor, the first line that attempts to drop this constraint in >>> 03_05_0050_event_notification_methods.sql: ALTER TABLE event_subscriber >>> DROP CONSTRAINT fk_event_subscriber_event_notification_methods; >>> fails. >> >> uhm, interesting. could this be caused be deinstallation of dwh reporting? How exactly did you do that?

Note that partial cleanup is not supported yet [1].

Can you please post all of /var/log/ovirt-engine/setup/* ?

Thanks! [1] https://bugzilla.redhat.com/show_bug.cgi?id=1060529 >> >>> >>> Please try to run the following manually and upgrade again >>> >>> psql -U engine -c "ALTER TABLE ONLY event_subscriber ADD CONSTRAINT >>> fk_event_subscriber_event_notification_methods FOREIGN KEY (method_id) >>> REFERENCES event_notification_methods(method_id) ON DELETE CASCADE;" >>> engine >>> >> >> it just drops "ERROR: relation "event_notification_methods" does not >> exist" > > OK > Lets check what do you have in your DB > > Please attach the result of the following > > psql -U engine -c "select table_name from information_schema.tables where > table_schema = 'public' order by table_name ;" engine > > Thanks > > >> >>> Let me know how it is going ... >>> >>> Eli >>> >> >> Thank you already for your help! >> >> Juergen >> _______________________________________________ >> Users mailing list >> Users(a)ovirt.org >> http://lists.ovirt.org/mailman/listinfo/users >> > _______________________________________________ > Users mailing list > Users(a)ovirt.org > http://lists.ovirt.org/mailman/listinfo/users >

>>>>> It seems that somebody had deleted manually the constraint >>>>> fk_event_subscriber_event_notification_methods from your database >>>>> Therefor, the first line that attempts to drop this constraint in >>>>> 03_05_0050_event_notification_methods.sql: ALTER TABLE event_subscriber >>>>> DROP CONSTRAINT fk_event_subscriber_event_notification_methods; >>>>> fails. >>>> >>>> uhm, interesting. could this be caused be deinstallation of dwh >>>> reporting? >> >> How exactly did you do that? > > > very good question, thats a few months ago. i whould guess with rpm -e > before an engine upgrade (if i remember correctly there was one ovirt > release where dwh was missing for el6). > >> >> Note that partial cleanup is not supported yet [1]. > > checking right after that mail :) > >> >> Can you please post all of /var/log/ovirt-engine/setup/* ? > > sure, sending you the dl link in a private mail. since i am not sure if > i sed´ed out all "private" things Based on these logs, it seems to me that: 1. At some point you upgraded to a snapshot of master (then-3.4), installing ovirt-engine-3.4.0-0.12.master.20140228075627.el6. 2. This package had an older version of the script dbscripts/upgrade/03_04_0600_event_notification_methods.sql . 3. Therefore, when you now try to upgrade, engine-setup tries to run the newer version, and fails. Why? Because it keeps in the database the checksum of every upgrade script it runs, and does not run again scripts with same checksum. But in your case the checksums are different, so it does try that. It fails, because the older version already dropped the table event_notification_methods. How to fix this? First, note that upgrades between dev/beta/rc/etc versions is not supported. So the "official" answer is to remove everything and start from scratch. Or, if you have good backups of the latest 3.3 version you had, restore to that one and then upgrade to 3.4 and then 3.5. If you want to try and force an upgrade, you can do the following, but note that it might fail elsewhere, or even fail in some future upgrade: 1. Following a 'git log' of this file, it seems to me that the only change it went through between the version you installed and the one in final 3.4, is [1]. It seems that the relevant part of this change can be done by you by running: ALTER TABLE event_subscriber ADD COLUMN notification_method CHARACTER VARYING(32) DEFAULT 'EMAIL' CHECK (notification_method IN ('EMAIL', 'SNMP_TRAP')); 2. After you do that, you can convince engine-setup that you already ran the version of the script you now have, by running: update schema_version set checksum='feabc7bc7bb7ff749f075be48538c92e' where version='03040600'; Backup everything before you start. No guarantee. Use at your own risk. As I said, better remove everything and setup again clean or restore your latest backup of a supported version and upgrade from that one. Good luck. Please report back :-) Thanks, [1] http://gerrit.ovirt.org/25393

From: "InterNetX - Juergen Gotteswinter" <juergen.gotteswinter(a)internetx.com&gt; To: users(a)ovirt.org Cc: "Yedidyah Bar David" <didi(a)redhat.com&gt; Sent: Tuesday, January 20, 2015 12:09:03 PM Subject: Re: [ovirt-users] Problem Upgrading 3.4.4 -> 3.5 So, did the dirty Way and it was successful :) Thanks to everyone who helped me out, great community! Cheers, Juergen Am 29.12.2014 um 10:28 schrieb InterNetX - Juergen Gotteswinter: > Hello both of you, > > thanks for your detailed explainations and support, still thinking which > way i will go. tending to try the dirty way in a lab setup before to see > what happens. > > Will post updates when i got more :) > > Cheers, > > Juergen > >>>>>>> It seems that somebody had deleted manually the constraint >>>>>>> fk_event_subscriber_event_notification_methods from your database >>>>>>> Therefor, the first line that attempts to drop this constraint in >>>>>>> 03_05_0050_event_notification_methods.sql: ALTER TABLE >>>>>>> event_subscriber >>>>>>> DROP CONSTRAINT fk_event_subscriber_event_notification_methods; >>>>>>> fails. >>>>>> >>>>>> uhm, interesting. could this be caused be deinstallation of dwh >>>>>> reporting? >>>> >>>> How exactly did you do that? >>> >>> >>> very good question, thats a few months ago. i whould guess with rpm -e >>> before an engine upgrade (if i remember correctly there was one ovirt >>> release where dwh was missing for el6). >>> >>>> >>>> Note that partial cleanup is not supported yet [1]. >>> >>> checking right after that mail :) >>> >>>> >>>> Can you please post all of /var/log/ovirt-engine/setup/* ? >>> >>> sure, sending you the dl link in a private mail. since i am not sure if >>> i sed´ed out all "private" things >> >> Based on these logs, it seems to me that: >> >> 1. At some point you upgraded to a snapshot of master (then-3.4), >> installing >> ovirt-engine-3.4.0-0.12.master.20140228075627.el6. >> >> 2. This package had an older version of the script >> dbscripts/upgrade/03_04_0600_event_notification_methods.sql . >> >> 3. Therefore, when you now try to upgrade, engine-setup tries to run the >> newer version, and fails. Why? Because it keeps in the database the >> checksum >> of every upgrade script it runs, and does not run again scripts with same >> checksum. But in your case the checksums are different, so it does try >> that. >> It fails, because the older version already dropped the table >> event_notification_methods. >> >> How to fix this? >> >> First, note that upgrades between dev/beta/rc/etc versions is not >> supported. >> So the "official" answer is to remove everything and start from scratch. >> Or, if you >> have good backups of the latest 3.3 version you had, restore to that one >> and then >> upgrade to 3.4 and then 3.5. >> >> If you want to try and force an upgrade, you can do the following, but >> note that >> it might fail elsewhere, or even fail in some future upgrade: >> >> 1. Following a 'git log' of this file, it seems to me that the only change >> it >> went through between the version you installed and the one in final 3.4, >> is [1]. >> It seems that the relevant part of this change can be done by you by >> running: >> >> ALTER TABLE event_subscriber ADD COLUMN notification_method CHARACTER >> VARYING(32) DEFAULT 'EMAIL' CHECK (notification_method IN ('EMAIL', >> 'SNMP_TRAP')); >> >> 2. After you do that, you can convince engine-setup that you already ran >> the >> version of the script you now have, by running: >> >> update schema_version set checksum='feabc7bc7bb7ff749f075be48538c92e' >> where version='03040600'; >> >> Backup everything before you start. >> >> No guarantee. Use at your own risk. >> >> As I said, better remove everything and setup again clean or restore your >> latest backup of a supported version and upgrade from that one. >> >> Good luck. Please report back :-) Thanks, >> >> [1] http://gerrit.ovirt.org/25393 >> > > _______________________________________________ > Users mailing list > Users(a)ovirt.org > http://lists.ovirt.org/mailman/listinfo/users >