we have imported the database, it is taking a very long time for EMR to convert it to version 6 – see screenshot, please.
We have tried it on 2 separate instances with the same result, it is sitting at 10% for few hours
We also upped the VM, SQL, and PHP resources, which doesn’t seem to have helped.
@stephenwaite what did you do to speed this process up?
Here is an alternative to a PHP and recordset based approach that you could incorporate in appropriate sql patch :
ALTER TABLE `patient_data` ADD `uuid` binary(16) DEFAULT NULL;
CREATE UNIQUE INDEX `uuid` ON `patient_data` (`uuid`);
UPDATE `patient_data` SET `uuid` = unhex(replace(uuid(),'-','')) WHERE `uuid` IS NULL;
SELECT `uuid`, count(`uuid`) from `patient_data` group by `uuid` having count(`uuid`)>1;
The last statement is to confirm that mysql did not insert duplicate uuid as some have reported.
For a modestly configured host, 100K rows took less than 10 seconds for the UUID insertion.
mySQL 8 or equivalent MariaDB support use of functions in default. So you should be able to let DB do the work of initializing UUIDs with unhex(replace(uuid(),'-','')).
@stephenwaite
It seems like all kind of weird things happen. I am converting a database from 4.2.2.
Is this normal?
There is an error in the bottom screen and the top screen is still moving.
Is that the new normal? Is the system still making progress or has it stopped? How do I tell?
UPDATE:
What I have learned. If and when the data upgrading fails/stops/quit working and you get those red bars on the screen. Look down at the progress bar to see what version has the database stopped at and start there when you restart the migration.
UPDATE II:
This is the third time that I have gotten the error below. I am now 8 hours into the data migration. However, this is far better than it use to be. There is at least a clue of where the progressed stopped. The best thing is to start up one level below where the migration bombed out the last time.
UPDATE III:
This has been a real pain. After 8+ hours, the upgrade is still stuck at
Skipping section #IfNotIndex procedure_result uuid
Going to add UUIDs to procedure_result table
Bottom panel shows the server requests, top panel show server activity. So if a request fails the server will still run it processes to completion. Because we set server to use only one process, it will eventually complete all it’s pending requests and thus, top panel still runs.
Same thing would happen if you closed browser window during upgrade. Sql server still runs til killed or competes it’s pending.
Looks to me in your case a table update was missed. Check upgrade scripts to see where that colume is created and rerun upgrade from there. If I recall, v500 to v501.
if i remember that Unknown column 'group_id' in 'field list' error means you have to drop the database, create it, then import, then run sql_upgrade
I have done all of the above. No errors in the error log. I did rerun the scripts. I manually updated tables as needed. I am posting this as a what could go wrong. So, some people will know that they are not the only ones that are having a difficult time getting from 4.2.x to 6.0.0. I ran out of time and had to roll back.
So, I made some changes this morning and I will update when I rerun the upgrade script again tonight.
Thanks @stephenwaite @sjpadgett
By the way, I would kill the upgrade by rebooting the server before trying the upgrade again. It was helpful that the screens showed where the upgrade left off as far as what version that the script was possibly up to so I did not have to keep starting at 4.2.1 each time.
you’ll want to drop the database, create it, import it and start over at the beginning every time for confidence
also you can just stop the sql and apache server w/out having to reboot
that sounds not good. So, every time the script fails, I need to drop the database and start over?
If I do that, I will never get the database converted because it will always fail in the same spot. Help me understand.
have to fix the underlying issue, for the above mentioned it means that you weren’t starting from a clean database
So, we are starting with the database from 4.2.2. I need to move the database to the cloud so I export it from the current server as an SQL file.
There is no attempt to upgrade the existing server. The database is being moved to a new server. The current 4.2.2 database is still in production.
I did a dump of the database Friday after the office closed. I truncated the log tables to keep the size of the SQL file more manageable. Then push the SQL file up to the server. I dropped the table that was created by the v6 install and created it again. Then I imported the 4.2.2 database into the empty database.
Then I started the sql_upgrade script and selected 4.2.1 as the starting point.
Where did I go wrong or what should I do differently?
I think everyone has lost track of what is the issue! So what is it now?
From the last screen you shared upgrade is running and WILL take a long time to complete if a long historied database.
You need to grab @stephenwaite fix for indexing. In patch 1 or in master…
if it’s 4.2.2 might have a prob selecting 4.2.1 then with the group_id
@sjpadgett I am trying to make sure that I am following the right advice. I did grab the indexing fix that @stephenwaite posted (thanks for that).
So, I will run again tonight or maybe early morning. I need to catch up on some sleep. 
Developers log, star date 20210305.
On this attempt, I followed all the steps of dropping the database, creating the database, importing the 4.2.2 database. Downloaded the latest codebase from dev. Launched upgrade and this is where it stopped. I let it sit all night so that even though the front end failed, the background would keep running.
I tried to login this morning and this is what I saw.
I will try a different approach to the data migration. I am going to export and import a few tables at a time. That way, maybe I can trap what is causing the system to hang.
umm start upgrade again on 502 to 6
Ok, I restarted the upgrade from 502 and the system timed out again as before.
Stats on the procedure_result table is that for us there are 415357 records in the table.
The UUID column has been added to the table. It seems if the upgrade is copying the UUID from the patient chart into the table. It is taking a long time to do it. I have restarted the upgrade from 5.0.2 for second time to see if the transfer of data can be completed.
It is only creating a UUID. I don’t understand it timing out. The upgrade app or server or …?
