Upgrade from 5.0.2 to 7.0.2 sql_upgrade script fails with missing uuid_registry table

whittech · September 22, 2024, 1:55am

Situation
Upgrading from 5.0.2 to 7.0.2, database imported OK, document files transferred, etc. Running the sql_upgrade.php script fails first about a missing uuid column in the facility table. I manually added that column after looking at the table description online, then the script fails looking for a table named uuid_registry which does not exist.

OpenEMR Version
I’m using OpenEMR version 5.0.2 (5)

Browser:
I’m using: Chrome latest

Operating System
I’m using: new server running on Amazon Linux 2023

Search
Did you search the forum for similar questions?
Yes, there are a couple about the uuid update process but that seems not the same thing.

Logs
Did you check the logs?
Was there anything pertinent in them?
Please paste them here (surround with three backticks (```) for readability.
You can also turn on User Debugging under Administration->Globals->Logging User Debugging Options=>All

first error about missing column in facility table:

[22-Sep-2024 01:28:59 UTC] Service with name &quot;Carecoordination\Model\EncounterccdadispatchTable&quot; could not be created. Reason: Failed to execute statement. Error: Unknown column &#039;uuid&#039; in &#039;where clause&#039; Statement: SELECT count(*) AS cnt FROM `facility` WHERE `uuid` IS NULL OR `uuid` = &#039;&#039; OR `uuid` = &#039;

second error after manually adding a uuid column to facility table:

[22-Sep-2024 01:37:54 UTC] Service with name &quot;Carecoordination\Model\EncounterccdadispatchTable&quot; could not be created. Reason: Failed to execute statement. Error: Table &#039;openemr.uuid_registry&#039; doesn&#039;t exist Statement: SELECT `uuid` FROM `uuid_registry` WHERE `uuid` = ?#0 /var/www/html/openemr/vendor/laminas/laminas-servicemanager/src/ServiceManager.php(264): Laminas\ServiceManager\ServiceManager-&gt;doCreate()
#1 /var/www/html/openemr/interface/modules/zend_modules/module/Carecoordination/config/module.config.php(182): Laminas\ServiceManager\ServiceManager-&gt;get()
#2 /var/www/html/openemr/vendor/laminas/laminas-servicemanager/src/ServiceManager.php(642): Carecoordination\Module-&gt;Carecoordination\{closure}()
#3 /var/www/html/openemr/vendor/laminas/laminas-servicemanager/src/ServiceManager.php(264): Laminas\ServiceManager\ServiceManager-&gt;doCreate()
#4 /var/www/html/openemr/interface/modules/zend_modules/module/Carecoordination/config/module.config.php(179): Laminas\ServiceManager\ServiceManager-&gt;get()
#5 /var/www/html/openemr/vendor/laminas/laminas-servicemanager/src/ServiceManager.php(642): Carecoordination\Module-&gt;Carecoordination\{closure}()
#6 /var/www/html/openemr/vendor/laminas/laminas-servicemanager/src/ServiceManager.php(264): Laminas\ServiceManager\ServiceManager-&gt;doCreate()
#7 /var/www/html/openemr/interface/modules/zend_modules/module/Carecoordination/Module.php(71): Laminas\ServiceManager\ServiceManager-&gt;get()
#8 /var/www/html/openemr/vendor/laminas/laminas-eventmanager/src/EventManager.php(320): Carecoordination\Module-&gt;onBootstrap()
#9 /var/www/html/openemr/vendor/laminas/laminas-eventmanager/src/EventManager.php(170): Laminas\EventManager\EventManager-&gt;triggerListeners()
#10 /var/www/html/openemr/vendor/laminas/laminas-mvc/src/Application.php(148): Laminas\EventManager\EventManager-&gt;triggerEvent()
#11 /var/www/html/openemr/src/Core/ModulesApplication.php(65): Laminas\Mvc\Application-&gt;bootstrap()
#12 /var/www/html/openemr/interface/globals.php(646): OpenEMR\Core\ModulesApplication-&gt;__construct()
#13 /var/www/html/openemr/sql_upgrade.php(48): require_once(&#039;...&#039;)
#14 {main}

stephenwaite · September 22, 2024, 7:08pm

hi @whittech , you probably chose 5.0.2 from the upgrade gui?

whittech · September 22, 2024, 9:23pm

I can’t get to that GUI - the errors happen before it shows me anything on the page (sql_upgrade.php).

stephenwaite · September 23, 2024, 12:56pm

ok, right, it’s checking for modules and gets caught up, try this this fix replacing lines 639 to 642 with the new code.

whittech · September 25, 2024, 4:18pm

That code doesn’t seem to affect what sql_upgrade.php is tripping on. I made the change but still see a blank page, and this error in the server error log. Seems like it is expecting uuid columns in tables that don’t have it yet in 5.0.2.

[25-Sep-2024 16:16:18 UTC] Service with name "Carecoordination\Model\EncounterccdadispatchTable" could not be created. Reason: Failed to execute statement. Error: Unknown column 'uuid' in 'where clause' Statement: SELECT count(*) AS cnt FROM facility WHERE uuid IS NULL OR uuid = '' OR uuid = '

stephenwaite · September 25, 2024, 5:02pm

hi @whittech , is the modules table empty?

whittech · September 25, 2024, 7:34pm

No, shows 5 rows.

MariaDB [openemr]> select * from modules;
±-------±----------------------±----------------------±-----------±---------±-----------±---------------------±------------------------------±-------------±--------------±----------------±--------------±-------------±-----------------------±----------±--------------------±--------±-----+
| mod_id | mod_name | mod_directory | mod_parent | mod_type | mod_active | mod_ui_name | mod_relative_link | mod_ui_order | mod_ui_active | mod_description | mod_nick_name | mod_enc_menu | permissions_item_table | directory | date | sql_run | type |
±-------±----------------------±----------------------±-----------±---------±-----------±---------------------±------------------------------±-------------±--------------±----------------±--------------±-------------±-----------------------±----------±--------------------±--------±-----+
| 2 | Immunization | Immunization | | | 1 | Immunization | public/immunization/ | 0 | 0 | | | | NULL | | 2024-09-04 12:47:31 | 1 | 1 |
| 3 | Syndromicsurveillance | Syndromicsurveillance | | | 1 | Syndromicsurveillanc | public/syndromicsurveillance/ | 0 | 0 | | | | NULL | | 2024-09-04 12:47:33 | 1 | 1 |
| 4 | Documents | Documents | | | 1 | Documents | public/documents/ | 0 | 0 | | | | NULL | | 2024-09-04 12:47:34 | 1 | 1 |
| 5 | Ccr | Ccr | | | 1 | Ccr | public/ccr/ | 0 | 0 | | | | NULL | | 2024-09-04 12:47:35 | 1 | 1 |
| 6 | Carecoordination | Carecoordination | | | 1 | Carecoordination | public/carecoordination/ | 0 | 0 | | | | NULL | | 2024-09-04 12:47:36 | 1 | 1 |
±-------±----------------------±----------------------±-----------±---------±-----------±---------------------±------------------------------±-------------±--------------±----------------±--------------±-------------±-----------------------±----------±--------------------±--------±-----+
5 rows in set (0.000 sec)

stephenwaite · September 25, 2024, 7:44pm

That’s odd because those rows should have been added in the version 6 upgrade.

whittech · September 25, 2024, 7:48pm

We are going from 5.0.2 to 7.0.2 and I’m trying to launch the sql_upgrade script. Is there some other intermediate step I should be doing?

stephenwaite · September 25, 2024, 7:50pm

That’s the usual process. If you haven’t been using any modules you could try truncating the table and then try sql_upgrade again.

whittech · September 25, 2024, 7:59pm

Thanks. Are modules related to the UUID columns in tables that it seems to be looking for?

stephenwaite · September 25, 2024, 8:01pm

For the upgrade, yes, since thinking that the rows in the module table are leading to the errors you are experiencing before you can load the page.

whittech · September 27, 2024, 11:34pm

I started over and upgraded from 5.0.2 to 6.1 and that worked. During the SQL upgrade script, I saw it creating the UUID columns in existing tables. So it seems you have to go to 6.1 and run its sql_upgrade script, then rinse and repeat to get to v7.