Read about OpenEMR's Response to the COVID-19 Pandemic at https://www.open-emr.org/covid19/

OpenEMR 5.0.2 Patch 3 to Patch 4 db update failed

Upgrading OpenEMR 5.0.2 Patch 3 to Patch 4 with sql_patch.php failed on a production server (since rolled back):

"ALTER TABLE users ADD portal_user TINYINT(1) NOT NULL DEFAULT ‘0’

Query Error

ERROR: query failed: ALTER TABLE users ADD portal_user TINYINT(1) NOT NULL DEFAULT ‘0’

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

…/library/sql_upgrade_fx.php at 933:sqlStatement
…/sql_patch.php at 66:upgradeFromSqlFile(patch.sql)"

The upgrade worked OK on a test server with a copy of the production server’s OpenEMR db and files, but failed on the production server. Both servers have the same version of OpenEMR, Debian and Maria. The servers differ in that the production server began life with Debian 9 and OpenEMR 5.0.1 Patch 6; and the test server began life with Debian 10 and OpenEMR 5.0.2 (5.0.2.0).

MariaDB [openemr]> DESCRIBE users;

+----------------------+--------------+------+-----+------------+----------------+
| Field                | Type         | Null | Key | Default    | Extra          |
+----------------------+--------------+------+-----+------------+----------------+
| id                   | bigint(20)   | NO   | PRI | NULL       | auto_increment |
| username             | varchar(255) | YES  |     | NULL       |                |
| password             | longtext     | YES  |     | NULL       |                |
| authorized           | tinyint(4)   | YES  |     | NULL       |                |
| info                 | longtext     | YES  |     | NULL       |                |
| source               | tinyint(4)   | YES  |     | NULL       |                |
| fname                | varchar(255) | YES  |     | NULL       |                |
| mname                | varchar(255) | YES  |     | NULL       |                |
| lname                | varchar(255) | YES  |     | NULL       |                |
| suffix               | varchar(255) | YES  |     | NULL       |                |
| federaltaxid         | varchar(255) | YES  |     | NULL       |                |
| federaldrugid        | varchar(255) | YES  |     | NULL       |                |
| upin                 | varchar(255) | YES  |     | NULL       |                |
| facility             | varchar(255) | YES  |     | NULL       |                |
| facility_id          | int(11)      | NO   |     | 0          |                |
| see_auth             | int(11)      | NO   |     | 1          |                |
| active               | tinyint(1)   | NO   |     | 1          |                |
| npi                  | varchar(15)  | YES  |     | NULL       |                |
| title                | varchar(30)  | YES  |     | NULL       |                |
| specialty            | varchar(255) | YES  |     | NULL       |                |
| billname             | varchar(255) | YES  |     | NULL       |                |
| email                | varchar(255) | YES  |     | NULL       |                |
| email_direct         | varchar(255) | NO   |     |            |                |
| url                  | varchar(255) | YES  |     | NULL       |                |
| assistant            | varchar(255) | YES  |     | NULL       |                |
| organization         | varchar(255) | YES  |     | NULL       |                |
| valedictory          | varchar(255) | YES  |     | NULL       |                |
| street               | varchar(60)  | YES  |     | NULL       |                |
| streetb              | varchar(60)  | YES  |     | NULL       |                |
| city                 | varchar(30)  | YES  |     | NULL       |                |
| state                | varchar(30)  | YES  |     | NULL       |                |
| zip                  | varchar(20)  | YES  |     | NULL       |                |
| street2              | varchar(60)  | YES  |     | NULL       |                |
| streetb2             | varchar(60)  | YES  |     | NULL       |                |
| city2                | varchar(30)  | YES  |     | NULL       |                |
| state2               | varchar(30)  | YES  |     | NULL       |                |
| zip2                 | varchar(20)  | YES  |     | NULL       |                |
| phone                | varchar(30)  | YES  |     | NULL       |                |
| fax                  | varchar(30)  | YES  |     | NULL       |                |
| phonew1              | varchar(30)  | YES  |     | NULL       |                |
| phonew2              | varchar(30)  | YES  |     | NULL       |                |
| phonecell            | varchar(30)  | YES  |     | NULL       |                |
| notes                | text         | YES  |     | NULL       |                |
| cal_ui               | tinyint(4)   | NO   |     | 1          |                |
| taxonomy             | varchar(30)  | NO   |     | 207Q00000X |                |
| calendar             | tinyint(1)   | NO   |     | 0          |                |
| abook_type           | varchar(31)  | NO   |     |            |                |
| pwd_expiration_date  | date         | YES  |     | NULL       |                |
| pwd_history1         | longtext     | YES  |     | NULL       |                |
| pwd_history2         | longtext     | YES  |     | NULL       |                |
| default_warehouse    | varchar(31)  | NO   |     |            |                |
| irnpool              | varchar(31)  | NO   |     |            |                |
| state_license_number | varchar(25)  | YES  |     | NULL       |                |
| weno_prov_id         | varchar(15)  | YES  |     | NULL       |                |
| newcrop_user_role    | varchar(30)  | YES  |     | NULL       |                |
| cpoe                 | tinyint(1)   | YES  |     | NULL       |                |
| physician_type       | varchar(50)  | YES  |     | NULL       |                |
| main_menu_role       | varchar(50)  | NO   |     | standard   |                |
| patient_menu_role    | varchar(50)  | NO   |     | standard   |                |
+----------------------+--------------+------+-----+------------+----------------+

MariaDB [openemr]> SHOW CREATE DATABASE openemr;

+----------+------------------------------------------------------------------+
| Database | Create Database                                                  |
+----------+------------------------------------------------------------------+
| openemr  | CREATE DATABASE `openemr` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+------------------------------------------------------------------+

MariaDB [openemr]> SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | openemr            | utf8                       | utf8_general_ci        | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+

MariaDB [openemr]> SELECT @@character_set_database, @@collation_database;

+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8                     | utf8_general_ci      |
+--------------------------+----------------------+

SHOW TABLE STATUS where name like ‘users’;
show table status like ‘users’;

+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment | Max_index_length | Temporary |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| users | InnoDB |      10 | Dynamic    |   17 |            963 |       16384 |               0 |            0 |         0 |             18 | 2020-08-27 18:27:23 | 2020-08-27 18:27:23 | NULL       | utf8_general_ci |     NULL |                |         |                0 | N         |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+

MariaDB [openemr]> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘users’;

+--------------+------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION |
+--------------+------------+-----------------+
| openemr      | users      | utf8_general_ci |
+--------------+------------+-----------------+

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = “users”;

+--------------+------------+----------------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME          | COLLATION_NAME  |
+--------------+------------+----------------------+-----------------+
| openemr      | users      | id                   | NULL            |
| openemr      | users      | username             | utf8_general_ci |
| openemr      | users      | password             | utf8_general_ci |
| openemr      | users      | authorized           | NULL            |
| openemr      | users      | info                 | utf8_general_ci |
| openemr      | users      | source               | NULL            |
| openemr      | users      | fname                | utf8_general_ci |
| openemr      | users      | mname                | utf8_general_ci |
| openemr      | users      | lname                | utf8_general_ci |
| openemr      | users      | suffix               | utf8_general_ci |
| openemr      | users      | federaltaxid         | utf8_general_ci |
| openemr      | users      | federaldrugid        | utf8_general_ci |
| openemr      | users      | upin                 | utf8_general_ci |
| openemr      | users      | facility             | utf8_general_ci |
| openemr      | users      | facility_id          | NULL            |
| openemr      | users      | see_auth             | NULL            |
| openemr      | users      | active               | NULL            |
| openemr      | users      | npi                  | utf8_general_ci |
| openemr      | users      | title                | utf8_general_ci |
| openemr      | users      | specialty            | utf8_general_ci |
| openemr      | users      | billname             | utf8_general_ci |
| openemr      | users      | email                | utf8_general_ci |
| openemr      | users      | email_direct         | utf8_general_ci |
| openemr      | users      | url                  | utf8_general_ci |
| openemr      | users      | assistant            | utf8_general_ci |
| openemr      | users      | organization         | utf8_general_ci |
| openemr      | users      | valedictory          | utf8_general_ci |
| openemr      | users      | street               | utf8_general_ci |
| openemr      | users      | streetb              | utf8_general_ci |
| openemr      | users      | city                 | utf8_general_ci |
| openemr      | users      | state                | utf8_general_ci |
| openemr      | users      | zip                  | utf8_general_ci |
| openemr      | users      | street2              | utf8_general_ci |
| openemr      | users      | streetb2             | utf8_general_ci |
| openemr      | users      | city2                | utf8_general_ci |
| openemr      | users      | state2               | utf8_general_ci |
| openemr      | users      | zip2                 | utf8_general_ci |
| openemr      | users      | phone                | utf8_general_ci |
| openemr      | users      | fax                  | utf8_general_ci |
| openemr      | users      | phonew1              | utf8_general_ci |
| openemr      | users      | phonew2              | utf8_general_ci |
| openemr      | users      | phonecell            | utf8_general_ci |
| openemr      | users      | notes                | utf8_general_ci |
| openemr      | users      | cal_ui               | NULL            |
| openemr      | users      | taxonomy             | utf8_general_ci |
| openemr      | users      | calendar             | NULL            |
| openemr      | users      | abook_type           | utf8_general_ci |
| openemr      | users      | pwd_expiration_date  | NULL            |
| openemr      | users      | pwd_history1         | utf8_general_ci |
| openemr      | users      | pwd_history2         | utf8_general_ci |
| openemr      | users      | default_warehouse    | utf8_general_ci |
| openemr      | users      | irnpool              | utf8_general_ci |
| openemr      | users      | state_license_number | utf8_general_ci |
| openemr      | users      | weno_prov_id         | utf8_general_ci |
| openemr      | users      | newcrop_user_role    | utf8_general_ci |
| openemr      | users      | cpoe                 | NULL            |
| openemr      | users      | physician_type       | utf8_general_ci |
| openemr      | users      | main_menu_role       | utf8_general_ci |
| openemr      | users      | patient_menu_role    | utf8_general_ci |
+--------------+------------+----------------------+-----------------+

Have you any thoughts on how to proceed please?

Have you checked this https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/