Doh. You are right. Thanks Vincent, Bob
On Nov 4, 2013, at 4:19 PM, Ma, Dong (Vincent, Open Source Program Office) <[email protected]> wrote: > The first sql will connect copyright and pfile table records into > copyright_pfile view, if the copyright records have pfile_fk but the pfile > records didn’t exist, the pfile_pk in copyright_pfile view will be NULL, so > in the secord sql delete the copyright records when the pfile_pk IS NULL in > copyright_pfile view, this will delete the wrong copyright records. > > Thanks, > Vincent > > From: Gobeille, Robert > Sent: 2013年11月4日 23:30 > To: Ma, Dong (Vincent, Open Source Program Office) > Cc: Michael Silberman; [email protected] > Subject: Re: [FOSSology] Post Upgrade to 2.3.0 Problems > > Except in your sql you are only deleting NULL pfile_pk’s and in Michael’s > case the copyright records have pfile_fk’s but the pfile records are gone. > Bob > > On Nov 1, 2013, at 11:05 PM, Ma, Dong (Vincent, Open Source Program Office) > <[email protected]> wrote: > > > You can find core-schema.dat file at > /usr/share/fossology/www/ui/core-schema.dat. > > Also I thought the proposal sql Bob provided cannot delete the bad records in > copyright table, you can try this: > 1. create view copyright_pfile as select ct_pk,pfile_fk,pfile_pk from > copyright left outer join pfile on pfile_fk=pfile_pk; > 2. delete from copyright where ct_pk in (select ct_pk from > copyright_pfile where pfile_pk IS NULL); > 3. drop view copyright_pfile; > > Thanks, > Vincent > > From: [email protected] > [mailto:[email protected]] On Behalf Of Gobeille, Robert > Sent: 2013年11月2日 0:19 > To: Michael Silberman > Cc: [email protected] > Subject: Re: [FOSSology] Post Upgrade to 2.3.0 Problems > > Hi Michael, > The DB upgrade is not necessary. PostgreSQL 8.4.12 should be fine. > In general, you can’t upgrade fossology and then restore an old copy of the > db as the new code sometimes requires a database schema change. > > I need to reread your original email to see what I missed. However, since > there are people banging on your wall, there is one thing you could try to > get you going. The problem seems to be that when fo_postinstall is updating > your database schema, it is trying to create a constraint: > > ALTER TABLE "copyright" ADD CONSTRAINT "copyright_pfile_fk_fkey" FOREIGN KEY > ("pfile_fk") REFERENCES "pfile" ("pfile_pk") ON UPDATE NO ACTION ON DELETE > CASCADE; > > Which is failing because you have a referential integrity violation in your > data. So if you go into the file core-schema.dat, look for the line: > > $Schema["CONSTRAINT"]["copyright_pfile_fk_fkey"] = "ALTER TABLE > \"copyright\" ADD CONSTRAINT \"copyright_pfile_fk_fkey\" FOREIGN KEY > (\"pfile_fk\") REFERENCES \"pfile\" (\"pfile_pk\") ON UPDATE NO ACTION ON > DELETE CASCADE;”; > > and delete it. Then rerun fo_postinstall. > > Unfortunately, I only install from source so I don’t know where the > core-schema.dat file is on your system. In the source tree it is in > src/www/ui/core-schema.dat, if that helps. > > Bob Gobeille > > > On Nov 1, 2013, at 9:54 AM, Michael Silberman <[email protected]> > wrote: > > > > Hi Bob, > I ran the queries as suggested but threw in a select to see what was in > the view: > # psql -d fossology -U fossy -h localhost > Password for user fossy: > psql (8.4.12) > Type "help" for help. > > fossology=> create view copyright_pfile as select ct_pk,pfile_fk,pfile_pk > from copyright right outer join pfile on pfile_fk=pfile_pk; > CREATE VIEW > fossology=> select * from copyright where ct_pk in (select ct_pk from > copyright_pfile where pfile_fk != pfile_pk); > ct_pk | agent_fk | pfile_fk | content | hash | type | copy_startbyte | > copy_endbyte > -------+----------+----------+---------+------+------+----------------+-------------- > (0 rows) > > fossology=> delete from copyright where ct_pk in (select ct_pk from > copyright_pfile where pfile_fk != pfile_pk); > DELETE 0 > fossology=> drop view copyright_pfile; > DROP VIEW > fossology=> > Nothing was picked up by the query. > Is there something in the query that needs to change? Is there a log file I > can send you to review that may shed more light on why my upgrade failed? > > I see Paul’s email suggests I should not be in this situation at all, but I > am, with frustrated users banging at my cubical wall L, alas, no doors to > assuage their interruptions, but I digress. If possible, could I get a copy > of the SQL commands run in the upgrades between 2.0.0 and 2.3.0 to check the > DB schema was correctly upgraded? > Also, Paul mentioned the DB upgrade; is the upgrade mandatory or suggested? I > currently have PostgreSQL 8.4.12. > > Could someone test whether making a backup of a DB and the repository folder, > deleting the instance of the FOSSology, i.e., “run a rpm/yum delete”, > installing the latest version of FOSSology, restoring the DB and replacing > the repository folder would work? > Regards, > Michael > > From: Gobeille, Robert [mailto:[email protected]] > Sent: Thursday, October 31, 2013 11:29 PM > To: Michael Silberman > Cc: Ma, Dong (Vincent, Open Source Program Office); [email protected] > Subject: Re: [FOSSology] Post Upgrade to 2.3.0 Problems > > I’m not sure why jumping from 2.0.0 to 2.3.0 would fail. I think Paul > Guttmann even did this recently. > Michael, this problem is because you have some inconsistent data. You have a > copyright record that points to a “pfile” record and that pfile record does > not exist. What libschema.php is trying to do is add a database constraint to > prevent this from happening in the future. Unfortunately, since it has > already happened to you, you need a fix. BTW, this situation may have > occurred in the past due to an error deleting an upload. The current delete > agent is much more robust. > > One solution is to delete all your copyright records. However we can be a > little more surgical and just delete the bad ones. For example, in psql (or > wherever you wish): > 1) create view copyright_pfile as select ct_pk,pfile_fk,pfile_pk from > copyright right outer join pfile on pfile_fk=pfile_pk; > 2) delete from copyright where ct_pk in (select ct_pk from copyright_pfile > where pfile_fk != pfile_pk); > 3) drop view copyright_pfile; > > Yes, you can do all the above in a single delete statement if you wish. I > just broke it up because I think it is easier to understand. > > Bob Gobeille > > On Oct 31, 2013, at 10:45 PM, Michael Silberman wrote: > > > > > Unfortunately the yum install worked at least to change the revision, but not > the DB. The script shows errors: > PHP Warning: pg_query(): Query failed: ERROR: insert or update on table > "copyright" violates foreign key constraint "copyright_pfile_fk_fkey" > DETAIL: Key (pfile_fk)=(55328) is not present in table "pfile". in > /usr/share/fossology/lib/php/libschema.php on line 456 > <hr>File: /usr/share/fossology/lib/php/libschema.php, Line number: > 457<br>ERROR: insert or update on table "copyright" violates foreign key > constraint "copyright_pfile_fk_fkey" > DETAIL: Key (pfile_fk)=(55328) is not present in table "pfile".<br> ALTER > TABLE "copyright" ADD CONSTRAINT "copyright_pfile_fk_fkey" FOREIGN KEY > ("pfile_fk") REFERENCES "pfile" ("pfile_pk") ON UPDATE NO ACTION ON DELETE > CASCADE;<pre>#0 debugbacktrace() called at > [/usr/share/fossology/lib/php/common-db.php:158] > #1 DBCheckResult(, ALTER TABLE "copyright" ADD CONSTRAINT > "copyright_pfile_fk_fkey" FOREIGN KEY ("pfile_fk") REFERENCES "pfile" > ("pfile_pk") ON UPDATE NO ACTION ON DELETE CASCADE;, > /usr/share/fossology/lib/php/libschema.php, 457) called at > [/usr/share/fossology/lib/php/libschema.php:457] > #2 ApplySchema(/usr/share/fossology/www/ui/core-schema.dat, , fossology) > called at [/usr/lib/fossology/fossinit.php:102] > Is there anything I can do to fix it? > From: Ma, Dong (Vincent, Open Source Program Office) [mailto:[email protected]] > Sent: Thursday, October 31, 2013 8:00 PM > To: Michael Silberman; [email protected] > Subject: RE: Post Upgrade to 2.3.0 Problems > > Hi, > > We suggest upgrade packages from previous latest release packages, I am sorry > we didn’t test from 2.0.0 upgraded to 2.3.0 (over 2 releases: 2.1.0 and > 2.2.0) . > > The error message show your DB upgrade not successful, > 1. I suggest you first check if the 2.3.0 packages install correctly, > use ‘rpm –qa|grep fossology’ to check fossology version > 2. If the 2.3.0 packages already installed, run the post install script > again, ‘/usr/lib/fossology/fo-postinstall ‘, check if any errors > > Hope the post install script can fix it. If you catch any errors, please > forward to us. > > Thanks, > Vincent > > From: [email protected] > [mailto:[email protected]] On Behalf Of Michael Silberman > Sent: 2013年11月1日 6:51 > To: [email protected] > Subject: [FOSSology] Post Upgrade to 2.3.0 Problems > > Hello, > After upgrading from 2.0.0 to 2.3.0 on RHEL6.3 using “yum upgrade > fossology*” per the instructions I’m seeing some errors that would indicate > that something went wrong with the DB upgrade portion. Unfortunately I didn’t > think to redirect the upgrade output to a log file. > <image001.jpg> > Is there any way to resolve this quickly? > Regards, > Michael Silberman > Sr. Systems Administrator > [email protected] > 1(408)890-3521 > <image002.jpg> > > > This e-mail, including attachments, may include confidential > and/or proprietary information, and may be used only by the > person or entity to which it is addressed. > If the reader of this e-mail is not the intended recipient or his or > her authorized agent, the reader is hereby notified that any > dissemination, distribution or copying of this e-mail is prohibited. > If you have received this e-mail in error, please notify the sender > by replying to this message and delete this e-mail immediately > > > This e-mail, including attachments, may include confidential > and/or proprietary information, and may be used only by the > person or entity to which it is addressed. > If the reader of this e-mail is not the intended recipient or his or > her authorized agent, the reader is hereby notified that any > dissemination, distribution or copying of this e-mail is prohibited. > If you have received this e-mail in error, please notify the sender > by replying to this message and delete this e-mail immediately > > _______________________________________________ > fossology mailing list > [email protected] > http://lists.fossology.org/mailman/listinfo/fossology > > This e-mail, including attachments, may include confidential and/or > proprietary information, and may be used only by the person or entity to > which it is addressed. If the reader of this e-mail is not the intended > recipient or his or her authorized agent, the reader is hereby notified that > any dissemination, distribution or copying of this e-mail is prohibited. If > you have received this e-mail in error, please notify the sender by replying > to this message and delete this e-mail immediately > >
_______________________________________________ fossology mailing list [email protected] http://lists.fossology.org/mailman/listinfo/fossology
