Error Upgrade 5.0.1 to 5.0.2

Day-to-Day Users
#1

Admin note: Anyone that is failing on the database upgrade at the eye forms tables, run sql_upgrade.php from browser again and this error will be skipped and the rest of the upgrade should complete allowing system use. I’ll try to fix this in first v5.0.2 patch.

This error:
…
OSREDDESAT varchar (20) DEFAULT NULL, PRIMARY KEY neuro_link (id), UNIQUE KEY id_pid (id,pid)) ENGINE = InnoDB
CREATE TABLE form_eye_locking ( id bigint(20) NOT NULL COMMENT ‘Links to forms.form_id’, pid bigint(20) DEFAULT NULL, IMP text, PLAN text, Resource varchar(50) DEFAULT NULL, Technician varchar(50) DEFAULT NULL, LOCKED varchar(3) DEFAULT NULL, LOCKEDDATE timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, LOCKEDBY varchar(50) DEFAULT NULL, PRIMARY KEY locking_link (id), UNIQUE KEY id_pid (id,pid)) ENGINE = InnoDB
ALTER TABLE form_eye_mag_orders CHANGE id id BIGINT(20) NOT NULL AUTO_INCREMENT, CHANGE ORDER_PID pid BIGINT(20) NOT NULL, ADD form_id bigint(20) NOT NULL AFTER id, DROP INDEX VISIT_ID
ALTER TABLE form_eye_mag_orders ADD UNIQUE KEY VISIT_ID (pid, ORDER_DETAILS, ORDER_DATE_PLACED)

Query Error

ERROR: query failed: ALTER TABLE form_eye_mag_orders ADD UNIQUE KEY VISIT_ID (pid, ORDER_DETAILS, ORDER_DATE_PLACED)

Error: Duplicate entry ‘1–0000-00-00’ for key ‘VISIT_ID’

C:\xampp\htdocs\openemr\library\sql_upgrade_fx.php at 933:sqlStatement
C:\xampp\htdocs\openemr\sql_upgrade.php at 88:upgradeFromSqlFile(5_0_1-to-5_0_2_upgrade.sql)
…
My table:

openemrupgrade
openemrupgrade1138×179 9.93 KB

Thank
Luis

Planning for OpenEMR 5.0.2 release
#2

hi @luisuriarte ,
The eye form upgrade was complicated.
Quick questions:

  1. Do you have eye form entries in your 5.0.1 install?
  2. What OS are you using?
  3. Are you coming from 5.0.1 (is so, which patch) or from 5.0.2-dev?
#3

I think I looked at this issue once before but don’t remember when.
I don’t think the index is being dropped as hoped. Maybe because columns belonging to the index are being renamed before index is dropped. ie
ALTER TABLE form_eye_mag_orders
CHANGE id id BIGINT(20) NOT NULL AUTO_INCREMENT,
CHANGE ORDER_PID pid BIGINT(20) NOT NULL,
ADD form_id bigint(20) NOT NULL AFTER id,
DROP INDEX VISIT_ID;
ALTER TABLE form_eye_mag_orders
ADD UNIQUE KEY VISIT_ID (pid, ORDER_DETAILS, ORDER_DATE_PLACED);

ORDER_PID is part of original index. I’d try to move the drop index up to first in the alter table.
Also if I remember correctly that now that the condition that sets off these upgrades has been satisfied, running sql_upgrade again will skip this alter table.

#4

Hi, I’m using 5.0.1 (7) with xampp on windows (chrome browser). I installed the eyes form, but never use it. I run sql_upgrade again and it worked. Thank you. Thank you very much for the work you are doing. Openemr is a great system.

#5

Thanks @luisuriarte but it takes a community. :slight_smile:
I had the same problem on windows and I believe Brady and I determined it was a mysql version issue.
Can you share what version php and mysql/maria and/or the xampp version?

#6

XAMPP Version 7.2.15

  • Apache 2.4.38
  • MariaDB 10.1.38
  • PHP 7.2.15 (VC11 X86 32bit thread safe) + PEAR

regards
Luis

#7

@brady.miller @rmagauran
I’m thinking this is a Windows compiled version sql issue. I guess time will tell.

New artifact shows after upgrade 5.0.0 patch 7 to 5.0.2 patch 4
#8

Hi
I got the same error while upgrading to 5.0.2.

OpenEMR Database Upgrade
Processing 5_0_1-to-5_0_2_upgrade.sql
……
ALTER TABLE form_eye_mag_orders ADD UNIQUE KEY VISIT_ID (pid, ORDER_DETAILS, ORDER_DATE_PLACED)

Query Error

ERROR: query failed: ALTER TABLE form_eye_mag_orders ADD UNIQUE KEY VISIT_ID (pid, ORDER_DETAILS, ORDER_DATE_PLACED)

Error: Duplicate entry ‘338–0000-00-00’ for key ‘VISIT_ID’

/var/www/html/openemr/library/sql_upgrade_fx.php at 933:sqlStatement
/var/www/html/openemr/sql_upgrade.php at 92:upgradeFromSqlFile(5_0_1-to-5_0_2_upgrade.sql)

And my “form_eye_mag_orders” table is similar to the one in the first post.

Apparently my OpenEMR instance doesn’t work after that unsuccessful attempt to upgrade giving me an error message: “Failure during database access! Check server error log.” I have to do something ASAP.

My question is: can I use the same script (sql_upgrade.php) again to finish the upgrade if I delete all the records from the table using phpMyAdmin or there is another recommended procedure for it?

I am using

  • AWS Linux 2018.03
  • Apache 2.4.39
  • PHP 7.2.19
  • MySQL 5.6.4
#9

yes run it again. Afterwards check your eye form if using eye module.