Error when adding field to Demographics

Hubtech · December 22, 2022, 9:10pm

Trying to add a field to demographics as seen below:

This is the error I’m getting when I click Save New Field

**Query Error**

ERROR: query failed: INSERT INTO layout_options ( form_id, source, field_id, title, group_id, seq, uor, fld_length, fld_rows, titlecols, datacols, data_type, edit_options, default_value, codes, description, max_length, list_id, list_backup_id ) VALUES ( 'DEM','','','','','','','','','','','','','','','','','','' )

Error: Duplicate entry 'DEM--0' for key 'PRIMARY'

/software/openemr/interface/super/edit_layout.php at 505:sqlStatement

Help

JaredBusch · December 23, 2022, 7:54pm

Here is the code in question:

    // Add a new field to a specific group
    $data_type = trim($_POST['newdatatype']);
    $max_length = $data_type == 3 ? 3 : 255;
    $listval = $data_type == 34 ? trim($_POST['contextName']) : trim($_POST['newlistid']);
    sqlStatement("INSERT INTO layout_options (" .
      " form_id, source, field_id, title, group_id, seq, uor, fld_length, fld_rows" .
      ", titlecols, datacols, data_type, edit_options, default_value, codes, description" .
      ", max_length, list_id, list_backup_id " .
      ") VALUES ( " .
      "'"  . add_escape_custom(trim($_POST['layout_id'])) . "'" .
      ",'" . add_escape_custom(trim($_POST['newsource'])) . "'" .
      ",'" . add_escape_custom(trim($_POST['newid'])) . "'" .
      ",'" . add_escape_custom($_POST['newtitle']) . "'" .
      ",'" . add_escape_custom(trim($_POST['newfieldgroupid'])) . "'" .
      ",'" . add_escape_custom(trim($_POST['newseq'])) . "'" .
      ",'" . add_escape_custom(trim($_POST['newuor'])) . "'" .
      ",'" . add_escape_custom(trim($_POST['newlengthWidth'])) . "'" .
      ",'" . add_escape_custom(trim($_POST['newlengthHeight'])) . "'" .
      ",'" . add_escape_custom(trim($_POST['newtitlecols'])) . "'" .
      ",'" . add_escape_custom(trim($_POST['newdatacols'])) . "'" .
      ",'" . add_escape_custom($data_type) . "'"                                  .
        ",'" . add_escape_custom(encodeModifier($_POST['newedit_options'] ?? null)) . "'" .
      ",'" . add_escape_custom(trim($_POST['newdefault'])) . "'" .
      ",'" . add_escape_custom(trim($_POST['newcodes'])) . "'" .
      ",'" . add_escape_custom(trim($_POST['newdesc'])) . "'" .
      ",'"    . add_escape_custom(trim($_POST['newmaxSize']))    . "'"  .
      ",'" . add_escape_custom($listval) . "'" .
      ",'" . add_escape_custom(trim($_POST['newbackuplistid'])) . "'" .
      " )");

Obviously, the form fields are filled out. But after clicking Save New Field, something is getting lost to cause the value that was attempted to be inserted to be an empty string ''.

 VALUES ( 'DEM','','','','','','','','','','','','','','','','','','' )

The system is running 6.1.0(1), but looking at the github, I do not see any difference in the current code.

Infeg_Services · December 25, 2022, 6:40pm

Hello Team

When we try to add a new field under WHO section of demographics in LBF - it gets added. This works in latest version of OpenEMR in demo server.

More details of the issue scenario or steps are needed to reproduce the issue and assist.

Thank You
Infeg Team (services@infeg.com)

htuck · December 26, 2022, 5:37pm

Hi @Hubtech

Your error messsage says it’s an ID value conflict.
LBF code isn’t my strong suit but it appears that you’re trying to insert an ID of ‘DEM’ for some object, when the default ID of the demographics LBF is ‘DEM’.

IDs do need to be unique within the LBF, so maybe that’s what’s going on?
Best- Harley

Nilesh_Hake · December 27, 2022, 3:34am

Please check your structure for layout_options table that might be have the problem

Param_CapMinds · December 27, 2022, 12:57pm

Hi Htuck,

Make sure your layout_options table structure be like this below.

 CREATE TABLE IF NOT EXISTS `layout_options` (

form_id varchar(31) NOT NULL DEFAULT ‘’,
field_id varchar(31) NOT NULL DEFAULT ‘’,
group_id varchar(31) NOT NULL DEFAULT ‘’,
title text,
seq int(11) NOT NULL DEFAULT ‘0’,
data_type tinyint(3) NOT NULL DEFAULT ‘0’,
uor tinyint(1) NOT NULL DEFAULT ‘1’,
fld_length int(11) NOT NULL DEFAULT ‘15’,
max_length int(11) NOT NULL DEFAULT ‘0’,
list_id varchar(100) NOT NULL DEFAULT ‘’,
titlecols tinyint(3) NOT NULL DEFAULT ‘1’,
datacols tinyint(3) NOT NULL DEFAULT ‘1’,
default_value varchar(255) NOT NULL DEFAULT ‘’,
edit_options varchar(36) NOT NULL DEFAULT ‘’,
description text,
fld_rows int(11) NOT NULL DEFAULT ‘0’,
list_backup_id varchar(100) NOT NULL DEFAULT ‘’,
source char(1) NOT NULL DEFAULT ‘F’ COMMENT ‘F=Form, D=Demographics, H=History, E=Encounter’,
conditions text COMMENT ‘serialized array of skip conditions’,
validation varchar(100) DEFAULT NULL,
codes varchar(255) NOT NULL DEFAULT ‘’,
PRIMARY KEY (form_id,field_id,seq)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Thanks
Param
help@capminds.com

JaredBusch · December 31, 2022, 7:41pm

Restore backup to new instance, and upgraded instance to 7.0.0(2). Same issues.

The duplicate key is coming because it is not returning the field data from the form.

It created this field once when we did not notice.

I can remove it with the GUI but it throws an error.
And no, there is no “following string” shown.

Reloading the tab, the field is gone.

Adding a new field though recreates that bad field instead of actually creating the new field.
recorded a screen session of it. Posted it in Telegram chat because it was too large for the forum.

JaredBusch · December 31, 2022, 8:23pm

I jumped into the actual code and added a vardump. and there is no result for newid or any of the other newXXXX fields in said dump. Well, that would be a problem…

As you can see the form action is certainly “addfield” and the layout_id is DEM

stephenwaite · January 5, 2023, 3:00pm

hi @JaredBusch , mind exporting the LBF and posting here?

enable System->Config->Configuration Export/Import
then go to Admin->System->Backup->Export Configuration

JaredBusch · January 5, 2023, 3:14pm

It is not an LBF we made, it is the core Demographics, but yeah, we can do that.

JaredBusch · January 5, 2023, 3:23pm

These options:

Resulted in this file:
openemr_config.zip (30.7 KB)

Hubtech · January 5, 2023, 7:39pm

Hey stephen, did that config.zip file show you anything?

stephenwaite · January 5, 2023, 7:50pm

did you try deleting

INSERT INTO `layout_options` (`form_id`, `field_id`, `group_id`, `title`, `seq`, `data_type`, `uor`, `fld_length`, `max_length`, `list_id`, `titlecols`, `datacols`, `default_value`, `edit_options`, `description`, `fld_rows`, `list_backup_id`, `source`, `conditions`, `validation`, `codes`) VALUES ('DEM','','','',0,0,0,0,0,'',0,0,'','','',0,'','',NULL,NULL,'');

this record from the layout_options table?

Hubtech · January 5, 2023, 9:25pm

I guess where the confusion is coming from, for me, is why using the LBF creator is creating the problem. I scroll down to the group, click add field, click save, and then boom this happens.

sjpadgett · January 5, 2023, 10:00pm

Because on save all items in the form are regenerated and saved not just the newly changed items.
So on a previous save there was an issues in a modified or newly create item. Looks like now the table may be corrupted.

JaredBusch · January 5, 2023, 11:12pm

Yes. It can be deleted. See my post above. I can delete it in the editor with the noted error, but it is deleted. Or direct via SQL. Watch the whole screen video I posted in Telegram and linked above.

Either way, the next field add will simply recreate it. Then if you add another field again because you didn’t notice it add the DEM 0 field, you will get a duplicate key.

Look at the vardump screenshot. I you can see where in the code I inserted it. At that point the $_POST array should contain all of the newXXX fields, but not a single one is in there. So the newly entered data is getting lost

sjpadgett · January 6, 2023, 12:22am

This is not a bug in openemr as far as I can tell. Somehow your instance DEM LBF is corrupted and looks like a errant item in the form. You need to share more info on how this issue came about such as has any users attempted to make changes. Also did this appear the first time you went to edit/create new item.

stephenwaite · January 6, 2023, 2:29pm

hi @JaredBusch, what are your php settings for max_input_vars and memory_limit?

I made a few tweaks like 10000 for max_input vars and 1G for memory limit in /etc/php8.1/php.ini in the easy dev docker.

Also had to change this line to
$tmp = sqlQuery("SHOW COLUMNS FROM $tablename LIKE '$field_id'");

now can add and delete fields, also found a few date fields in the patient_data table that needed to have NULL allowed

and here’s the sql with that bad field removed
hubtech_demo_layout_ok_to_import.sql (537.2 KB)

JaredBusch · January 6, 2023, 10:43pm

I increased them the other day as recommended, but not to those values.

I’ll test these changes. Thanks for the feedback.

JaredBusch · January 9, 2023, 3:13am

We had already set the memory_limit to 1G and the max_input_vars to 4000 (from the default of 3000 on our OS).

I changed max_input_vars to 10000 and everything worked again.

As I stated previously, the field was easily removed in the GUI or via SQL.

Yeah, did not change the code and it still works. Not sure what you want here, I do not see a commit with this change either, so leaving it alone.

I never submitted anything from patient_data, is there going to be a patch?