Problems restoring OpenDental

For users or potential users.
Post Reply
Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Problems restoring OpenDental

Post by Tom Zaccaria » Fri Jul 22, 2016 8:30 am

For the past few months unless I Optimize OpenDental and then back it up to a jump drive I am getting this error when trying to restore at home.

MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate entry '1665470' for key 'PRIMARY'

Any ideas?
Thanks,
drtmz

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Problems restoring OpenDental

Post by jsalmon » Fri Jul 22, 2016 9:54 am

That's quite a strange error to be having when making a backup. Are you using Open Dental to make your backup? If not, are you shutting the MySQL service down before making your backup?
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

Tom Zaccaria
Posts: 353
Joined: Mon Feb 25, 2008 3:09 am

Re: Problems restoring OpenDental

Post by Tom Zaccaria » Sat Jul 23, 2016 3:03 am

I have been on OD since it was OpenDentalSoft, one of the originals, and use Karen's Replicator for backup. Currently we are on 16.2.30.
I have never had to stop MYSQL in the past to backup successfully. I do stop the service on my home computer before restoring.

If I kick everybody off OD and run the Optimization tool, I can backup in the office and then restore at home with no issues.

Curious Captain!!
drtmz

User avatar
jsalmon
Posts: 1551
Joined: Tue Nov 30, 2010 12:33 pm
Contact:

Re: Problems restoring OpenDental

Post by jsalmon » Mon Jul 25, 2016 9:41 am

Using a third party automated backup tool runs the risk of running into these types of errors and you just have to accept that fact or take additional steps to guarantee the backup. We use a third party automated backup system here at HQ and have these kinds of errors periodically. It's like trying to take a picture while driving a car, often times you'll get a blurry image because the car is in motion and you just have to take another picture and hope that you get lucky next time and are at a stop light for a clean image. Stopping the MySQL service and making a backup is a way you can put the car in park to guarantee a clean image.

Running the Optimization tool is really running the "REPAIR" command first and then the "OPTIMIZE" command on every single table in the db. The REPAIR command is what is fixing your duplicate PK issue. The fact that you do it before using your backup tool is the interesting part which makes it sound like this duplicate PK problem is already present somehow. My best guess is that it is a duplicate key in the SecurityLog table which we use a lot more than we did back in the OpenDentalSoft days. I've also seen this as a problem with the preference table which has always baffled me.

If you back up once a day or once a week you could try turning off Karen's Replicator automation and doing it manually (just to see if that is the culprit or not). Finding out what is responsible for the duplicate key is going to be the hard part because it shouldn't be possible for MySQL to allow such a thing to happen (in theory).
The best thing about a boolean is even if you are wrong, you are only off by a bit.

Jason Salmon
Open Dental Software
http://www.opendental.com

Post Reply