Restore error

rdh61 wrote on Monday, June 20, 2016:

I have version 4.2.1 installed on Lubuntu 15.10. I have beeen trying to restore a backup, but get the following error.

ERROR: insert failed: INSERT INTO log_comment_encrypt (log_id, encrypt, checksum) VALUES ( ‘35106’,‘No’,’’)

Error: Table ‘openemr_it.log_comment_encrypt’ doesn’t exist

/var/www/openemr/library/log.inc at 49:sqlInsertClean_audit
/var/www/openemr/library/authentication/login_operations.php at 116:newEvent(login,admin,Default,1,success: 127.0.0.1)
/var/www/openemr/library/auth.inc at 50:validate_user_password(admin,Default)
/var/www/openemr/interface/globals.php at 352:include_once(/var/www/openemr/library/auth.inc)
/var/www/openemr/interface/main/main_screen.php at 25:require_once(/var/www/openemr/interface/globals.php)

Is this recuperable?

Thank you.

gowepp wrote on Tuesday, June 21, 2016:

Hello Robert,

Please try backup and restore process again.
And share the steps how you are restoring the database.

Thanks,
Wepp Team

rdh61 wrote on Tuesday, June 21, 2016:

Hi, thanks for your reply. I used the restore script. Please see terminal output below. I forgot to say that the above error is shown once I try to log in to OpenEMR in my browser.

robert@robert-VirtualBox:~$ sudo ./restore
[sudo] password for robert: 
WARNING: This script is experimental.
It may have serious bugs or omissions.
Use it at your own risk!
Now you will be asked for the backup file.
By default this is named emr_backup.tar, although you may have saved it as something else.
 
Enter path/name of backup file: /home/robert/emr_backup_it.tar
 
Extracting /home/robert/emr_backup_it.tar ...
Extracting /tmp/emr_backup/openemr.tar.gz ...
 
Do you want to specify site ID, locations or database names for the restore? [N/y] 
 
If you have a particular requirement for the UTF-8 collation to use, 
then please specify it here.  Hit Enter to accept the default 'utf8_general_ci'.
Enter 'none' if you do not want UTF-8.
 
UTF-8 collation [utf8_general_ci]?  
 
Your Site ID will be 'italy'.
Only site-specific files will be restored to '/var/www/openemr/sites/italy' in the existing OpenEMR web directory.
I will restore the OpenEMR database backup to the MySQL database 'openemr_it'.
The OpenEMR database user will be 'openemr' with password 'G64WiuKPLE5h'.
MySQL will use character set 'utf8' with collation 'utf8_general_ci'.
 
Please check the above very carefully!
Any existing databases and directories matching these names will be DESTROYED.
Do you wish to continue? [N/y] y
 
In order to create MySQL databases and users on this computer, I will need to
log into MySQL as its 'root' user.  The next question asks for the MySQL root
user's password for this server, the one that you are restoring to.  This is
a MySQL password, not a system login password.  It might be blank.
 
Enter the password, if any, for the MySQL root user: XXXXX
 
Dropping old OpenEMR database if it exists ...
Database "openemr_it" dropped
Restoring OpenEMR database ...
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Restoring site subdirectory ...
All done.
robert@robert-VirtualBox:~$ 

rdh61 wrote on Thursday, June 23, 2016:

I’m getting a little nervous here now. Is there anyone who knows something which may help me restore my backup? Many thanks.

sunsetsystems wrote on Thursday, June 23, 2016:

Script output indicates that /var/www/openemr already exists and so is not being overwritten. Perhaps you are restoring an older release? Try renaming the existing directory to openemr.old and then re-run the restore. If you are upgrading that needs to happen after the restore.

Also, by the way, never trust your backup process until you have successfully tested the corresponding restore.

Rod
http://www.sunsetsystems.com/

rdh61 wrote on Friday, June 24, 2016:

Yes, that worked thank you. I didn’t think I had backed up from an older version of OpenEMR, but I’m not in a position to check that right now. The reason there was already a www/openemr directory is that in the past a situation arose (the details of which to be honest I can’t remember) where I thought that I needed two sites, named ‘default’ and ‘italy’, for two different databases. What I have been trying to do now is to use a backup file and the restore script to transfer my databases from one computer to another. ‘default’ worked, ‘italy’ didn’t. However, as you suggested, I renamed the original openemr directory and created a new one. I then restored ‘italy’ to the new directory, and copied over ‘default’. Now both work. Thank you very much.

sunsetsystems wrote on Friday, June 24, 2016:

You’re welcome, good to hear you are up and running.

Rod
http://www.sunsetsystems.com/

rdh61 wrote on Monday, July 11, 2016:

Unfortunately, this same problem has recurred in a different context. First let me answer Rod’s question: “Perhaps you are restoring an older release?” No, the versions of OpenEMR on which the backup was made and restored to were the same: 4.1.2.

The new context is the following. I have upgraded the software on which the backup was made to 4.2.2. Before the upgrade I could log in to both http://localhost/openemr/interface/main/main_screen.php?auth=login&site=default and http://localhost/openemr/interface/main/main_screen.php?auth=login&site=italy . After the upgrade, I can log in to “default”, but when I try to log in to “italy”, I get the same error as before:

ERROR: insert failed: INSERT INTO log_comment_encrypt (log_id, encrypt, checksum) VALUES ( ‘35106’,‘No’,’’)

Error: Table 'openemr_it.log_comment_encrypt' doesn't exist

/var/www/openemr/library/log.inc at 49:sqlInsertClean_audit
/var/www/openemr/library/authentication/login_operations.php at 116:newEvent(login,admin,Default,1,success: 127.0.0.1)
/var/www/openemr/library/auth.inc at 50:validate_user_password(admin,,Default)
/var/www/openemr/interface/globals.php at 352:include_once(/var/www/openemr/library/auth.inc)
/var/www/openemr/interface/main/main_screen.php at 25:require_once(/var/www/openemr/interface/globals.php)

This time if I try Rod’s strategy of renaming the existing directory to openemr.old and then running restore, I can log in to both sites again, but then I see that the version of OpenEMR has reverted to 4.1.2.

Any further ideas what to do?

Many thanks.

rdh61 wrote on Thursday, July 14, 2016:

Any thoughts?

sunsetsystems wrote on Thursday, July 14, 2016:

Not sure what exactly you did, but sounds like your “italy” database has not yet been upgraded to the new release. Try going to http://localhost/openemr/admin.php?site=italy and it will probalby give you a link for the database upgrade.

When the upgrade completes, be sure to scroll through the listing of messages to check for any errors (which would be in red).

Rod
http://www.sunsetsystems.com/

rdh61 wrote on Thursday, July 14, 2016:

I simply downloaded the 4.2.2 deb package and installed in the normal way - it installed over the previous version, apparently without errors.

When I go to http://localhost/openemr/admin.php?site=italy I can see both “italy” and “default”. “italy” is listed as being version 4.1.2 (7), while “default” is 4.2.1 (7). Clicking the “Upgrade Database” link for either takes me to a blank page where there is no evidence of anything happening! The address of the “italy” upgrade database page is: http://localhost/openemr/sql_upgrade.php?site=italy. Even if I wait and wait, nothing happens there…

sunsetsystems wrote on Thursday, July 14, 2016:

A blank sql_upgrade.php page is definitely a problem and would make it hard to upgrade the database! Check your Apache error log to find out why it crashed.

Rod
http://www.sunsetsystems.com/

bradymiller wrote on Thursday, July 14, 2016:

Hi,

deb package “secures” that script:

following command should allow it to be run:
sudo chmod 666 /var/www/openemr/sql_upgrade.php

Sound like an error happened in the upgrade. There are 2 files (1 in /etc/openemr/ and 1 in /var/log/openemr/ ) that can be used to debug. Feel free to paste the output of them here.

-brady

rdh61 wrote on Friday, July 15, 2016:

The plot thickens…

I used the following command: sudo chmod 666 /var/www/openemr/sql_upgrade.php

That allowed “default” to upgrade (at least I am assuming it did, see note below), but not “italy”. The latter gave:

Not Found
The requested URL /login/loginframe.php was not found on this server.
Apache/2.4.7 (Ubuntu) Server at localhost Port 80

Note: The assumed successful upgrade of “default” gives an output that leaves one somewhat hanging in the air…

OpenEMR Database Upgrade
Processing 4_2_1-to-4_2_2_upgrade.sql ...
INSERT INTO `clinical_rules` ( `id`, `pid`, `active_alert_flag`, `passive_alert_flag`, `cqm_flag`, `cqm_nqf_code`, `cqm_pqri_code`, `amc_flag`, `amc_code`, `patient_reminder_flag` ) VALUES ('rule_blood_pressure', 0, 0, 0, 0, '', '', 0, '', 0)
INSERT INTO `list_options` ( `list_id`, `option_id`, `title`, `seq`, `is_default` ) VALUES ('clinical_rules', 'rule_blood_pressure', 'Measure Blood Pressure', 1610, 0)
INSERT INTO `rule_action` ( `id`, `group_id`, `category`, `item` ) VALUES ('rule_blood_pressure', 1, 'act_cat_measure', 'act_bp')
INSERT INTO `rule_reminder` ( `id`, `method`, `method_detail`, `value` ) VALUES ('rule_blood_pressure', 'clinical_reminder_pre', 'week', '2')
INSERT INTO `rule_reminder` ( `id`, `method`, `method_detail`, `value` ) VALUES ('rule_blood_pressure', 'clinical_reminder_post', 'month', '1')
INSERT INTO `rule_reminder` ( `id`, `method`, `method_detail`, `value` ) VALUES ('rule_blood_pressure', 'patient_reminder_pre', 'week', '2')
INSERT INTO `rule_reminder` ( `id`, `method`, `method_detail`, `value` ) VALUES ('rule_blood_pressure', 'patient_reminder_post', 'month', '1')
INSERT INTO `rule_target` ( `id`, `group_id`, `include_flag`, `required_flag`, `method`, `value`, `interval` ) VALUES ('rule_blood_pressure', 1, 1, 1, 'target_database', '::form_vitals::bps::::::ge::1', 0)
INSERT INTO `rule_target` ( `id`, `group_id`, `include_flag`, `required_flag`, `method`, `value`, `interval` ) VALUES ('rule_blood_pressure', 1, 1, 1, 'target_database', '::form_vitals::bpd::::::ge::1', 0)
INSERT INTO `clinical_rules` ( `id`, `pid`, `active_alert_flag`, `passive_alert_flag`, `cqm_flag`, `cqm_nqf_code`, `cqm_pqri_code`, `amc_flag`, `amc_code`, `patient_reminder_flag` ) VALUES ('rule_inr_measure', 0, 0, 0, 0, '', '', 0, '', 0)
INSERT INTO `list_options` ( `list_id`, `option_id`, `title`, `seq`, `is_default` ) VALUES ('clinical_rules', 'rule_inr_measure', 'Measure INR', 1620, 0)
INSERT INTO `rule_action` ( `id`, `group_id`, `category`, `item` ) VALUES ('rule_inr_measure', 1, 'act_cat_measure', 'act_lab_inr')
INSERT INTO `rule_reminder` ( `id`, `method`, `method_detail`, `value` ) VALUES ('rule_inr_measure', 'clinical_reminder_pre', 'week', '2')
INSERT INTO `rule_reminder` ( `id`, `method`, `method_detail`, `value` ) VALUES ('rule_inr_measure', 'clinical_reminder_post', 'month', '1')
INSERT INTO `rule_reminder` ( `id`, `method`, `method_detail`, `value` ) VALUES ('rule_inr_measure', 'patient_reminder_pre', 'week', '2')
INSERT INTO `rule_reminder` ( `id`, `method`, `method_detail`, `value` ) VALUES ('rule_inr_measure', 'patient_reminder_post', 'month', '1')
INSERT INTO `rule_target` ( `id`, `group_id`, `include_flag`, `required_flag`, `method`, `value`, `interval` ) VALUES ('rule_inr_measure', 1, 1, 1, 'target_proc', 'INR::CPT4:85610::::::ge::1', 0)
SET @group_name = (SELECT group_name FROM layout_options WHERE field_id='lname' AND form_id='DEM')
SET @backup_group_name = (SELECT group_name FROM layout_options WHERE field_id='DOB' AND form_id='DEM')
SET @seq = (SELECT MAX(seq) FROM layout_options WHERE group_name = IFNULL(@group_name,@backup_group_name) AND form_id='DEM')
INSERT INTO `layout_options` (`form_id`, `field_id`, `group_name`, `title`, `seq`, `data_type`, `uor`, `fld_length`, `max_length`, `list_id`, `titlecols`, `datacols`, `default_value`, `edit_options`, `description`) VALUES ('DEM', 'billing_note', IFNULL(@group_name,@backup_group_name), 'Billing Note', @seq+1, 2, 1, 60, 0, '', 1, 3, '', '', 'Patient Level Billing Note (Collections)' )
ALTER TABLE patient_data ADD COLUMN billing_note text NOT NULL default ''
UPDATE `patient_data` SET `billing_note` = `genericval2` WHERE `genericname2` = 'Billing'
UPDATE `patient_data` SET `genericval2` = '', `genericname2` = '' WHERE `genericname2` = 'Billing'
ALTER TABLE `lang_languages` ADD COLUMN `lang_is_rtl` TINYINT DEFAULT 0
UPDATE `lang_languages` SET `lang_is_rtl`=1 WHERE `lang_code` IN ('he','ar') OR `lang_description` IN('Hebrew','Arabic')
ALTER TABLE `procedure_report` ADD COLUMN `date_collected_tz` varchar(5) DEFAULT '' COMMENT '+-hhmm offset from UTC'
ALTER TABLE `procedure_report` ADD COLUMN `date_report_tz` varchar(5) DEFAULT '' COMMENT '+-hhmm offset from UTC'
UPDATE `clinical_rules` SET `cqm_2014_flag` = 1 WHERE `id` = 'rule_influenza_ge_50_cqm' AND `pid` = 0
UPDATE `clinical_rules` SET `cqm_2014_flag` = 1 WHERE `id` = 'rule_dm_a1c_cqm' AND `pid` = 0
ALTER TABLE `lists` ADD COLUMN `subtype` varchar(31) NOT NULL DEFAULT ''
ALTER TABLE `list_options` ADD COLUMN `subtype` varchar(31) NOT NULL DEFAULT ''
INSERT INTO list_options (list_id,option_id,title) VALUES ('lists','issue_subtypes','Issue Subtypes')
INSERT INTO list_options (list_id, option_id,title, seq) VALUES ('issue_subtypes', 'eye', 'Eye',10)
UPDATE `clinical_rules` SET `amc_2014_stage1_flag` = 1, `amc_2014_stage2_flag` = 1 WHERE `id` = 'med_reconc_amc' AND `pid` = 0
UPDATE `clinical_rules` SET `amc_2014_stage1_flag` = 1, `amc_2014_stage2_flag` = 1 WHERE `id` = 'med_reconc_amc' AND `pid` = 0
Processing 4_2_2-to-4_3_1_upgrade.sql ...
Updating global configuration defaults...
Updating Access Controls...

rdh61 wrote on Friday, July 15, 2016:

I am attaching /etc/openemr/openemr.conf and /var/log/openemr/install

bradymiller wrote on Friday, July 15, 2016:

Hi,

Do in browser:

<browser_address>sql_upgrade.php?site=italy

The deb package should of upgraded both of these. Definitely interested to see what is in the debug files listed above.

-brady
OpenEMR

bradymiller wrote on Friday, July 15, 2016:

The upgrade script errored out here:

<p><p><font color='red'>ERROR:</font> query failed: UPDATE `clinical_rules` SET `cqm_2014_flag` = 1 WHERE `id` = 'rule_influenza_ge_50_cqm' AND `pid` = 0<p>Error: <font color='red'>Unknown column 'cqm_2014_flag' in 'field list'</font><p><br>/var/www/openemr/library/sql_upgrade_fx.php at 546:sqlStatement<br>/var/www/openemr/TEMPsql_upgrade.php at 56:upgradeFromSqlFile(4_2_1-to-4_2_2_upgrade.sql)SQL Error with statement:query failed: UPDATE `clinical_rules` SET `cqm_2014_flag` = 1 WHERE `id` = 'rule_influenza_ge_50_cqm' AND `pid` = 0--Unknown column 'cqm_2014_flag' in 'field list'==>/var/www/openemr/library/sql_upgrade_fx.php at 546:sqlStatement

bradymiller wrote on Friday, July 15, 2016:

Super weird, because that sql query worked in your upgrade script above:

UPDATE `clinical_rules` SET `cqm_2014_flag` = 1 WHERE `id` = 'rule_influenza_ge_50_cqm' AND `pid` = 0

bradymiller wrote on Friday, July 15, 2016:

btw,
I didn’t mention it, but the default upgrade you did looks good.
-brady

rdh61 wrote on Friday, July 15, 2016:

Sorry to be dense, but what do you mean by “<browser_address>” in “<browser_address>sql_upgrade.php?site=italy”.

Thanks.