Cannot choose patient from list due to mysql 8

v.5.02 with patch 1 running on Windows10 (Apache2 4, PHP7.1, MySQL8, php.ini as indicated in setup and in FAQ.

Installation appears to work, can add new patient. But, when choosing patient from patient list, get this error…

Query Error

ERROR: query failed: SELECT title FROM lists WHERE type=‘allergy’ AND activity=1 AND ( enddate IS NULL OR enddate=’’ OR enddate > NOW() ) AND pid=?

Error: Incorrect DATE value: ‘’

C:\Apache2\htdocs\openemr\library\clinical_rules.php at 305:sqlStatement
C:\Apache2\htdocs\openemr\interface\patient_file\summary\demographics.php at 50:allergy_conflict(2,new,username)

Also, clicking on Manage Modules gives this error…

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator at admin@example.com to inform them of the time this error occurred, and the actions you performed just before this error.

More information about this error may be available in the server error log.

… apache2 error log …

[Fri Feb 21 16:46:55.739472 2020] [php7:notice] [pid 4016:tid 1388] [client ::1:50273] PHP Notice: Trying to access array offset on value of type bool in C:\Apache2\htdocs\openemr\library\translation.inc.php on line 44, referer: http://localhost/openemr/interface/main/tabs/main.php?token_main=gibberish

any ideas? thanks!

hi @sole_practitioner, might be a mysql 8 bug

you can edit C:\Apache2\htdocs\openemr\library\clinical_rules.php at line 305 and remove
OR enddate=''

Hello stephenwaite,
That got it moving, but led to many other errors, all related to date. I’m not married to Windows, or any particular version of “AMP”. Is there another OS that openemr is faster/more stable? I tried Debian and I tried Docker a year or so ago without much luck. What is the “favorite”. Thanks. -tbh

hi @sole_practitioner ,

Mysql8 causes odd issues with php if don’t set the following for your mysql setting:
default-authentication-plugin = mysql_native_password

I am guessing that will fix this issue (I’d rec a reinstall after fix above mysql setting).

Regarding docker, it should be super simple, but there’s a recent thread where a user is having issues getting docker-compose to work on a mint (ubuntu based) os. If we can get that straightened out, then getting a OpenEMR docker install literally just takes several commands followed by a 10 minute wait:
XAMPP version for EMR - 5.0.2. Windows 10

hi @brady.miller,
I am getting the same Invalid Date error when trying to select a patient, and I have set the

I am working through trying to set different date formats in OpenEMR to see if that helps any, but wondering if anyone else found a way through this one.

I’m on Windows 10, MySQL 8.0.19, php 7.4.1

Thanks!

hi @stephengreen, did you restart mysql after changing the setting?

Yes, and I even tried to re-install OpenEMR, but got the same error. I did go to the clincal_rules.php file and remove a reference to enddate = ‘’. That removed the error, but as @sole_practitioner found, all comparisons of a date equal to ‘’ return the same error. I will try to remove all of those situations, but not sure if that is safe to do or not. All of those that I did see, also had a comparison of date = NULL, so maybe ok? Thanks for your help

hi @stephengreen, i’d wait until the mysql8 is investigated a little more, in the meantime try to follow the docker install mentioned above

thank you

0 length string as date is invalid. Until old way of coding is stripped, temporary workaround is to modify sql where db session is established with

$db->setSessionVariable('sql_mode','ALLOW_INVALID_DATES');

the manual offers this:

ALLOW_INVALID_DATES

Do not perform full checking of dates. Check only that the month is in the range from 1 to 12 and the day is in the range from 1 to 31. This may be useful for Web applications that obtain year, month, and day in three different fields and store exactly what the user inserted, without date validation. This mode applies to DATE and DATETIME columns. It does not apply TIMESTAMP columns, which always require a valid date.

With ALLOW_INVALID_DATES disabled, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES .

hi, looks like related to this issues:

I also confirmed this bug. It seems like every mysql8 versions release is causing new bugs :slight_smile:
(we just recently fixed a mysql8 bug in a prior version)

btw,

Here’s a nice post regarding this issue (see last post):

And how it was fixed on another project:

At the end of the day, it just seems like mysql is trying to convert everybody to mariadb :slight_smile:

2 Likes

Why would mysql introduce a breaking change on a patch release??? just insane. Not even clear what the solution is going to be…

https://bugs.mysql.com/bug.php?id=95462
states:
"
This is intended behaviour
"

Probably gonna be a very large amount of places where will need to fix this. I think we’ll need to work in a function maybe to encapsulate all places where need to fix this (then will be easy to make adjustments to them all at once if doesn’t work etc.):

function dateEmptySql ($sqlColumn, $time = false)
{
    if ($time) {
        $return = " (`"  .  $sqlColumn . "` IS NULL OR `" .  $sqlColumn . "`= '0000-00-00 00:00:00') "
    } else {
        $return = " (`"  .  $sqlColumn . "` IS NULL OR `" .  $sqlColumn . "`= '0000-00-00') "
    }  
}
1 Like

I want to work on this issue. can you please help me to create this issue on github.

Wow, you have sold me on docker! I was able to install a windows version, used the mariadb container and presto!

In my efforts (pre-docker), I noticed that the default my.ini file for windows installation of mysql has…
default_authentication_plugin=mysql_native_password

note the syntax - I believe Brady’s syntax is correct

default-authentication-plugin=mysql_native_password

@im-Amitto This could become a big deal.

There have been warnings since 5[.7] that do not put invalid dd or mm in date field. Allowing some scripts to continue to push 0 length string into date field and supporting it in code is bad practice. One can possibly have a custom datetime transform and gloss over current state. But that also means all external applications will now need to incorporate that fix or risk breaking at unexpected places.

To fix it correctly,

  1. Scrub code looking for ‘0000-00-00’ and replace with simple IS NULL. This is easier said than done because some parts of logic may expect ‘0000-00-00’ or ‘0000-00-00 00:00:00’ as legit value with specific meaning.
  2. Build a simple library that uses dictionary for locating every datetime field and runs a simple update replacing ‘0000-00-00’ by NULL. This should also replace the defaults e.g. batchcom table

msg_date_sent datetime NOT NULL default ‘0000-00-00 00:00:00’,

  1. For now include the library in logon script to run it once depending on value of an entry in globals table for something like ‘fixed_null_dates’.
  2. Also need to fix the sqls that seed the database as well so future installations are not set up with invalid dates.

I will give it a quick try even if I fail(most probably :grimacing:) I will get a basic idea about the current database structure.

Hi @im-Amitto ,

Go for it. I’d break it down into steps with the first step being the most important (since this will fix the bug in the short-term).

  1. Encapsulate the places in a function (see my post above).

The following steps could essentially by the basis for a GSoC project (ie. modernizing database) :slight_smile:

  1. Address bad date/datetime default in sql/database.sql and sql/5_0_2-to-5_0_3_upgrade.sql

  2. Convert “bad” fields stored in database (which will identify in step 2) via a upgrade function called from sql/5_0_2-to-5_0_3_upgrade.sql

  3. Support the default sql_mode settings (https://mariadb.com/kb/en/sql-mode/) (https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html) (note mysql8 has more by default). Note that at this time OpenEMR prevents many issues by overriding the sql_mode when it makes the database connection (search the code for sql_mode to see that I mean).

  4. Support utf8mb4 encoding. OpenEMR now supports utf8 and I’m guessing just need to truncate some key lengths to support utf8mb4.

-brady

1 Like

Thanks, i will start working on it.