?
Solved

Consistency check problems with database

Posted on 2006-03-31
6
Medium Priority
?
1,183 Views
Last Modified: 2013-12-25
My database works fine most of the time, except I need to restart the relational services every few weeks because they stop responding.  I ran a consistency check and here are the results.  I'm not a Pervasive/Btrieve expert, so I'm not too sure what to do about these problems.  Again, the database generally works fine.  I searched for information in the Pervasive KB, but could not find much useful information.


===============================================
Database Check Statistics
===============================================
Pervasive.SQL Compatibility Mode  =  9
Tables Checked  =  19
Tables Passed  =  7
Tables Failed  =  12

Illegal Object Names  =  3
Record Length Inconsistencies  =  6
Variable Length Column Inconsistencies  =  0
Data Type Inconsistencies  =  0
Column Overlaps  =  0
Full Path (instead of Relative) Table Locations  =  0
Table Files Don't Exist  =  1
Index Inconsistencies  =  41
System Table Inconsistencies  =  0
General Dictionary Inconsistencies  =  1


===============================================
Tables That Passed Test
===============================================
pallet_scan
X$Attrib
X$Occurs
X$Relate
X$Rights
X$User
X$Variant


===============================================
===============================================
Problems Detected with Database Definitions
===============================================
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name  =  Pallet_Carton

The Data File Index is Not Defined in the Dictionary.
The data file defines index 0 seg 0
The dictionary does not.
The Data File Index is Not Defined in the Dictionary.
The data file defines index 0 seg 1
The dictionary does not.
The Data File Index is Not Defined in the Dictionary.
The data file defines index 0 seg 2
The dictionary does not.
The Data File Index is Not Defined in the Dictionary.
The data file defines index 0 seg 3
The dictionary does not.
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name  =  Performance

The Data File Index is Not Defined in the Dictionary.
The data file defines index 0 seg 0
The dictionary does not.
The Data File Index is Not Defined in the Dictionary.
The data file defines index 0 seg 1
The dictionary does not.
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name  =  prioritytable

illegal column name 'timestamp'.
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name  =  Product

The Data File Index is Not Defined in the Dictionary.
The data file defines index 0 seg 0
The dictionary does not.
The Data File Index is Not Defined in the Dictionary.
The data file defines index 0 seg 1
The dictionary does not.
The Data File Index is Not Defined in the Dictionary.
The data file defines index 0 seg 2
The dictionary does not.
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name  =  Scale

This table data file doesn't exist.
Table data file = 'SCALE.BTR'
An error occurred while reading statistics from the btrieve data file.
illegal column name 'RELEASE'.
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name  =  Shipment_Order

illegal column name 'RELEASE'.
The Data File Index is Not Defined in the Dictionary.
The data file defines index 0 seg 0
The dictionary does not.
The Data File Index is Not Defined in the Dictionary.
The data file defines index 0 seg 1
The dictionary does not.
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name  =  X$Field

Index Data Type Inconsistency.
The dictionary index 0 seg 0 indicates 1 (Column = 'Xe$Id')
The data file index 0 seg 0 indicates 14
Index Data Type Inconsistency.
The dictionary index 1 seg 0 indicates 1 (Column = 'Xe$File')
The data file index 1 seg 0 indicates 14
Index Case Flag Inconsistency.  
The dictionary index 2 seg 0 indicates case sensitive. =  No
Data file index 2 seg 0 indicates case sensitive. =  Yes
Index ACS Flag Inconsistency.  
The dictionary index 2 seg 0 indicates No
Data file index 2 seg 0 indicates Yes
Index Data Type Inconsistency.
The dictionary index 3 seg 0 indicates 1 (Column = 'Xe$File')
The data file index 3 seg 0 indicates 14
Index Case Flag Inconsistency.  
The dictionary index 3 seg 1 indicates case sensitive. =  No
Data file index 3 seg 1 indicates case sensitive. =  Yes
Index ACS Flag Inconsistency.  
The dictionary index 3 seg 1 indicates No
Data file index 3 seg 1 indicates Yes
Index Data Type Inconsistency.
The dictionary index 4 seg 2 indicates 1 (Column = 'Xe$Dec')
The data file index 4 seg 2 indicates 0
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name  =  X$File

Index Data Type Inconsistency.
The dictionary index 0 seg 0 indicates 1 (Column = 'Xf$Id')
The data file index 0 seg 0 indicates 14
Index Case Flag Inconsistency.  
The dictionary index 1 seg 0 indicates case sensitive. =  No
Data file index 1 seg 0 indicates case sensitive. =  Yes
Index ACS Flag Inconsistency.  
The dictionary index 1 seg 0 indicates No
Data file index 1 seg 0 indicates Yes
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name  =  X$Index

Index Data Type Inconsistency.
The dictionary index 0 seg 0 indicates 1 (Column = 'Xi$File')
The data file index 0 seg 0 indicates 14
Index Data Type Inconsistency.
The dictionary index 1 seg 0 indicates 1 (Column = 'Xi$Field')
The data file index 1 seg 0 indicates 14
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name  =  X$Proc

Fixed Record Length Mismatch.
Dictionary indicates length 34
Data file indicates length 0.
Variable Data Type Column Mismatch.
Dictionary indicates that a variable length column exists.
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 0 seg 0
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 0 seg 1
The data file does not.
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name  =  X$Trigger

Fixed Record Length Mismatch.
Dictionary indicates length 40
Data file indicates length 0.
Variable Data Type Column Mismatch.
Dictionary indicates that a variable length column exists.
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 0 seg 0
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 0 seg 1
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 1 seg 0
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 1 seg 1
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 1 seg 2
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 2 seg 0
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 2 seg 1
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 2 seg 2
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 2 seg 3
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 2 seg 4
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 2 seg 5
The data file does not.
+++++++++++++++++++++++++++++++++++++++++++++++
Table Name  =  X$View

Fixed Record Length Mismatch.
Dictionary indicates length 22
Data file indicates length 0.
Variable Data Type Column Mismatch.
Dictionary indicates that a variable length column exists.
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 0 seg 0
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 1 seg 0
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 1 seg 1
The data file does not.
Dictionary Index Not Defined in Data File.
Dictionary defines index 1 seg 2
The data file does not.


===============================================
0
Comment
Question by:internetcreations
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:mirtheil
ID: 16346503
Those errors probably aren't causing the engine to stop responding.  Most of those errors are the result of old DDFs/old data files and changes in the engine.  

What version of PSQL are you using?  
0
 
LVL 29

Accepted Solution

by:
Bill Bach earned 2000 total points
ID: 16347687
As Mirteil indicates, these issues are probably not related to your hang.  With what you can see there, though, indicates a a few issues:
1) You may have a "bad" version also of the CheckDB Wizard.  Pervasive issued an update to it not too long ago, which will be included in the next full service pack.  If you need to get it early, let me know and I can probably make the FTF available, or you can contact Pervasive Technical Support to get it.
2) Your DDF's were originally built by an older tool, somewhere in the Btrieve 6.x timeframe.  This is evidenced by the Mismatches in X$File.  You may wish to build brand new DDF's (create another database) and create your tables in the new system instead.  This should clean up some of the dictionary issues.  We have tools to make this easier, but with the few tables you have, doing it manually shouldn't be too bad.
3) The illegal object names are not big deal, as you can always override the SQL parser by including the fiel names in double-quotes whenever you want to use them.  However, if you CAN change the names, you'll want to do so as quickly as you can.
4) The Btrieve files have indices that are NOT defined in the SQL data dictionary (DDF's).  This would cause queries to return data MUCH more slowly than you would want to.  If you have VERY large files, this may appear to be a hang, especially if the database decides that it has to process a join across three 100,000-record databases with no indices.  (This would cause the database to build a 1,000,000,000,000,000-record result set, which would appear to be a hang to anyone but Rip Van Winkle.)  Obviously, if you CAN define the indices, then the SQL optimizer will be able to make short work of most joins, and queries will run better.

There are a few other issues on PSQLv9 that you can try to stabalize things:
a) Your PSQLv9 database engine may need to be patched.  If you are not yet running PSQLv9.1, you may wish to upgrade first & see if that helps.
b) Disable the L2 cache: Change the Max Microkernel Memory Usage from 60 to 0.  This often buys stabaliztion due to memory restrictions (each process only gets 2GB of memory space).
c) Disabling the Client Cache Engine: Change the Allow Cache Engine Connections setting to OFF.  This can eliminate some extra overhead on the server, especially helpful for large installations.

If these don't help, it might be better to start by describing as much about your install as you can...
0
 
LVL 1

Author Comment

by:internetcreations
ID: 16363881
I have PSQL V. 9.1 with the most current patches.  I will try the other suggestions to stabilize it.  I am querying the SQL engine with an ODBC connection via an ASP page.  The relational engine occasionally becomes unresponsive and needs to be cycled.  I note errors in the IIS server log:

2005-12-29 09:00:44 192.168.0.78 GET /monitor.asp coid=3&mtype=pt|8|8000ffff|Catastrophic_failure 80 - 192.168.0.155 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98) 500 0 0
2005-12-29 09:04:45 192.168.0.78 GET /monitor.asp coid=3&mtype=pt|8|80004005|Unspecified_error 80 - 192.168.0.234 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322) 500 0 0


and

2005-12-06 21:36:30 192.168.0.78 GET /default.asp coid=3|13|80004005|[Pervasive][ODBC_Client_Interface][Client_LNA][SHM]Connection_to_the_server_could_not_be_established__please_check_if_server_is_running. 80 - 192.168.0.226 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+NT+5.1;+SV1;+.NET+CLR+1.1.4322) 500 0 0
2005-12-06 21:36:33 192.168.0.78 GET /monitor.asp coid=3&mtype=pt|71|80004005|[Pervasive][ODBC_Client_Interface][Client_LNA]Your_connection_to_the_server_is_no_longer_working._To_continue__disconnect_from_the_data_source_and_re-connect_again._Contact_your_system_administrator_if_you_still_need_assistance. 80 - 192.168.0.155 Mozilla/4.0+(compatible;+MSIE+6.0;+Windows+98) 500 0 0

0
 
LVL 29

Expert Comment

by:Bill Bach
ID: 16364095
Sounds like the messages just indicate that the engine goes away.  The stabalization changes will likely take care of it, but if they don't then it may be a timing issue or specific query that is flaking out the engine.  I would recommend opening an incident direct with Pervasive if that is the case, as they'll be able to take your test data and reproduce the problem...
0
 
LVL 1

Author Comment

by:internetcreations
ID: 16420401
I've made the recommended stabilization changes and I haven't had the problem again yet.  We shall see...
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question