6-0-0-4 Patch - Error: Invalid default value for 'modified_date'

Support
1

Situation
Applying the latest patch and getting the SQL error when running the sql_patch.php

OpenEMR Version
I’m using OpenEMR version v6.0.0 (3)

Browser:
I’m using: Chrome/latest. Same on firefox.

Operating System
I’m using: Server is CentOS7 with MariaDB server 5.5.68

Logs

Query Error

ERROR: query failed: CREATE TABLE document_templates ( id bigint(21) UNSIGNED NOT NULL, pid bigint(20) DEFAULT NULL, provider int(11) UNSIGNED DEFAULT NULL, encounter int(11) UNSIGNED DEFAULT NULL, modified_date datetime NOT NULL DEFAULT current_timestamp(), profile varchar(31) DEFAULT NULL, category varchar(63) DEFAULT NULL, location varchar(255) DEFAULT NULL, template_name varchar(255) DEFAULT NULL, status varchar(31) DEFAULT NULL, exclude_portal tinyint(1) NOT NULL DEFAULT 0, exclude_dashboard tinyint(1) UNSIGNED NOT NULL DEFAULT 0, size int(11) NOT NULL DEFAULT 0, template_content mediumblob DEFAULT NULL, mime varchar(31) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY location (pid,category,template_name,status)) ENGINE=InnoDB

Error: Invalid default value for ‘modified_date’

/var/www/library/sql_upgrade_fx.php at 1175:sqlStatement
/var/www/sql_patch.php at 53:upgradeFromSqlFile(patch.sql)

2

Updating to MariaDB 10.2 or higher I am getting a little further in the upgrade script but getting this error:

Query Error

ERROR: query failed: ALTER TABLE patient_data ADD patient_groups 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

/var/www/library/sql_upgrade_fx.php at 1175:sqlStatement
/var/www/sql_patch.php at 53:upgradeFromSqlFile(patch.sql)

3

hi @madmax ,
What specific mariadb version are you using and what does your patient_data table schema look like?

4

First error was 5.5.68 - second error was 10.2

Will have to get back to you on the other thing.

5

Also when you get the other thing will also be helpful to know the specific mariadb version (ie. 10.2.3 )

6

Schema is unchanged from the default from what I can tell.

MariaDB version is 5.5.68 and also tested 10.2.43 - 10.5.15 and 10.5.15

7

related to this:
Error Installing Version 5.0.2

wouldn’t expect the problem in mariadb 10.2.25 or lower
"
Prior to MariaDB 10.2.26, 10.3.17, and 10.4.7, MariaDB didn’t properly calculate the row sizes while executing DDL, so “unsafe” tables could be created, even with innodb_strict_mode=ON set. This was fixed by MDEV-19292. As a side effect, tables that could be created in previous versions may get rejected after the latest releases.
"

that being said better solution would be to convert some of the varchar(255) fields to TEXT fields. That why seeing your patient_data schema would be helpful to see how many more custom fields you have (legacy OpenEMR added varchar(255) fields there while modern OpenEMR adds text fields.

1 Like
8

I had to change a lot over to text, but its working fine now. Thanks for the support :slight_smile:

I don’t think we have any custom fields, i have looked and rechecked, unless userlists etc count as that?

1 Like
9

great to hear things are fixed up. userlists are not custom fields (these have been in openemr since the old days); custom fields are generally things that have been added by practice in the layout editor for the demographics layout.

10

Hi Brady, Was able to do some more testing with this today.

I am getting the following SQL error on the patient “My Documents” drop down menu:

ERROR: query failed: Select pd.pid, Concat_Ws(‘, ‘, lname, fname) as name, pd.patient_groups, tplId.profile, tplId.member_of, tplId.recurring, tplId.event_trigger, tplId.period, tplId.modified_date as profile_date, tpl.* From patient_data pd Join document_template_profiles as ptd On pd.patient_groups LIKE CONCAT(’%’,ptd.member_of, ‘%’) And pd.pid = ? Join (Select * From document_template_profiles) tplId On tplId.profile = ptd.profile And ptd.active = ‘1’ Join (Select id, category, template_name, location, template_content, mime, modified_date From document_templates ) as tpl On tpl.id = tplId.template_id Group By pid, category, template_name Order By lname

Error: Unknown column ‘pd.patient_groups’ in ‘field list’

/var/www/src/Services/DocumentTemplates/DocumentTemplateService.php at 80:sqlStatement
/var/www/src/Services/DocumentTemplates/DocumentTemplateService.php at 850:getPortalAssignedTemplates(3,1)
/var/www/portal/patient/templates/OnsiteDocumentListView.tpl.php at 307:renderPortalTemplateMenu(3,-patient-,1)
/var/www/portal/patient/fwk/libs/savant/Savant3.php at 1022:include(/var/www/portal/patient/templates/OnsiteDocumentListView.tpl.php)
/var/www/portal/patient/fwk/libs/savant/Savant3.php at 958:fetch()
/var/www/portal/patient/fwk/libs/savant/Savant3.php at 944:getOutput(OnsiteDocumentListView.tpl.php)
/var/www/portal/patient/fwk/libs/verysimple/Phreeze/SavantRenderEngine.php at 71:display(OnsiteDocumentListView.tpl.php)
/var/www/portal/patient/fwk/libs/verysimple/Phreeze/PortalController.php at 851:display(OnsiteDocumentListView)
/var/www/portal/patient/libs/Controller/OnsiteDocumentController.php at 95:Render()
/var/www/portal/patient/fwk/libs/verysimple/Phreeze/Dispatcher.php at 172:ListView()
/var/www/portal/patient/index.php at 59:Dispatch

If I go into the groupings menu inside a dummy patient I’m getting:

ERROR: query failed: SELECT pid, pubpid, fname, mname, lname, DOB, patient_groups FROM patient_data WHERE patient_groups <> “” ORDER BY lname

Error: Unknown column ‘patient_groups’ in ‘field list’

11

I fixed the above by adding the column patient_groups as a text column in patient_data - is that correct? (Its working now at least).

12

Hi @madmax ,
That is odd since the sql patch upgrade script should of done that for you:
https://github.com/openemr/openemr/blob/v6_0_0_4/sql/patch.sql#L218