Bug 1080542

Please look at the taskcleaner.sh script 

--snip---

# Install taskcleaner procedures
psql -w -U ${USERNAME} -h ${SERVERNAME} -p ${PORT} -f ./taskcleaner_sp.sql ${DATABASE} > /dev/null

---snip---

that means that this script was running once with postgres and therefor the SPs were installed and owned by postgres 

to fix that , please paste the following content to any file , lets assume drop.sql 

---code---
DROP FUNCTION IF EXISTS GetAsyncTasksZombies();
DROP FUNCTION IF EXISTS DeleteAsyncTaskZombiesByTaskId(v_task_id UUID);
DROP FUNCTION IF EXISTS DeleteAsyncTaskZombiesByCommandId(v_command_id UUID);
DROP FUNCTION IF EXISTS DeleteAsyncTaskByCommandId(v_command_id UUID);
DROP FUNCTION IF EXISTS DeleteAsyncTasksZombies();
DROP FUNCTION IF EXISTS DeleteJobStepsByTaskId(v_task_id UUID);
DROP FUNCTION IF EXISTS DeleteEntitySnapshotByZombieTaskId(v_task_id UUID);
DROP FUNCTION IF EXISTS DeleteEntitySnapshotByTaskId(v_task_id UUID);
DROP FUNCTION IF EXISTS DeleteEntitySnapshotZombies();
DROP FUNCTION IF EXISTS DeleteJobStepsByZombieCommandId(v_command_id UUID);
DROP FUNCTION IF EXISTS DeleteJobStepsByCommandId(v_command_id UUID);
DROP FUNCTION IF EXISTS DeleteJobStepsZombies();
DROP FUNCTION IF EXISTS DeleteAllJobs();
DROP FUNCTION IF EXISTS DeleteAllEntitySnapshot();
---code---

Then run

psql -U postgres -f ./drop.sql engine 

After that you can run :

taskcleaner.sh -z -R -J -A

(In reply to Eli Mesika from comment #2)
> Please look at the taskcleaner.sh script 
> 
> --snip---
> 
> # Install taskcleaner procedures
> psql -w -U ${USERNAME} -h ${SERVERNAME} -p ${PORT} -f ./taskcleaner_sp.sql
> ${DATABASE} > /dev/null
> 
> ---snip---
> 
> that means that this script was running once with postgres and therefor the
> SPs were installed and owned by postgres 

Don't you think that we should prevent that? Perhaps do one or both of:

1. Create the SPs in engine-setup and not in taskcleaner

2. Use only the engine user there (by reading engine config) and do not allow passing -u

If you do not like (2.) because you want to keep dbutils independent of engine, perhaps we should create a wrapper for it.

(In reply to Yedidyah Bar David from comment #5)
> (In reply to Eli Mesika from comment #2)
> > Please look at the taskcleaner.sh script 
> > 
> > --snip---
> > 
> > # Install taskcleaner procedures
> > psql -w -U ${USERNAME} -h ${SERVERNAME} -p ${PORT} -f ./taskcleaner_sp.sql
> > ${DATABASE} > /dev/null
> > 
> > ---snip---
> > 
> > that means that this script was running once with postgres and therefor the
> > SPs were installed and owned by postgres 
> 
> Don't you think that we should prevent that? Perhaps do one or both of:
> 
> 1. Create the SPs in engine-setup and not in taskcleaner

taskcleaner can be executed directly so I don't think its a good solution 

> 
> 2. Use only the engine user there (by reading engine config) and do not
> allow passing -u
> 
> If you do not like (2.) because you want to keep dbutils independent of
> engine, perhaps we should create a wrapper for it.

I don't like this either 

Maybe we can check who is the DB owner and prevent passing anything else ...
what do you think ?

(In reply to Eli Mesika from comment #6)
> (In reply to Yedidyah Bar David from comment #5)
> > (In reply to Eli Mesika from comment #2)
> > > Please look at the taskcleaner.sh script 
> > > 
> > > --snip---
> > > 
> > > # Install taskcleaner procedures
> > > psql -w -U ${USERNAME} -h ${SERVERNAME} -p ${PORT} -f ./taskcleaner_sp.sql
> > > ${DATABASE} > /dev/null
> > > 
> > > ---snip---
> > > 
> > > that means that this script was running once with postgres and therefor the
> > > SPs were installed and owned by postgres 
> > 
> > Don't you think that we should prevent that? Perhaps do one or both of:
> > 
> > 1. Create the SPs in engine-setup and not in taskcleaner
> 
> taskcleaner can be executed directly so I don't think its a good solution 

As we discussed, this might still be the best way.

engine-setup loads the SPs, taskcleaner uses them.

> 
> > 
> > 2. Use only the engine user there (by reading engine config) and do not
> > allow passing -u
> > 
> > If you do not like (2.) because you want to keep dbutils independent of
> > engine, perhaps we should create a wrapper for it.
> 
> I don't like this either 
> 
> Maybe we can check who is the DB owner and prevent passing anything else ...
> what do you think ?

Not sure how this is different from (2.)...

Please take over. Thanks.

Will move SPs under dbutils to be installed by engine-setup, this will prevent installing those procedures as another user

Fails on upgrade if SPs were not loaded yet.

We need to load them at upgrade before calling taskcleaner/fkvalidator

Didi, following our conversation, this will be done by you, can you take teh bug
Also consider mark as duplicate 1235707

fixing patch (42655) was reverted by 42932 since it caused problems in upgrade

*** Bug 1235707 has been marked as a duplicate of this bug. ***

Eventually we decided to drop them at end of each util.

To run them with engine db user:
================================

su -

. /usr/share/ovirt-engine/bin/engine-prolog.sh
export PGPASSWORD="${ENGINE_DB_PASSWORD}"

cd /usr/share/ovirt-engine/setup/dbutils

./fkvalidator.sh -u "${ENGINE_DB_USER}" -d "${ENGINE_DB_DATABASE}"

./taskcleaner.sh -u "${ENGINE_DB_USER}" -d "${ENGINE_DB_DATABASE}"

./unlock_entity.sh -u "${ENGINE_DB_USER}" -d "${ENGINE_DB_DATABASE}" -t all

To run them with postgres db user:
==================================
(assuming db on localhost)

su - postgres

cd /usr/share/ovirt-engine/setup/dbutils

./fkvalidator.sh -u postgres -d engine -s /tmp

./taskcleaner.sh -u postgres -d engine -s /tmp

./unlock_entity.sh -u postgres -d engine -s /tmp -t all

If you still get an error message
=================================
It's probably because you ran it with user postgres in the past (with an older version) and now try with engine db user.

Just run once with postgres, it will run and then drop the SPs. Then engine db user will be able to create them and run successfully.

comment 13 above refers to the behavior with a pending patch (42980). Without the patch, everything in the comment still works, but will not drop the SPs, so a next attempt might fail (third case there). If so you can still run with user postgres, but running with user engine (as does engine-setup) will still fail. See comment 2 for how to drop manually.

Verified with instructions from #c13 with:
ovirt-engine-3.6.0-0.0.master.20150627185750.git6f063c1.el6.noarch

unlock all  completed successfully.

Since the problem described in this bug report should be
resolved in a recent advisory, it has been closed with a
resolution of ERRATA.

For information on the advisory, and where to find the updated
files, follow the link below.

If the solution does not work for you, open a new bug report.

https://rhn.redhat.com/errata/RHEA-2016-0376.html