Switching Servers 4.1.2 (on Linux) to 5 (on Windows)

drjccaldwell wrote on Thursday, March 09, 2017:

I have pseudo successfully migrated an old system from linux to windows. Initial start went great on Windows with a clean install of OpenEMR. Imported data from old. No obvious problems there. Then imported MySQL DB dump file and ran script to upgrade.

Now I can log in with old user name and passwords so obviously most of it worked but I have this error:


Reminders
ERROR: query failed: SELECT dr.pid, dr.dr_id, dr.dr_message_text,dr.dr_message_due_date, u.fname ffname, u.mname fmname, u.lname flname FROM dated_reminders dr JOIN users u ON dr.dr_from_ID = u.id JOIN dated_reminders_link drl ON dr.dr_id = drl.dr_id WHERE drl.to_id = ? AND dr.message_processed = 0 AND dr.dr_message_due_date < ADDDATE(NOW(), INTERVAL 5 DAY) ORDER BY dr_message_due_date ASC , message_priority ASC LIMIT 0,

Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘’ at line 10

C:\xampp\htdocs\openemr\library\dated_reminder_functions.php at 47:sqlStatement
C:\xampp\htdocs\openemr\interface\main\dated_reminders\dated_reminders.php at 63:RemindersArray(5,1489014000,)
C:\xampp\htdocs\openemr\interface\main\messages\messages.php at 67:require_once(C:\xampp\htdocs\openemr\interface\main\dated_reminders\dated_reminders.php)


I have looked at the above named files but I am not a php programmer and I do not see any open parenthesis or other glaring issues.

Any ideas?

Thank you,
Clif

bradymiller wrote on Friday, March 10, 2017:

Hi,

Looks like issue may be that $alerts_to_show is empty:

What setting do you have at Administration->Globals->CDR->‘Dated reminders maximum alerts to show’ ?

-brady
OpenEMR

Following was posted by drjcc on old sourceforge forum:

Please forgive the delay in responding. I cannot get into the new system "Globals" system. I did however find the "/library/globals,inc.php" file and on line 1924 changed the default # from 5 to 2 just to see if that would do anything. dated_reminders_max_alerts_to_show' => array( xl('Dated reminders maximum alerts to show'), 'num', // data type '2', // default xl('Dated reminders maximum alerts to show') This did not change a thing. Looked on the old system and I must be missing something because I do not see a switch in the "Administration>Globals>CDR>Dated reminders maximum alerts to show?" Will keep plugging away. Thank you for the response. Clif

Hi Clif,

This is actually making more sense now. The settings are stored in the database and it appears something broke and didn’t bring in all the new global settings into the database, and thus things are breaking that require a global setting in OpenEMR. What error do you see in the error log when you try to go to Globals settings in OpenEMR?

-brady
OpenEMR

Brady,
Thank you for the help.

The following error comes up with attempting to go into the Administration>Globals menu:


ERROR: query failed: SELECT dr.pid, dr.dr_id, dr.dr_message_text,dr.dr_message_due_date, u.fname ffname, u.mname fmname, u.lname flname FROM dated_reminders dr JOIN users u ON dr.dr_from_ID = u.id JOIN dated_reminders_link drl ON dr.dr_id = drl.dr_id WHERE drl.to_id = ? AND dr.message_processed = 0 AND dr.dr_message_due_date < ADDDATE(NOW(), INTERVAL 5 DAY) ORDER BY dr_message_due_date ASC , message_priority ASC LIMIT 0,


I looked at the PHP tmp files and did not see any reference to an error.

Thank you,
Clif Caldwell

Attempted to change the /library/globals.inc.php file line 1924 from 5 to 0 and no change. So evidently it is in the mysql structure. Is there a way to go back and change the 4.1.2 MySQL structure, do another dump, rebuild the database?

I would assume that I would need to do a clean install of OpenEMR also and re-import the data and database structure.

This new box is obviously not yet a productions machine.

Thanks,
Clif Caldwell

I’d call this a bug. I suspect the problem is that the new global is not set at upgrade time, rather when you next save global settings.

Try modifying the file library/dated_reminder_functions.php and adding this line after the line that begins with “function RemindersArray”:

if (empty($alerts_to_show)) $alerts_to_show = 5;

It’s odd, though, because the upgrade script sql_upgrade.php should be importing the new globals. I’d be interested to see what the output looks like when run the sql_upgrade.php script.

You’re right, that should have happened. Any time sql_upgrade.php is run it’s important to scroll through the output and look for errors (will be in red).

That line eliminated the error message. However going into “Globals” still does not work.
I will run the sql_upgrade.php script again and will look for errors.

Thank you,
Clif

Probably a different error when going to Globals, right? Make sure when you run sql_upgrade that you specify an early enough original version number.

OK I have had time to rerun the upgrade script. The is the abridged output including the error:


Processing patch.sql …
Updating global configuration defaults…

ERROR: query failed: SELECT * FROM lang_definitions JOIN lang_constants ON lang_definitions.cons_id = lang_constants.cons_id WHERE lang_id=? AND constant_name = ? LIMIT 1

Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation ‘=’

C:\xampp\htdocs\openemr\library\translation.inc.php at 36:sqlStatementNoLog
C:\xampp\htdocs\openemr\library\globals.inc.php at 2732:xl(Māori)
C:\xampp\htdocs\openemr\sql_upgrade.php at 72:require_once(C:\xampp\htdocs\openemr\library\globals.inc.php)


So you were right. It was when it hit the “Globals” section. The weird thing to me is that the script stopped here on this run but I do not remember it stopping before. I did make sure I was going from ver. 4.1.2 to 5.0.

Clif

Hi @Clif_Caldwell ,

Recommend manually making the following changes (in the below 2 commits) to
the library/globals.php script:


Then will be able to run upgrade successfully and open the globals gui in
OpenEMR.

Sorry we don’t have this in patch form yet (plan to do this over the next
several weeks).

-brady

Please forgive my ignorance. Should I change the previous globals.php (version 4.1.2) or the new globals.php (version 5.0).

With humility,
Clif Caldwell

You are a very bright man. Everything went off without a hitch. Everything is available and working except tabs and nav are at the top not the left side. I am guessing I need to change to tree view and something else to change it to the old familiar screen with the nav on the left and in tree form.

Thank you does not express how grateful I am.

Clif Caldwell

Nice!

The default setting in 5.0 is the Tabs user interface. It can be changed
back to the Frames user interface at Administration->Appearance->Layout
http://www.open-emr.org/wiki/index.php/Administration_Globals#Layout_.28need_to_logout.2Flogin_after_change_this_setting.29

-brady