Replication Troubleshooting

If you experience issues while using Replication, the steps below may be helpful. If you are unable to determine the issue, contact us. We will need the error log message to troubleshoot. We charge an additional $100 per hour for replication support.

Offices that are down can connect directly to a working server that has the most up-to-date data so they can continue working, but with a slower than normal connection.

Initial Troubleshooting

  1. Go to the server(s) where replication failed. Go to the command line and change the directory to C:\Program Files\MySQL\MySQL Server 5.5\bin and type the following: mysql -u root opendental where opendental is the name of the database.
  2. Run the SHOW SLAVE STATUS\G; in the mysql command interface.
  3. Look through the error log for details about what caused replication to fail.
  4. Refer to the problems and possible solutions below, or contact support for assistance. If support becomes involved they will need a copy of the database and all log files.

Errors/Issues

Below are common errors. While a possible solutions is provided, we recommend contacting support for assistance. Only users who have an expert understanding of replication and how the office is setup should attempt a solution.

Problem: SHOW SLAVE STATUS is showing an error of "Got fatal error 1236 from master when reading data from binary log: ‘Client requested master to start replication from impossible position."

Point all locations to one database, then Reinitialize Replication. We recommend that all locations connect to the database on the server with the most up-to-date data.

Problem: Receive a message "This database is temporarily unavailable. Please connect instead to your alternate database at the other location".

This means replication has failed on the server. Follow your response plan or contact your replication administrator. We recommend that all locations connect to the database on the server with the most up-to-date data.

If running a SHOW SLAVE STATUS command on the replication server which is giving the error message reveals that SLAVE_IO_RUNNING and SLAVE_SQL_RUNNING both says Yes, then log in to Open Dental on another replication server and access the Replication Setup Window (Replication: Add Servers and Designate a Slave Monitor) to use the Clear button for the Replication Failure at Server_id (this field is not shown in the screen shot, because it only shows when a replication failure has occurred). When this issue occurs, it is because the replication problem has been resolved, but Open Dental is not aware that a fix has been implemented. Using the Clear button informs Open Dental that the issue has been resolved and allows users to access the server that was having issues.

Problem: A replication failure is suspected, but the slave monitor does NOT send the message to the clients of the failed slave server.

Check that the slave monitor is on and Open Dental is running.

Error: Unable to connect to host.

  1. Check that your Server Description exactly matches the computer name of the server for that location.
  2. If you did a SHOW SLAVE STATUS and there are no errors then check your server's Event Viewer. If the Event Viewer has a lost connection error, the likely scenario is that the slave's read timeout aborted before the command was completed. Increase the read/write timeout. You may then be faced with a max packets error, in which case increase the allowed max packets as needed.

If none of these work, there is a chance your ibdata file was corrupted prior to performing these steps.

Problem: CREATE TABLE mytable.

This is the usual culprit. At a minimum, it must always be prefaced by DROP TABLE IF EXISTS mytable. This applies equally to temp tables that you may create. But even then, it's still dangerous because someone at another node might reference the same table at around the same time.

Problem: Duplicate entry '0001-01-01 00:00:00' for key IndexAckTime.

This is due to a corrupted index file. Dropping and recreating the index may solve the problem.

Problem: Duplicate primary key

Contact support and provide a copy of the database and all log files.

Error: replication server has tried to connect a number of times to repl@'servername':3306.

Make sure you open the port of the server. This occurs when the Slave Status on one replication server says SLAVE_IO_RUNNING Yes, SLAVE_SQL_RUNNING Yes but the other replication server has a status of SLAVE_IO_RUNNING Connecting, SLAVE_SQL_RUNNING Yes.

Data won't replicate even though the SLAVE_IO_RUNNING and SLAVE_SQL_RUNNING both says Yes.

Ping the server name. If you can't ping by server name but can ping the IP address, stop the slave, then repeat steps 7 - 10 in setup. For step 7 use the IP address as the MASTER_HOST instead of the server name.

CHANGE MASTER TO

MASTER_HOST = 'IPofMASTER',

MASTER_USER = 'repl',

MASTER_PASSWORD = 'od1234';

Problem: Slave_IO_Running and Slave_SQL_Running show as Yes for both, but database is not syncing.

Review the name of the database if using upper casing for database name. Re-initialize replication using lower case lettering for your .ini files and your Grant commands. Contact support for more information.