Upgrading from v4.2.2 to v6.0.0 10% per hours

Csrf is for security to prevent outsiders from running the script. Unsure why the session that holds the token is lost however, it only affects watcher. No harm to upgrade.
You can stop and start the Server status using the green || button. You can also watch the sql servers process status from command line.

The upgrade doesn’t need sessions so, no worries there and is not the cause of upgrade difficulties.
A mature database history will take some time to complete. You’re almost there and the upgrade should continue the UUID adds from where it left off.
If you look at the servers process, you see the record it’s on. Brady regex’ed any useful info out of status where I may put some back.

1 Like

Don’t have access to github from work, but ensure you have the changes in your upgrade script that Stephen pointed to previously. Having the uuid key set up prior to populating the uuid’s is extremely important or else the process will take longer (in an exponential fashion) on larger databases.

1 Like

2GB of RAM? that’s kind of puny, betcha it’s swapping, can you put some more sticks in?

He has the index change. He has some 410,000 procedure results and a slim machine.

1 Like

It is on AWS so they won’t let me add more sticks. However, they will allow me to purchase more power
To get more RAM, I would need to go to the t3.medium. So, I will do the upgrade and get back to you all.

ok, that sounds wise, good luck :four_leaf_clover:

@juggernautsei If you are going to restart, may be you want to try the SQL approach we outlined earlier on the t3.small machine. Steps would be:

  1. Restore 4.x database in current mySQL engine instance.
  2. Add guid field to the guid affected table(s).
  3. Run the update statement - mySQL should take few seconds for 400K records.
  4. Run standard upgrade. Only error to expect is if standard logic does not handle existing guid fields.
  5. Standard update logic looks for empty guid. So those steps will finish in no time.

Very interested in this because we have few places with well more than 500K result rows. Revising that structure is a discussion for later date.

will only take @juggernautsei 20 mins at most to upgrade uuids with standard process

def a cool way to speed things up @mdsupport, thanks for the savvy sql statements

edit: ec2 users will have to look at the volumes iop’s which can be limited by aws

image

1 Like

UPDATE:

Per the conversation suggests that I was given during the Saturday call. I loaded the database on a local machine. It is 4 days later and it is still running. But at least it is running. There have been 358174 UUID’s registered in the registry table. There are 6 tables in the registry. They are patient data, form_encounter, users, facility, immunizations, lists is the current table that is being loaded to the table.

1 Like

It is an hour later and the record count is up to 367425 in the uuid registry table.
In the attached file, the last table to be added was list. The list table has 35093 records. The uuid registry has 35094 entries.

I am looking at the 5_0_2-to-6_0_0_upgrade.sql file and it seems the next table to get UUID’s is the documents table that has 27519 records in it.

In between these two entries, I downloaded the rel 600 and applied that code to openemr folder and restarted the upgrade from where the previous attempt was halted. I was expecting the system to fly through the rest. It did not.

@mdsupport where can I find a list of tables that will be getting the UUID columns. This is what I came up with. Is it right?

 patient data   ALTER TABLE `patient_data` ADD `uuid` binary(16) DEFAULT NULL;  CREATE UNIQUE INDEX `uuid` ON `patient_data` (`uuid`);
 form_encounter  ALTER TABLE `form_encounter` ADD `uuid` binary(16) DEFAULT NULL;  CREATE UNIQUE INDEX `uuid` ON `form_encounter` (`uuid`);
 users  ALTER TABLE `users` ADD `uuid` binary(16) DEFAULT NULL;  CREATE UNIQUE INDEX `uuid` ON `users` (`uuid`);
 facility  ALTER TABLE `facility` ADD `uuid` binary(16) DEFAULT NULL;  CREATE UNIQUE INDEX `uuid` ON `facility` (`uuid`);
  immunizations  ALTER TABLE `immunizations` ADD `uuid` binary(16) DEFAULT NULL;  CREATE UNIQUE INDEX `uuid` ON `immunizations` (`uuid`);
 lists ALTER TABLE `lists` ADD `uuid` binary(16) DEFAULT NULL;  CREATE UNIQUE INDEX `uuid` ON `lists` (`uuid`);
 ccda  ALTER TABLE `ccda` ADD `uuid` binary(16) DEFAULT NULL;  CREATE UNIQUE INDEX `uuid` ON `ccda` (`uuid`);
 document  ALTER TABLE `documents` ADD `drive_uuid` binary(16) DEFAULT NULL;  CREATE UNIQUE INDEX `drive_uuid` ON `documents` (`drive_uuid`);
 facility_user_ids  ALTER TABLE `facility_user_ids` ADD `uuid` binary(16) DEFAULT NULL; CREATE INDEX `uuid` ON `facility_user_ids` (`uuid`);
 drugs  ALTER TABLE `drugs` ADD `uuid` binary(16) DEFAULT NULL;  CREATE UNIQUE INDEX `uuid` ON `drugs` (`uuid`);
 prescriptions  ALTER TABLE `prescriptions` ADD `uuid` binary(16) DEFAULT NULL;  CREATE UNIQUE INDEX `uuid` ON `prescriptions` (`uuid`);
 procedure_order  ALTER TABLE `procedure_order` ADD `uuid` binary(16) DEFAULT NULL; CREATE UNIQUE INDEX `uuid` ON `procedure_order` (`uuid`);
 procedure_results   ALTER TABLE `procedure_result` ADD `uuid` binary(16) DEFAULT NULL;

BTW, the graphical interface does not work. I am counting on service to continue to run even though the interface is not.

database_reading.txt (2.3 KB)

as @benmarte suggested on the call, try upgrading locally first, can get past aws iops bottlenecks, confirm upgrade goes smoothly, then upload that sql and away you go :slight_smile:

1 Like

@stephenwaite I am running it locally and it is going just as low. I am on a machine that has a 1 TB HD, 16GB RAM and AMD 64bit processor. :no_mouth:

you don’t have the update then

I would grep database.sql for binary(16) to be on safe side.

Your upgrade issues are kind of concerning as some of these tables could be huge for many old installations.

Without knowing your specific situation, prior suggestions should speed up things. A small change to original suggestion : drop all indexes before table updates and (re)create final indexes after all updates are done. @stephenwaite probably saw improvement because the standard code is doing individual record update based on a where clause.

the upgrade script is working great and will be easily accessed with first patch any day now

I downloaded rel-600. that does not contain the update?

maybe not, how did you d/l it?

sudo wget https://github.com/openemr/openemr/archive/rel-600.zip

sudo unzip rel-600

sudo cp -R openemr-rel-006/* /var/www/html/openemr

I know it overwrote the files because I had to go reset the sqlconf information

nice, should be good, your php and innodb all beefed up?

SET GLOBAL innodb_buffer_pool_size=8589934592;

edit: although always best to mv that whole directory out of there if not deleting
mv /var/www/html/openemr /tmp