Forum OpenACS Q&A: dml and other sql errors

Posted by Justin A on 03/14/00 02:05 PM

I also got the dml errors when running acs. Ill try the suggestion
for reloading the data-model when I get home but i'm not sure it will
fix it. I've wrestled with the 2 pg drivers with varying success. It
seems they both have problems whether linked to libcrypt or not.
Because of that I get something like fe_sendauth: no password
supplied. With the other driver I was getting ns_db: command not
enabled. I finally got it to load but I dont remember which driver.
The database was coming up in the pool all right but acs was not
working right at all.

I read through a .tcl fle and managed to write a simple "select * from
links" to work on an existing db that I added to the pool. That
failed too with an error like "XXXX error select was not a query
returning rows". I dont remember what XXXX was except that it was
four letters in caps. I changed it to "select * from foo" (a
non-existant table) and it said table not found so something is
working.

I'm beginning to think this is a problem with my version of postgres.
I dont remember which I have only that it's the rpm from mandrake 6.

Also I can not get an index.tcl file to load automatically. It is
listed in nsd.ini and in paramaters/.

I know I am so close to getting this thing working. Any help will be
appreciated.

2: Response to dml and other sql errors (response to 1)

Posted by Don Baccus on 03/14/00 06:02 PM

It would help a lot if you'd post your actual log files - the pertinent parts, not the entire thing. Set Verbose=On in the pool you're using.

For simplicity's sake you might try creating the database with no password protection, in which case the datapath contains no password.

The new driver works, there's no reason to try the old one, that's a waste of time when trying to track down problems. The site you are reading this note on is running the new driver.

The version from mandrake 6 SHOULD be a 6.5.* version so you should be able to get things up and running. We've only tested the entire ACS release under 6.5.3, though, and won't make any guarantees for any earlier 6.5 release. 6.4 or older? FORGET IT. Won't work. Look at PG_VERSION in whatever path the RPM stores the database in, it will tell you if it is a 6.5.* version or 6.4.* or older.

As far as getting index.tcl to load automatically, are you sure you have something like this:

[ns/server/acspg]
PageRoot=/home/acs/acs3-pg/www
DirectoryFile=index.tcl

in your .ini file?

3: Response to dml and other sql errors (response to 1)

Posted by Justin A on 03/14/00 06:44 PM

Not home yet to check my system but I found an old mirror with mandrake 6.0 rpms: (ftp://ftp.nectec.or.th/pub/linux.tle/6.0/Mandrake/RPMS/)
postgresql-6.4.2-8mdk.i586.rpm 2480 Kb
postgresql-clients-6.4.2-8mdk.i58... 349 Kb
postgresql-devel-6.4.2-8mdk.i586.rpm 209 Kb
Ok now. Is there any particular version to get/stay away from when I upgrade?

4: Response to dml and other sql errors (response to 1)

Posted by Don Baccus on 03/14/00 07:52 PM

The safest approach is to upgrade to 6.5.3.

5: Response to dml and other sql errors (response to 1)

Posted by Lamar Owen on 03/15/00 01:31 AM

Mandrake 6.0 shipped 6.4.2, Mandrake 6.1 shipped, IIRC, 6.4.2, and Mandrake 7 shipped 6.5.2, I think.

RedHat 6.0 shipped 6.4.2, and RedHat 6.1 shipped 6.5.2. RedHat 6.2 will ship 6.5.3.

RPM's for RedHat or Mandrake 6 are available at ftp.postgresql.org in /pub/bindist/RPM

--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

6: Response to dml and other sql errors (response to 1)

Posted by Justin A on 03/15/00 03:30 AM

I upgraded postgres to 6.5.3 with the same results
This is the error i get:

[14/Mar/2000:21:30:34][4964.5124][-conn1-] Error: Ns_PgExec: result status: 7
[14/Mar/2000:21:30:34][4964.5124][-conn1-] 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.........

The gethandle works ok and i have datasource, dbtype driver, poolname and user printed to a table.

It is not until set sql_query "select name, url, descr from links order by name" is executed that i get errors.

7: Response to dml and other sql errors (response to 1)

Posted by Roberto Mello on 03/15/00 03:45 AM

In my experience, the best is to start from scratch and making sure you have these steps:

Make sure you compiled the enhanced PostgreSQL driver available here at the acspg site. Without that, nothing will work.

Make sure you have the right permissions and that the datamodel is being loaded by the user that AOLserver will be connecting to PostgreSQL as.

Follow the steps I described in http://acspg.benadida.com/bboard/q-and-a-fetch-msg.tcl?msg_id=00000x&topic_id=5&topic=ACS%2fpg%20Installation%20and%20Configuration>.

Since you are using the RPM version of PostgreSQL, make sure you are RTFM, and you are uncommenting (and commenting) the right lines in postgres.sql (that includes uncommenting postgres65.sql)

8: Response to dml and other sql errors (response to 1)

Posted by Justin A on 03/15/00 04:08 AM

I removed the db, fixed the top of postgres.sql.

No change.

I logged in as nsadmin, ran psql acs. "select * from users" works.
psql play
"select * from links" works.

I've noticed if i leave nsd running it spews out errors from
send_scheduled_spam_messages

9: Response to dml and other sql errors (response to 1)

Posted by Roberto Mello on 03/15/00 04:27 AM

So you are analyzing PG's output of when loading the datamodel and it's being loaded fine right ? (I always load the datamodel saving the output to a file to make sure psql -f load-data-model.sql dbname 2> datamodel.txt)

If the data model is being loaded fine, then your problem is either on the driver or on the AOLserver configurations. Did you compile the driver ? Any errors ? What version of AOLserver are you running ? Your pools are setup right ?

10: Response to dml and other sql errors (response to 1)

Posted by Justin A on 03/15/00 04:55 AM

I edited the .ini and took out the acs auxconfig line and library line.
It loaded up without any of the extra libraries etc.

I tested it on my simple tcl script. Same error.

I recompiled the acs-pg driver after I upgraded postgres(6.5.3)
I have as3b61.

It has to be something with the config.(that being the only thing that I did myself) I did start with linux.usu.edu/nsd.txt though.

I'll play with this more tomorrow, sleep for now.

11: Response to dml and other sql errors (response to 1)

Posted by Justin A on 03/16/00 04:06 AM

I tracked down the error I'm getting to nsd/dbdrv.c
.

Ns_Set *Ns_DbSelect(Ns_DbHandle *handle, char *sql);
The code execs the sql command on the db handle. I know the sql query is valid and should work. The nsd gets the db handle all right and even releases it if I let it sit for a few minutes.

As far as I can the problem starts after the sql query is executed and when the data is being recieved. Tomorrow I will see how to turn logging on for postgres so I can see what it looks like from that end.

12: Response to dml and other sql errors (response to 1)

Posted by Lamar Owen on 03/16/00 06:07 AM

Ah. It just hit me -- Mandrake 6.0 uses pgcc -- which is known to cause problems with AOLserver (I had a _bear_ of a time trying to get PostgreSQL RPM's to build on Mandrake 6.0 -- which is why I reverted to "straight" RedHat 6). You need to get someone who has built AOLserver 3.0b61 on _RedHat_ 6.x to let you download a binary tree -- so, tell you what I'm going to do. Go to http://www.ramifordistat.net/aolserver3, and download as3b61-rh61.tar.gz and postgres/postgres.so.rh61, and try them.

Caution: as3b61-rh61.tar.gz is 15MB. It is a simple tarball of a built aolserver 3.0b61 tree. Or, if you don't want to download that beast, get as3b61-rh61-root.tar.gz, which is a tarball of the root tree under as3b61, and is only 1.67MB. Your choice.

13: Response to dml and other sql errors (response to 1)

Posted by Justin A on 03/16/00 01:49 PM

Ahhh. I was going crazy here. I wonder why I've never had a problem compiling anything else before.

I will download that as soon as I get home.
Thank you all so very much.

14: Response to dml and other sql errors (response to 1)

Posted by Justin A on 03/17/00 04:22 AM

Well I got the binary nsd and postgres.so but I'm not sure it made any difference. I was able to login as system finally but many things fail with that same error. I tried the simple select query and that still fails as well.

It seams like sometimes a query will work but other times it will fail. This has to be a configuration error. -Just got the links.tcl to work it _was_ a conf error. The links db now works but acs is still giving errors. Can someone post the section for main, subquery and log, I think mine are conflicting.

15: Response to dml and other sql errors (response to 1)

Posted by Roberto Mello on 03/17/00 04:28 AM

Look at the thread "ns_db: conf error" or something like that in this forum. Don posted his working Pools configuration there. I also posted my working nsd.ini at http://linux.usu.edu/nsd.txt

16: Response to dml and other sql errors (response to 1)

Posted by Justin A on 03/17/00 04:48 AM

Yea.. I started with your nsd.txt... I think the data model is just corrupted... This is so close to working now.

I thought I just had it, the line Pools=* would include play which is not an acs db, commenting out play did not fix it though.

The ini is at http://ductape.net/~hax0r/nsd.txt maybee someone will see something I'm not.

17: Response to dml and other sql errors (response to 1)

Posted by Roberto Mello on 03/17/00 05:05 AM

I don't know if all that I'll point out here is the absolutely best way to do this, just what I think should help ok ?

The section [ns/db/drivers] should come _before_ the ns/db/driver/postgres (I think)
Are you running the RPM PostgreSQL ? If so, you probably need DataSource=localhost:5432:acs
Remove the play pool. After you get everything working you can play a bit :)
No need for StackSize=xxx in ns/parameters. Oracle-only thing
I am assuming your ACS/pg files are in the right place (/home/nsadmin/servers/server1/*)
Did you rename ad.ini to server1.ini in /home/nsadmin/servers/server1/parameters and configured it ?
In DirectoryFile=index.tcl,..., remove the spaces between them.
Remove the ns/setup , it does not exist in AS 3

18: Response to dml and other sql errors (response to 1)

Posted by Lamar Owen on 03/21/00 10:48 PM

Which glibc is Mandrake 6.? rpm -qa|grep glibc and check versions -- if not glibc 2.1.2 or greater, this could be causing your problem.