Forum OpenACS Improvement Proposals (TIPs): Tip #121: (Approved) acs_log Logging for APM Upgrade Script Output

Request notifications


We would like to add a few "acs_log__debug()" calls to the "db_source_sql_file" procedure in order to store the error messages of upgrade scripts in the acs_logs log table.

Why? The Problem:

During upgrades with the OpenACS APM we have repeatedly found the problem that some upgrade scripts weren't executed. We estimate that this happens during about every 2nd upgrade. However, we haven't been able to consistently reproduce this problem. This is a BIG problem for us, because the people upgrading ]po[/OpenACS are not OpenACS experts.

The Solution:

In order to gather more information about the upgrade problem we need to actually log the information. acs_logs already exists and calls to "acs_log__debug()" (PlPg/SQL)
are cheap. With this information we'll be able to reproduce
what has happened.

Why not just using the log file?

Upgrade problems are reported somethimes weeks after the upgrade has happened so that the error.log files are not there anymore.

The code:

Below is a snippet of the code from db_source_sql_file in the acs-tcl/tcl/00-database-procs.tcl file wit the two extra lines added:

catch {db_string log "SELECT acs_log__debug(:file, 'db_source_sql_file: Executing file')"} err_msg
while { [gets $fp line] >= 0 } {
# Don't bother writing out lines which are purely whitespace.
if { ![string is space $line] } {
apm_callback_and_log $callback "[ad_quotehtml $line]\n"
catch {db_string log "SELECT acs_log__debug(:file, coalesce(:line, ''))"} err_msg

This extra lines would apply to both the PostgreSQL and the Oracle part of the procedure (2 x 2 lines = 4 extra lines...)


Posted by Dave Bauer on
This sounds like a great idea.

I have a couple of questions.

Looks like log_key is limited varchar(100). This is probably not long enough to hold the path to all sql files. Maybe it is, but seems like it might run out.

Also I think the db_string calls would more correctly be db_exec_plsql.

Posted by Frank Bergmann on
I've found 100 chars to be sufficient to hold the upgrade script's filename. However, yes, extending that wouldn't hurt probably, maybe varchar(400) or is more suitable and works on both Oracle and PG.

I actually haven't seen acs_log to be used much, so the impact should be small.

> db_exec_plsql

No problems with that if you think it's more correct. We used to use the function, too, but there is actually no big difference in practical terms, isn't it?


Posted by Don Baccus on
but please use db_exec_plsql for consistency with other code (it's separate just in case some DB needs it to be, or perhaps originally the oracle or PG driver needed it to be, it's easy to use it instead of db_string so let's do that since all of the rest of our code does)

Also, make the change on HEAD, not the oacs-5-4 branch, which closed for our last 5.4 release (we hope), 5.4.2 (already in beta)

Posted by Dave Bauer on
I approve