Hi everyone. If anyone has any insight to give on this debacle, I would greatly appreciate it.
One of our administrators went to admin>layouts>demographics and accidentally erased the “who” group. so that patient data was gone from the database. Then I went to the “localdisk> xampp> mysql> data> openemr>patient_data.MYD (and patient_data.MYI)”, right clicked and tried to restore previous versions of the two before the “Who” group in the layout was erased. When I did that, I logged back in to openemr and and received this error …
" ERROR: query failed: SELECT pnotes.id, pnotes.user, pnotes.pid, pnotes.title, pnotes.date, pnotes.message_status, IF(pnotes.user != pnotes.pid,users.fname,patient_data.fname) as users_fname, IF(pnotes.user != pnotes.pid,users.lname,patient_data.lname) as users_lname, patient_data.fname as patient_data_fname, patient_data.lname as patient_data_lname FROM ((pnotes LEFT JOIN users ON pnotes.user = users.username) JOIN patient_data ON pnotes.pid = patient_data.pid) WHERE pnotes.message_status != ‘Done’ AND pnotes.activity = 1 AND pnotes.deleted != ‘1’ AND pnotes.assigned_to LIKE ?"
And in the admin>database> patient_data section , i received this error…
Error
SQL query: Edit
SHOW INDEX FROM patient_data ;
MySQL said: Documentation #1034 - Incorrect key file for table ‘patient_data’; try to repair it
“Incorrect key file for table ‘patient_data’; try to repair it”
All patient data is now missing. I’m trying to stay calm. Lesson learned. I’m not gonna try to race a tiger or box a kangaroo ever again. If anyone has any tips please share with me.
Sorry. i hope this clarifies: I went on the “server computer” that is hosting and sharing openemr though the network. And then i went to " computer localdisk> xampp> mysql> data> openemr>patient_data.MYD (and patient_data.MYI)". If you right click on those MYI or MYD files gives you the option to restore to previous versions.
The back up is unavailable at this point.
I saved the remaining patient data from the first mystake in a CSV file
I am in a pickle
I read about “myisamchk”. Do you think that may help?
Make a new OpenEMR installation or go to one of the OpenEMR Demo versions comparable to your own version and make a small sample of the missing data table you try to Restore.
Export this Demo-table to you local computer. Now make a comparison between the two files and correct your backup CSV file. After done that try to Restore. If this fails, you might have a problem with deleted/corrupted fields in the table.
It’s very upsetting for any of us to lose data. If there is a backup, albeit unavailable at present, it’s not catastrophic.
Because a server is more complex than a desktop, it would be wise to enlist professional support rather than to wing it yourself. Here is a list of U.S. certified vendors if you have no one to assist you.
Alternatively you can click on Kevin’s name and email him a phone number where you can be reached. You have to be logged in to send the message. Kevin is an IT professional as well as a physician.
After the data has been recovered, deterrents must be put into place to prevent such accidents from happening again. It goes without saying, a robust backup and recovery system is vital.
In the meantime, take out pen and paper until the system has been restored. Fortunately our clinical skills are not dependent upon computers.
I learned my lesson, I am not touching that computer. It is not an actual server. It is a desktop that stores files and shares them with the other desktops. Sorry for the confusion. I believe i will need expertise on this matter.
It is one of the ‘quirks’ of core design - demographics uses layout forms for flexibility. Unfortunately when you delete an entry (instead of setting it to be unused) in that editor, it drops the field from underlying database table. Every few weeks someone posts the same issue with layouts.
May be the core developers of the package need to hide the delete button when working with non LBFxxx type of layouts or force the unsuspecting administrators save their skin by changing the field status to ‘unused’.
Dr. Smith, suggest you to do the following:
Keep current table intact - it contains the ‘id’ that will be needed during restore.
Add the fields in ‘who’ tab - you can use demo for reference or use Admin->Other->Database to create new fields in patient_data manually. That will stop the errors from being displayed.
If you have any reasonable backup, restore ‘patient_data’ table from that backup as ‘patient_data_old’.
Use database update mechanism to copy affected fields from ‘patient_data_old’ table to ‘patient_data’ using ‘id’ as the only matching criteria.
Run database query listing all records with blank fields - mainly pubpid or pid. These are patient master records added since the backup you used to restore partial data.
Hopefully that will be very short list if your backup is recent. If you can identify the information from the other fields, you can look at either appointments or encounters table to get pid / pubpid value to establish links to rest of EMR information.
The log table viewer may be an excellent source to get some additional information to help in restore process.
If you restore only the entire patient_data from backup and decide to add new patients, be aware of the risk of mismatched keys unless you recreate the data in exactly same order. Not sure if the id counters are stored elsewhere or calculated on the fly. Of course that is not a problem if you restore entire database in which case you will reenter all other data.
Please follow below steps to recover deleted data from backup.
Note: Copy xampp mysql folder before continuing with below steps (this is to ensure you have existing data available).
1. Stop Mysql
2. Restore patient_data.MYD, patient_data.MYI, patient_data.frm to the server
3. Restore list_options.MYD, list_options.MYI, list_options.frm to the server
4. Start Mysql
5. In windows, To check table using "myisamchk" command, use command prompt "localdisk>xampp>mysql>bin>myisamchk.exe -r localdisk>xampp>mysql\data\db_name\table_name"
Above steps will help you to completely restoring the table & it’s data. Please do check and let us know if any issues.
I am sorry to revive this thread… but we are encountering this same error as of last night following a server crash/restart.
Incorrect key file for table ‘patient_data’; try to repair it
I have tried copying older versions (we have an automated back-up that saves the last 10 versions of a changed file), but no matter which version is restored, the same error occurs.
I have followed the suggestion to use myiasmchk command, that did not work - no error message.
I need HELP… we have many patients that will come to our office in the morning…
Turns out that not the actual patient table (so it appears) was broken but the keyfile itself. To add to the problem, the main server our EMR is hosted on did not have enough working memory to allow for a the repair commands to function. I then copied backup versions of all three files (*.myd, *.frm, and .myi) to a different server with more memory, ran the command line mysql repair, then was able to check the tables. I then copied it back over to the original server, and - voila, all is working.