Upgrading OpenEMR 5.0.2 Patch 3 to Patch 4 with sql_patch.php failed on a production server (since rolled back):
"ALTER TABLE
usersADDportal_userTINYINT(1) NOT NULL DEFAULT ‘0’Query Error
ERROR: query failed: ALTER TABLE
usersADDportal_userTINYINT(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?