Ticket #50 (closed defect: worksforme)

Opened 4 years ago

Last modified 3 years ago

storing TEXT fields does not work in production environment

Reported by: kees.vanbochove@… Owned by: kees.vanbochove@…
Priority: major Milestone:
Component: Export functionality Version:
Keywords: Cc:
Product: Operating system:
URL: Hardware:

Description

Storage of a TEXT field does not work in production environment. When a string longer than 255 characters is entered, we get a Postgres error (see below), although in TemplateField? the constraint is defined:

static mapping = {

tablePerHierarchy false

templateTextFields type: 'text'

}

Hibernate seems to pass this to the in-mem HSQLDB, but not to Postgres.

Error:
Caused by: org.springframework.dao.DataIntegrityViolationException?: Could not execute JDBC batch update; SQL [insert into study_template_text_fields (study_id, template_text_fields_idx, template_text_fields_elt) values (?, ?, ?)]; nested exception is org.hibernate.exception.DataException?: Could not execute JDBC batch update

at dbnp.studycapturing.TemplateEntity?.invokeMethod(TemplateEntity?.groovy)
at BootStrap?$_closure1_closure50_closure102.doCall(BootStrap?.groovy:550)
at BootStrap?$_closure1_closure50.doCall(BootStrap?.groovy:549)
at BootStrap?$_closure1.doCall(BootStrap?.groovy:540)
at grails.util.Environment.evaluateEnvironmentSpecificBlock(Environment.java:251)
at grails.util.Environment.executeForEnvironment(Environment.java:244)
at grails.util.Environment.executeForCurrentEnvironment(Environment.java:220)

Caused by: org.hibernate.exception.DataException?: Could not execute JDBC batch update

... 7 more

Caused by: java.sql.BatchUpdateException?: Batch entry 0 insert into study_template_text_fields (study_id, template_text_fields_idx, template_text_fields_elt) values (130, Description, C57Bl/6 mice were fed a high fat (45 en%) or low fat (10 en%) diet after a four week run-in on low fat diet. After 1 week 10 mice that received a low fat diet were given an IP leptin challenge and 10 mice of the low-fat group received placebo injections. The same procedure was performed with mice that were fed the high-fat diet. After 4 weeks the procedure was repeated. In total 80 mice were culled.) was aborted. Call getNextException to see the cause.

at org.postgresql.jdbc2.AbstractJdbc?2Statement$BatchResultHandler?.handleError(AbstractJdbc?2Statement.java:2533)
at org.postgresql.core.v3.QueryExecutorImpl?.processResults(QueryExecutorImpl?.java:1317)
at org.postgresql.core.v3.QueryExecutorImpl?.execute(QueryExecutorImpl?.java:350)
at org.postgresql.jdbc2.AbstractJdbc?2Statement.executeBatch(AbstractJdbc?2Statement.java:2592)
at org.apache.commons.dbcp.DelegatingStatement?.executeBatch(DelegatingStatement?.java:297)
... 7 more

Change History

Changed 4 years ago by j.a.m.wesbeek@…

"Hibernate seems to pass this to the in-mem HSQLDB, but not to Postgres."

That field has changed; have you rebuilt the database? Otherwise these fields are still 'String' which has a maximum of 255 characters...

Hence: Not a bug...

Changed 4 years ago by j.a.m.wesbeek@…

bug confirmed while inserting 5 paragraphs of Lorem ipsum in the study description on Apache Tomcat 6 / PostgreSQL 8.1.11 / WAR created from svn revison #512:

Caused by: java.sql.BatchUpdateException?: Batch entry 0 insert into study_template_text_fields (study_id, template_text_fields_idx, template_text_fields_elt) values (247, Description, Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nunc volutpat consequat faucibus. Phasellus non lorem quis lacus pretium laoreet. Nullam facilisis interdum lacus ac dignissim. Donec varius metus eu ante placerat non posuere sapien tincidunt. Maecenas cursus turpis nec tellus vestibulum at rhoncus nunc placerat. Aliquam erat volutpat. Ut a magna odio. Sed eu purus at elit fringilla interdum nec quis libero. Cras lorem enim, placerat eget sagittis non, sagittis ac lorem. Sed et odio nunc, vel congue lectus. In a odio leo, sed pellentesque urna. Maecenas in lobortis ligula. Etiam rutrum luctus sollicitudin. Maecenas faucibus lacinia urna non ultricies. Pellentesque vehicula, lorem malesuada hendrerit tincidunt, tellus libero tempor eros, vel pharetra felis tortor eget turpis. Suspendisse lobortis aliquam condimentum. Sed ante nunc, faucibus sed ultricies in, eleifend ut nunc.

Suspendisse sed mauris dolor, non volutpat elit. Curabitur non sollicitudin nulla. Cras euismod, diam eu imperdiet auctor, dui ante consequat erat, sit amet euismod tortor quam ut nisi. Vestibulum nec viverra tellus. In hac habitasse platea dictumst. Aliquam commodo hendrerit pharetra. Duis porta metus sed est fringilla sollicitudin. Mauris posuere adipiscing lorem, vitae scelerisque orci elementum vitae. Nunc vel quam dui, nec consectetur justo. In eu enim metus, vitae vulputate libero. Aliquam erat volutpat.

Proin et turpis condimentum leo congue pharetra. Duis adipiscing porttitor felis, vel fermentum sapien rutrum luctus. Nam ullamcorper adipiscing feugiat. Duis eu magna urna. Praesent est nunc, ullamcorper eget accumsan fermentum, fringilla eget eros. Integer nec lorem sem, non hendrerit velit. Phasellus justo est, porttitor vitae suscipit id, pharetra at ipsum. Nullam quam velit, vulputate eu tincidunt aliquam, semper et nulla. Suspendisse egestas laoreet consequat. In hac habitasse platea dictumst. Ut diam magna, interdum id vulputate eget, pellentesque vitae turpis. Suspendisse potenti. Aenean eget quam turpis. Nam urna felis, luctus molestie pellentesque sit amet, dictum et neque. Suspendisse potenti. Ut et purus risus. Curabitur ultricies neque eget ante pulvinar id pretium felis posuere. Etiam id libero nec eros accumsan consectetur sed a lorem. Curabitur convallis placerat sapien a pharetra.

Fusce condimentum risus non orci pellentesque adipiscing. Fusce at enim vitae enim porta tincidunt et ut urna. Sed orci lorem, venenatis id malesuada a, pellentesque quis eros. Nunc a blandit sapien. Morbi vestibulum pretium neque, et lobortis ante luctus sed. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Phasellus non justo justo, tristique semper dui. Sed cursus blandit nulla ut auctor. Curabitur porttitor, erat et tincidunt tristique, libero ligula mattis tortor, ac euismod tellus libero in neque. Cras id mi sit amet diam gravida euismod ut in erat. Quisque pellentesque nulla quis leo tempor accumsan. Nulla eu dolor non leo faucibus accumsan sed vitae leo. In vel enim a elit aliquet vehicula. Maecenas porta nulla in tortor bibendum suscipit.

exception → org.springframework.dao.DataIntegrityViolationException?: Could not execute JDBC batch update; SQL [insert into study_template_text_fields (study_id, template_text_fields_idx, template_text_fields_elt) values (?, ?, ?)]; nested exception is org.hibernate.exception.DataException?: Could not execute JDBC batch update, see log for stacktrace
....
Nullam nisi metus, ornare a malesuada in, rhoncus non odio. Nulla suscipit mauris a arcu luctus sed ultrices sapien ornare. In volutpat tincidunt fermentum. Praesent lectus dui, blandit sed cursus nec, commodo vitae tortor. Nunc cursus enim id sapien iaculis imperdiet. Nam bibendum, erat eu dictum semper, eros ante tristique erat, at suscipit justo arcu a nunc. Donec tempor est at urna sagittis nec sodales dui viverra. Quisque blandit neque id mi mollis interdum interdum lacus euismod. Aliquam ac nisi eros, eget feugiat nibh. Sed pellentesque sagittis leo, id congue est porta id. Nullam faucibus dolor quis diam facilisis sit amet scelerisque augue tincidunt. ) was aborted. Call getNextException to see the cause.

Changed 4 years ago by kees.vanbochove@…

On the previous comment: the database was updated, this remains a real bug.

See also #96, which turned out to be a result of this issue.

Changed 4 years ago by kees.vanbochove@…

This is a really strange bug if you look into the tables in Postgres:

gscf=# d subject_template_text_fields

Table "public.subject_template_text_fields"
Column | Type | Modifiers


subject_id | bigint |
template_text_fields_string | text |
template_text_fields_idx | character varying(255) |
template_text_fields_elt | text | not null

gscf=# d study_template_text_fields

Table "public.study_template_text_fields"

Column | Type | Modifiers


study_id | bigint |
template_text_fields_string | character varying(255) |
template_text_fields_idx | character varying(255) |
template_text_fields_elt | character varying(255) | not null

There hardly can be a reason for this, because both classes extend from the same TemplateEntity?. The only explanation would be that those classes are generated in different versions, but this has been in the code for quite a while and last week we re-generated the whole database from scratch. So this remains a mystery.

Changed 4 years ago by kees.vanbochove@…

Fixed on nbx14 by manually changing the database schema for study_template_text_fields (made the _string and _elt columns into text). This works, but it's not a good fix. If this behaviour surfaces again, we should look into it in more detail. It could be a Hibernate bug.

TODO: try to reproduce this behaviour.

Changed 4 years ago by j.a.m.wesbeek@…

I was tailing catalina.out while Leny was working on NBX14 and I noticed that some values she tried to save were longer than the allowed 255 characters:

She was setting this template field value:

.setting [class dbnp.studycapturing.Event] template field: [Description] ([Glucose tolerance tests were performed after a 5h-fasting period. Mice were injected intraperitoneally with glucose and blood glucose levels were monitored for 120 minutes using hand-held glucose analyzer (Free Style, Disectronics, Vianen, The Netherlands) at t = 0, 15 min, 30 min, 60 min and 120 min after glucose injection.] of type [class java.lang.String])

And when trying to save the exception was:

2010-06-29 15:10:59,401 [TP-Processor2] ERROR util.JDBCExceptionReporter - Batch entry 0 insert into event_template_string_fields (event_id, template_string_fields_idx, template_string_fields_elt) values (379, Description, Glucose tolerance tests were performed after a 5h-fasting period. Mice were injected intraperitoneally with glucose and blood glucose levels were monitored for 120 minutes using hand-held glucose analyzer (Free Style, Disectronics, Vianen, The Netherlands) at t = 0, 15 min, 30 min, 60 min and 120 min after glucose injection.) was aborted. Call getNextException to see the cause.
2010-06-29 15:10:59,401 [TP-Processor2] ERROR util.JDBCExceptionReporter - ERROR: value too long for type character varying(255)
2010-06-29 15:10:59,401 [TP-Processor2] ERROR events.PatchedDefaultFlushEventListener? - Could not synchronize database state with session

I noticed she kept retrying, but somehow that broke the database / application state:

2010-06-29 15:13:27,212 [TP-Processor3] ERROR events.PatchedDefaultFlushEventListener? - Could not synchronize database state with session
org.hibernate.StaleStateException?: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

I would have assumed the validation method would also have checked if the value was < 255 characters, but apparently it doesn't...

Changed 3 years ago by business@…

  • status changed from accepted to closed
  • component set to Export functionality

This depends on  http://jira.codehaus.org/browse/GRAILS-6754 being solved.
A workaround was committed in [900].

Note: See TracTickets for help on using tickets.