Error Upgrade 5.0.1 to 5.0.2


(Luis Angel Uriarte) #1

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:

Thank
Luis


Planning for OpenEMR 5.0.2 release
(Brady Miller) #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?

(Jerry P) #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.


(Luis Angel Uriarte) #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.


(Jerry P) #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?


(Luis Angel Uriarte) #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


(Jerry P) #7

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