# OpenEMR 5.0.2 Patch 3 to Patch 4 db update failed

**URL:** <https://community.open-emr.org/t/openemr-5-0-2-patch-3-to-patch-4-db-update-failed/15092>\
**Category:** Management and Administration\
**Created:** [August 28, 2020, 8:00am UTC](https://community.open-emr.org/t/openemr-5-0-2-patch-3-to-patch-4-db-update-failed/15092 "2020-08-28T08:00:57Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![PeteBoyd](https://community.open-emr.org/user_avatar/community.open-emr.org/peteboyd/32/14391_2.png) [@PeteBoyd](https://community.open-emr.org/u/PeteBoyd)\
**Post date:** [August 28, 2020, 8:00am UTC](https://community.open-emr.org/t/openemr-5-0-2-patch-3-to-patch-4-db-update-failed/15092/1 "2020-08-28T08:00:57Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![madmax](https://community.open-emr.org/letter_avatar_proxy/v4/letter/m/8baadc/32.png) [@madmax](https://community.open-emr.org/u/madmax)\
**Post date:** [September 3, 2020, 12:11am UTC](https://community.open-emr.org/t/openemr-5-0-2-patch-3-to-patch-4-db-update-failed/15092/2 "2020-09-03T00:11:54Z")

</div>

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