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
