Restore of OpenEMR 4.2.2 system failed - help REALLY needed

Management and Administration
#1

I helped set up an OpenEMR 4.2.2 system on a Windows 7 computer for a rural clinic in Honduras. The computer they are using as a server has failed but the external backups exist. There was a complete (c:\xampp) backup made shortly after the system was set up in March and “daily” backups made every 30 minutes since. I adapted a backup script I found on this site to create the daily backups that I will include below. I coached the local physician to restore the “complete” backup to another computer and it does run but without the clinic data that has been added since the initial deployment. She ran the script to restore the latest daily back up from earlier today but gets errors when trying to bring the system backup.

After restarting apache and mysql and connecting via the browser, the following errors are reported:
ERROR: query failed: SELECT gl_name, gl_index, gl_value FROM globals ORDER BY gl_name, gl_index
Error: Can’t find file: ‘_\openemr\globals.MYT’ (errno: 2 “No such file or directory”)

C:\xampp\htdocs\openemr\interface\globals.php at 202:sqlSatement
C:\xampp\htdocs\openemr\interface\login\login_frame.php at 3:include_once(C:\xampp\htdocs\openemr\interface\global

The script that backed up essential files uses the following xcopy commands, the restore script puts the same back in place. Note backup_loc would be set to the external drive (f:\oemr and hoy would be the current day)

if exist %bkup_loc%%hoy%\openemr\ copy %bkup_loc%%hoy%\openemr c:\xampp\mysql\data\openemr
if exist %bkup_loc%%hoy%\documents\ copy %bkup_loc%%hoy%\documents c:\xampp\htdocs\openemr\documents
if exist %bkup_loc%%hoy%\edi\ copy %bkup_loc%%hoy%\edi c:\xampp\htdocs\openemr\edi
if exist %bkup_loc%%hoy%\era\ copy %bkup_loc%%hoy%\era c:\xampp\htdocs\openemr\era
if exist %bkup_loc%%hoy%\custom\letter_templates\ copy %bkup_loc%%hoy%\custom\letter_templates c:\xampp\htdocs\openemr\custom\letter_templates

I am here in the US and trying to help via phone so any help at all would be greatly appreciated -thanks

#2

hi @BenBriggs, have you verified any of the daily backups?

#3

hi @stephenwaite, thanks for your response. The complete and daily backups are being copied up to a google drive now so I should have them today to work with (assuming their internet connection stays up). My plan is to start with the complete backup and verify that the system runs okay first. After that I will restore the latest daily backup and see what happens - this is what they did in the clinic and it failed. I am afraid I do not have experience beyond trying each of the 6 daily backups one at a time and hoping one will work. What specifically did you mean by “verified” any of the daily backups?

#4

hi @BenBriggs, that you were able to independently restore the data from the backup file. Good luck, here to help.

#5

@stephenwaite, I was able to get the old “complete backup” and daily backup files from the clinic last night. They did not run complete backups on a regular bases, or at all after my son brought the system down to them in March, from what I can see. I restored my own system from their old complete backup (which is a file by file copy of the \xampp directory and an \openemr directory I set up to hold backup scripts and documentation). After restoring the old complete backup but without layering on any of the daily backups I can start apache and mysql but get the following error when trying to log in via my browser:
ERROR: query failed: SELECT * FROM lang_definitions JOIN lang_constants ON lang_definitions.cons_id = lang_constants.cons_id WHERE lang_id=? AND constant_name = ? LIMIT 1

Error: Can’t find file: ‘.\openemr\lang_definitions.MYI’ (errno: 2 “No such file or directory”)

C:\xampp\htdocs\openemr\library\translation.inc.php at 36:sqlStatementNoLog
C:\xampp\htdocs\openemr\interface\globals.php at 350:xl((More))
C:\xampp\htdocs\openemr\interface\login\login_frame.php at 3:include_once(C:\xampp\htdocs\openemr\interface\globals.php)

restoring the daily did not fix the problem ( that was sort of a lame wish) so I need assistance from this point and I do have all the code locally and can work with it. -much thanks for your assistance.

#6

Hi BenBriggs, It could be a problem with the sql-mode on your database mysql, the sql_mode needs to be empty. I just fix a similar restored fail few days ago. I’m happy that Brady helped me. I know that yours is windows and mine was ubuntu. If you run out of options let me know and I will share with you the steps I did to fix it.
Roberto

#7

Hi @BenBriggs, maybe @robertovasquez is on to something there. Did you restore to the c:\ directory?

#8

@robertovasquez and @stephenwaite - I copied the c:\xampp directory from the clinic’s complete backup to my c: directory. This is what allows me to start apache and mysql but does not allow me to log in. I believe and hope that all of the clinic’s patient data is in the daily backup files. Is it reasonable to hope that if I empty the sql_mode in c:\xampp that I would then be able to restore from a daily backup over that? I know very little about mysql, how would I go about emptying sql_mode?

#9

We can try to set the sql_mode=‘empty’;
How I do is using the command line terminal $
and log into mysql:
mysql -u root -p
mysql>SELECT @@SQL_MODE; //will tell you what sql_modes your mysql has right now, it needs to be empty
mysql>SET GLOBAL sql_mode=’’; // the ’ ’ with no space between will set sql_mode to empty

mysql>SELECT @@SQL_MODE; //will show the sql_mode empty. this is TEMPORARY

Know run openemr to see. If it works we will change the sql_mode permanently.

I have no idea how to do this in windows. Do not give up @BenBriggs

#10

other OPTION that I would try is to set it up
a computer with the same same same windows version, patch; same mysql version patch, same php version patch that the original computer it was running at the clinic AND them restore the back up in that computer.

#11

@robertovasquez, thanks once again for your help. It does not appear that this has done anything. Below are the exact steps I followed and the results - any thoughts?

cd c:\xampp\mysql\bin
mysql -u root -p

MariaDB [(none)]>SELECT @@SQL_MODE;
±-----------------------------
| @@SQL_MODE
±-----------------------------
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
±-----------------------------
1 row in set (0.00 sec)

MariaDB [(none)]>SET GLOBAL sql_mode=’’;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>SELECT @@SQL_MODE;
±-----------------------------
| @@SQL_MODE
±-----------------------------
| NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
±-----------------------------
1 row in set (0.00 sec)

stop mysql
start apache
start mysql
open browser and attempt to connect to localhost/openemr

ERROR: query failed: SELECT * FROM lang_definitions JOIN lang_constants ON lang_definitions.cons_id = lang_constants.cons_id WHERE lang_id=? AND constant_name = ? LIMIT 1

Error: Can’t find file: ‘.\openemr\lang_definitions.MYI’ (errno: 2 “No such file or directory”)

C:\xampp\htdocs\openemr\library\translation.inc.php at 36:sqlStatementNoLog
C:\xampp\htdocs\openemr\interface\globals.php at 350:xl((More))
C:\xampp\htdocs\openemr\interface\login\login_frame.php at 3:include_once(C:\xampp\htdocs\openemr\interface\globals.php)

#12

TRY mysql>SET sql_mode=’’;
mysql>SELECT @@SQL_MODE;

#13

the sql_mode have to be @@SQL_MODE //EMPTY

#14

hi @BenBriggs, for starters are you able to show databases from the mysql prompt, and hopefully then

USE openemr;

database and run some queries to see if there is any data in the tables?``

SELECT * FROM patient_data;
#15

@stephenwaite Thank you again for the help. I logged in to mysql and first tried the SET sql_mode=’’; and SELECT @@SQL_MODE; commands with results similar to what I reported earlier. I then executed the USE openemr; command and the prompt did change to MariaDB[openemr]>. I then ran the SELECT * FROM patient_data; command got the following response:
ERROR 1146 942S02): Tabke ‘openemr.patient_data’ doesn’t exist.

I then tried restoring a daily backup over the complete backup but got similar results.

Any thoughts? There should be several months of patient records in there. -thanks!!

#16

hi @BenBriggs, sounds like you’re working with an empty database. You should at least be able to achieve this: [quote=“BenBriggs, post:1, topic:8620”]
I coached the local physician to restore the “complete” backup to another computer and it does run but without the clinic data that has been added since the initial deployment.
[/quote]

#17

@stephenwaite It does not surprised me that the complete backup does not have patient records since that was made before they started using the system and probably not since. The daily backups “should” contain patient records though. So 2 things:

1 - I need to get past the hump of not being able to log into the system - is this error message when I attempt to connect something we can deal with:
ERROR: query failed: SELECT * FROM lang_definitions JOIN lang_constants ON lang_definitions.cons_id = lang_constants.cons_id WHERE lang_id=? AND constant_name = ? LIMIT 1

Error: Table ‘openemr.lang_definitions’ doesn’t exist

C:\xampp\htdocs\openemr\library\translation.inc.php at 36:sqlStatementNoLog
C:\xampp\htdocs\openemr\interface\globals.php at 350:xl((More))
C:\xampp\htdocs\openemr\interface\login\login_frame.php at 3:include_once(C:\xampp\htdocs\openemr\interface\globals.php)

And 2: The daily backup script I wrote, which was derived from a posting on this forum, the testing I did before sending the system down to Honduras indicated it worked to backup and restore patient and account records - does it look right to you (the script is much longer but here is are the essential copy commands)?
bkup_loc is set earlier in the script to the location of their external drive, such as “e:\oemr_bkup”
and hoy is the current day of the week so we would have the most recent 7 days preserved.

xcopy c:\xampp\mysql\data\openemr*.* %bkup_loc%%hoy%\openemr\ /d /e /c /i /f /h /k /y
xcopy c:\xampp\htdocs\openemr\documents*.* %bkup_loc%%hoy%\documents\ /d /e /c /i /f /h /k /y
xcopy c:\xampp\htdocs\openemr\edi*.* %bkup_loc%%hoy%\edi\ /d /e /c /i /f /h /k /y
xcopy c:\xampp\htdocs\openemr\era*.* %bkup_loc%%hoy%\era\ /d /e /c /i /f /h /k /y
xcopy c:\xampp\htdocs\openemr\custom\letter_templates*.* %bkup_loc%%hoy%\letter_templates\ /d /e /c /i /f /h /k /y

#18

hi @BenBriggs, it’s not that there are no records it’s that there are no tables

#19

@stephenwaite ok that it bad. So is there anything I can do other than try to get the original SSD drive from the clinic or see if they can get the original computer fixed? It does not appear to have failed due to a drive issue. I really wish I could figure out why these backups doe not appear to contain any data tables. - thanks once again for your help.

#20

@BenBriggs it could be that your copy from the google drive is flawed or the process copying from the drive to the xampp is flawed