Upgrading from 4.2.0 to OpenEMR 5.0.0

also @smirzaoe ,
What does the schema of your layout_options sql table look like (it is possible that since some upgrades worked and some didn’t that you likely added indexes to text or text-like fields).
-brady

Hi Brady,

Thanks for your questions. That’s putting us in right direction. We were running MySQL 5.5.59 version. So we upgraded MySql to 5.6. After upgrading MySQL, we ran the sql_upgrade.php. We didn’t get that error on layout_options table. However, it is now giving below error now. I checked for the "select max(seq) from list_options where list_id = ‘order_type’ "it is null in prod too.

Processing 4_2_2-to-5_0_0_upgrade.sql …
Skipping section #IfTextNullFixNeeded
ALTER TABLE ar_activity MODIFY sequence_no int UNSIGNED NOT NULL COMMENT ‘Sequence_no, incremented in code’
ALTER TABLE ar_activity ENGINE=“InnoDB”
ALTER TABLE claims MODIFY version int(10) UNSIGNED NOT NULL COMMENT ‘Version, incremented in code’
ALTER TABLE claims ENGINE=“InnoDB”
ALTER TABLE procedure_answers MODIFY answer_seq int(11) NOT NULL COMMENT ‘Supports multiple-choice questions. Answer_seq, incremented in code’
ALTER TABLE procedure_answers ENGINE=“InnoDB”
ALTER TABLE procedure_order_code MODIFY procedure_order_seq int(11) NOT NULL COMMENT ‘Supports multiple tests per order. Procedure_order_seq incremented in code’
ALTER TABLE procedure_order_code ENGINE=“InnoDB”
Skipping section #IfInnoDBMigrationNeeded
Skipping section #IfNotTable valueset
Skipping section #IfMissingColumn openemr_postcalendar_categories pc_active
Skipping section #IfMissingColumn openemr_postcalendar_categories pc_seq
Skipping section #IfNotRow openemr_postcalendar_categories pc_catname Health and Behavioral Assessment
Skipping section #IfNotRow openemr_postcalendar_categories pc_catname Preventive Care Services
SET @max_seq = (select max(seq) from list_options where list_id = ‘order_type’)
INSERT INTO list_options (list_id,option_id,title,seq,is_default) values (‘order_type’,‘enc_checkup_procedure’,‘Encounter Checkup Procedure’,@max_seq+10,0)
ERROR: query failed: INSERT INTO list_options (list_id,option_id,title,seq,is_default) values (‘order_type’,‘enc_checkup_procedure’,‘Encounter Checkup Procedure’,@max_seq+10,0)

Error: Column ‘seq’ cannot be null

/var/www/openemr/library/sql_upgrade_fx.php at 675:sqlStatement
/var/www/openemr/sql_upgrade.php at 56:upgradeFromSqlFile(4_2_2-to-5_0_0_upgrade.sql)

hi @smirzaoe ,
Guessing that you removed the order_type (title Order Types) list (Administration->Lists) in that OpenEMR multisite instance. If you add that list back, does this upgrade script error go away?
-brady

You the man! Yes, Order Types list is not there. Hmm…why would I remove order types from the list? I don’t see Order Statuses list too.I will add them back. I will keep you posted. Thanks a lot!
Mirza

Okay. We added Order Types list in the list_options table and after that we had a few more similar hiccups. Finally we were able to run sql_upgrade.php successfully.

We are able to login now. Which is great. When we tried to import layout_options custom data from Openemr 4.0, into 5.0 it failed. Upon comparing table structures from 4.2 and 5.0, I see there is a small difference. We have to work on to fix the data manually and import, I guess.

hi @smirzaoe ,
You could try to run the sql_upgrade script on your custom layout_options data from 4.0 to 5.0 in a testing openemr instance. Then could try to import that primed 5.0 layout_options data. I think this should work.
-brady

Hi Brady,

Thanks for the suggestion. I did exactly as you suggested and it worked. We have to apply few more custom changes and do thorough testing. We are almost done!
-Mirza

Hi Brady,

Just wanted to let you know that we were able to successfully apply the 5-0-0-Patch-7.

Thanks!

1 Like

what if i am not able to do it? what i should do? please?! thanks

hi @Hite1981, do you mean applying patch 7 or upgrading to 5.0.0?