Dental (database) problem

roland · May 3, 2018, 9:39pm

Hi,
I made a dental chart/exam form but I am running into problems when trying to create the sql table, which has of course many fields for all possible aspects of every dental element. The form registers OK but when i click “install DB” I get this error

ERROR: query failed: CREATE TABLE IF NOT EXISTS form_vet_dentalsto_exam ( id bigint(20) NOT NULL auto_increment, date…(lots of fields)…TEXT, PRIMARY KEY (id) ) ENGINE=InnoDB

Error: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

I searched the net, but only found something about changing the database engine as a (temp) solution but I don’t know what that will do to my OpenEMR installation.

thanks,
Roland

jesdynf · May 3, 2018, 10:48pm

Can you go ahead and show me what the full query looks like, please? I don’t mind if it’s long, or you can link to Pastebin if you prefer.

roland · May 3, 2018, 11:05pm

OK, it is long…

ERROR: query failed: CREATE TABLE IF NOT EXISTS form_vet_dentalsto_exam ( id bigint(20) NOT NULL auto_increment, date datetime DEFAULT NULL, pid bigint(20) NOT NULL DEFAULT 0, user TEXT, groupname TEXT, authorized tinyint(4) NOT NULL DEFAULT 0, activity tinyint(4) NOT NULL DEFAULT 0, presenting_complaint TEXT, dental_status varchar(5), salivation varchar(5), tempmandib_joint varchar(5), arttm_rom TEXT, general_notes TEXT, bws_bodyscore varchar(5), vitals_weight float(5,2) DEFAULT 0, vitals_temperature float(5,2) DEFAULT 0, dem101c tinyint(4), dem101g tinyint(4), dem101r tinyint(4), dem101d tinyint(4), dem101f tinyint(4), dem101m tinyint(4), dem101a varchar(10), dem101q varchar(10), dem101p varchar(10), dem101w varchar(10), dem101u varchar(15), dem102c tinyint(4), dem102g tinyint(4), dem102r tinyint(4), dem102d tinyint(4), dem102f tinyint(4), dem102m tinyint(4), dem102a varchar(10), dem102q varchar(10), dem102p varchar(10), dem102w varchar(10), dem102u varchar(15), dem103c tinyint(4), dem103g tinyint(4), dem103r tinyint(4), dem103d tinyint(4), dem103f tinyint(4), dem103m tinyint(4), dem103a varchar(10), dem103q varchar(10), dem103p varchar(10), dem103w varchar(10), dem103u varchar(15), dem104c tinyint(4), dem104g tinyint(4), dem104r tinyint(4), dem104d tinyint(4), dem104f tinyint(4), dem104m tinyint(4), dem104a varchar(10), dem104q varchar(10), dem104p varchar(10), dem104w varchar(10), dem104u varchar(15), dem105c tinyint(4), dem105g tinyint(4), dem105r tinyint(4), dem105d tinyint(4), dem105f tinyint(4), dem105m tinyint(4), dem105a varchar(10), dem105q varchar(10), dem105p varchar(10), dem105w varchar(10), dem105u varchar(15), dem106c tinyint(4), dem106g tinyint(4), dem106r tinyint(4), dem106d tinyint(4), dem106f tinyint(4), dem106m tinyint(4), dem106a varchar(10), dem106q varchar(10), dem106p varchar(10), dem106w varchar(10), dem106u varchar(15), dem107c tinyint(4), dem107g tinyint(4), dem107r tinyint(4), dem107d tinyint(4), dem107f tinyint(4), dem107m tinyint(4), dem107a varchar(10), dem107q varchar(10), dem107p varchar(10), dem107w varchar(10), dem107u varchar(15), dem108c tinyint(4), dem108g tinyint(4), dem108r tinyint(4), dem108d tinyint(4), dem108f tinyint(4), dem108m tinyint(4), dem108a varchar(10), dem108q varchar(10), dem108p varchar(10), dem108w varchar(10), dem108u varchar(15), dem109c tinyint(4), dem109g tinyint(4), dem109r tinyint(4), dem109d tinyint(4), dem109f tinyint(4), dem109m tinyint(4), dem109a varchar(10), dem109q varchar(10), dem109p varchar(10), dem109w varchar(10), dem109u varchar(15), dem110c tinyint(4), dem110g tinyint(4), dem110r tinyint(4), dem110d tinyint(4), dem110f tinyint(4), dem110m tinyint(4), dem110a varchar(10), dem110q varchar(10), dem110p varchar(10), dem110w varchar(10), dem110u varchar(15), dem111c tinyint(4), dem111g tinyint(4), dem111r tinyint(4), dem111d tinyint(4), dem111f tinyint(4), dem111m tinyint(4), dem111a varchar(10), dem111q varchar(10), dem111p varchar(10), dem111w varchar(10), dem111u varchar(15), dem201c tinyint(4), dem201g tinyint(4), dem201r tinyint(4), dem201d tinyint(4), dem201f tinyint(4), dem201m tinyint(4), dem201a varchar(10), dem201q varchar(10), dem201p varchar(10), dem201w varchar(10), dem201u varchar(15), dem202c tinyint(4), dem202g tinyint(4), dem202r tinyint(4), dem202d tinyint(4), dem202f tinyint(4), dem202m tinyint(4), dem202a varchar(10), dem202q varchar(10), dem202p varchar(10), dem202w varchar(10), dem202u varchar(15), dem203c tinyint(4), dem203g tinyint(4), dem203r tinyint(4), dem203d tinyint(4), dem203f tinyint(4), dem203m tinyint(4), dem203a varchar(10), dem203q varchar(10), dem203p varchar(10), dem203w varchar(10), dem203u varchar(15), dem204c tinyint(4), dem204g tinyint(4), dem204r tinyint(4), dem204d tinyint(4), dem204f tinyint(4), dem204m tinyint(4), dem204a varchar(10), dem204q varchar(10), dem204p varchar(10), dem204w varchar(10), dem204u varchar(15), dem205c tinyint(4), dem205g tinyint(4), dem205r tinyint(4), dem205d tinyint(4), dem205f tinyint(4), dem205m tinyint(4), dem205a varchar(10), dem205q varchar(10), dem205p varchar(10), dem205w varchar(10), dem205u varchar(15), dem206c tinyint(4), dem206g tinyint(4), dem206r tinyint(4), dem206d tinyint(4), dem206f tinyint(4), dem206m tinyint(4), dem206a varchar(10), dem206q varchar(10), dem206p varchar(10), dem206w varchar(10), dem206u varchar(15), dem207c tinyint(4), dem207g tinyint(4), dem207r tinyint(4), dem207d tinyint(4), dem207f tinyint(4), dem207m tinyint(4), dem207a varchar(10), dem207q varchar(10), dem207p varchar(10), dem207w varchar(10), dem207u varchar(15), dem208c tinyint(4), dem208g tinyint(4), dem208r tinyint(4), dem208d tinyint(4), dem208f tinyint(4), dem208m tinyint(4), dem208a varchar(10), dem208q varchar(10), dem208p varchar(10), dem208w varchar(10), dem208u varchar(15), dem209c tinyint(4), dem209g tinyint(4), dem209r tinyint(4), dem209d tinyint(4), dem209f tinyint(4), dem209m tinyint(4), dem209a varchar(10), dem209q varchar(10), dem209p varchar(10), dem209w varchar(10), dem209u varchar(15), dem210c tinyint(4), dem210g tinyint(4), dem210r tinyint(4), dem210d tinyint(4), dem210f tinyint(4), dem210m tinyint(4), dem210a varchar(10), dem210q varchar(10), dem210p varchar(10), dem210w varchar(10), dem210u varchar(15), dem211c tinyint(4), dem211g tinyint(4), dem211r tinyint(4), dem211d tinyint(4), dem211f tinyint(4), dem211m tinyint(4), dem211a varchar(10), dem211q varchar(10), dem211p varchar(10), dem211w varchar(10), dem211u varchar(15), dem301c tinyint(4), dem301g tinyint(4), dem301r tinyint(4), dem301d tinyint(4), dem301f tinyint(4), dem301m tinyint(4), dem301a varchar(10), dem301q varchar(10), dem301p varchar(10), dem301w varchar(10), dem301u varchar(15), dem302c tinyint(4), dem302g tinyint(4), dem302r tinyint(4), dem302d tinyint(4), dem302f tinyint(4), dem302m tinyint(4), dem302a varchar(10), dem302q varchar(10), dem302p varchar(10), dem302w varchar(10), dem302u varchar(15), dem303c tinyint(4), dem303g tinyint(4), dem303r tinyint(4), dem303d tinyint(4), dem303f tinyint(4), dem303m tinyint(4), dem303a varchar(10), dem303q varchar(10), dem303p varchar(10), dem303w varchar(10), dem303u varchar(15), dem304c tinyint(4), dem304g tinyint(4), dem304r tinyint(4), dem304d tinyint(4), dem304f tinyint(4), dem304m tinyint(4), dem304a varchar(10), dem304q varchar(10), dem304p varchar(10), dem304w varchar(10), dem304u varchar(15), dem305c tinyint(4), dem305g tinyint(4), dem305r tinyint(4), dem305d tinyint(4), dem305f tinyint(4), dem305m tinyint(4), dem305a varchar(10), dem305q varchar(10), dem305p varchar(10), dem305w varchar(10), dem305u varchar(15), dem306c tinyint(4), dem306g tinyint(4), dem306r tinyint(4), dem306d tinyint(4), dem306f tinyint(4), dem306m tinyint(4), dem306a varchar(10), dem306q varchar(10), dem306p varchar(10), dem306w varchar(10), dem306u varchar(15), dem307c tinyint(4), dem307g tinyint(4), dem307r tinyint(4), dem307d tinyint(4), dem307f tinyint(4), dem307m tinyint(4), dem307a varchar(10), dem307q varchar(10), dem307p varchar(10), dem307w varchar(10), dem307u varchar(15), dem308c tinyint(4), dem308g tinyint(4), dem308r tinyint(4), dem308d tinyint(4), dem308f tinyint(4), dem308m tinyint(4), dem308a varchar(10), dem308q varchar(10), dem308p varchar(10), dem308w varchar(10), dem308u varchar(15), dem309c tinyint(4), dem309g tinyint(4), dem309r tinyint(4), dem309d tinyint(4), dem309f tinyint(4), dem309m tinyint(4), dem309a varchar(10), dem309q varchar(10), dem309p varchar(10), dem309w varchar(10), dem309u varchar(15), dem310c tinyint(4), dem310g tinyint(4), dem310r tinyint(4), dem310d tinyint(4), dem310f tinyint(4), dem310m tinyint(4), dem310a varchar(10), dem310q varchar(10), dem310p varchar(10), dem310w varchar(10), dem310u varchar(15), dem311c tinyint(4), dem311g tinyint(4), dem311r tinyint(4), dem311d tinyint(4), dem311f tinyint(4), dem311m tinyint(4), dem311a varchar(10), dem311q varchar(10), dem311p varchar(10), dem311w varchar(10), dem311u varchar(15), dem401c tinyint(4), dem401g tinyint(4), dem401r tinyint(4), dem401d tinyint(4), dem401f tinyint(4), dem401m tinyint(4), dem401a varchar(10), dem401q varchar(10), dem401p varchar(10), dem401w varchar(10), dem401u varchar(15), dem402c tinyint(4), dem402g tinyint(4), dem402r tinyint(4), dem402d tinyint(4), dem402f tinyint(4), dem402m tinyint(4), dem402a varchar(10), dem402q varchar(10), dem402p varchar(10), dem402w varchar(10), dem402u varchar(15), dem403c tinyint(4), dem403g tinyint(4), dem403r tinyint(4), dem403d tinyint(4), dem403f tinyint(4), dem403m tinyint(4), dem403a varchar(10), dem403q varchar(10), dem403p varchar(10), dem403w varchar(10), dem403u varchar(15), dem404c tinyint(4), dem404g tinyint(4), dem404r tinyint(4), dem404d tinyint(4), dem404f tinyint(4), dem404m tinyint(4), dem404a varchar(10), dem404q varchar(10), dem404p varchar(10), dem404w varchar(10), dem404u varchar(15), dem405c tinyint(4), dem405g tinyint(4), dem405r tinyint(4), dem405d tinyint(4), dem405f tinyint(4), dem405m tinyint(4), dem405a varchar(10), dem405q varchar(10), dem405p varchar(10), dem405w varchar(10), dem405u varchar(15), dem406c tinyint(4), dem406g tinyint(4), dem406r tinyint(4), dem406d tinyint(4), dem406f tinyint(4), dem406m tinyint(4), dem406a varchar(10), dem406q varchar(10), dem406p varchar(10), dem406w varchar(10), dem406u varchar(15), dem407c tinyint(4), dem407g tinyint(4), dem407r tinyint(4), dem407d tinyint(4), dem407f tinyint(4), dem407m tinyint(4), dem407a varchar(10), dem407q varchar(10), dem407p varchar(10), dem407w varchar(10), dem407u varchar(15), dem408c tinyint(4), dem408g tinyint(4), dem408r tinyint(4), dem408d tinyint(4), dem408f tinyint(4), dem408m tinyint(4), dem408a varchar(10), dem408q varchar(10), dem408p varchar(10), dem408w varchar(10), dem408u varchar(15), dem409c tinyint(4), dem409g tinyint(4), dem409r tinyint(4), dem409d tinyint(4), dem409f tinyint(4), dem409m tinyint(4), dem409a varchar(10), dem409q varchar(10), dem409p varchar(10), dem409w varchar(10), dem409u varchar(15), dem410c tinyint(4), dem410g tinyint(4), dem410r tinyint(4), dem410d tinyint(4), dem410f tinyint(4), dem410m tinyint(4), dem410a varchar(10), dem410q varchar(10), dem410p varchar(10), dem410w varchar(10), dem410u varchar(15), dem411c tinyint(4), dem411g tinyint(4), dem411r tinyint(4), dem411d tinyint(4), dem411f tinyint(4), dem411m tinyint(4), dem411a varchar(10), dem411q varchar(10), dem411p varchar(10), dem411w varchar(10), dem411u varchar(15), canine_angulation varchar(100), comments TEXT, therapy_notes TEXT, skull_type varchar(15), jaw_relationship varchar(15), presumptive_diagnosis TEXT, differential_diagnosis TEXT, PRIMARY KEY (id) ) ENGINE=InnoDB

Error: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

/var/www/git-openemr/library/registry.inc at 80:sqlStatement
/var/www/git-openemr/interface/forms_admin/forms_admin.php at 22:installSQL(/var/www/git-openemr/library/…/interface/forms/vet_dentalsto_exam)

sjpadgett · May 3, 2018, 11:33pm

You may try changing the innodb_log_buffer_size in my.ini.

roland · May 4, 2018, 12:31am

tried searching for that, but don’t see it anywhere. I use linux mint, can only find mysqld.cnf but there is no such variable in there… do you know where that is?

jesdynf · May 4, 2018, 12:40am

The SQL server is pretty much correct to reject this outright – this schema is not one you should proceed with using. If this represents the left and the right sides of the upper and lower jaws, like I think it does, please instead consider a table form_vet_dentalsto_exam_tooth with a PK of fvde.id and ‘toothnum’, and then the expanded names for fields c, g, r, d, f, m, a, q, p, and w.

Even if you manage to find some way to jam this into MySQL, it will severly affect the performance of critical internal engine caches and pools, and you may well have to exacerbate the problem if you come back wanting to add some new field to all 44 entries.

roland · May 4, 2018, 1:28am

Since one of the OpenEMR ophthalmological exam tables has 368 columns (and the InnoDB maximum is 1000??? don’t see how that could work…), I thought my 508 columns would fit, but you’re right, it was rejected. I wanted to be able to (later) add any species to the table, those 44 cover almost all usual and unusual land mammals (sorry American opossums and armadillos , thou hast too many teeth…)
Better redesign it. I could split the table into the four jaw quadrants, brings the column # down to about 125, but complicates dental chart form.
Had to think about your suggested design a while, not sure I understand it now (it’s 3 am here in Germany…should get some sleep). will rethink the matter tomorrow morning…
thanks
Roland

luisuriarte · May 4, 2018, 2:30am

Hi. It is not the number of columns, but the width. Can you convert varchar to text? … it’s better.
https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.6/en/column-count-limit.html

jesdynf · May 4, 2018, 2:48am

A database table about a visit shouldn’t have ten fields for each of 44 teeth – anytime you have to hardcode a number like that, it’s an indication that things have gone wrong for you.

There should be a table for the visit, like you have now, and then a table full of teeth, and 44 of those teeth-rows correspond to that visit and that patient.

brady.miller · May 4, 2018, 5:04am

As @luisuriarte states, changing all varchar fields to text should get you out of the woods. As @jesdynf points out, this is not pretty, though

sjpadgett · May 4, 2018, 11:56am

While I agree this design defeats the whole purpose of relational databases, I would like to point out this is not a limitation of Maria or Mysql.

roland · May 4, 2018, 10:04pm

well, it didn’t…, only when I split the table into the four dental quadrants it worked, but I agree the design had to be prettified. So back to the drawing board, rewrote the sql tables which should now accomodate just about any land species (also the American opossum…). got myself into a dental mess now I have to redesign the form also.
@jesdynf, thanks for your suggestion @sjpadgett @luisuriarte thanks, yesyes I’m just a country vet, no developer (yet), thank you
to be continued
R

roland · May 10, 2018, 2:18pm

@sjpadgett , @brady.miller etc,
I redesigned the database, so it now uses just 3 tables with about 20 columns each for the same job. In my enthusiasm to get it done, i somehow forgot that OpenEMR is a >>relational<< database.
Redoing the form was a bit more work though, 4 sleepless nights, and a ton of chocolate to keep me awake (now i need to go see the dentist myself)
thanks everyone for pointing that out
R:

luisuriarte · May 22, 2018, 9:50pm

@roland Have you been able to finish your form ?. Is it functional ?. Could you share it please? Thank you.

roland · May 24, 2018, 9:44pm

Hi Luis,
the form is functional, but it is still a long way from being finished (see attached screenshot), and I’m afraid it won’t be very useful for a human dentist since it is hardcoded to use the modified Triadan system and only for dogs, cats and camels…
I don’t know what you use, but when my dentist is torturing me in the chair he always calls out some alphanumeric system to his assistent where the cavity is… I guess it could be adapted but I have no idea what exact data humanoid dentists want to record…
At the moment it cannot be used for deciduous and mixed yet, only permanent teeth (101-411), I’m working on that (and on a way to draw on the picture like you would on a paper record, but that still a long way to go).

.
I will also have to have it revised by a real OpenEMR developer before releasing it, I surely made a mess of proper coding conventions, (but it works…)
thanks

luisuriarte · May 24, 2018, 11:21pm

Wow Awesome. What a good form. Is very complete. It’s a great job. I congratulate you a lot. Do not worry about me. But, I also think that OpenEMR developers should review them. regards

brady.miller · May 25, 2018, 2:06am

even though it’s not human, that form is just beautiful!

roland · May 26, 2018, 12:43am

@brady.miller, @luisuriarte,
thanks for the compliments. Today had to solve the persistent deciduous problem in a hurry, both on the brand new form and in the dromedary’s mouth. Noortje says hello, she’s also happy with the result… noortje_dromedary
she didn’t fit in the chair though,
R

Elijah_Wisdom · May 26, 2018, 6:31pm

If i have dentist contribute can we build for humans?

roland · May 27, 2018, 10:48pm

@Elijah_Wisdom
If you have a human dentist tell me what system is used internationally, what parameters and how they note it on the chart, i guess it could be done. It can do up to 44 elements, so humans would certainly fit in. also needed to remove the species-specific code lines.