Forum OpenACS Q&A: Help! Fixing Databases

Collapse
1: Help! Fixing Databases
Posted by Sean Harrison on 07/03/03 12:32 AM

I've been having some database troubles.

Originally, when I first installed CMS, I would try to insert an item and would get the following error: 

Request Error

Query did not return any rows.
    while executing
"db_1row get_storage_type {select 
        storage_type, item_id 
        from 
        cr_items 
        where 
        item_id = (select 
           ..."
    (procedure "upload_content" line 15)

.....

So I uninstalled CMS, with difficulty. Now, trying to reinstall it results in DB errors as well

Then, my photo album stopped showing the content of the pictures that I loaded into it, including new ones that I added after the problem started. I unmounted the photo-album and tried to delete, then got this error: 

Database operation "0or1row" failed (exception NSDB, 
"Query was not a statement returning rows.")

ERROR:  pa_pack_fldr_map_pack_id_fk referential integrity
violation - key in apm_packages still referenced from 
pa_package_root_folder_map

SQL: 
	select apm_package__delete('343');

Today, I tried to delete an umounted instance of Edit-This-Page and found the following error: 

Database operation "0or1row" failed (exception NSDB, 
"Query was not a statement returning rows.")

ERROR:  cr_flder_pkg_id_fk referential integrity violation 
- key in apm_packages still referenced from cr_folders

SQL: 
	select apm_package__delete('1183');

This looks to me to be all a similar type of error, but I don't know enough about DB programming to fix it (or, I'm worried that I'll mess things up worse!).

My basic question, I think is this: If an application installation gets munged, what is the method for cleaning it out of the database without hurting anything else? The goal is to be able to reinstall it later.

I think what I need is a pointer in the right direction, along with a short explanation of the principles involved. If someone could give that, I'd be grateful.

Thanks,

-Shawn

Collapse
2: Re: Help! Fixing Databases (response to 1)
Posted by Jade Rubick on 07/03/03 06:58 PM
Shawn

You've been having a run of bad luck, haven't you? Well, before anything else, I'd say that I wouldn't use CMS yet. It has a terrible UI, although it does have a very good data model, at least from what I've heard. There are some other efforts going on to build a better user experience on top of much of the CMS's core.

Have you done anything that you want to keep in the database? If not, the easiest thing to do is to drop the database, and restart it, with a new installation.

I think there is a document somewhere which shows the list of supported applications.

But to answer your question: to clean it out,

If the package is written well, you can clean it out through the admin UI

If not, then you may have to do portions of it manually.

What I'd suggest is getting on IRC and asking as you encounter your problems. You'd probably get better help there, and in real time. Info on that is at https://openacs.org/irc

We know this is very annoying, but things are improving very quickly. There are a lot of people working very hard right now to make this whole experience seemless.

Collapse
3: Re: Help! Fixing Databases (response to 2)
Posted by Sean Harrison on 07/04/03 03:59 PM
Hi, Jade,

Thanks for your note. I'm chocking it all up to experience. I don't mind most of the problems, and I'm glad to know that a lot of work is being done to improve some of these issues! I hope I will be able to pitch in some, myself.

Thanks for the tip about IRC -- I'll go there next time. I tried the admin UI, then dropping the tables in psql directly, then -- oh brilliant idea -- "reindex database," which I know now never to do.

In the end I built a new OpenACS and put that up on port 80, and put the old one (from a nightly backup) on 8000 for tinkering. There are a few people who came and registered on my site last weekend, so I'll unfortunately lose their registrations, but that's all.

Unless: Is there a way to transfer registered users from one instance of OpenACS to another? That would seem to be a very helpful admin script to have...

Regards,
Shawn