Basic Troubleshooting
How to Identify and Solve Common Problems
The following topics provide information for troubleshooting and resolving the most commonly encountered problems you may encounter as you implement a replication solution.
•
Troubleshooting Resources
•
Troubleshooting Strategies
•
Multiple First Sites
•
Uninstalling
•
Network Communications
•
Database Engine
•
Replication Engine
•
Log Files
•
Data Replication
•
Notification Agent
Troubleshooting Resources
The following table describes resources available to help you solve problems.
Feature/Component
Function
For More Information
DataExchange log files
Logs information during replication processing.
See Log Files
DataExchange Table Synchronization and Check utility
Ensures that every record in the data tables has a corresponding record in the replication control tables.
See dxsynctables
PSQL System Analyzer
Tests active engine installations and network communications in DataExchange.
See Network Communications
Knowledge Base
Provides information about many PSQL software configurations and common environments.
Search the knowledge base on the Actian website
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.
*
Does my DataExchange configuration have more than one First Site?
*
Did DataExchange uninstall correctly?
*
Does the network function correctly?
*
Is the database engine running?
*
Is the replication engine running?
*
Is data being replicated correctly?
*
Is the notification agent sending email?
*
Do the log files contain errors?
Continue reading for details about each checklist item.
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.
Uninstalling
If you have activated databases that are located under C:\ProgramData\Actian\PSQL\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.
Network Communications
PSQL System Analyzer (PSA) is a diagnostic utility included with the PSQL database engine. PSA can be used as a stand-alone diagnostic tool to help you troubleshoot network problems.
Using PSA for Network Troubleshooting
►
To start PSA
1
Select PSQL System Analyzer from the Start menu.
2
To troubleshoot network communications for DataExchange, select Test Active Installation in the System Analyzer Options dialog in PSA.
Note PSQL User's Guide covers the features and use of PSA. For DataExchange, use PSA only to troubleshoot network problems. PSA has other uses with PSQL database engines that do not apply to DataExchange.
Database Engine
The PSQL database engine must be running to perform replication.
►
To verify PSQL Server engine is running
1
Open the Windows Control Panel.
2
Click Administrative Tools, then open Services.
3
Scroll the list until you reach the following services:
•
Actian PSQL Transactional Engine
•
Actian PSQL Relational Engine
Both of these services must be started for the 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
If a service is not started, right-click it and select Start.
►
To verify Actian PSQL Workgroup Engine is running
1
Check for the Workgroup icon in the notification area of the Windows Taskbar:
If this icon is showing, the engine is running.
The engine is not running if either of the following is true:
•
The engine-running icon is not showing in the Windows Taskbar.
•
The following icon (engine stopped) is showing in PSQL Explorer:
If PSQL Workgroup Engine is installed as an application, you can start it from the Start menu.
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 PSQL License Administrator utility to determine if you have a permanent license installed for DataExchange or to see if a temporary license has expired. See the License Administrator topic in PSQL User's Guide.
►
To verify the replication engine is running with PSQL Server
1
Access the Services Control Panel.
2
Scroll the list of services until you reach the service Actian PSQL Replication.
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
If the service is not started, right-click it and select Start.
►
To verify the replication engine is running with Actian PSQL Workgroup Engine
1
Check for the DataExchange replication engine icon in the notification area of the Windows Taskbar:
If this icon is showing, the replication engine is running. The engine has exited if this icon is not showing.
You can start the DataExchange replication engine from the Start menu.
Log Files
DataExchange allows you to enable event logging. You may capture the activity of the DataExchange Monitoring Tools, DataExchange Manager, and DataExchange 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.
The DataExchange replication engine (DRE) records its operations in the file dre.log, located by default in C:\ProgramData\Actian\PSQL\Replication\LogFiles. You can review replication activity in this log from PCC by right-clicking the Replication icon under the Databases branch and selecting Statistics and Log Views. You can also use a text editor to open the log file directly.
You can set a number of log file options in DataExchange Manager. You can change the default logging location. You can also change the default log size and the default number of files kept as history. For instructions, see DataExchange Manager Tasks.
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 dre.log, when the log file reaches its maximum size, the file is reassigned to the next history file name. For example, when dre.log reaches its maximum size, it gets renamed to dre.LO1 and a new dre.log gets created. The dre.log file 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 file 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. Each time the utility is activated the associated filename.log file is copied to filename.LO1. The utility then begins logging in the now empty filename.log file. Again, the Maximum Logs to Keep setting controls retention.
The DataExchange Manager and 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.
 
Log File (.log)
Description
da
Updated by DataExchange Manager when you modify schedules, user access, and so forth.
DX_Dnewsite
The replication installation log. This log is created when you install DataExchange and contains information about setting up the replication DNA database.
dre
Contains detailed information on the status of the replications, and is the most active log file. The replication engine updates this log as replication occurs.
The default maximum size of this log is 2 MB. You may want to limit this log to a maximum 5 MB. Some text editors cannot open files larger than 10 MB.
When the replication engine reassigns the DRE log to the next history version (dre.log to dre.lo1, for instance), some log messages may be lost. The loss is typically minimal and may not even occur, depending on the replication activity when the history version is assigned.
DX_Dregdtk
Created if you install a First Site. The log contains information about registering the licenses.
dxact
Updated by the activation utility when you activate a site.
dxdeact
Updated by the deactivation utility when you deactivate a site.
dxdeploy
Updated by the dxdeploy utility. See also Replication Deployment Using DXdeploy with an XML Descriptor File for a hands-on example in which information is written to dxdeploy.log.
dxevent
Updated by the dxevent.dll, which handles replication session event callbacks.
dxsynctables
Updated by the dxsynctables utility, which synchronizes data files and the replication data control files.
<DX prefix>Install
Created to record information related only to installation. This log file is especially useful if installation fails. On 64-bit systems this file is DataExchange_vnn_64_Install.log, and on 32-bit systems it is DataExchange_vnn_x86_Install.log, where nn is the version number.
mer
Written if errors occur while DataExchange routes calls to the MKDE. This log is usually empty. If you suspect that data is not getting replicated correctly, check this log.
msg
Lists messages about the replication sessions, the sites that replicated and if the replication was successful. The replication engine writes to this log. The dre log is more detailed.
reh
Contains information only if the Replication event handler encounter problems updating control tables. If you suspect that data is not getting replicated correctly, check this log.
sess####
Contain information about particular replication sessions. The #### represents a four-digit number. The logs are created if you set up a replication schedule and then replicate. The logs are deleted if you stop the replication engine, and are also deleted at regular intervals. The deletion interval is set by a registry setting.
►
To change the deletion interval for session logs
Note Incorrectly editing the Windows registry can damage it, causing undesirable results, such as your computer failing to start. If you do not feel comfortable editing the registry, work with an IT professional. Actian Corporation accepts no responsibility for a damaged registry. We suggest that you create a backup of your registry before editing it.
1
Run the regedit utility.
2
In the Registry Editor window, find the following registry key:
HKEY_LOCAL_MACHINE\Software\Pervasive Software\Pervasive Replication\SessionExpiry
3
In this key, double-click REG_DWORD.
4
In the Edit DWORD Value dialog in the Value Data field, change the number of minutes (default 5).
5
Click OK to exit Registry Editor.
Data Replication
The following topics pertain to replicating data.
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.
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.
Definitive 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 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 PSQL 7, or with TRUENULLCREATE set to Off, do not have true null support and do not have this limitation.
See the SQL Engine Reference in PSQL 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, 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, 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.
Conflict Types
Data conflicts are divided into types by 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 show 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:
•
local site has more recent record and initiator is local site
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.
•
local site has more recent record and initiator is partner site
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.
•
partner site has more recent record and initiator is local site
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.
•
partner site has more recent record and initiator is partner site
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:
•
record updated or inserted locally and deleted on the partner site
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.
•
record updated or inserted at the partner site and deleted locally
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 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.
Site 1
Site 2
RegionID
NameStr
RegionID
NameStr
8
Site1Data
8
Site2Data
We replicate and we get the primary key conflict, replication is stopped, no tables are updated.
Site 1
Site 2
RegionID
NameStr
RegionID
NameStr
8
Site1Data
8
Site2Data
Update one of the sites to a new unique value.
Site 1
Site 2
RegionID
NameStr
RegionID
NameStr
9
Site1Data
8
Site2Data
Replicate the new change.
Site 1
Site 2
RegionID
NameStr
RegionID
NameStr
8
Site2Data
8
Site2Data
9
Site1Data
9
Site1Data
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 PSQL Server product. It is not available for the Workgroup product. If the notification agent is not sending email, check the following:
•
The agent is running as a service.
•
The agent is correctly configured (see dxagent).
•
The SMTP port through which the agent communicates does not require authentication or encryption.
•
Each replication machine with an agent has access to the SMTP server.
•
The SMTP server is set up to accept email from each replication site running the agent.
►
To verify notification agent is running
1
Open the Windows Control Panel.
2
Click Administrative Tools, then open Services.
3
In the list of services, find Actian DataExchange Agent.
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
If the service is not started, right-click it and select Start.
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 email correctly. Reference the documentation supplied for your SMTP server software or check for testing procedures on vendor websites.