Primary key duplicate

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Primary key duplicate

Post by Mifa » Mon Feb 26, 2018 10:01 am

I've just got a call from my staff, they can't move appointments in the calendar or create new tasks. After the server was powered out mistakenly for a few minutes, they are getting an exception thrown at them every time they try to move an appointment stating OD found a duplicate primary key. I was able to reproduce the problem (see error message below). From what I can gather when looking at the various tables with Navicat, it seems that signalod is the table causing the problem. Its current autoincrement value is 1482965 and last record in it shows 1482964 (first one is above 88000)

Question: what's the best course of action to solve this? I am leaning towards performing an ALTER TABLE signalod AUTO_INCREMENT = 1482966; but I don't want to break anything else in the process.

We are using OD 14.3.35.

Thanks in advance for your help!

===============

MySql.Data.MySqlClient.MySqlException (0x80004005): Duplicate entry '1482965' for key 'PRIMARY'
at OpenDentBusiness.DataConnection.NonQ(String commands, Boolean getInsertID, OdSqlParameter[] parameters)
at OpenDentBusiness.DataCore.NonQ(String command, Boolean getInsertID, OdSqlParameter[] parameters)
at OpenDentBusiness.Db.NonQ(String command, Boolean getInsertID, OdSqlParameter[] parameters)
at OpenDentBusiness.Crud.SignalodCrud.Insert(Signalod signalod, Boolean useExistingPK)
at OpenDentBusiness.Crud.SignalodCrud.Insert(Signalod signalod)
at OpenDentBusiness.Signalods.Insert(Signalod sig)
at OpenDental.FormOpenDental.DataValid_BecameInvalid(ValidEventArgs e)
at OpenDental.DataValid.OnBecameInvalid(ValidEventArgs e)
at OpenDental.DataValid.SetInvalid(DateTime date)
at OpenDental.ContrAppt.SetInvalid()
at OpenDental.ContrAppt.OnBreak_Click()
at OpenDental.ContrAppt.butBreak_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


************** Loaded Assemblies **************
mscorlib
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2115.0 built by: NET47REL1LAST
CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll
----------------------------------------
OpenDental
Assembly Version: 14.3.35.0
Win32 Version: 14.3.35.0
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/OpenDental.exe
----------------------------------------
System
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2634.0 built by: NET471REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Windows.Forms
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2110.0 built by: NET47REL1LAST
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Windows.Forms/v4.0_4.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System.Drawing
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2046.0 built by: NET47REL1
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Drawing/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Configuration
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2046.0 built by: NET47REL1
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Core
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2634.0 built by: NET471REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
----------------------------------------
System.Xml
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2612.0 built by: NET471REL1LAST_B
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
CodeBase
Assembly Version: 7.1.0.0
Win32 Version: 7.1.0.0
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/CodeBase.DLL
----------------------------------------
OpenDentBusiness
Assembly Version: 14.3.35.0
Win32 Version: 14.3.35.0
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/OpenDentBusiness.DLL
----------------------------------------
NHunspell
Assembly Version: 0.9.6.0
Win32 Version: 0.9.6.0
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/NHunspell.DLL
----------------------------------------
SparksToothChart
Assembly Version: 4.7.1.0
Win32 Version: 4.7.1.0
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/SparksToothChart.DLL
----------------------------------------
Microsoft.DirectX.Direct3D
Assembly Version: 1.0.2902.0
Win32 Version: 9.05.132.0000
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/Microsoft.DirectX.Direct3D.DLL
----------------------------------------
xImageDeviceManager
Assembly Version: 1.0.0.0
Win32 Version: 1.0.0.0
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/xImageDeviceManager.DLL
----------------------------------------
SigPlusNET
Assembly Version: 1.1.4050.24928
Win32 Version:
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/SigPlusNET.DLL
----------------------------------------
msvcm90
Assembly Version: 9.0.30729.9279
Win32 Version: 9.00.30729.9279
CodeBase: file:///C:/WINDOWS/WinSxS/x86_microsoft.vc90.crt_1fc8b3b9a1e18e3b_9.0.30729.9279_none_50939ec6bcb7c97c/msvcm90.dll
----------------------------------------
System.Web.Services
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2046.0 built by: NET47REL1
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Web.Services/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Web.Services.dll
----------------------------------------
OpenDental.XmlSerializers
Assembly Version: 14.3.35.0
Win32 Version: 14.3.35.0
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/OpenDental.XmlSerializers.DLL
----------------------------------------
AxInterop.AcroPDFLib
Assembly Version: 1.0.0.0
Win32 Version: 1.0.0.0
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/AxInterop.AcroPDFLib.DLL
----------------------------------------
System.Data
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2623.0 built by: NET471REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_32/System.Data/v4.0_4.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
CDT
Assembly Version: 12.3.20.0
Win32 Version: 12.3.20.0
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/CDT.DLL
----------------------------------------
MySql.Data
Assembly Version: 6.2.4.0
Win32 Version: 6.2.4.0
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/MySql.Data.DLL
----------------------------------------
System.Transactions
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2046.0 built by: NET47REL1
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_32/System.Transactions/v4.0_4.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
Oracle.DataAccess
Assembly Version: 2.112.2.0
Win32 Version: 2.112.2.0
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/Oracle.DataAccess.DLL
----------------------------------------
System.EnterpriseServices
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2046.0 built by: NET47REL1
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_32/System.EnterpriseServices/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
----------------------------------------
System.Numerics
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2046.0 built by: NET47REL1
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_MSIL/System.Numerics/v4.0_4.0.0.0__b77a5c561934e089/System.Numerics.dll
----------------------------------------
System.Web
Assembly Version: 4.0.0.0
Win32 Version: 4.7.2623.0 built by: NET471REL1LAST_C
CodeBase: file:///C:/WINDOWS/Microsoft.Net/assembly/GAC_32/System.Web/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Web.dll
----------------------------------------
Ionic.Zip
Assembly Version: 1.9.1.4
Win32 Version: 1.9.1.4
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/Ionic.Zip.DLL
----------------------------------------
Tao.OpenGl
Assembly Version: 2.1.0.4
Win32 Version: 2.1.0.4
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/Tao.OpenGl.DLL
----------------------------------------
Tao.Platform.Windows
Assembly Version: 1.0.0.4
Win32 Version: 1.0.0.4
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/Tao.Platform.Windows.DLL
----------------------------------------
Health.Direct.Common
Assembly Version: 1.2.0.6
Win32 Version: 1.2.0.6
CodeBase: file:///C:/Program%20Files%20(x86)/Open%20Dental/Health.Direct.Common.DLL
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

Mifa
Posts: 141
Joined: Wed Nov 21, 2007 6:52 pm
Location: Saint-Bruno, QC, Canada
Contact:

Re: Primary key duplicate

Post by Mifa » Mon Feb 26, 2018 12:03 pm

OK, problem solved. The database maintenance tool has repaired it. We never have problems with OD, that's why it took me so long to think about using it... :D

User avatar
cmcgehee
Posts: 711
Joined: Tue Aug 25, 2015 5:06 pm
Location: Salem, Oregon

Re: Primary key duplicate

Post by cmcgehee » Mon Feb 26, 2018 12:22 pm

What I would try first is REPAIR TABLE signalod. That often works for me when I get the duplicate primary key error. It would be a good idea to backup your database before running this command.
Chris McGehee
Open Dental Software
http://www.opendental.com

tgriswold
Posts: 122
Joined: Fri Jun 07, 2013 8:52 am

Re: Primary key duplicate

Post by tgriswold » Mon Feb 26, 2018 1:48 pm

Looks like you figured it out, but if you're curious why this happens its because mysql was in the middle of inserting a row into the table when the power cut out, so the row got inserted but the auto-increment didn't get incremented. Its fairly common when power is interrupted during use, and DBM should take care of it in the future. And if you can't run DBM for some reason, then what Chris posted is the next step. But as he mentioned always backup before you run a repair command because if you have some larger data corruption than just autoincrement it can cause you to lose data from the table, and OD support techs are also always available to help with these issues if you call in.
Travis Griswold
Open Dental Software
http://www.opendental.com

Post Reply