Hi Pete,
This issue is bugging me(get it!). You’re prob not in a joking mood about now!
This table trimming started in v5.0.1 when in 5.0.2 we replaced varchar with text to fix a Maria bug. New databases will create correct table so a task that may help:
Don’t worry I’m fine, I’m patient because I’m in a safe position able to test this out on a test instance before committing to it in production. However I’m concerned that I can’t deploy get the new ICD10 COVID codes and other updates; and for those who might have OpenEMR out in the open, there are many security updates being held up by this. I really appreciate the time you guys are putting into helping though.
I hope in a way that this is a wider issue and so others are able to benefit from the attention you’re giving it, which it looks to be.
I’ll do as you request and get back to you. Cheers
I forgot to say, you saying “This could be troublesome” does give me pause for concern with regard the production data.
I exported form_eye_neuro from production and imported to test. No errors seen during the import.
Did you try sql_upgrade or sql_patch again?
Neither yet. Which would you like me to try first? Presumably sql_patch because this data comes from 5.0.2 anyway?
I have a small hope but either is fine or both while there…
All green!
sql_patch.php:
OpenEMR 5.0.2 Database Patch 3
Applying Patch to site : default
Processing patch.sql ...
Skipping section #IfNotRow2D list_options list_id apps option_id oeSignerRemote
Skipping section #IfNotColumnType form_eye_neuro ACT5CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT1CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT2CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT3CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT4CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT6CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT7CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT8CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT9CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT10CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT11CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT1SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT2SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT3SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT4SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT5SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT6SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT7SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT8SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT9SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT10SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT11SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT1SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT2SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT3SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT4SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT5CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT6CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT7CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT8CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT9CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT10CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT11CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT5SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT6SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT7SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT8SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT9SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT10SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT11SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT1CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT2CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT3CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT4CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ODNPA text
Skipping section #IfNotColumnType form_eye_neuro OSNPA text
Skipping section #IfNotColumnType form_eye_neuro VERTFUSAMPS text
Skipping section #IfNotColumnType form_eye_neuro DIVERGENCEAMPS text
Skipping section #IfNotColumnType form_eye_neuro ODCOLOR text
Skipping section #IfNotColumnType form_eye_neuro OSCOLOR text
Skipping section #IfNotColumnType form_eye_neuro ODCOINS text
Skipping section #IfNotColumnType form_eye_neuro OSCOINS text
INSERT INTO `supported_external_dataloads` (`load_type`, `load_source`, `load_release_date`, `load_filename`, `load_checksum`) VALUES('ICD10', 'CMS', '2019-10-01', '2020-ICD-10-CM-Codes.zip', '745546b3c94af3401e84003e1b143b9b')
INSERT INTO `supported_external_dataloads` (`load_type`, `load_source`, `load_release_date`, `load_filename`, `load_checksum`) VALUES('ICD10', 'CMS', '2019-10-01', '2020-ICD-10-PCS-Order.zip', '8dc136d780ec60916e9e1fc999837bc8')
ALTER TABLE `patient_access_onsite` ADD `portal_login_username` VARCHAR(100) DEFAULT NULL COMMENT 'User entered username', ADD `portal_onetime` VARCHAR(255) DEFAULT NULL
UPDATE `patient_access_onsite` SET `portal_pwd_status` = '0', `portal_login_username` = `portal_username`
ALTER TABLE `api_token` ADD `token_auth_salt` varchar(255)
ALTER TABLE `api_token` ADD `token_auth` varchar(255)
ALTER TABLE `openemr_postcalendar_events` ADD INDEX `index_pcid` (`pc_pid`)
ALTER TABLE `medex_recalls` ADD INDEX `i_eventDate` (`r_eventDate`)
ALTER TABLE `medex_outgoing` ADD INDEX `i_msg_date` (`msg_date`)
INSERT INTO `icd10_dx_order_code`(`dx_code`, `formatted_dx_code`, `valid_for_coding`, `short_desc`, `long_desc`, `active`, `revision`)VALUES ('U071', 'U07.1', '1', 'COVID-19, virus identified', 'COVID-19, virus identified', '1', '1')
set @newMax = (SELECT MAX(revision) from icd10_dx_order_code)
UPDATE `icd10_dx_order_code` SET `revision` = @newMax WHERE `dx_code` = 'U071'
INSERT INTO `icd10_dx_order_code`(`dx_code`, `formatted_dx_code`, `valid_for_coding`, `short_desc`, `long_desc`, `active`, `revision`)VALUES ('U072', 'U07.2', '1', 'COVID-19, virus not identified', 'COVID-19, virus not identified', '1', '1')
set @newMax = (SELECT MAX(revision) from icd10_dx_order_code)
UPDATE `icd10_dx_order_code` SET `revision` = @newMax WHERE `dx_code` = 'U072'
Updating global configuration defaults...
Updating version indicators...
Database Patch 3 finished.
OpenEMR Version = 5.0.2(3).What do ya know! Do you know what version Maria created this table?
@PeteBoyd This just hit me. The production table has already been converted! So, no issue on production upgrade.
But shouldn’t the test server we’ve been working on thus far also have already been converted? Being as it had a fresh install of OpenEMR 5.0.2.0.
The test server was a fresh install of Debian 10 with mariabdb-server 10.3.15 > 10.3.17 > 10.3.22, and a fresh install of OpenEMR 5.0.2.0 that was upgraded to 5.0.2.1. I will upgrade this to OpenEMR 5.0.2.3 next – obviously this is just a re-run of what we’ve just done.
It was a mirror of this test server on which I’ve been testing the upgrade to OpenEMR 5.0.2.3, thus it had already some time ago had sql_upgrade.php run against it.
Production server 1 was a fresh install of Debian 9 with mariabdb-server 10.1.38. It was upgraded to Debian 10 and now has mariabdb-server 10.3.22.
It had a fresh install of OpenEMR 5.0.1.6 that was upgraded to 5.0.2.1.
Production server 2 was a fresh install of Debian 10 with a fresh install of OpenEMR 5.0.2.1.
I can take a copy of production OpenEMR databases to test against in isolation before upgrade of production servers.
I replaced the openemr database on the test server I’ve been testing on thus far, with a copy of the database from production server 1, ran sql_patch.php, and it was successful
Perhaps the reason I’m seeing the issue on the test server, but not on production server 1, is because this MariaDB issue was in prior to 10.3.17, but occurred in versions later than the version 10.1.38 I used to create the openemr db on production server 1.
I’m trying to test production server 2’s db in the same way but have hit unrelated issues so can’t report back on that one yet.
Thanks
will the latest version be available on AWS/GCP
thanks
Thank you so much @sjpadgett for your considerable help in navigating around this database issue, I’ve now successfully upgraded all production servers.
Outstanding Pete, glad we could help.
Hi,
The 4th patch for OpenEMR 5.0.2 has been released:
https://www.open-emr.org/wiki/index.php/OpenEMR_Patches
The patch includes improvements and fixes :
This majority of the improvements in this patch were by @sjpadgett, which included marked improvements in overall performance, DICOM viewer, Billing, and Patient Portal. Thank you @sjpadgett ! Although it was very difficult to find, we were able to find slow motion footage of @sjpadgett working :
(just amazing stuff; I guess nobody’s privacy is safe anymore )
-brady
Hi,
The 5th patch for OpenEMR 5.0.2 has been released and fixes a security vulnerability (since this vulnerability was made public recently, decided to get out a quick patch):
https://www.open-emr.org/wiki/index.php/OpenEMR_Patches
This patch includes a security fix :
-brady
For those folks that installed my pre-patches for 5, they are not included in patch 5. So if you’ve install patch 5 you will have overwritten my pre patches.
Pre-patches were for billing, encounters, new LBF templates for Portal and a feature change of using the inactive portal-user to assign permissions for portal reports.
I’ve attached a full patch that includes my pre-patches and the security fix. I don’t have time to do an official patch so, this at least won’t leave you hanging.