This might belong in the Support category, but since it’s specifically related to details of how the application code works at a fairly low level, I thought I’d start here.
I have been prototyping version upgrade paths for some extremely large working OpenEMR V5 systems, and how to get from there to V7.0.1.
During the phases of the version upgrade routine between V6 & V7, when the UUID fields are added to the database schema, and subsequently populated, is there a specific reason or logic as to where the new UUID column is placed in the existing tables?
For example, in the “procedure_report” table, the UUID is inserted as the second column, but in “procedure_result”, it is added as the 2nd to last column. Or, in “prescriptions”, the UUID is inserted 7 rows from the last field in the table, but in “documents”, it’s again placed as the second column in the table.
Reason I’m wondering, is that for upgrade purposes, in large environments where these tables have millions of rows, the UUID phase either takes excessive time to complete, or in some cases, it is unable to complete. One of my current upgrade tests involves a database with 2.1 million documents, attempting the version upgrade from 5 → 7, once the UUID phase hits the documents table, the process ran for 10 days and then eventually just died, no errors, no actual failure reports, it just eventually stopped running. The underlying hardware was a physical server with SAS disk storage, 64GB of memory, and dual 8-core CPUs, so I am not sure that throwing more horsepower at the problem will make any significant changes in elapsed processing time. Given that the existing data tables include over 2 million documents, 1 million + prescription entries, and hundreds of thousands of laboratory tests/results, even if the problem that causes those UUID population routines to run for 10 days and then exit without completing was corrected, that would still mean that the required time to execute the version upgrade would run into weeks of continuous SQL processing, possibly well over a month. One possible workaround, would be to implement and populate those fields gradually in the database, prior to the 5 → 7 upgrade being performed, but before I start a modified round of testing based around that plan, I’d like to gain a better understanding of the seemingly random placement of the UUID fields in the affected tables. If anyone has any insights, it would be much appreciated!
You can look at our approach here that is valid for current mariadb/mysql databases. This is one area where project has taken a very peculiar approach in handling UUID where php scripts are used to do work that most current dbengines do natively. To give an idea, when you disable the strange logic of UUID registry and delegate UUID generation to database engine, all table updates take seconds instead of hours.
Best.
Yes, I should have mentioned the back end was MariaDB 10.6. I am done trying to work with MySQL 8, just dealing with the strange default table collation and character set handling on MySQL 8 adds hours of wasted processing time to the overall version upgrade process. MariaDB/Galera is the far superior platform at this point. Browsing through that folder , it looks like you discovered the same bug with passwords, and the glitch in the UUID code where it fails on the postcalendar_events table. It all starts to get interesting once you’re dealing with millions of database rows instead of a few hundred K. Thanks for pointing that out! We’ll be digging deeper into this for sure next week and I’ll update here with any new information or fixes that we find, in case it will help others.
See here for why the uuid stuff was started:
Create patient uuid mechanism · Issue #2277 · openemr/openemr · GitHub
(medium term goal of having the uuid identifiers for fhir and long-term goal of supporting offsite data synchronization at some point (goal of replacing database id indexing with uuid indexing))
Essentially needed a balance of being time based (version 1 uuid that mysql/mariadb produce is time based and sticks a server specific code on end) and not guessable (so instead went with a custom uuid (Timestamp-first COMB Codec) that is time based and sticks a random entry instead of a server specific code, so not guessable)
Are you stepping through each version on your upgrade? Rec not doing this and would try to jump from 5 directly to 7 since the uuid mechanism in the more recent version upgrades is magnitudes faster then older version upgrades.
If still issues even with that, then could consider blocking out stuff here:
https://github.com/openemr/openemr/blob/master/src/Common/Uuid/UuidRegistry.php#L42-L65
(maybe make it empty on the initial upgrade, then show one at a time and run the upgrade from 7.0.0 to 7.0.1 each time open one up)
We had about 400K documents. But it was the appx 1.5M rows of proc* that made it impossible to justify use of standard code. Next was the bloat factor that would have affected timing and storage of backups without adding any benefit for foreseeable future.
In addition, our upgrade was several ETL steps that were refined iteratively. Since the test cycles were turning out to be so long, we ended with an cli upgrader that can be incorporated into one of the scripts. This script also lets you upgrade components in their own sequence. Hopefully this project will use that approach if the modules are going to be self contained.
Best.
Hi @brady.miller ,
I am doing an upgrade from v7.0.0 (1) to v7.0.2.
The upgrade is stuck on the UUID creation.
Is there a way to do this later? It has been running for +12 hrs.
UPDATE
I went back and started the upgrade from v6.0.0 and ran it. Either the UUIDs finished or the starting further back fixed the issue that was hanging up the UUID. I don’t know at this point.
Any updates on this uuid issue pl… i am also stuck at 15 % while upgrading a 690mb sql upgrade fron v6.1 to v 7.02.
Any guidance pl…
Not really, I am still fighting with it on larger sites with extensive data. The installation that’s giving me the worst problems has a database that’s nearly a terabyte, with extremely large document & forms tables, and I’ve run test upgrade sequences allowing the UUID routine to keep going for days or even weeks at a time but it always ends up in nowhere land just executing pointless select queries on the back end , yet never does any writes to any tables.
I had started experimenting with trying to get the UUIDRegistry to only do one table at a time, but if you make any changes to that code, it will result in a fatal error that prevents the version upgrade master routine from loading/executing.
Next option is to remove the UUID registration process from the version upgrade, and find a way to run it separately on a table by table basis after, but that’s going to require a significant amount of code re-writing.
Provided that you are running your V6.1 to V7.0.2 upgrade in a test environment, and not on a production system, if it’s finishing the version upgrade and then hanging up during the UUID phase, you might want to try just killing the upgrade and restarting the process multiple times to see if it eventually finishes all the steps for UUIDs. Also, verify that the new table schemas actually have the UUID columns present, I have had some cases where for inexplicable reasons, the SQL commands to create the new columns failed, and they had to be added manually in order for the UUID routines to do their work. If it’s sticking at the same place every time, then check those tables that need UUIDs and try to find which one it’s hitting that causes it to hang, and see if that table is missing the required UUID column. But, beware, the UUID column is not always at the beginning, or end of, the table structure, in some cases it is, but in others it’s inserted randomly somewhere in the middle of the table schema, look carefully before assuming that it’s not there.
I’m pretty strapped for time right now, but ping me in two weeks. I have a script I put together for another large OpenEMR installation that will generate several million uuids pretty quickly.
If there are several rounds of testing, add uuid columns with default values to your production database before upgrade. If you must, you can also populate uuidregistry table in minutes with sql rather than waiting for the standard code with its interesting approach to finish (maybe).
Best.