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]<mailto:[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 ☹, 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]<mailto:[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]<mailto:[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]> [mailto:[email protected]] On Behalf Of Michael Silberman Sent: 2013年11月1日 6:51 To: [email protected]<mailto:[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]<mailto:[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]<mailto:[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
