yes, sql_upgrade.php
Under what circumstances should I know to run sql_upgrade.php? I just knew from your upgrade instructions to run sql_patch.php.
I’ve now run sql_upgrade.php and chosen the most recent option, 5.0.1. At the end of the log to the screen I get:
ALTER TABLE form_eye_neuro MODIFY ACT5CCDIST text
Query Error
ERROR: query failed: ALTER TABLE form_eye_neuro MODIFY ACT5CCDIST text
Error: Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.
…/library/sql_upgrade_fx.php at 933:sqlStatement
…/sql_upgrade.php at 98:upgradeFromSqlFile(patch.sql)
does this query work from the mariadb command line?
ALTER TABLE `form_eye_neuro` MODIFY `ACT5CCDIST` text;“ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.”
always good to go back and see what brady recommended:
it’s the collation of the openemr database 
1 Like
He’s saying to use utf8_general_ci and not utf8mb4_unicode_ci, but:
+--------------+--------------------+----------------------------+------------------------+----------+ | 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 | +--------------+--------------------+----------------------------+------------------------+----------+
hi @PeteBoyd, how about the output of
MariaDB [openemr]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8 | utf8_general_ci |
+--------------------------+----------------------+
1 row in set (0.000 sec)Same as yours:
> +--------------------------+----------------------+ > | @@character_set_database | @@collation_database | > +--------------------------+----------------------+ > | utf8 | utf8_general_ci | > +--------------------------+----------------------+
Check the collation for the table
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘form_eye_neuro’;
+--------------+----------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_COLLATION |
+--------------+----------------+-----------------+
| openemr | form_eye_neuro | utf8_general_ci |
+--------------+----------------+-----------------+
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ‘form_eye_neuro’;
+--------------+----------------+----------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | COLLATION_NAME |
+--------------+----------------+----------------+-----------------+
| openemr | form_eye_neuro | id | NULL |
| openemr | form_eye_neuro | pid | NULL |
| openemr | form_eye_neuro | ACT | utf8_general_ci |
| openemr | form_eye_neuro | ACT5CCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT1CCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT2CCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT3CCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT4CCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT6CCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT7CCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT8CCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT9CCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT10CCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT11CCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT1SCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT2SCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT3SCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT4SCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT5SCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT6SCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT7SCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT8SCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT9SCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT10SCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT11SCDIST | utf8_general_ci |
| openemr | form_eye_neuro | ACT1SCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT2SCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT3SCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT4SCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT5CCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT6CCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT7CCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT8CCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT9CCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT10CCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT11CCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT5SCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT6SCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT7SCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT8SCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT9SCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT10SCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT11SCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT1CCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT2CCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT3CCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ACT4CCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | MOTILITYNORMAL | utf8_general_ci |
| openemr | form_eye_neuro | MOTILITY_RS | utf8_general_ci |
| openemr | form_eye_neuro | MOTILITY_RI | utf8_general_ci |
| openemr | form_eye_neuro | MOTILITY_RR | utf8_general_ci |
| openemr | form_eye_neuro | MOTILITY_RL | utf8_general_ci |
| openemr | form_eye_neuro | MOTILITY_LS | utf8_general_ci |
| openemr | form_eye_neuro | MOTILITY_LI | utf8_general_ci |
| openemr | form_eye_neuro | MOTILITY_LR | utf8_general_ci |
| openemr | form_eye_neuro | MOTILITY_LL | utf8_general_ci |
| openemr | form_eye_neuro | MOTILITY_RRSO | NULL |
| openemr | form_eye_neuro | MOTILITY_RLSO | NULL |
| openemr | form_eye_neuro | MOTILITY_RRIO | NULL |
| openemr | form_eye_neuro | MOTILITY_RLIO | NULL |
| openemr | form_eye_neuro | MOTILITY_LRSO | NULL |
| openemr | form_eye_neuro | MOTILITY_LLSO | NULL |
| openemr | form_eye_neuro | MOTILITY_LRIO | NULL |
| openemr | form_eye_neuro | MOTILITY_LLIO | NULL |
| openemr | form_eye_neuro | NEURO_COMMENTS | utf8_general_ci |
| openemr | form_eye_neuro | STEREOPSIS | utf8_general_ci |
| openemr | form_eye_neuro | ODNPA | utf8_general_ci |
| openemr | form_eye_neuro | OSNPA | utf8_general_ci |
| openemr | form_eye_neuro | VERTFUSAMPS | utf8_general_ci |
| openemr | form_eye_neuro | DIVERGENCEAMPS | utf8_general_ci |
| openemr | form_eye_neuro | NPC | utf8_general_ci |
| openemr | form_eye_neuro | DACCDIST | utf8_general_ci |
| openemr | form_eye_neuro | DACCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | CACCDIST | utf8_general_ci |
| openemr | form_eye_neuro | CACCNEAR | utf8_general_ci |
| openemr | form_eye_neuro | ODCOLOR | utf8_general_ci |
| openemr | form_eye_neuro | OSCOLOR | utf8_general_ci |
| openemr | form_eye_neuro | ODCOINS | utf8_general_ci |
| openemr | form_eye_neuro | OSCOINS | utf8_general_ci |
| openemr | form_eye_neuro | ODREDDESAT | utf8_general_ci |
| openemr | form_eye_neuro | OSREDDESAT | utf8_general_ci |
+--------------+----------------+----------------+-----------------+@stephenwaite In case it’s of use, this is taken from the log of upgrading a different server, from OpenEMR 5.0.1 to 5.0.2.1 in November 2019:
Skipping section #IfNotColumnType form_eye_neuro ACT5CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT1CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT2CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT3CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT4CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT6CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT7CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT8CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT9CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT10CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT11CCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT1SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT2SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT3SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT4SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT5SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT6SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT7SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT8SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT9SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT10SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT11SCDIST text
Skipping section #IfNotColumnType form_eye_neuro ACT1SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT2SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT3SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT4SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT5CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT6CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT7CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT8CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT9CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT10CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT11CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT5SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT6SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT7SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT8SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT9SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT10SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT11SCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT1CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT2CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT3CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ACT4CCNEAR text
Skipping section #IfNotColumnType form_eye_neuro ODNPA text
Skipping section #IfNotColumnType form_eye_neuro OSNPA text
Skipping section #IfNotColumnType form_eye_neuro VERTFUSAMPS text
Skipping section #IfNotColumnType form_eye_neuro DIVERGENCEAMPS text
Skipping section #IfNotColumnType form_eye_neuro ODCOLOR text
Skipping section #IfNotColumnType form_eye_neuro OSCOLOR text
Skipping section #IfNotColumnType form_eye_neuro ODCOINS text
Skipping section #IfNotColumnType form_eye_neuro OSCOINS textAre you currently using the eye module? ie are the tables empty?
Yes it’s empty. I have a test server on which there hasn’t been much configuration or data entered. This server we’re dealing with here is a duplicate of that test server, spun up in order to test the upgrade from 5.0.2.1, before running the upgrade on the test server, and before running the upgrade on production servers.
I was going to rec dropping the eye tables and then adding from newer database.sql but unsure this is a solution for your eventual process.
Did you start at 5.0.2.0 then patch up to patch 2?
You could run some queries to determine current row size or better yet, change the update table order in patch.sql to a smaller table first.
nevermine looks like form_eye_neuro is only one in patch for some reason!
I believe the test server was a fresh install of 5.0.2.0 that was upgraded to 5.0.2.1; (and I have one production server that was a fresh install of 5.0.1.6 that was upgraded to 5.0.2.1, and another production server that was a fresh install of 5.0.2.1).
I believe my setup to be pretty vanilla / by-the-book Debian and OpenEMR.
Thinking about your suggestion to change the update table order made me think, this server only has 1 GB RAM (others have 2 GB), could that be a factor?
Maybe for performance but row size is calculated. So it is just calculated a bit slower imo.
I’m not sure what the issue is here. Not sure if there is a difference in default page size between servers but, text only uses about 10 bytes max. Were these databases created by the same maria as they are running on now?
In this server’s life, all of which has been on Debian 10, it’s gone through mariabdb-server 10.3.15 > 10.3.17 > 10.3.22.
It’s funny(not funny ha ha) but my guess is the tables in question were created on a maria prior to 10.3.17 where row lengths were calculated wrong(maria bug). Meaning the existing table is already over the 8k/16k limit for row data and index. This is the only thing I can think of why maria won’t allow you to decrease row size because existing row sizes already exceeds max by at least 9-12 bytes…
This could be troublesome if proves to be the case.
Thoughts?
Gulp.
MariaDB replaced MySQL in Debian 9. It released with version 10.1.23 and is now at 10.1.44. Debian 10 released with MariaDB 10.3.15 and is now at 10.3.22.