Strange Unhandled Exception

[Tom Zaccaria]

When trying to restore a backup at home I am getting;

Unhandled exception from ProgramEntry: Table '.\opendental\sigmessage' is marked as crashed and should be repaired
MySql.Data.MySqlClient.MySqlException
at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at OpenDentBusiness.DataConnection.<>c__DisplayClass81_0.<GetTable>b__1() in C:\Development\OPEN DENTAL SUBVERSION\opendental17.4\OpenDentBusiness\Db\DataConnection.cs:line 610
at OpenDentBusiness.DataConnection.RunDbAction(Action actionDb, DbConnection connection) in C:\Development\OPEN DENTAL SUBVERSION\opendental17.4\OpenDentBusiness\Db\DataConnection.cs:line 949
at OpenDentBusiness.DataConnection.GetTable(String command, Boolean hasConnLost) in C:\Development\OPEN DENTAL SUBVERSION\opendental17.4\OpenDentBusiness\Db\DataConnection.cs:line 610
at OpenDentBusiness.DataCore.GetTable(String command) in C:\Development\OPEN DENTAL SUBVERSION\opendental17.4\OpenDentBusiness\Db\DataCore.cs:line 14
at OpenDentBusiness.Db.GetTableWithRemotingRoleCheck(String command) in C:\Development\OPEN DENTAL SUBVERSION\opendental17.4\OpenDentBusiness\Db\Db.cs:line 76
at OpenDentBusiness.Db.GetTable(String command) in C:\Development\OPEN DENTAL SUBVERSION\opendental17.4\OpenDentBusiness\Db\Db.cs:line 42
at OpenDentBusiness.Crud.SigMessageCrud.SelectMany(String command) in C:\Development\OPEN DENTAL SUBVERSION\opendental17.4\OpenDentBusiness\Crud\SigMessageCrud.cs:line 36
at OpenDentBusiness.SigMessages.GetSigMessagesSinceDateTime(DateTime dateTimeSince) in C:\Development\OPEN DENTAL SUBVERSION\opendental17.4\OpenDentBusiness\Data Interface\SigMessages.cs:line 73
at OpenDental.ContrStaff.RefreshFullMessages() in C:\Development\OPEN DENTAL SUBVERSION\opendental17.4\OpenDental\Main Modules\ContrStaff.cs:line 1431
at OpenDental.ContrStaff.InitializeOnStartup() in C:\Development\OPEN DENTAL SUBVERSION\opendental17.4\OpenDental\Main Modules\ContrStaff.cs:line 813
at OpenDental.FormOpenDental.FormOpenDental_Load(Object sender, EventArgs e) in C:\Development\OPEN DENTAL SUBVERSION\opendental17.4\OpenDental\Main Modules\FormOpenDental.cs:line 2724
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.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)

Any help would be appreciated
drtmz

[Tom Zaccaria]

I was able to run the database maintenance tool on the server, take a new backup and restore with no problem.

drtmz
OD 17.3.66

[cmcgehee]

With this sort of error, you can make a backup of the database, run the REPAIR TABLE command for the table that is crashed, and that should usually take care of it. I would still recommend calling support in this situation because sometimes the REPAIR TABLE command doesn't completely work.

[jsalmon]

A lot of "hot backups" will have this problem because the tables are still being used which runs the risk of having the tables flagged as corrupt. If using a 3rd party software to make backups of Open Dental database files, make sure to stop the MySQL service in order to get a guaranteed good backup (tables won't be in use). Backups made from within Open Dental will not have this problem because they utilize MySQL in order to make the backup (thus each table will wait in line for a lock on the table before backing it up).

We here at HQ continue to use "hot backups" and just accept the fact that some tables will be flagged as corrupt and will either discard the backup (using only verified backups) or will repair them (as Chris mentioned).
Note: running the repair table command has the potential to lose partial or all of the data in the table depending on how MySQL deems it necessary to repair the table.

[boboffice]

Would it be possible for the back-up function to automatically stop the necessary services, back up, then restart them? Better for the average user.

[Tom Zaccaria]

Although I did not stop mysql noone was logged in at the time of the backup.

drtmz

[jsalmon]

Would it be possible for the back-up function to automatically stop the necessary services, back up, then restart them? Better for the average user.

Yes, MySQL is just a typical service (on any Operating System) so whatever application you are using as the "back-up function" simply needs to get privileges to start and stop said service.

[jsalmon]

Although I did not stop mysql noone was logged in at the time of the backup.

Open Dental doesn't technically need to be logged in to run queries, it simply needs to be running (e.g. a computer set as the "Computer Name to Receive New Email from" can download emails while no user is logged in).
http://www.opendental.com/manual/emailsetup.html

[jsalmon]

...If using a 3rd party software to make backups of Open Dental database files, make sure to stop the MySQL service in order to get a guaranteed good backup (tables won't be in use). Backups made from within Open Dental will not have this problem because they utilize MySQL in order to make the backup (thus each table will wait in line for a lock on the table before backing it up).

A little clarification; "Automatic backups made from Upgrading versions of Open Dental will not have this problem..."
I happened to be in the neighborhood of our Backup/Restore Tool code and the Backup button does not stop the service, thus making a "hot backup" which could have the aforementioned problem.
http://www.opendental.com/manual/backuptool.html

Also, our Backup/Restore Tool is not acceptable for users utilizing InnoDB table types.