Forum OpenACS Q&A: Loading the data model incorrectly

Posted by Angel Hernandez on 03/09/00 11:06 AM

After finishing the ACS/PG install I got a dml error when trying to add myself as a user.

Now from the servers admin pages I can select and insert, so the server seems to be talking to the db without a problem. I think the dml error is a result of me loading the data model incorrectly. I'm using the RPM version of Postgres, and I forgot to uncomment the postgres.sql file. That's what I get for trying to hurry through an install at 2 am.

I also noticed that the default system and anonymous users are not present. Looks like all of the data that was supposed to insert when the data model loaded, didn't quite make it.

So if this is the cause of my grief, the question is... how do I recover from forgetting to uncomment postgres.sql?

Thanks

2: Response to Loading the data model incorrectly (response to 1)

Posted by Chari Daignault on 03/09/00 03:12 PM

I am having the exact same problem as Angel... do we need to re-populate the database model after correcting any problem we can find in the postgres.sql file? Which, BTW, I DID edit correctly... :)

I've got to do some digging now to find the source of this one.

Chari

3: Response to Loading the data model incorrectly (response to 1)

Posted by Don Baccus on 03/09/00 04:07 PM

Can you post the errors you got from Postgres here, please? Thanks...

4: Response to Loading the data model incorrectly (response to 1)

Posted by Chari Daignault on 03/09/00 04:22 PM

Invalid return code from filter proc: Database opertaion "dml" failed (must be filter_ok, filter_return, or filter_break)

This was from the server.log file.

Chari

5: Response to Loading the data model incorrectly (response to 1)

Posted by Roberto Mello on 03/09/00 05:02 PM

I've had these erros before when I started using ACS/pg, here's a quick recipe:

Destroy the db and recreated it. Remember to be logged in as the AOLserver user when doing this. (destroydb yourdb and createdb yourdb)
Go to the www/doc/sql directory, edit the postgres.sql to point to your pgsql libraries (it comes set up for the compiled version of postgres). If you don't have this, the datamodel will die.
Edit the file load-data-model.sql and uncomment the line "--i postgres65.sql" if you have it. It should not be commented, but some people may have downloaded ACS/pg like this. To uncomment, remove the "--" in front of the line.
Make sure the user your loading the datamodel as is a postgres superuser (otherwise it won't have authority to create some stuff needed for ACS/pg.
Reload the data model. I usually do a "psql -f load-data-model.sql mydb 2> datamodel.txt" , the "2> datamodel.txt" dumps the output to a file that can I edit and see if the datamodel was loaded right, or if I missed some step. I will add this to the documentation.
If you are using AOLserver 3bx, MAKE SURE you downloaded and compiled the enhanced driver available at acspg.benadida.com. You need the postgresql-devel package and gcc, glibc, glibc-devel to compile it.

Let us know how it goes.

Roberto Mello

6: Response to Loading the data model incorrectly (response to 1)

Posted by Chari Daignault on 03/09/00 06:46 PM

That fixed most of the problems... I had to recompile the postgres driver, and then reloaded the data model into the database. However, the index.tcl pages don't come up automatically when I go to the pageroot or the root of any directory. I've got .tcl, .html, .htm, and .shtml files listed as viable starting pages...

And, I couldn't login and create an account for myself still. I could, however, login as system and create myself as a user that way.

I'm at an Apache conference right now, so I don't have access to my intranet or the log files to tell you exactly what errors I'm getting.

Thanks,
Chari

7: Response to Loading the data model incorrectly (response to 1)

Posted by Angel Hernandez on 03/09/00 11:07 PM

Thanks Roberto! Everything is working great now. I'm able to add users, messages to the bboard, news, etc., all without a problem.

So far I really like Postgres and ACS/PG. I already have ACS running on Oracle, but since I'm going to be installing the ACS for a non-profit org in a few days, I thought I better give ACS/PG a spin on my system first. It's great!

On a wimpy Linux box, Postgres feels really fast and lightweight compared to Oracle. I also love how simple and straightforward the commands are (eg: "createdb" "destroydb")

I've run into a few errors though. Out of five, I only found one as a reported bug (viewing birthdays), so I'm not sure if the rest are bugs as well, or just something that I need to change. Here's where I ran into an error:

Uploading Portrait - could choose the file, but failed after submitting.

Calendar - No states in table. Guessing I just need to load another .sql file, but it seems like it should have been done when feeding the data model.

Who's online? - error.

bboard search - I'm sure the problem is probably in the my_server.ini file; then again, I used the defaults for this module, so it doesn't seem like I should get an error.

Are these already known errors, or are they unique to my setup? If these aren't known bugs, I'll post back with the error messages.

I wish I knew Postgres better, and exactly how it's SQL differs from Oracle's...would make it easier for me to troubleshoot.

Anyhow, thanks for the help, and thanks to the team for a great job porting the ACS to Postgres!

8: Response to Loading the data model incorrectly (response to 1)

Posted by Kevin Davis on 03/09/00 11:47 PM

I also have run into a few errors that seem like bugs.

Submitting user pic: I choose the file but the submit fails. The server.log says it is because of an ns_ora call. DOH!

User login: If I turn on the intranet module, then users can not login. They get the password page, but it chokes on the pvt/home.tcl page. With the intranet module off, it works fine.

9: Response to Loading the data model incorrectly (response to 1)

Posted by Don Baccus on 03/10/00 12:58 AM

portrait upload ... we'll have that in our next beta, it's working but I didn't get to it in time for the first beta. Requires the latest driver to work (plus the new beta's script files).
no states loaded ... you need to go to /www/install and load the geo tables, most likely. The data model fakes those tables so you'll need to drop them first. There are three tables, it will be clear which to drop when you take a look at the script in the install directory.
bboard search ... not yet implemented. Postgres isn't any real help here. I think Ben will probably implement something for the short term. I have a few tricks up my virtual sleeve for the future.
intranet turning off logins ... intranet wasn't ported in the first beta, it just got done. I think we'd gotten to part of the data model so it probably is getting just far enough along when you enable it to mess up the /pvt/home.tcl page when it tries to make use of it.
Who's online? That's a new one to me, give the sdm a spin and submit a bug report!

10: Response to Loading the data model incorrectly (response to 1)

Posted by Lachlan Myers on 03/12/00 11:48 AM

Thanks Ben for ACS/pg. Thanks Roberto and Don for all above answers - I too now have ACS up and running locally. Painless, really.

If anyone's interested, here's the platform;

Thinkpad 760ED Pentium 133. Mandrake Linux 6.0 on a 1.4meg partition. Postgres 6.5.3 compiled from tarball (not RPM).

Haven't done much else other than a quick check of the modules.

I'll investigate these from the server logs, so this is not (yet) a bug report/ call for help. Some may be due not having any fully built-up users. Problems with ACS sourcing (ie error page delivered)
- bookmarks
- file storage
- intranet (as you already know)
- events
- stolen eqipment
- stolen equipment

11: Response to Loading the data model incorrectly (response to 1)

Posted by Ben Adida on 03/13/00 05:15 AM

Lachlan, you shouldn't be thanking just me for the ACS/pg. Don, Roberto, and the rest of the ACS/pg team did a tremendous amount of work, too. The ACS/pg would never have come about without them.

12: Response to Loading the data model incorrectly (response to 1)

Posted by Daniel Katz on 03/17/00 06:08 PM

I seem to be having a related problem. Every time I start up the AOLserver, I see the following in the logs:

[17/Mar/2000:11:35:19][12535.1026][-sched-] Error: Ns_PgExec: result status: 7 message: ERROR: ad_partner: Table does not exist.

[17/Mar/2000:11:35:19][12535.1026][-sched-] Error: nsd.tcl: Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.")
Database operation "select" failed (exception NSDB, "Query was not a statement returning rows.")
while executing
"ns_db select $db "select distinct partner_cookie from ad_partner""
invoked from within
"set sub_selection [ns_db select $db "select distinct partner_cookie from ad_partner"]..."
invoked from within
"if { [catch {set db [ns_db gethandle subquery]} err_msg] } {
ns_log Notice "ad-partner: Can't get db handle. Using list_of_cookies to register cookie ..."
(procedure "ad_partner_initialize" line 7)
invoked from within
"ad_partner_initialize"

which would seem to imply that the data model has been incorrectly loaded. Note that I

1) have edited www/doc/sql/postgres.sql correctly for the rpm-based install of postgresql (v. 6.5.3) which I am using,

2) have downloaded and installed the latest driver from the ACS/PG site,

3) have destroyed the db and recreated it,

4) have redirected the stderr output of the load-data-model.sql load to a file to examine for errors and have found none,

5) have examined the file load-data-model.sql for the string "postgres65.sql" and have not found it, commented out or otherwise, although I did find the string "i postgres.sql" (which was uncommented), and

6) have made sure that I'm loading the data model as a postgresql superuser.

The system works to extent that I can add new users, become system and do certain administration functions, etc., but fails when (as a user) I try to access the Homepage Maintenance or Neighborhood functions, I end up with the message "Server Error : The server encountered a configuration or operating system error while attempting to satisfy your request" and more server log entries about missing a missing users_homepages tables and failing operations due to queries not returning rows. Again, since I can write certain information to the database (e.g., email addresses and names of users), I would guess that the data model is the problem, rather than the PostgreSQL itself.

Any suggestions?

Dan Katz

13: Response to Loading the data model incorrectly (response to 1)

Posted by Don Baccus on 03/17/00 10:15 PM

Homepage and partnering weren't available in the beta1 ACS-pg release, if that's what you're using. Homepage at least will be in beta2.

The modules that work in beta1 are listed in the release notes.

14: Response to Loading the data model incorrectly (response to 1)

Posted by Lachlan Myers on 03/26/00 01:22 PM

Loading the data model incompletely was the source of a number of those dml errors, I found. In most cases I just loaded the relevant *.sql files into Postgres and steamed ahead.
I loaded
- homepages.sql
- postgres-pgtcl.sql
- partner.sql
manually.

There may be more. At present, I am assuming its a local problem - slow machine/ loose nut on keyboard, but will find some time to look more closely at the load-data-models.sql.