Problem with Mysql 5.6

[Mark Goodrich]

We've been looking to upgrade from mysql 5.5 to 5.6 because we've seen
significant performance improvement for some of our reports (some
reports that took hours on 5.5 take seconds on 5.6). However, we just
upgraded one of our staging servers to 5.6 and discovered an annoying
complication.

Evidently, 5.5 only supports DATETIME with precision to the nearest
second. Starting in 5.6, milliseconds are supported up to 6 digits of
precision. However to support backward compatibility, by default in 5.6
DATETIMEs are still only stored to the nearest second. We want
backwards compatibility, so so far so good....

The problem is that there is a change in the way mysql handles saving
DATETIMEs that it receives with a millisecond component. In 5.5, it
simply drops the millisecond component, while in 5.6 it rounds. The
problem with this is that since java Dates go to the millisecond, if you
save a date and then immediately retrieve it in a new session, you can
run into (and we are seeing) issues like this:

Date someDate = new Date()
(save someDate, [if ms >.500, mysql rounds up], close session)
(new session, retrieve someDate)
someDate.after(new Date()) == true

Specifically, we are running into a problem on the Mirebalais patient
dashboard where if you click the "start a new visit" button, on page
reload the system doesn't always display the visit as being open. I
would also guess we might start seeing some failing validation issues in
places are well (shouldn't normally be a problem, but any object that is
reloaded and then resaved less than a second after creation, it is
possible).

Thoughts? We haven't found any documentation online yet as to how to
modify this behaviour. Obviously for backwards compatibility reasons,
changing all the DATETIME fields so that they store milliseconds is a
non-starter. The only idea we have so far is to just make sure we zero
out the millisecond component of encounterDate, visitDate, etc, when
saving. It's possible that Hibernate might be able to help us... we
need to research a little more in that area.

Take care,
Mark

[Lluis Martinez]

You can try defining a UserType in Hibernate, and do the rounding there.

At work we have a UserType that trims and converts strings to null if empty:

                <property name="description" column="DESCRIPTION" type="es.tao.framework.db.type.StringToNullType"/>

StringToNullType extends org.hibernate.usertype.UserType and does the stuff in the nullSafeSet method.

Cheers

--
OpenMRS Developers: http://go.openmrs.org/dev
Post: d...@openmrs.org | Unsubscribe: dev+uns...@openmrs.org
Manage your OpenMRS subscriptions at https://id.openmrs.org/

To unsubscribe from this group and stop receiving emails from it, send an email to dev+uns...@openmrs.org.

[Mark Goodrich]

We've come up with a few possible solutions:

1) Do nothing overall, and just fix errors when they occur (in this case, we've fixed the one problem we had by making sure the method to ensure an active visit in EMR API always zeros out the millisecond component when creating a new visit)

2) Create a Hibernate Interceptor that listens for the saving of *any* Date object and trims off the milliseconds component in that case

3) Update all our DATETIME columns to DATETIME(3) --i.e., update the columns to support milliseconds

Thoughts as to the best approach? If we choose #3, we'd end up with a custom data model that differs slightly from the OpenMRS model, which could cause us complications down the road. #2 seems like the best choice to me (though there isn't complete agreement between us here :)  What would people think about including #2 in core, perhaps configurable by a global property?

Take care,
Mark

[Darius Jazayeri]

Hi All,

FYI, we went with #2, creating a Hibernate Interceptor that will strip milliseconds off of any date that is saved, which gets us the (desired) MySQL 5.5 behavior under MySQL 5.6.

The commit is here: https://github.com/PIH/openmrs-module-mirebalais/commit/b27827bc9b6b168ba5eaaeb85198683bb5571e03

We would like to move this functionality into OpenMRS core in some way. I have created TRUNK-4252 to track this and put it in the Needs Design state.

-Darius

[Burke Mamlin]

Any chance that we could leverage the solution for this issue to move us to (or toward) timestamps instead of datetimes in the database?

-Burke

[Darius Jazayeri]

I thought about this a bit, but wasn't able to envision any magic bullet.

I suppose we _could_ explore something like (in 1.11) changing all our database date columns to be timestamps, while leaving the java fields as java.util.Date, and then using this interceptor to do any necessary conversion...

-Darius

[Darius Jazayeri]

We just ran into what I think is the same issue on the Ebola project I'm working on.

I'd like to apply this fix directly to OpenMRS core, since (a) more and more people will be using MySQL 5.6, and (b) I don't see any downside to doing this even on older MySQL versions. Any objections?

-Darius