Sounds like a good candidate for engine_3.1 gerrit branch (after push to master), and probably a release blocker as well.
----- Original Message ----- > > > ----- Original Message ----- > > From: "Eli Mesika" <[email protected]> > > To: "Dead Horse" <[email protected]> > > Cc: [email protected] > > Sent: Sunday, July 22, 2012 12:41:36 PM > > Subject: Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue > > > > > > > > ----- Original Message ----- > > > From: "Eli Mesika" <[email protected]> > > > To: "Dead Horse" <[email protected]> > > > Cc: [email protected] > > > Sent: Sunday, July 22, 2012 10:46:48 AM > > > Subject: Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue > > > > > > > > > > > > ----- Original Message ----- > > > > From: "Dead Horse" <[email protected]> > > > > To: "Robert Middleswarth" <[email protected]> > > > > Cc: "Eli Mesika" <[email protected]>, [email protected] > > > > Sent: Friday, July 20, 2012 6:04:26 PM > > > > Subject: Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue > > > > > > > > Any thoughts here on this, should I file a bug? > > > > Per: http://wiki.ovirt.org/wiki/Second_Release > > > > Working upgrade is one of the release criteria. > > > > I will help in any way possible to get a 3.0 to 3.1 upgrade > > > > path > > > > working. > > > > > > > > - DHC > > > > > > > > > > > > On Thu, Jul 19, 2012 at 3:40 PM, Dead Horse < > > > > [email protected] > wrote: > > > > > > > > > > > > If it helps here is the contents of "upgrade.sh.log": > > > > > > I had succeeded to simulate the problem, will update you ASAP. > > > > Hi again > > Problem was resolved > > 1) pre-upgrade scripts did not run because no md5 change was found > > in > > files after restore > > 2) pre-upgrade scripts did not run in the correct order after > > fixing > > 1) > > > > I will issue a patch upstream for that, please let me know if you > > want me to send you the patch before if you want to test it on your > > environment. > > Opened BZ 842119 > patch attached > > > > > > > > Thanks > > > > > > > > > > > > > > ********* QUERY ********** > > > > create or replace function __temp_fn_create_schema_version () > > > > returns void > > > > AS $procedure$ > > > > begin > > > > if not exists (select 1 from information_schema.tables where > > > > table_name = 'schema_version') then > > > > CREATE SEQUENCE schema_version_seq INCREMENT BY 1 START WITH 1; > > > > CREATE TABLE schema_version > > > > ( > > > > id INTEGER DEFAULT NEXTVAL('schema_version_seq') NOT NULL, > > > > "version" varchar(10) NOT NULL, > > > > script varchar(255) NOT NULL, > > > > checksum varchar(128), > > > > installed_by varchar(30) NOT NULL, > > > > started_at timestamp DEFAULT now(), > > > > ended_at timestamp , > > > > state character varying(15) NOT NULL, > > > > "current" boolean NOT NULL, > > > > CONSTRAINT schema_version_primary_key PRIMARY KEY (id) > > > > ); > > > > > > > > insert into > > > > schema_version(version,script,checksum,installed_by,ended_at,state,current) > > > > values > > > > ('03000000','upgrade/03_00_0000_add_schema_version.sql','0','postgres',now(),'INSTALLED',true); > > > > end if; > > > > > > > > END; $procedure$ > > > > LANGUAGE plpgsql; > > > > ************************** > > > > > > > > CREATE FUNCTION > > > > ********* QUERY ********** > > > > select __temp_fn_create_schema_version(); > > > > ************************** > > > > > > > > > > > > > > > > ********* QUERY ********** > > > > DROP FUNCTION __temp_fn_create_schema_version(); > > > > ************************** > > > > > > > > DROP FUNCTION > > > > ********* QUERY ********** > > > > CREATE OR REPLACE FUNCTION __temp_Upgrade_add_job_table() > > > > RETURNS void > > > > AS $function$ > > > > BEGIN > > > > IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE > > > > table_name ILIKE 'job') THEN > > > > > > > > -- Add the job table. > > > > > > > > CREATE TABLE job > > > > ( > > > > job_id UUID NOT NULL, > > > > action_type VARCHAR(50) NOT NULL, > > > > description TEXT NOT NULL, > > > > status VARCHAR(32) NOT NULL, > > > > owner_id UUID, > > > > visible BOOLEAN NOT NULL DEFAULT true, > > > > start_time TIMESTAMP WITH TIME ZONE NOT NULL, > > > > end_time TIMESTAMP WITH TIME ZONE default NULL, > > > > last_update_time TIMESTAMP WITH TIME ZONE default NULL, > > > > correlation_id VARCHAR(50) NOT NULL, > > > > CONSTRAINT pk_jobs PRIMARY KEY(job_id) > > > > ) > > > > WITH OIDS; > > > > > > > > END IF; > > > > END; $function$ > > > > LANGUAGE plpgsql; > > > > ************************** > > > > > > > > CREATE FUNCTION > > > > ********* QUERY ********** > > > > SELECT * FROM __temp_Upgrade_add_job_table(); > > > > ************************** > > > > > > > > > > > > > > > > ********* QUERY ********** > > > > DROP FUNCTION __temp_Upgrade_add_job_table(); > > > > ************************** > > > > > > > > DROP FUNCTION > > > > ********* QUERY ********** > > > > insert into > > > > schema_version(version,script,checksum,installed_by,started_at,ended_at,state,current,comment) > > > > values > > > > (trim('03010260'),'upgrade/03_01_0260_add_job_table.sql','c74d309cfb8f9d802e54f2ff66d560b4','postgres', > > > > cast(trim(' 2012-07-19 15:30:52.500231-05') as > > > > timestamp),cast(trim(' > > > > 2012-07-19 15:30:52.565532-05') as > > > > timestamp),'INSTALLED',false,''); > > > > ************************** > > > > > > > > ********* QUERY ********** > > > > create or replace function __temp_fn_create_schema_version () > > > > returns void > > > > AS $procedure$ > > > > begin > > > > if not exists (select 1 from information_schema.tables where > > > > table_name = 'schema_version') then > > > > CREATE SEQUENCE schema_version_seq INCREMENT BY 1 START WITH 1; > > > > CREATE TABLE schema_version > > > > ( > > > > id INTEGER DEFAULT NEXTVAL('schema_version_seq') NOT NULL, > > > > "version" varchar(10) NOT NULL, > > > > script varchar(255) NOT NULL, > > > > checksum varchar(128), > > > > installed_by varchar(30) NOT NULL, > > > > started_at timestamp DEFAULT now(), > > > > ended_at timestamp , > > > > state character varying(15) NOT NULL, > > > > "current" boolean NOT NULL, > > > > CONSTRAINT schema_version_primary_key PRIMARY KEY (id) > > > > ); > > > > > > > > insert into > > > > schema_version(version,script,checksum,installed_by,ended_at,state,current) > > > > values > > > > ('03000000','upgrade/03_00_0000_add_schema_version.sql','0','postgres',now(),'INSTALLED',true); > > > > end if; > > > > > > > > END; $procedure$ > > > > LANGUAGE plpgsql; > > > > ************************** > > > > > > > > CREATE FUNCTION > > > > ********* QUERY ********** > > > > select __temp_fn_create_schema_version(); > > > > ************************** > > > > > > > > > > > > > > > > ********* QUERY ********** > > > > DROP FUNCTION __temp_fn_create_schema_version(); > > > > ************************** > > > > > > > > DROP FUNCTION > > > > ********* QUERY ********** > > > > CREATE OR REPLACE FUNCTION __temp_Upgrade_add_job_table() > > > > RETURNS void > > > > AS $function$ > > > > BEGIN > > > > IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE > > > > table_name ILIKE 'job') THEN > > > > > > > > -- Add the job table. > > > > > > > > CREATE TABLE job > > > > ( > > > > job_id UUID NOT NULL, > > > > action_type VARCHAR(50) NOT NULL, > > > > description TEXT NOT NULL, > > > > status VARCHAR(32) NOT NULL, > > > > owner_id UUID, > > > > visible BOOLEAN NOT NULL DEFAULT true, > > > > start_time TIMESTAMP WITH TIME ZONE NOT NULL, > > > > end_time TIMESTAMP WITH TIME ZONE default NULL, > > > > last_update_time TIMESTAMP WITH TIME ZONE default NULL, > > > > correlation_id VARCHAR(50) NOT NULL, > > > > CONSTRAINT pk_jobs PRIMARY KEY(job_id) > > > > ) > > > > WITH OIDS; > > > > > > > > END IF; > > > > END; $function$ > > > > LANGUAGE plpgsql; > > > > ************************** > > > > > > > > CREATE FUNCTION > > > > ********* QUERY ********** > > > > SELECT * FROM __temp_Upgrade_add_job_table(); > > > > ************************** > > > > > > > > > > > > > > > > ********* QUERY ********** > > > > DROP FUNCTION __temp_Upgrade_add_job_table(); > > > > ************************** > > > > > > > > DROP FUNCTION > > > > ********* QUERY ********** > > > > insert into > > > > schema_version(version,script,checksum,installed_by,started_at,ended_at,state,current,comment) > > > > values > > > > (trim('03010260'),'upgrade/03_01_0260_add_job_table.sql','c74d309cfb8f9d802e54f2ff66d560b4','postgres', > > > > cast(trim(' 2012-07-19 15:34:00.060083-05') as > > > > timestamp),cast(trim(' > > > > 2012-07-19 15:34:00.124151-05') as > > > > timestamp),'INSTALLED',false,''); > > > > ************************** > > > > > > > > > > > > > > > > > > > > > > > > > > > > On Thu, Jul 19, 2012 at 2:26 PM, Dead Horse < > > > > [email protected] > wrote: > > > > > > > > > > > > Eli, > > > > I am following the procedure for DB backup/restore from > > > > here: http://wiki.ovirt.org/wiki/Backup_engine_db > > > > and > > > > here: > > > > http://docs.redhat.com/docs/en-US/Red_Hat_Enterprise_Virtualization/3.0/html/Administration_Guide/Administration_Guide-Backing_Up.html#Administration_Guide-BU_Database > > > > (Which the RHEV proceadure BTW has a syntax error): > > > > "psql -U postgres -d rhevm -W -f > > > > /usr/share/rhevm/db-backups/dump_RHEVDB_BACKUP_`date > > > > "+%Y%m%d_%R"`.sql" > > > > should be: > > > > psql -U postgres -W -f > > > > /usr/share/rhevm/db-backups/dump_RHEVDB_BACKUP_`date > > > > "+%Y%m%d_%R"`.sql > > > > > > > > Either of the above procedures worked with nightly builds up > > > > until > > > > about a month back. I was able to backup a 3.0 database and > > > > restore > > > > it per the above on a system running the nightly build, then > > > > run > > > > the > > > > upgrade.sh script located in /usr/share/ovirt-engine/dbscripts. > > > > There is no script in /usr/share/ovirt-engine/dbscripts called > > > > "upgrade_db.sh" I always ran upgrade.sh -u postgres and it > > > > worked > > > > fine. > > > > > > > > I am not using create_db.sh script in > > > > /usr/share/ovirt-engine/dbscirpts to create the database and > > > > objects. The createdb command I use is the standard postgres > > > > command > > > > to create an new empty database. (/bin/createdb"). Thus what > > > > imports > > > > is actually a clean restore of the 3.0 database > > > > > > > > Similarly I am using "/bin/dropdb" to destroy/delete the engine > > > > database. > > > > > > > > Thus importing the dump from 3.0 works using those procedures > > > > but > > > > results in the previous error I noted when attempting to run > > > > "/usr/share/ovirt-engine/dbscripts/upgrade.sh". > > > > > > > > - DHC > > > > > > > > > > > > > > > > > > > > On Thu, Jul 19, 2012 at 7:04 AM, Robert Middleswarth < > > > > [email protected] > wrote: > > > > > > > > > > > > > > > > > > > > On 07/19/2012 04:10 AM, Eli Mesika wrote: > > > > > > > > > > > > > > > > ----- Original Message ----- > > > > > > > > > > > > From: "Dead Horse" < [email protected] > > > > > To: [email protected] > > > > Sent: Thursday, July 19, 2012 2:22:10 AM > > > > Subject: Re: [Users] ovirt-engine upgrade 3.0 to 3.1 issue > > > > > > > > > > > > Forgot reply-all sending to ovirt-users as well. > > > > -DHC > > > > > > > > > > > > On Wed, Jul 18, 2012 at 6:03 PM, Dead Horse < > > > > [email protected] > wrote: > > > > > > > > > > > > 3.0 engine database dump attached as: "engine.sql.tar.gz" > > > > - DHC > > > > > > > > > > > > > > > > > > > > On Wed, Jul 18, 2012 at 1:05 PM, Eli Mesika < > > > > [email protected] > > > > > > > > > wrote: > > > > > > > > > > > > > > > > > > > > > > > > > > > > ----- Original Message ----- > > > > > > > > > > > > From: "Dead Horse" < [email protected] > > > > > To: [email protected] > > > > Sent: Wednesday, July 18, 2012 9:08:55 AM > > > > Subject: [Users] ovirt-engine upgrade 3.0 to 3.1 issue > > > > > > > > > > > > Steps taken: > > > > > > > > Load up bare metal or a VM with FC16 > > > > Install 3.0 from http://www.ovirt.org/releases/ > > > > stable/fedora/16/ > > > > Setup up something minimal (EG: engine-setup then setup a basic > > > > datacenter/cluster/etc) > > > > Add a FC16 or EL based node for fun as well and some VM's if > > > > feeling > > > > ambitious. > > > > > > > > Back up database: > > > > systemctl stop jboss-as.service > > > > pg_dump -C -E UTF8 --column-inserts --disable-dollar-quoting > > > > --disable-triggers -U postgres --format=p -f "/temp/engine.sql" > > > > engine > > > > > > > > Back up the following files within /etc/pki/ovirt-engine from a > > > > 3.0 > > > > install (preserve owner/group). > > > > /etc/pki/ovirt-engine/cacert. conf > > > > /etc/pki/ovirt-engine/ca.pem > > > > /etc/pki/ovirt-engine/cert. conf > > > > /etc/pki/ovirt-engine/certs/ 01.pem > > > > /etc/pki/ovirt-engine/certs/ 02.pem > > > > /etc/pki/ovirt-engine/certs/ ca.der > > > > /etc/pki/ovirt-engine/certs/ engine.cer > > > > /etc/pki/ovirt-engine/certs/ engine.der > > > > /etc/pki/ovirt-engine/ database.txt > > > > /etc/pki/ovirt-engine/ database.txt.attr > > > > /etc/pki/ovirt-engine/ database.txt.attr.old > > > > /etc/pki/ovirt-engine/ database.txt.old > > > > /etc/pki/ovirt-engine/keys/ engine_id_rsa > > > > /etc/pki/ovirt-engine/keys/ engine.ssh.key.txt > > > > /etc/pki/ovirt-engine/private/ ca.pem > > > > /etc/pki/ovirt-engine/ requests/ca.csr > > > > /etc/pki/ovirt-engine/ requests/engine.req > > > > > > > > Load up bare metal or a VM with FC17 (Upgrade from FC16 --> > > > > FC17 > > > > is > > > > still rather messy) > > > > Install 3.1 from http://www.ovirt.org/releases/ beta/fedora/17/ > > > > > > > > Run engine-setup to get an initial setup. > > > > stop ovirt-engine (systemctl stop ovirt-engine.service) > > > > > > > > Copy the previously backed up /etc/pki/ovirt-engine files from > > > > above > > > > over top of the ones just generated (be sure to preserve > > > > owner/group > > > > EG: cp -a) > > > > > > > > Drop the existing engine database: dropdb -U postgres engine > > > > Create a new blank database: createdb -U postgres engine > > > > Well, the problem is that you are creating here the database > > > > with > > > > createdb. > > > > This creates all DB objects and runs all upgrade scripts. > > > > Then when you try to restore from your file , you have > > > > problems, > > > > since > > > > your file also tries to create the same objects. > > > > instead of : createdb -U postgres engine > > > > do : > > > > > > > > > > > > echo "create database engine;" | psql -U postgres template1 > > > > Then continue on the next steps and it will work. > > > > > > > > optionally, in your backup command: > > > > instead of > > > > pg_dump -C -E UTF8 --column-inserts --disable-dollar-quoting > > > > use > > > > pg_dump --data-only -E UTF8 --column-inserts > > > > --disable-dollar-quoting > > > > > > > > then , createdb will create the db & schema and your engine.sql > > > > will > > > > include only "insert" > > > > > > > > Eli > > > > I am anything but a PG expert but exporting only the data then > > > > inserting it on top of the upgrade scheme is a really dumb > > > > idea. > > > > It > > > > might work today if the scheme doesn't change much but it could > > > > really breaks things tomorrow if the scheme sees a massive > > > > change. > > > > You were correct Just create the database restore then run the > > > > upgrade scheme. > > > > > > > > Thanks > > > > Robert > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Populate with with the backup of the above 3.0 database: > > > > psql -U postgres -d engine -w < "/temp/engine.sql" > > > > Can you please attach the engine.sql file so I will be able to > > > > reproduce , thanks > > > > > > > > > > > > > > > > > > > > > > > > > > > > Attempt to upgrade the DBschema via > > > > /usr/share/ovirt-engine/ dbscripts/upgrade.sh > > > > upgrade.sh -u postgres > > > > > > > > The DB schema upgrade will fail like so: > > > > > > > > Running upgrade script upgrade/03_01_0260_add_job_ table.sql > > > > psql:upgrade/03_01_0260_add_ job_table.sql:29: NOTICE: CREATE > > > > TABLE > > > > / > > > > PRIMARY KEY will create implicit index "pk_jobs" for table > > > > "job" > > > > CONTEXT: SQL statement "CREATE TABLE job > > > > ( > > > > job_id UUID NOT NULL, > > > > action_type VARCHAR(50) NOT NULL, > > > > description TEXT NOT NULL, > > > > status VARCHAR(32) NOT NULL, > > > > owner_id UUID, > > > > visible BOOLEAN NOT NULL DEFAULT true, > > > > start_time TIMESTAMP WITH TIME ZONE NOT NULL, > > > > end_time TIMESTAMP WITH TIME ZONE default NULL, > > > > last_update_time TIMESTAMP WITH TIME ZONE default NULL, > > > > correlation_id VARCHAR(50) NOT NULL, > > > > CONSTRAINT pk_jobs PRIMARY KEY(job_id) > > > > ) > > > > WITH OIDS" > > > > PL/pgSQL function "__temp_upgrade_add_job_table" line 6 at SQL > > > > statement > > > > psql:/tmp/tmp.mXz8U4xpWr:3: ERROR: column "comment" of relation > > > > "schema_version" does not exist > > > > LINE 1: > > > > ...ksum,installed_by,started_ at,ended_at,state,current, > > > > comment) > > > > > > > > My assumption here is that if the DB schema upgrade would work, > > > > that > > > > this should be the cleanest way to upgrade from 3.0 to 3.1. > > > > > > > > - DHC > > > > > > > > ______________________________ _________________ > > > > Users mailing list > > > > [email protected] > > > > http://lists.ovirt.org/ mailman/listinfo/users > > > > > > > > > > > > > > > > ______________________________ _________________ > > > > Users mailing list > > > > [email protected] > > > > http://lists.ovirt.org/ mailman/listinfo/users > > > > > > > > ______________________________ _________________ > > > > Users mailing list > > > > [email protected] > > > > http://lists.ovirt.org/ mailman/listinfo/users > > > > > > > > > > > > > > > > > > > > > > > _______________________________________________ > > > Users mailing list > > > [email protected] > > > http://lists.ovirt.org/mailman/listinfo/users > > > > > _______________________________________________ > > Users mailing list > > [email protected] > > http://lists.ovirt.org/mailman/listinfo/users > > > > _______________________________________________ > Users mailing list > [email protected] > http://lists.ovirt.org/mailman/listinfo/users > _______________________________________________ Users mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/users
