Upgrading from 4.2.0 to OpenEMR 5.0.0

hi @smirzaoe ,

What is the path of your openemr instance (ie. /var/www/openemr or something else?)?

-brady

Hi Brady,

The path of my openemmr instance is /var/www/openemr

Thanks

Do I need to worry about the file and folder permissions?

hi @smirzaoe ,

Can you paste here what you see in the following files:
/etc/openemr/<the only file here- forgot the name of it>
/var/log/openemr/<the only file here- forgot the name of it>

thanks,
-brady

Hi Brady,

I looked into the install file from one of these folders and I see my issue. Seems like we had bad credentials for one of the databases. We didn’t notice because this is a test region. Thank you so much for your reply. We should be able to make progress after fixing the db credentials issue.

Thank you!

We were successfully upgraded the OpenEMR to 5.0.0. We haven’t applied the 5.0.0 Patch (12/04/17) yet.

Got the login page. After entering the username and password, we got this

ERROR: insert failed: insert into log ( date, event,category, user, groupname, success, comments, crt_user, patient_id) values ( NOW(), ‘login’,‘login’,‘admin’,‘Default’,‘1’,‘success: 99.7.252.73’,NULL,NULL)

Error: Unknown column ‘category’ in ‘field list’

/var/www/openemr/library/log.inc at 45:sqlInsertClean_audit
/var/www/openemr/library/authentication/login_operations.php at 116:newEvent(login,admin,Default,1,success: 99.7.252.73)
/var/www/openemr/library/auth.inc at 50:validate_user_password(admin,Default)
/var/www/openemr/interface/globals.php at 449:include_once(/var/www/openemr/library/auth.inc)
/var/www/openemr/interface/main/main_screen.php at 25:require_once(/var/www/openemr/interface/globals.php)

Trying to read the forum for help. Seems like below two threads are for similar issue:


Not sure what to do at this time. Should I apply the patch 5.0.0 Patch?

@smirzaoe,
Can you please check whether you have run the sql_upgrade.php.If not can you please check by executing the file.

Thanks,
ViSolve

hi,

The ubuntu package should of run sql_upgrade.php and would check the /var/log/openemr/<the only file here- forgot the name of it> to see why the upgrade didn’t run correctly. Also, what do you see if go to admin.php in your browser?

-brady

Good morning guys,

Thank you for the quick responses. I had tried running sql_upgrade.php but I got “HTTP ERROR 500”. I ran sql_patch.php which appeared to ran successfully, though I am not sure if I should have ran that or not.
I ran sql_upgrade.php still getting that “HTTP ERROR 500”.

That being said, thanks to Brady I went to admin.php and I see the page shown below which is great. I am able to login to the sites which are showings as version 5.0.0. However, when I click on “Upgrade Database” link next to 4.2.0, still getting that HTTP 500.

hi @smirzaoe ,

The ubuntu package (for security reasons), changes permissions to sql_upgrade.php script after install/upgrade is complete. So, you just need to change permissions to that script (allow all to read) and then it will work (after done using it , recommend again changing it to the strict permissions).

As an aside, it appears your are using multisite and the ubuntu package should upgrade each of these, so appears something may of gone wrong there. Feel feel to email me your /var/log/openemr/<the only file here- forgot the name of it> at brady.g.miller@gmail.com (and we can ensure there isn’t a bug in the package), if you aren’t comfortable pasting it publicly.

thanks,
-brady

Thanks Brady. I changed the file permission of sql_upgrade.php and ran it. After running for a few minutes, it failed with the below message at the end of the file. I will email you the install.log file from /var/log/openemr/

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”
Starting migration to InnoDB, please wait.
ERROR: query failed: ALTER TABLE layout_options ENGINE=?

Error: The used table type doesn’t support FULLTEXT indexes

/var/www/openemr/library/sql_upgrade_fx.php at 297:sqlStatement
/var/www/openemr/library/sql_upgrade_fx.php at 653:MigrateTableEngine(layout_options,InnoDB)
/var/www/openemr/sql_upgrade.php at 56:upgradeFromSqlFile(4_2_2-to-5_0_0_upgrade.sql)

Thanks,
Mirza

I tried re-running sql_upgrade.php to upgrade database from 4.2.2. to 5.0.0. but got the same above error. I don’t know what else to try.

hi @smirzaoe,
What Mysql/MariaDB version are you using?
-brady

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