Database Too Large (Patient Data)

Support
#1

I need to add around 250 fields divided over 5 years (50 fields per year) to track objectives of various Domains (Cognitive, social/emotional, self help, etc).

image
image857×771 10.6 KB

It is breaking the patient data table because it is exceeding 8126 bytes per row when I try to add year 4. All new fields added are in text format.

Error Message :
ERROR: query failed: ALTER TABLE patient_data ADD com_Objective_2_4 TEXT

Error: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8126. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

#2

Hi Kyle Caminita,

You should use column datatype as  TEXT or LONGTEXT instead of varchar.it should work.

Thanks
Param
help@capminds.com

1 Like
#3

Not a good idea to store de-normalized data in a core table of a relational database.

#4

All of the fields are textbox.

image
image831×838 113 KB

#5

Do what? You realize this is a web GUI and that we have no control over where/how OpenEMR store things?

#6

Per the MariaDB documentation, max row size is approximately half of the innodb_page_size setting, which defaults to 16k.

There was a recent (September) pull request from @stephenwaite to fix this for docker images.

@Hubtech’s instance is a local install on Oracle Linux 8. I tested restoring his database to a new OEL8 server where the innodb settings were set prior to DB creation and it resolves this problem.
image

1 Like
#7

Explore encounter layout rather than extending patient-data. Layouts use different storage scheme - not ideal but it will work for your requirements.

#8

Encounter layout is actually using these fields. The fields in the Demographics are filled out once per year and when the encounter form loads, they are seeing this data on the form (read only) and then filling out responses/status based on that data.

#9

If this data is used only once per year, having that many columns on the patient_data record could really mess with the SQL Query engine in some of the OpenEMR queries where we do complex logic / joins.

If you enter the data in once a year, it seems like this would be encounter data and could be stored inside its own form_* table instead of saving it in the patient_demographics form. patient_data is joined in all kinds of ways all over the place and maxing that table record out could impact you in weird and hard to find ways elsewhere. There’s a tradeoff, just wanted to make you aware at the application level how that could impact you since you seem to know your way around on the db side of things.

#10

Then how does the application create data in some different table for this purpose?

It is used (read) all the time by the layout that is used for encounters. The encounter layout reads these fields as read only data for the therapist to reference when they enter the current status of the goal on the encounter form clinic note.

image
image1134×559 58.5 KB