Error in .lg file

[timhowarduk]

Hi

Has anyone seen anything like this before please. I'm posting this here because it looks to me as if the SQLSRV is crashing horribly, either caused by ODBC access or a java trigger having a problem.

Extract from database lg file:

04:06:09 SQLSRV2 2: SQL Server 9.1E.04 started, configuration: "petra.virtualconfig" 
04:06:09 SQLSRV2 2: "/usr/local/petra/db22/petra" started on port 1026, pid 18143 (0x000046df).
04:06:09 SQLSRV2 2: Thread stack size: 1024000 (bytes).
04:06:09 SQLSRV2 2: DLC from ENVIRONMENT VARIABLE is: /usr/local/progress 
04:06:09 SQLSRV2 2: WRKDIR from ENVIRONMENT VARIABLE is: /tmp/ 
04:06:09 SQLSRV2 2: JDKHOME from ENVIRONMENT VARIABLE is: /usr/java/jdk1.3.1_11 
04:06:09 SQLSRV2 2: JREHOME from ENVIRONMENT VARIABLE is: /usr/java/jdk1.3.1_11/jre 
04:06:09 SQLSRV2 2: CLASSPATH from ENVIRONMENT VARIABLE is: /usr/java/jdk1.3.1_11/lib/tools.jar:/usr/local/progress/java/progress.jar:/usr/local/progress/java/messages.jar:/usr/local/petra/java22/JPetraSP.jar:/usr/local/petra/java22/JPetraConstraintTriggers.jar:: 
04:06:09 SQLSRV2 2: PROSQL_LOCKWAIT_TIMEOUT value is: 5 seconds
04:06:10 SQLSRV2 2: Login usernum 84, remote SQL client. (8873)
04:06:12 SQLSRV2 2: Usr 84 set name to petraserver. (7129)
04:06:13 Usr    21: Logout by  on batch. (453)
09:31:15 SRV     3: Started on port 1025 using tcp, pid 3171. (5646)
09:31:16 SRV     3: Login usernum 83, userid jackiep, on glc-36. (742)
09:31:21 SRV     3: Userid is now JACKIEP. (708)
09:31:21 SRV     3: Previous message sent on behalf of user 83. (5512)
09:31:40 SQLSRV2 2: Login usernum 82, remote SQL client. (8873)
09:31:40 SQLSRV2 2: Usr 82 set name to petraserver. (7129)
09:32:17 SRV     4: Started on port 1027 using tcp, pid 3981. (5646)
09:32:18 SRV     4: Login usernum 81, userid helenh, on SVC-113. (742)
09:32:25 SRV     4: Userid is now HELENH. (708)
09:32:25 SRV     4: Previous message sent on behalf of user 81. (5512)
09:32:29 SQLSRV2 2: Login usernum 80, remote SQL client. (8873)
09:32:29 SQLSRV2 2: Usr 80 set name to petraserver. (7129)
09:32:33 SRV     5: Started on port 1028 using tcp, pid 4245. (5646)
09:32:34 SRV     5: Login usernum 79, userid alant, on FDL-161. (742)
09:32:54 SRV     5: Userid is now ALANT. (708)
09:32:54 SRV     5: Previous message sent on behalf of user 79. (5512)
09:33:00 SQLSRV2 6: SQL Server 9.1E.04 started, configuration: "petra.virtualconfig" 
09:33:00 SQLSRV2 6: "/usr/local/petra/db22/petra" started on port 1029, pid 4625 (0x00001211).
09:33:00 SQLSRV2 6: Thread stack size: 1024000 (bytes).
09:33:00 SQLSRV2 6: DLC from ENVIRONMENT VARIABLE is: /usr/local/progress 
09:33:00 SQLSRV2 6: WRKDIR from ENVIRONMENT VARIABLE is: /tmp/ 
09:33:00 SQLSRV2 6: JDKHOME from ENVIRONMENT VARIABLE is: /usr/java/jdk1.3.1_11 
09:33:00 SQLSRV2 6: JREHOME from ENVIRONMENT VARIABLE is: /usr/java/jdk1.3.1_11/jre 
09:33:00 SQLSRV2 6: CLASSPATH from ENVIRONMENT VARIABLE is: /usr/java/jdk1.3.1_11/lib/tools.jar:/usr/local/progress/java/progress.jar:/usr/local/progress/java/messages.jar:/usr/local/petra/java22/JPetraSP.jar:/usr/local/petra/java22/JPetraConstraintTriggers.jar:: 
09:33:00 SQLSRV2 6: PROSQL_LOCKWAIT_TIMEOUT value is: 5 seconds
09:33:01 SQLSRV2 6: Login usernum 78, remote SQL client. (8873)
09:33:01 SQLSRV2 6: Usr 78 set name to petraserver. (7129)

Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" Exception in thread "Thread-0" java.lang.StackOverflowError
09:43:20 SQLSRV2 2: SYSTEM ERROR: Memory violation. (49)

09:43:53 BROKER  0: Disconnecting client 80 of dead server 2. (2526)
09:43:53 BROKER  0: Disconnecting client 82 of dead server 2. (2526)
09:43:53 BROKER  0: Begin transaction backout. (2252)
09:43:53 BROKER  0: Transaction backout completed. (2253)

I'm guessing it's either trigger recursion or an odbc query pushing the database too hard.

Does anyone know, is there a way of getting a java stack trace to give a better clue exactly what is causing this?

The same database and application is in use in about 30 countries worldwide, and I have had this happen in 3 locations. Two of the sites use 9.1D and one is on 9.1E. (I realise the instant response of some will be to upgrade to 10 or even 11, however the logistics of rolling this out worldwide are complicated and it can't happen soon)

Many thanks for any help of any kind

Tim

 

[rstanciu]

stop the database server.
export this OS variable.
PROSQLTRC="Y"; export PROSQLTRC
start your database again.
execute the SQL application.
You will find a verbose sql trace in a file named [PID].trc
do a find/search/locate of *.trc and take a look at.

 

[timhowarduk]

Thank you, I have got that working, and it will be a big help to know about this facility.

One more question please.
This problem is (so far) not directly reproducible, if fact I would go as far to say it is generally non-existent apart from one installation where it occurs approx. weekly

I don't suppose there is any way of controlling the verbosity of the trace is there?

(I have already tried and failed to find any mention of PROSQLTRC in the progress manuals)

Thanks again
Tim

 

[rstanciu]

You have not choice. Let the event occurs and save this file.
Analyse this trace and the database.lg file at the crash momment.
The trace can gives you the SQL query executed.
If you have the query you can grep for what code-source cause this.

* PROSQLTRC is a non-documented trick.

 

[timhowarduk]

Ok. Thank you for help.
Tim