I got the same problem when updating Openemr. My database is using mysql.
Error message as below.
ERROR: query failed: UPDATE icd10_dx_order_codeSET revision = (SELECT MAX(revision) from icd10_dx_order_code) WHERE dx_code = ‘U071’
Error: Table ‘icd10_dx_order_code’ is specified twice, both as a target for ‘UPDATE’ and as a separate source for data
I tried Mark solution but it seems like it did not work by making changes to patch.sql
Please follow up.
Thanks
Hi @jhunyan ,
What version of mysql are you using?
And what errors did you see when using @madmax 's solution?
thanks,
-brady
Hi @brady.miller,
Thanks for prompt reply. I checked my server and it is MarianDB, not mysql as I thought.
The error remains the same when I tried @madmax 's solution.
hi @jhunyan ,
Try it after replacing your sql/patch.sql file with the following:
https://raw.githubusercontent.com/openemr/openemr/dc1947df370cde40c49ced6e03884c47d8d6437f/sql/patch.sql
Let me know if this works. It looks like this is an issue in older mysql/mariadb versions. Luckily, it doesn’t impact anything else in the patch (the only thing this error stops is the importing of the covid icd10 codes).
thanks,
-brady
Hi @brady.miller,
I tried replacing the patch as you mentioned and it is working normally. Thank you very much.
The message of the update is as below.
OpenEMR 5.0.2 Database Patch 2
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
Skipping section #IfNotRow4D supported_external_dataloads load_type ICD10 load_source CMS load_release_date 2019-10-01 load_filename 2020-ICD-10-CM-Codes.zip
Skipping section #IfNotRow4D supported_external_dataloads load_type ICD10 load_source CMS load_release_date 2019-10-01 load_filename 2020-ICD-10-PCS-Order.zip
Skipping section #IfMissingColumn patient_access_onsite portal_login_username
Skipping section #IfMissingColumn api_token token_auth_salt
Skipping section #IfMissingColumn api_token token_auth
Skipping section #IfNotIndex openemr_postcalendar_events index_pcid
Skipping section #IfNotIndex medex_recalls i_eventDate
Skipping section #IfNotIndex medex_outgoing i_msg_date
Skipping section #IfNotRow2D icd10_dx_order_code dx_code U071 active 1
set @newMax = (SELECT MAX(revision) from icd10_dx_order_code)
UPDATE icd10_dx_order_code SET revision = @newMax WHERE dx_code = ‘U071’
Skipping section #IfNotRow2D icd10_dx_order_code dx_code U072 active 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 2 finished.
OpenEMR Version = 5.0.2(2).
Hi @madmax and @jhunyan ,
Thanks for your quick reports, fix, and testing. Because of that, I was able to replace the patch with a working script (the patch file that folks download will now contain this fix). Thanks!
-brady
I received the following error when testing sql_patch.php:
"OpenEMR 5.0.2 Database Patch 2
Applying Patch to site : default
Processing patch.sql …
INSERT INTO list_options (list_id,option_id,title,seq,is_default,activity) VALUES (‘apps’,‘oeSignerRemote’,’./…/portal/sign/assets/signit.php’,30,0,0)
ALTER TABLE form_eye_neuro MODIFY ACT5CCDIST text
Query Error
ERROR: query failed: ALTER TABLE form_eye_neuro MODIFY ACT5CCDIST text
Error: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
…/library/sql_upgrade_fx.php at 933:sqlStatement
…/sql_patch.php at 66:upgradeFromSqlFile(patch.sql) "
Debian 10
mariadb-server 10.3
Any help would be very gratefully received. Thanks
Running it a second time, instead of “INSERT INTO list_options…” I get:
Skipping section #IfNotRow2D list_options list_id apps option_id oeSignerRemote
hi @PeteBoyd ,
Sorry for the delayed reply. See here for details on that issue:
Error Installing Version 5.0.2
Hi,
The 3rd 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 :
-brady
@brady.miller Sorry if it’s me not understanding, but having read that post, isn’t the fix for that MariaDB issue in OpenEMR 5.0.2? Where as I’ve already been running OpenEMR 5.0.2.0, 5.0.2.1 and now hitting the issue in trying to install 5.0.2.2?
That says it’s a MariaDB 10.4+ fix, where as I have MariaDB 10.3.22 on Debian 10. I’ve also now tried the upgrade using OpenEMR 5.0.2.3 (OpenEMR 5.0.2 Database Patch 3) and hit the same issue.
what do you see from mariadb when you run
DESCRIBE form_eye_neuro;
+----------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| id | bigint(20) | NO | PRI | NULL | |
| pid | bigint(20) | YES | | NULL | |
| ACT | char(3) | NO | | on | |
| ACT5CCDIST | varchar(50) | YES | | NULL | |
| ACT1CCDIST | varchar(50) | YES | | NULL | |
| ACT2CCDIST | varchar(50) | YES | | NULL | |
| ACT3CCDIST | varchar(50) | YES | | NULL | |
| ACT4CCDIST | varchar(50) | YES | | NULL | |
| ACT6CCDIST | varchar(50) | YES | | NULL | |
| ACT7CCDIST | varchar(50) | YES | | NULL | |
| ACT8CCDIST | varchar(50) | YES | | NULL | |
| ACT9CCDIST | varchar(50) | YES | | NULL | |
| ACT10CCDIST | varchar(50) | YES | | NULL | |
| ACT11CCDIST | varchar(50) | YES | | NULL | |
| ACT1SCDIST | varchar(50) | YES | | NULL | |
| ACT2SCDIST | varchar(50) | YES | | NULL | |
| ACT3SCDIST | varchar(50) | YES | | NULL | |
| ACT4SCDIST | varchar(50) | YES | | NULL | |
| ACT5SCDIST | varchar(50) | YES | | NULL | |
| ACT6SCDIST | varchar(50) | YES | | NULL | |
| ACT7SCDIST | varchar(50) | YES | | NULL | |
| ACT8SCDIST | varchar(50) | YES | | NULL | |
| ACT9SCDIST | varchar(50) | YES | | NULL | |
| ACT10SCDIST | varchar(50) | YES | | NULL | |
| ACT11SCDIST | varchar(50) | YES | | NULL | |
| ACT1SCNEAR | varchar(50) | YES | | NULL | |
| ACT2SCNEAR | varchar(50) | YES | | NULL | |
| ACT3SCNEAR | varchar(50) | YES | | NULL | |
| ACT4SCNEAR | varchar(50) | YES | | NULL | |
| ACT5CCNEAR | varchar(50) | YES | | NULL | |
| ACT6CCNEAR | varchar(50) | YES | | NULL | |
| ACT7CCNEAR | varchar(50) | YES | | NULL | |
| ACT8CCNEAR | varchar(50) | YES | | NULL | |
| ACT9CCNEAR | varchar(50) | YES | | NULL | |
| ACT10CCNEAR | varchar(50) | YES | | NULL | |
| ACT11CCNEAR | varchar(50) | YES | | NULL | |
| ACT5SCNEAR | varchar(50) | YES | | NULL | |
| ACT6SCNEAR | varchar(50) | YES | | NULL | |
| ACT7SCNEAR | varchar(50) | YES | | NULL | |
| ACT8SCNEAR | varchar(50) | YES | | NULL | |
| ACT9SCNEAR | varchar(50) | YES | | NULL | |
| ACT10SCNEAR | varchar(50) | YES | | NULL | |
| ACT11SCNEAR | varchar(50) | YES | | NULL | |
| ACT1CCNEAR | varchar(50) | YES | | NULL | |
| ACT2CCNEAR | varchar(50) | YES | | NULL | |
| ACT3CCNEAR | varchar(50) | YES | | NULL | |
| ACT4CCNEAR | varchar(50) | YES | | NULL | |
| MOTILITYNORMAL | char(3) | NO | | on | |
| MOTILITY_RS | char(1) | YES | | 0 | |
| MOTILITY_RI | char(1) | YES | | 0 | |
| MOTILITY_RR | char(1) | YES | | 0 | |
| MOTILITY_RL | char(1) | YES | | 0 | |
| MOTILITY_LS | char(1) | YES | | 0 | |
| MOTILITY_LI | char(1) | YES | | 0 | |
| MOTILITY_LR | char(1) | YES | | 0 | |
| MOTILITY_LL | char(1) | YES | | 0 | |
| MOTILITY_RRSO | int(1) | YES | | NULL | |
| MOTILITY_RLSO | int(1) | YES | | NULL | |
| MOTILITY_RRIO | int(1) | YES | | NULL | |
| MOTILITY_RLIO | int(1) | YES | | NULL | |
| MOTILITY_LRSO | int(1) | YES | | NULL | |
| MOTILITY_LLSO | int(1) | YES | | NULL | |
| MOTILITY_LRIO | int(1) | YES | | NULL | |
| MOTILITY_LLIO | int(1) | YES | | NULL | |
| NEURO_COMMENTS | text | YES | | NULL | |
| STEREOPSIS | varchar(25) | YES | | NULL | |
| ODNPA | varchar(50) | YES | | NULL | |
| OSNPA | varchar(50) | YES | | NULL | |
| VERTFUSAMPS | varchar(50) | YES | | NULL | |
| DIVERGENCEAMPS | varchar(50) | YES | | NULL | |
| NPC | varchar(10) | YES | | NULL | |
| DACCDIST | varchar(20) | YES | | NULL | |
| DACCNEAR | varchar(20) | YES | | NULL | |
| CACCDIST | varchar(20) | YES | | NULL | |
| CACCNEAR | varchar(20) | YES | | NULL | |
| ODCOLOR | varchar(50) | YES | | NULL | |
| OSCOLOR | varchar(50) | YES | | NULL | |
| ODCOINS | varchar(50) | YES | | NULL | |
| OSCOINS | varchar(50) | YES | | NULL | |
| ODREDDESAT | varchar(20) | YES | | NULL | |
| OSREDDESAT | varchar(20) | YES | | NULL | |
+----------------+-------------+------+-----+---------+-------+you upgraded from 5.0.1 to 5.0.2?
could rerun the upgrade script and choose 5.0.1 and see if it changes those columns to text
This was a fresh install of 5.0.2.1. (I have another server on which I did a 5.0.1 to 5.0.2.1 upgrade and hit the form_eye_mag_orders issue.) I’ll try what you suggest though, thanks.
How do I “choose” that please?
Presumably you mean I’m going to somehow cause it to run sql/5_0_1-to-5_0_2_upgrade.sql?
In case it’s worth noting, as per installation instructions I have this in place:
sudo chmod 644 library/sqlconf.php
sudo chmod 600 acl_setup.php
sudo chmod 600 acl_upgrade.php
sudo chmod 600 setup.php
sudo chmod 600 sql_upgrade.php
sudo chmod 600 gacl/setup.php
sudo chmod 600 ippf_upgrade.php
And:
sudo chmod 600 admin.php
sudo chmod 600 sql_p
(I’m in the OpenEMR Slack workspace if that’s more convenient)
the upgrade script let’s you choose the version in the dropdown
sql_patch.php just runs, gives no options. Did it once give a drop-down, and I’ve forgotten, and it’s now set something in a config file that I need to reset? Am I confused and I should be running sql_upgrade.php instead?