Basic Troubleshooting

Basic Troubleshooting
How to Identify and Solve Common Problems
This appendix provides information for troubleshooting and resolving the most commonly encountered problems you may encounter as you implement a replication solution . It includes the following sections:
*
*
*
*
*
*
*
*
*
*
*
Troubleshooting Resources
The following table describes resources available to help you solve problems.
See dxsynctables in Pervasive DataExchange User’s Guide
Search the Pervasive Knowledge base at:
http://www.pervasive.com/kb
 
Troubleshooting Strategies
You must first diagnose a problem before you can fix it. The following checklist contains items to help you diagnose problems with DataExchange.
o
o
o
o
o
o
o
o
o
The rest of this chapter discusses each checklist item in more depth.
 
Installation
This section discusses topics that pertain to installation.
Multiple First Sites
Unpredictable results can occur if you set up multiple First Sites per replication network. Install only one First Site per replication network.
Terminal Services
To install DataExchange to a Terminal Server client, you must first modify a registry setting on the Terminal Server client. See Installing Over Windows Terminal Services in Getting Started with Pervasive DataExchange.
Uninstalling
If you have activated databases that are located under <Installation Directory >\Replication, first deactivate them then delete the replication template with the Template Remover wizard. If you do not deactivate the database then remove the template, uninstall leaves the database files and the associated replication files. The remaining files do not cause any problems but you may prefer to reclaim physical storage by deleting them. You may delete the files manually if you want. Remember, also, to remove the DSN associated with the database.
See Working with the Examples for a discussion of how to deactivate a database and remove a template.
Network Communications
Pervasive System Analyzer (PSA) is a diagnostic utility included with the Pervasive PSQL database engines. PSA can be used as a stand-alone diagnostic tool to help you troubleshoot network problems.
Note For DataExchange, use PSA only to troubleshoot network problems. PSA is capable of other functions pertaining only to the Pervasive PSQL database engines. The additional functions in PSA (such as archiving) do not apply to DataExchange.
How to Start PSA
*
1
Select Pervasive System Analyzer from the operating system Start menu or Apps screen.
Note To troubleshoot your network communications for DataExchange, select Test Active Installation on the System Analyzer Options dialog in PSA.
Documentation for PSA
The use of PSA is detailed in the Pervasive PSQL User's Guide. Please see that guide for complete information regarding PSA.
Database Engine
The Pervasive PSQL database engine must be running to perform replication.
*
1
Open the Windows Control Panel.
2
Click Administrative Tools, then open Services.
3
*
*
Both of these services must be started for the Pervasive PSQL database engine to function correctly.
The Status column shows whether the service is currently running. The Startup column shows whether the service is set to automatically start on system startup or start manually.
4
*
1
If this icon (engine running) is showing, the engine is running.
The engine is not running if either of the following is true:
*
*
If the Workgroup engine is installaed as an application, you can start it from the operating system Start menu or Start screen.
Replication Engine
The DataExchange replication engine must be running to perform replication. The engine will not run, for example, if a temporary license has expired.
Use the Pervasive PSQL License Administrator utility to determine if you have a permanent license installed for Pervasive DataExchange or to see if a temporary license has expired. See the the License Administrator chapter in Pervasive PSQL User's Guide.
*
1
Access the Services functionality from the operating system.
2
This service must be started for the DataExchange replication engine to function correctly.
The Status column shows whether the service is currently running. The Startup column shows whether the service is set to automatically start on system startup or start manually.
3
*
1
If this icon is showing, the engine is running. The engine is not running if this icon is not showing.
If the Workgroup engine is installaed as an application, you can start it from the operating system Start menu or Start screen.
Log Files
Pervasive DataExchange allows you to enable event logging via log files. You may record the activity of the DataExchange Monitoring Tools, the DataExchange Manager, and the Replication Engine. Additionally, DataExchange keeps a messages log and an installation log.
All logs are text files and have a file extension of .log for the most current one saved. The files produce any mix of data: Information, Warning, Error, and Debugging. You can choose verbose (as opposed to terse) messages. Verbose messages contain the name of the program and a line number within the program to help you debug situations. Verbose applies only to the Debug logging level.
You can change the default location of the logs, which is <Installation Directory >\Replication\LogFiles. If you want, you can also change the default log size and the default number of files kept (a history). You can view the LogFiles folder from the operating system Start menu or Apps screen.
Log File Size
If you set the maximum log size to zero, or no limit, the log will increase in size to whatever capacity the physical storage allows. We recommend that you do not set a maximum log size of zero except for troubleshooting. Even then, avoid using the no limit setting for an extended period (typically, more than four hours). The DRE log, for example, can grow rapidly depending on the type of logging being performed (such as Debug logging), the frequency of replication, and the number of sites replicating.
In the case of the DRE.log, when the log file reaches its maximum size, the file is reassigned to the next history file name. For example, when the Dre.log reaches its maximum size, it gets renamed to Dre.lo1 and a new Dre.log gets created. Dre.log is then empty and starts acquiring data once more. When the maximum size is again reached, Dre.lo1 gets renamed to Dre.lo2, Dre.log gets renamed to Dre.lo1, and a new Dre.log is again empty and able to acquire data. The Maximum Logs to Keep setting determines the extent of the history retained: lo1, lo2, lo3, and so forth.
Other DataExchange components automatically start a new log file when the utility or service executes. In this situation, each time the utility is activated the associated XXX.log file is copied to a new XXX.lo1 file. The utility then begins logging in the now empty XXX.log file. Again, the Maximum Logs to Keep setting determines the extent of the history retained: lo1, lo2, lo3, and so forth.
The DataExchange Manager and the DataExchange Monitoring Tools have a Logging dialog that allows you to change the logging settings.
Log File Descriptions
The following table describes the content of the various log files.
 
The log updated by the dxdeploy utility. See also Working with the Demodata Sample Database for a hands-on example in which information is written to dxdeploy.log.
*
1
2
Click Tools Manager.
3
Log on to Manager.
4
Click Options.
*
1
In PCC, right-click Replication under the name of a database.
2
Select Statistics and Log Views.
3
*
1
In PCC, right-click Replication - Connected and select Statistics and Log Views.
2
A drop-down menu displays log commands and settings, which are explained in Pervasive DataExchange User’s Guide in Chapter 9, Using the Replication Progress and Log Viewers under Recording and Monitoring Replication Activity.
*
Caution Incorrectly editing your computer’s registry can damage the registry. The damage can cause undesirable results, such as your computer not being able to boot. If you do not feel comfortable editing the registry, obtain the services of a qualified technician. Pervasive Software accepts no responsibility for a damaged registry.
We suggest that you create a backup of your registry before editing the registry. Refer to the online help for your operating system. Look for index entries such as “registry, backing up,” or “emergency repair disk.”
1
Registry Editor opens.
2
HKEY_LOCAL_MACHINE\Software\Pervasive Software\Pervasive Replication\SessionExpiry
3
Double-click REG_DWORD for the key.
The Edit DWORD Value dialog opens.
4
In the Value Data field, change the value to the desired minutes (5 is the default).
5
6
Data Replication
This section discusses topics that pertain to replicating data.
List of Sites to Replicate With Seems Inconsistent
The Initiate Replication dialog allows you to start a replication. (Right-click Replication - Connected in the PCC tree, then click Tasks Initiate Replication to display the dialog.) On this dialog, you select a source database and the sites to replicate with. The list of sites to replicate with may seem inconsistent depending on how you activated your Partner Sites.
Here’s an example of how the list may appear inconsistent. You create Partner Site PS1 and perform an initial replication with the First Site (the default behavior). You create Partner Site PS2 but do not perform an initial replication with the First Site.
In the Initiate Replication dialog, if you select as the source database the First Site or PS1, neither shows PS2 as a site to replicate with. This may lead you to question, “what became of my second Partner Site? Why isn’t PS2 in the list?”
It is not in the list because the information about the replication network, such as the sites on it, is also replicated. If you select PS2 as the source database, the choice First Replication Site shows as a site to replicate with. After you replicate with the First Site, then PS2 appears in the list for the First Site and PS1.
The easiest way to ensure consistency in the list is to perform an initial replication with the First Site when you activate each Partner Site. A Partner Site cannot replicate with other sites until it replicates with the First Site.
Replication Fails for a Deactivated Site
The other sites on your replication network do not know that a site has been deactivated until you perform a replication. Therefore, the first replication after deactivating a site from the replication network will fail for that site. This is the normal behavior.
The DRE.log file contains an error message similar to the following:
E 019f 0301-15:46:25 Partner site Partner_Site_2 (site number: 00LFLU) has been removed from the replication network - it cannot replicate with any other site.
After the first replication, this error no longer occurs because all sites on the replication network are aware of the site’s status (deactivated).
Note If you deactivate a First Site, you must perform a manual replication if you reactivate the First Site. After you reactivate the First Site, you must manually initiate a replication session from each Partner Site to the First Site. The replication sessions ensure that all available sites appear on the Initiate Replication dialog.
This action is not required if you deactivate/reactivate a Partner Site. It applies only to a First Site.
False Alert Because of Schedule Manipulation
If you delete, disable, or modify a schedule, the other replication sites are not aware of this because replication does not take place. The notification agent on the other sites continues to contact the scheduling site. If the scheduling site is down or unreachable, the agent sends a failure alert. The alert is false because the schedule no longer applies.
To prevent such false alerts, manually initiate replication after you delete, disable, or modify a schedule. The replication ensures that the schedule changes get replicated. Alternatively, if your entire replication network no longer needs replication, deactivate all replication sites on the network.
Correct Alarms but Replication on Wrong Schedule
This situation occurs if you change a schedule remotely. For example, you start DataExchange Manager on site B and use it to change the schedule on site A. The replication engine on site A will not use the new settings until you restart the engine. The notification agent, however, uses the new schedule without having to be restarted. The agent properly notifies of replication being off schedule.
To prevent this situation, do not update schedules remotely.
Dynamically Created Tables Not Being Replicated
If your dynamically created tables are not being replicated, verify that the file matching patterns in the dCNF table are correct. DataExchange provides a utility, Dxdynpath, to help you verify file matching patterns. See dxdynpath in Pervasive DataExchange User’s Guide.
Note that relative paths are relative to a home directory that contains data dictionary files (DDFs) recognized by DataExchange.
Note Dxdynpath should only be used with the Real-Time Backup Edition.
SQL Triggers and Replication
SQL triggers are a type of stored procedure that are automatically executed when data in a table is modified with an INSERT, UPDATE, or DELETE. (Stored procedures are SQL statements that are predefined and saved in the database dictionary.)
Replication updates the base table before updating the dependent tables. This sequence maintains the correct foreign key relationships. In the case of the sample database Tracker, for example, the Region table is updated before the Employee table.
For illustration, suppose you have a database with a base table A and a dependent table B (table B has a foreign key relationship to table A). You create a trigger that updates base table A when a new record is inserted into table B.
Partner site 1 inserts a new record into table B, causing the trigger to execute. Table A is updated on Partner Site 1. Then you replicate Partner Site 1 with your other sites.
On the other sites, replication updates table A, then inserts a new record into table B, causing the trigger in table B to execute and again attempt to update table A. This may not be the desired behavior because the Replication Engine has already updated the record in table A with changes made on Partner Site 1.
Definite rules concerning triggers and replication cannot be given. In general, carefully consider each trigger that is part of a replicated database. Make certain that the trigger’s functionality and the behavior of replication are compatible to ensure the outcome you desire.
Data Conflicts When Activating Partner Sites
The template data prepared for replication during design is marked differently (with timestamps and other internal methods) than the data prepared during activation. The Replication Engine resolves the differences during replication. In a few cases, data conflicts may result between how you activate your First Site versus how you activate your Partner Sites.
Index Segments
For data files with 4096 byte page size, you are limited to 119 index segments per file. Because each indexed nullable column with true NULL support requires an index consisting of 2 segments, you cannot have more than 59 indexed nullable columns in a table (or indexed nullable true NULL fields in a Btrieve file). This limit is smaller for smaller page sizes.
Any file created with Pervasive PSQL, with file create mode set to 7.x, and TRUENULLCREATE set to the default value of On, has true null support. Files created using an earlier file format, or with Pervasive PSQL 7, or with TRUENULLCREATE set to Off, do not have true null support and do not have this limitation.
Refer to the Pervasive PSQL book SQL Engine Reference for more information about TRUENULLCREATE.
Data Conflicts
Central to any replication solution is its ability to detect and resolve conflicts when they occur. The best method of handling conflicts is a preventative one: avoid conflicts by design. In addition to this, Pervasive DataExchange includes a default last-in-wins policy. By maintaining time stamps in control tables and synchronizing clocks on replication sites, an accurate account of when records where inserted, updated, and deleted is kept. The Replication Engine is responsible for enforcing the conflict policy (and logging conflicts) as they occur.
In addition, Pervasive DataExchange provides an interface with which you can define your own conflict resolution. You can design the appropriate conflict resolution for any set of business rules and provide the resolution through an event handler DLL. The DLL overrides or extends the functionality of the Replication Engine.
For information about creating an event handler DLL, contact Pervasive Software. We will supply you the Event Callback API documentation.
Conflict Types
Data conflicts are divided into types by Pervasive DataExchange. Each conflict is recorded in the log dre.log. You can read the log entries and determine what, if any, action needs to be taken. Messages similar to the following appear in the log. It contains the type of conflict, the key of the record involved, and how the conflict was resolved.
When a conflict occurs and a record must be overwritten, the newest record is always used. To alter this behavior, you can register your customized Event Handler DLL with the Replication Engine.
W 0130 0321-17:44:04 CONFLICT: Type I: Record 2 for table Customer has been altered at both sites (key: 123).
W 0130 0321-17:44:04 Type I conflict resolved: Record 2 will be updated at partner (Table Customer, key: 123).
The log file of the Site where the record was not updated will contain a message saying that the record at the partner Site was updated (as above). At the Site where a record was overwritten messages similar to the following indicate the data value that was updated as well as the new value (in this case the last name Yin was replaced by the last name Yan:
I 0130 0321-17:44:04 Local field Customer.LastName replaced by remote value
I 0130 0321-17:44:04 Before: Yin
I 0130 0321-17:44:04 After: Yan
Type I Conflict
A Type I conflict occurs when a record has been updated at both Sites since the last replication session. When this occurs the newer record is replicated and a log message similar to the following is generated:
W 0130 0321-17:44:04 Type I conflict resolved: Record 2 will be updated at partner (Table Customer, key: 123).
Variations of this conflict type include the following possible scenarios and associated log messages:
*
W 01f8 08-17 19:09:41 CONFLICT: Type I: a record in table Employee has been altered at both sites GIDSysKey: 632940095990000027 GIDSiteID: 0 Unless overridden by an event handler, the DRE will choose the most recent (partner) record.
*
W 055c 08-17 19:00:51 CONFLICT: Type I: a record in table Employee has been altered at both sites GIDSysKey: 632940095990000028 GIDSiteID: 0 Unless overridden by an event handler, the DRE will choose the most recent (local) record.
*
W 0518 08-17 19:14:25 CONFLICT: Type I: a record in table Employee has been altered at both sites GIDSysKey: 632940095990000029 GIDSiteID: 0 Unless overridden by an event handler, the DRE will choose the most recent (local) record.
*
W 0954 08-17 19:05:23 CONFLICT: Type I: a record in table Employee has been altered at both sites GIDSysKey: 632940095990000030 GIDSiteID: 0 Unless overridden by an event handler, the DRE will choose the most recent (partner) record.
Type V Conflict
Type V conflicts occur when there is an error with the starter data at each Site (the data that already exists in the database when your database application is activated).
If starter data exists at one Site and not at the other, the starter data is replicated to the other site. If the existing starter data is different, however, a type V conflict is logged and replication stops:
W 0130 0321-17:44:04 CONFLICT: Type V: Starter data in record 2 for table Customer is different at the two sites - the conflict must be resolved manually.
Type VI and VIa Conflicts
Conflicts between a record inserted or updated at one site and deleted at another site are treated as a Type VI conflict. During conflict processing, if all records are updated at one site (or the record was inserted) and the record is deleted at the other site, a type VIa conflict is logged and the most recent operation wins. If any records are not updated, then the delete operation must always win. This type VI conflict needs to be resolved in this way because there is insufficient information available to reinsert the entire record at the site where the record was deleted. The following describes the possible scenarios and the associated log messages:
*
W 0130 0321-17:44:04 CONFLICT: Type VI: A locally-updated record in table Customer has been deleted at the partner site (key: 123)
Creation date: 2000/05/10 12:26:45
Deletion date: 2000/05/11 11:18:23
The record will be deleted locally.
W 0130 0321-17:44:04 CONFLICT: Type VIa: A newly-inserted record in table
Customer has been deleted at the partner site (key: 123)
Creation date: 2000/05/10 12:26:45
Deletion date: 2000/05/11 11:18:23
The record will be transmitted.
*
W 0a78 08-17 13:05:13 CONFLICT: Type VI: A record in table t1 has been deleted at the partner site and updated locally. GIDSysKey: 632939880130000000 GIDSiteID: 0 The record will be deleted locally.
Successful Conflict Resolution
Note that if conflicts were successfully resolved during a replication cycle, a message similar to the following appears in the replication log file:
0082 0818-10:47:36 Replication of Tracker with 000030 ended successfully with 2 conflicts resolved.
Resolving Primary Key Conflicts
The best way to avoid primary key conflicts is to design your source database with unique primary keys. If you are using an existing database for replication that does not have unique primary keys, you can designate primary keys with the Pervasive PSQL Control Center.
A primary key conflict occurs when two rows have been inserted into two different activated databases that have the same primary key. Because each row could potentially have relevant data, the replication engine stops the replication and will not modify these rows.
You will see the following errors the DRE.log file on the system that detects the primary key conflict. For this example, the primary key conflict has been detected on the Region table of the sample database Tracker.
E 01cb 1108-11:42:21 sqlhelp 852 ODBC Error -1: (S1000) ‘[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager] The record has a key field containing a duplicate value(Btrieve Error 5)’ <-4994>
E 01cb 1108-11:42:21 dbutil 629 ODBC statement failed: -1 from function Execute
E 01cb 1108-11:42:21 dbutil 636 ODBC statement: INSERT INTO "Region" ("RegionID","NameStr") VALUES (?,?)
E 01cb 1108-11:42:21 dsectbl 927 Unable to insert new record into table Region having key: 8
E 01cb 1108-11:42:21 dresyncs5368 FSM:{22: An ODBC error occurred}: Unable to store next PD2PQQ88002.Region record from partner’s data list (key:PDCID(1000010))
All other systems that attempt to replicate with this system will fail with the Error:
E 01e8 1108-11:36:40 dresyncs1542 Error received from partner replication engine: ‘Update of Region failed’
Replication stopped.
Remedying a primary key conflict requires manual intervention. You must update the primary key of one of these conflicting rows to a new, unique value. Following that, you must start a replication session.
For instance, from the example above, the conflict occurred with primary key 8. So, on one of the databases involved you would update the primary key to 9, a unique key value for the table. Then you would replicate the two systems together.
Note that during this replication, the replication control tables are cleaned up to correct the primary key conflict, and the following message is written to the DRE.log file as a warning message. The purpose of this message is to let you know that the primary key conflict was successfully resolved.
W 01b7 1107-17:12:56 dsectbl 893 A primary key conflict has been rectified on a replication control table. Table: PDCRegion, Column: CRegionID with key value of: 8
So, what transpired in terms of data in the database? Here is the contents of the Region table throughout the process.
First, a row with the same key is inserted onto each site.
We replicate and we get the primary key conflict, replication is stopped, no tables are updated.
Update one of the sites to a new unique value.
Replicate the new change.
Now both sites are replicated to a consistent copy and no data has been lost.
Notification Agent
The notification agent is installed only if the machine contains the Pervasive PSQL Server product. It is not available for the Workgroup product. If the notification agent is not sending e-mail, check the following:
*
*
The agent is correctly configured (see dxagent in Pervasive DataExchange User’s Guide)
*
*
*
*
1
Open the Windows Control Panel.
2
Click Administrative Tools, then open Services.
3
This service must be started for the notification agent to function correctly.
The Status column shows whether the service is currently running. The Startup column shows whether the service is set to automatically start on system startup or start manually.
4
Testing the Mail Server
The SMTP mail server must also be functioning correctly. If required, verify that the SMTP mail server is sending and receiving e-mail correctly. Reference the documentation supplied for your SMTP server software or check for testing procedures on vendor Web sites.
How to Get Additional Help
Pervasive Software strives to ensure that your product installation is easy and successful. If you encounter problems during or after the installation that are not covered in the user documentation, please contact Pervasive Software and we will address your problem promptly.
The following table lists a variety of resources to help you get answers to your questions, troubleshoot problems, and interact with the Pervasive team as well as with other customers.
Access installed documentation from the Pervasive program on the Start menu or from the installation CD-ROM.
Technical Support
If you still have questions or problems relating to your Pervasive DataExchange installation, you can obtain help from the Pervasive Customer Support department.