simple query causes NTDBSMGR.EXE to crash 1

[xift]

Hi, I'm Xift. I work with MrBucket and am the guy who caused the error leading to his thread "w3dbsmgr.exe pegs CPU around 100% constantly".

We are using Pervasive.SQL 8.1, version 8.10.117.17.

It seems the above error has two parts. The first part is a query that causes some of the database services to fail. The second part is running another program like PCC or PSA which causes the W3DBSMGR process to run and never finish, which then causes the processor to spike. That's what seems to happen, because the W3DBSMGR process is not present most of the time.

So my question is about this query. We use Pervasive as the database behind a clinical management package called Anasazi. One of the tables is CDASSIGN, which tracks assignments of clients to case workers. Here are examples of queries against this table:

this query works:

  select client_id, server_id, sub_unit_id, date_opened
  from cdassign
  where (server_id = 722)

this also works:

  ...
  where (date_closed is null)

this query fails:

  select client_id, server_id, sub_unit_id, date_opened
  from cdassign
  where (server_id = 722) and (date_closed is null)

These are all run directly from the SQL Data Manager, on the server computer. The error that occurs is:

  NTDBSMGR.EXE - Application Error
  The instruction at "0x00b4bc74" referenced memory at
  "0x00000000". The memory could not be "written".

Then a few seconds later an ODBC error saying that the server is no longer available. Restarting the two Pervasive.SQL services allows me to access the database again.

I've tried the server_id code 722 with and without quotes in both working and failing queries, it doesn't seem to be the problem. What is a problem is that a "normal" query should crash the database server software.

I did this testing by installing Pervasive.SQL server on my local computer and copying all of the Anasazi data files over so I can "crash" it without interfering with users. When this crashes on the actual Anasazi server, no one can access Anasazi until the services are restarted.

Hopefully someone has seen this before, or has a course of action to suggest. We have sent the information on to Anasazi Software, and either they or we will send it on to Pervasive as well.

- Xift

 

[mirtheil]

Do the DDFs pass a Database Consistency check? Have you tried rebuilding the files?
If neither of those help, open a support incident with Pervasive.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[xift]

Thanks for your suggestions Mirtheil.

The files have just recently been converted from DataFlex .dat files to .btr files. I will try the Database Consistency check.

Is this a common situation with Pervasive, or rare? Have you heard of this sort of thing before? I see you have a rebuild utility on your site. Is this something that needs to be done often?

We are brand new to Pervasive.SQL and are slowly wading through the documentation and support material. If you have any info or tips for those just getting started, it would be a big help. Thanks again.

- Xift

 

[mirtheil]

This is fairly rare in my experiences and usually indicates a problem with the database. Pervasive is not known for crashing unless there's a hardware or database corruption issue.
My Rebuild DLL is geared for rebuilding corrupt data files programmatically. You shouldn't have to use it and can probably get by with using the standard Pervasive tools.


info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[MrBucket]

Hello. MrBucket here. I have tried running consistency check and most of the tables have failed it, citing either invalid columns or missing files. I also tried rebuilding the table in question today. I have been able to rebuild it but upon testing the query again I crashed the system. I am still leaning toward some sort of data corruption or perhaps incompatibility of the null fields. I have read that PV8 is using more storage in their fields that are marked as null.

What is puzzling though is the fact that where clause is null works but combining it with another clause fails the query (i.e. where date_field is null AND bucket = 432)

The table files have btr extension as opposed to the md.. Does it matter what extension those database files have ?

 

[mirtheil]

THe extension doesn't matter as long as the DDFs define the file name properly (I've used MGC and DRT for some of my Btrieve files).
Data corruption could cause the crash you're seeing. One question, what happens if you change the Where clause to:
... where date_field = null AND bucket = 432
or
... where date_field = '' AND bucket = 432
PV8 only uses an extra byte per field if you created the table(s) using Create Table SQL statement using PV8. If the DDFs were created previously, then they won't use that extra byte.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[xift]

My esteemed colleague MrBucket said:

What is puzzling though is the fact that where clause is null works but combining it with another clause fails the query (i.e. where date_field is null AND bucket = 432)

but some queries with even more clauses work correctly:

this works:

  select
    id, client_id, sub_unit_id, server_id, date_opened, date_closed
  from
    cdassign
  where
    (server_id = '722') and
    (date_opened <= {d '2004-03-31'}) and
    ((date_closed is null) or (date_closed >= {d '2004-03-01'}))
  order by
    sub_unit_id, client_id

this variation, removing the specific date test, fails:

  select
    id, client_id, sub_unit_id, server_id, date_opened, date_closed
  from
    cdassign
  where
    (server_id = '722') and
    (date_opened <= {d '2004-03-31'}) and
    ((date_closed is null))
  order by
    sub_unit_id, client_id

Mirtheil, you suggested trying "date_closed = ''". Do you have any comments on the different ways to specify dates? It seems Pervasive is more flexible in this area than the previous product we used (FlexODBC for accessing the original DataFlex files) which required the "Vendor String" format above. Specifically, does "date_closed = ''" get treated differently than "date_closed is null", and how does the vendor string version come through to Pervasive?

On a semi-related topic, have you used the Query Plan Viewer, and do you think it would provides us with any additional info on this situation? For a query that crashes the server, would it also crash the query plan viewer, or does that evaluate it somehow without "executing" it?

 

[mirtheil]

Dates are specified yyyy-mm-dd. Depending on the age of the data, nulls are handled differently. For example, Pre-Pervasive.SQL 2000, there was no such thing as a "True Null". Nulls were basically a specific value stored in the database. In 2000 and later, "True Null" support was added and are handled with an extra byte before the actual data.
If the database is a "legacy" database then the "date_closed=''" is the equivalent of "date_closed is null" except that the "null" keyword was a fairly foreign concept to older Pervasive databases. You shouldn't need the vendor string.
The QUery Plan Viewer may not crash but won't probably help. It's designed to show the way the engine optimizes the statement.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[MrBucket]

We have new development in this issue. We have converted the field Date_closed to char and then back to date format. Now the engine DOES NOT CRASH anymore. The trouble is that along with the null fields also about 10 records with date values in the date_closed are also included in the result set. For query that is asking for server=722 and date_closed is null it is funny that any value could be in the date_closed and the engine still considering it as null. I was hoping that perhaps our where clause needs to be reworded somewhat but it seems to be quite clear.