After upgrading to 5 Error: Specified key was too long; max key length is 1000 bytes

Management and Administration
#1

ajperezcrespo wrote on Tuesday, March 07, 2017:

Just upgraded from 4.2.1 to 5.0 using the deb file. All seemed fine and dandy until I logged in.
First error was this.

ERROR: insert failed: insert into log ( date, event,category, user, groupname, success, comments, crt_user, patient_id) values ( NOW(), ‘login’,‘login’,‘admin’,‘Default’,‘1’,‘success: 192.168.1.100’,NULL,NULL)

Error: Unknown column ‘category’ in 'field list’
/var/www/openemr/library/log.inc at 45:sqlInsertClean_audit
/var/www/openemr/library/authentication/login_operations.php at 116:newEvent(login,admin,Default,1,success: 192.168.1.100)
/var/www/openemr/library/auth.inc at 50:validate_user_password(admin,Default)
/var/www/openemr/interface/globals.php at 449:include_once(/var/www/openemr/library/auth.inc)
/var/www/openemr/interface/main/main_screen.php at 25:require_once(/var/www/openemr/interface/globals.php)

I then ran the sql_upgrade.php

Table version migrated to InnoDB.
Table x12_partners migrated to InnoDB.
CREATE TABLE valueset ( nqf_code varchar(255) NOT NULL DEFAULT ‘’, code varchar(255) NOT NULL DEFAULT ‘’, code_system varchar(255) NOT NULL DEFAULT ‘’, code_type varchar(255) DEFAULT NULL, valueset varchar(255) NOT NULL DEFAULT ‘’, description varchar(255) DEFAULT NULL, valueset_name varchar(500) DEFAULT NULL, PRIMARY KEY (nqf_code,code,valueset)) ENGINE=InnoDB

ERROR: query failed: CREATE TABLE valueset ( nqf_code varchar(255) NOT NULL DEFAULT ‘’, code varchar(255) NOT NULL DEFAULT ‘’, code_system varchar(255) NOT NULL DEFAULT ‘’, code_type varchar(255) DEFAULT NULL, valueset varchar(255) NOT NULL DEFAULT ‘’, description varchar(255) DEFAULT NULL, valueset_name varchar(500) DEFAULT NULL, PRIMARY KEY (nqf_code,code,valueset)) ENGINE=InnoDB
Error: Specified key was too long; max key length is 1000 bytes
/var/www/openemr/library/sql_upgrade_fx.php at 675:sqlStatement
/var/www/openemr/sql_upgrade.php at 56:upgradeFromSqlFile(4_2_2-to-5_0_0_upgrade.sql)

Ubuntu 14.04 LTS
MySQL 5.5.54
Apache 2.4.7
PHP 5.5.9

Upgrading from 4.2.0 to OpenEMR 5.0.0
#2

aperezcrespo wrote on Wednesday, March 08, 2017:

Had to manually create the table valueset. Odd.

#3

bradymiller wrote on Wednesday, March 08, 2017:

Hi Alfonso,

Thanks for the report.

This is a concerning bug. I didn’t pick this up when I tested upgrading of the package (I tested it on Ubuntu 14.04). I’ll test this again, and if confirm this, then will push us to need to fix this issue (make the keys smaller), place in patch, and release new packages.

Either way, we need to make the keys smaller via the upgrade/patch mechanism (this mechanism already exists, since we actually had to do this many years ago when we migrated to utf8 since utf8 makes the keys larger than the older latin1).

I’ll keep you updated on the progress of this here.

-brady
OpenEMR

#4

bradymiller wrote on Wednesday, March 08, 2017:

Just posted this issue on github here(it’s easier for me to track the progress there):