Upgrade from v5.0.2 to v7.0.0 failed

juggernautsei · April 29, 2023, 11:07am

The upgrade fails with this error messages

   OpenEMR Error : Decryption failed authentication.
   OpenEMR Error : Key in drive is not compatible (ie. can not be decrypted) with key in database - Exiting.

PHP 8.1.2-1
ubuntu 2.11
Apache/2.4.52

Here were the steps taken to get here.

Created new server to move to.
Install dependencies from the dependencies page
Followed the instructions to upgrade from the upgrade page
Backup database from v5 and imported into a new empty database created on the new server and copied key set sixa and sixb to new server before next step
Ran the upgrade script. No problems. Upgrade script completed with no errors.

Attempted to access the login page. A blank screen shows. Tried accessing the login screen from two more different browsers, blank. Keeps repeating the same error message.

According to @brady.miller , keys should never be deleted and keys should follow the database.

If I delete the keys, then the login username and password do not work.

I have tried upgrading from v5 - v6. That does not work. There is an internal error displayed.

There are no modifications to the v5 database.

My question. Is there a routine that upgrades the key set between versions?

juggernautsei · April 29, 2023, 9:58pm

As a test I decided to upgrade a different server of mine with version 7.0.0 (2) to 7.0.1.
I move the current version to oemr-old, copied the contents of the old
oemr/sites/default/documents/* folder into oemr/sites/default/documents/

Tried to run the upgrade script and I get

 [Sat Apr 29 21:22:18.561369 2023] [php:notice] [pid 76103] [client 154.209.125.66:61385] OpenEMR Error : Decryption failed authentication.
 [Sat Apr 29 21:22:18.561413 2023] [php:notice] [pid 76103] [client 154.209.125.66:61385] OpenEMR Error : Key in drive is not compatible (ie. can not be decrypted) with key in database - Exiting

I reverse the folders and put the system back and everything works fine.
I guess, I will have to setup xdebug to see where this is breaking at.

stephenwaite · April 29, 2023, 10:05pm

hi @juggernautsei , are you using cp -r for a recursive copy?

juggernautsei · April 30, 2023, 2:01am

Yes, I am otherwise the error message shows that says skipping directory.

mdsupport · April 30, 2023, 5:59pm

If asking users to reset their passwords is an option, after upgrade reset admin to ‘pass’ and reset passwords for self and other active users.

stephenwaite · April 30, 2023, 6:04pm

ok, what are the permissions on the key since it can only get to the

OpenEMR Error : Key in drive is not compatible (ie. can not be decrypted) with key in database - Exiting

if the key is empty.

juggernautsei · April 30, 2023, 10:46pm

The key is not empty. The permission is 644. Owner www-data.
I went back to the previous install that did not work. It was not due to a key issue. It was a module failure. I removed the failing module and everything worked fine. I am going back to the v5 and copy over the entire documents folder this time.

juggernautsei · May 8, 2023, 9:47pm

I let some time pass before trying this again. I am still having the same issue upgrading from version v5 on ubuntu to v7.0.1 on ubuntu.

I still get the same error.

 [Mon May 08 20:52:06.639111 2023] [php:notice] [pid 125577] [client 70.169.17.133:50690] OpenEMR Error : Decryption failed authentication.
 [Mon May 08 20:52:06.639325 2023] [php:notice] [pid 125577] [client 70.169.17.133:50690] OpenEMR Error : Key in drive is not compatible (ie. can not be decrypted) with key in database - Exiting.

I will pull the database down to a local machine so I can troubleshoot it better.
I can get into it by resetting the password. But the calendar does not show. I ran the SQL patch but that did not solve the calendar issue.

juggernautsei · May 9, 2023, 2:14pm

I need help understanding what I am looking at. I dumped out the key. The Collect key keysource is database. In the trap, I get this output below.

 [Tue May 09 08:53:09.815296 2023] [php:notice] [pid 169856:tid 1872] [client ::1:50619] OpenEMR : Key retrieved from database\xb5\xcc\xa28\x13|BEB\xaa\xbb\xee\xd2#\x89W]^\xfc7yS\x10\xc2-;\x8f^\bi\xa0\xea

The line above is from

github.com

openemr/openemr/blob/e089ae8fc39bbd030298eb0e8c1904e2b3690701/src/Common/Crypto/CryptoGen.php#L460

      
        
                        file_put_contents($GLOBALS['OE_SITE_DIR'] . "/documents/logs_and_misc/methods/" . $label, base64_encode($newKey));
                    } else {
                        file_put_contents($GLOBALS['OE_SITE_DIR'] . "/documents/logs_and_misc/methods/" . $label, $this->encryptStandard($newKey, null, 'database'));
                    }
                }
            }
            
            
// Collect key
            if ($keySource == 'database') {
                $sqlKey = sqlQueryNoLog("SELECT `value` FROM `keys` WHERE `name` = ?", [$label]);
                $key = base64_decode($sqlKey['value']);
            } else { //$keySource == 'drive'
                if (($version == "one") || ($version == "two") || ($version == "three") || ($version == "four")) {
                    // older key versions that did not encrypt the key on the drive
                    $key = base64_decode(rtrim(file_get_contents($GLOBALS['OE_SITE_DIR'] . "/documents/logs_and_misc/methods/" . $label)));
                } else {
                    $key = $this->decryptStandard(file_get_contents($GLOBALS['OE_SITE_DIR'] . "/documents/logs_and_misc/methods/" . $label), null, 'database');
                }
            }
            
            
// Ensure have a key (if do not have key, then is critical error, and will exit)

I dumped the key again after line 470

   This is the encryption version: 6
   1. OpenEMR : Key retrieved from database \t\xc5\x7f\\\xaaj\xde \xd2hp\x1e\xae\t\xce\xc6\xa1\xf9\xeco,iA\xcdb\xa4\xdf@h\xf3\x92\xdb
   2. Decrypt key: \t\xc5\x7f\\\xaaj\xde \xd2hp\x1e\xae\t\xce\xc6\xa1\xf9\xeco,iA\xcdb\xa4\xdf@h\xf3\x92\xdb
   1. OpenEMR : Key retrieved from database \xb5\xcc\xa28\x13|BEB\xaa\xbb\xee\xd2#\x89W]^\xfc7yS\x10\xc2-;\x8f^\bi\xa0\xea
   2. Decrypt key: \xb5\xcc\xa28\x13|BEB\xaa\xbb\xee\xd2#\x89W]^\xfc7yS\x10\xc2-;\x8f^\bi\xa0\xea
    OpenEMR Error : Decryption failed authentication.
    2. Decrypt key: 
    3. Is the key empty

@stephenwaite The key is empty.

So, I went back to the version 5 original database and looked for the keys table. There is no keys table in version 5.0.2 (5) that I am working with. Because the keys table was not created. There is no key.

My guess is that the system is doing what it is supposed to do and creates a key when the key table is empty. It creates a new key and calls that key. Now when that new key is called. It does not match the version 5 keys from the methods folder.

Now I have gone back to version 5.0.2 (5) and added the keys table. The keys table is now populated with a key. I will run the upgrade again to see what happens.

stephenwaite · May 9, 2023, 3:07pm

that’s odd @juggernautsei since the upgrade script from 501 to 502 should have created that table

github.com

openemr/openemr/blob/master/sql/5_0_1-to-5_0_2_upgrade.sql#L708

      
        
            #EndIf
            
            
#IfMissingColumn facility mail_zip
            ALTER TABLE `facility` ADD `mail_zip` VARCHAR(10) default NULL;
            #EndIf
            
            
#IfMissingColumn facility oid
            ALTER TABLE `facility` ADD `oid` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'HIEs CCDA and FHIR an OID is required/wanted';
            #EndIf
            
            
#IfNotTable keys
            CREATE TABLE `keys` (
              `id` bigint(20) NOT NULL auto_increment,
              `name` varchar(20) NOT NULL DEFAULT '',
              `value` text,
              PRIMARY KEY (`id`),
              UNIQUE KEY (`name`)
            ) ENGINE=InnoDB;
            #EndIf
            
            
#IfNotColumnType amendments pid bigint(20)

juggernautsei · May 9, 2023, 3:28pm

Yes, very odd but that should fix the issue. I got a chance to use new tools for me and some old ones as well. Never assume all the tables are where they are supposed to be even though those tables should have been created. Verify all data sources. I started tracking backward just to make sure that a key was existing in the previous database before the upgrade. Because after the upgrade the table exits. I had no proof it existed before the upgrade. Turned out to be something basic and simple to fix. I want to wait until the end of the day to make a current copy of the database and run the upgrade script on it.

juggernautsei · May 10, 2023, 12:05pm

This rabbit hole keeps getting deeper. Version 5 is not producing the database key. I created the table. The system has not populated the table with a key entry. Now I need to figure out why.

brady.miller · May 10, 2023, 8:13pm

maybe it was a 5.0.2-dev version? (in that case would try to sql-upgrade from 5.0.1 instead of 5.0.2)

juggernautsei · May 10, 2023, 11:34pm

@brady.miller I am not sure where you are talking about running sql_upgrade from. When I am doing the upgrade from v5. I aways start two version back from where I think the database is. So, in this case the database should be 5.0.2 (5). I started the upgrade at 5.0.0.

But the issue is that the original 5.0.2 (5) code is not creating a key in the database. The mechanism for inserting the key into the table has been the same since v5.0.2. I put in a trap just before the insert statement and the code is not hitting the trap.

This has been an exercise in paying attention.
This is an old multi-site. I was grabbing the wrong database.

All is well now. Good exercise in the encryption process.

pedro · June 13, 2025, 6:07am

Hi @juggernautsei - I am having the same errors thrown when trying to migrate from 5.0.2 (WAMP) to 7.0.3 (docker on raspberryPi5).
I keep getting the error “Key in drive is not compatible…”
When I run the sql_upgrade.php script it works fine until “Updating Access Controls…” and then never finishes.
I have managed to make it work twice on a linux workstation running development-easy, but not on the Pi5 installation. I have managed to get this far with help from @stephenwaite, @Penguin8R and @jesdynf.
I do not have a multi site installation.
I know your post is about 2 years old, but I would appreciate any help you can provide.

juggernautsei · June 13, 2025, 4:48pm

@pedro I went back and read what is in this thread. The advice I can give you is about what to do prior to upgrading. In the v5x that you have. Make sure you have key files in the sites/default/documents/log_and_misc it should six_a and six_b. Those keys are what was causing my issue. It is best if when upgrading from v5 to version 7 that you remove and replace the entire sites directory in v7 with the entire sites directory from v5. Then you will be sure to have the key set that matches the database. When you run the upgrade script everything should work.

pedro · June 15, 2025, 9:22am

Thankyou,@juggernautsei, I have done that and can now log in to oemr and see the data. However, the sql_upgrade.php script to 7.0.3 stops with yhe message “Upgrading ACL Controls …” and doesn’t finish. When I log in, rhe “About OpenEMR” tells me it is 5.0.2 (which is also what the version table in mariadb also says.

juggernautsei · June 16, 2025, 2:28pm

When you get to this point, do you close and restore the script to see if it will continue on the next try?

pedro · June 22, 2025, 7:52am

Thankyou for your reply @juggernautsei . I have just returned from travelling and tried what you suggested and it still just hangs.
Looking into the sql_upgrade.php code, immediately after the last message (“Updating Access Controls…”) is the line:

require("acl_upgrade.php");

which does not appear to be in the openemr root directory. The only copy I could find is in “interface/modules/zen_modules/module/PatientFilter/acl”, so I changed the require(“acl_upgrade.php”) line to

require("interface/modules/zen_modules/module/PatientFilter/acl/acl_upgrade.php");

and tried again. This time I got an Authentication error, and in the error.log file I got

PHP Warning: Undefined variable $aclSetupFlag in /var/www/localhost .......  acl_upgrade.php on line 4, referrer: http://localhost/sql_upgrade.php

I will re-read the input I received from @stephenwaite @jesdynf and @Penguin8R an review all I have done…

juggernautsei · June 23, 2025, 12:49am

I just read what I wrote and that was absolutely wrong. I meant to write do you run the upgrade script again. There are times will the upgrade script will stop. However, if you rerun the script. It can finish.