Adding an interceptor to drop milliseconds for compatibility with MySQL 5.6 and above

Development
1

Summary:

  • Starting with MySQL 5.6, when you save a value with millisecond precision (like a java.util.Date) into a database field with second precision (like OpenMRS date fields), the value is rounded. (Through MySQL 5.5 it was truncated.)
  • e.g. in MySQL 5.5 if I save “2015-06-04 12:34:56.789” => it saves as “2015-06-04 12:34:56”
  • but in MySQL 5.6 if I save “2015-06-04 12:34:56.789” => it saves as “2015-06-04 12:34:57”
  • people have complained about this, but no response from MySQL developers (see bug below)
  • This means that 50% of the time if you try to save a real-time visit or encounter, it will fail OpenMRS validation because it is “in the future”

More details:

The identical fix (adding a DropMillisecondsHibernateInterceptor) has been applied in Mirebalais, Bahmni, Ebola and has been working fine in production for years. And basically you can’t run OpenMRS on MySQL 5.6+ for point-of-care purposes without this fix.

I will add this to openmrs-core shortly, unless anyone objects.

(In fact I asked of anyone had thoughts/objects a year ago, so I think it’s well past time to actually apply this fix. Though I’m surprised that nobody else has ever complained about this. The driver for me noticing this now is that if you try to run Bahmni + Mirebalais together, things fail, because of the incompatible bean definitions.)

4 Likes
2

Sounds fine to me! Will you backport it 1.10.x? We are running the bleeding edge of 1.10.x, so I should be able to remove the interceptor from the PIH EMR as soon as it’s in.

3

No objection, but do we want this to happen for all date fields for all domain objects? I think it would be nice to make the interceptor configurable and tell it for which date fields and types this should happen

4

What would be the real world use case of making it configurable?

Are you thinking that there are implementations/modules that have fields at millisecond precision? Nothing in openmrs-core does.

(It may make sense to use hibernate metadata to verify that it’s not a SQL timestamp field, to be future-proof in case we ever manage to shift from datetime to that.)

5

Can the interceptor be attached to specific entities, or does it run for all of them? Another alternative is UserTypes, not sure which one is better.

6

@lluismf it works for all entities but you can bake code into it to filter some types out. @darius i guess i have no real world reason to make it configurable, i was just imagining that in case i don’t mind what MySQL does to the date field, i should be able to by pass it.