Lucene search index failing on some patients after platform upgrade to 2.4.0

We have recently upgraded KenyaEMR to run on platform 2.4.0. We are currently experiencing failing lucene indexing on certain patient records and that affects searching and even reporting. The trace can be found at pastebin.

We have investigated the issue and it points to something related to mysql connector for java 8. Has anyone experienced something similar, and how did you go about it?

Our setup has the following:

  1. Ubuntu 20.04
  2. Tomcat 9
  3. MySQL 5.6
  4. Java 11

Update: I have appended serverTimezone=UTC to the mysql connection string (according to known limitations documentation) and indexing can pass. Searching and reports now work fine although we are still not confident as this could come with some time loss. More updates to follow.

@dkayiwa @ibacher @ssmusoke @patryllus @makombe

Just for troubleshooting purposes, without doing anything to the mysql connection string, are you able to delete the lucene folder from the application data folder and try again?

I just tried your suggestion and the error remains the same. Thanks

What happens if you try with serverTimezone=EAT?

It still errors.

@corneliouzbett I seem to recall we ran into a similar issue while using the cohort module. Do you know what the fix was?

Before we got the root cause, we had to change some reporting code from using hql to sql and the issue got resolved. May be it was similar

1 Like

@aojwang has this been resolved? @drizzentic and @corneliouzbett , now that we have upgraded to 2.4, has this caused a problem for us?

Hey @jdick, @bett was able to resolve this by downgrading the MySQL connector to mysql-connector 5.1.x in openmrs core 2.4. So far we haven’t experienced any issues with indexing or search.

The platform, therefore, cannot support MySQL versions higher than 5.6

Do you mind explaining this a bit more?

I think @aojwang meant downgrading MySQL-connector to 5.1.x won’t work for MySQL versions higher than 5.6 (i.e 5.7 and 8.0)