MySQL / MariaDB error on recent versions of Ubuntu: "Index column size too large"

jeromecc commented 7 years ago

"SQL Error: 1709 QMYSQL: Unable to execute query Index column size too large. The maximum column size is 767 bytes."

The new default trendy character set for My/Maria is utf8mb4. It allows emojis and a few mathematicals symbols that are not present in the 3 bytes MySQL style "utf8".

It requires 4 bytes instead of 3 bytes for the old MySQL "utf8" character set. 767/4 is slightl over 190. fhio code defined FieldIsShortText as VARCHAR(200). Trying to create an index on those columns will fail and the tables will not be created. These index columns should be resized to 190. Here are the 6 VARCHAR(200) columns of interest:

Db fmf_agenda Clear

Db: fmf_alerts

ALR.A_PUID 190 (CREATE INDEX ALR__A_PUID ON ALR (A_PUID))
PACKS.UID 190

Db: fmf_categories

CATEGORIES.UUID 190 (CREATE INDEX CATEGORIES__UUID ON CATEGORIES (UUID))
CATEGORIES.MIME 190

Db: fmf_dosages: Clear

Db: fmf_episodes

EPISODES.FORM_PAGE_UID 190 (CREATE INDEX EPISODES__FORM_PAGE_UID ON EPISODES (FORM_PAGE_UID)
FORM_FILES.SUBUID 190
FORM_FILES.IP 190

Db: fmf_patients

PATIENTS_IDENTITY.NAME 10
PATIENTS_IDENTITY.SURNAME 10
PATIENTS_IDENTITY.SECONDNAME 10

Db: fmf_pmh Clear

Db: fmf_templates Clear

Db: fmf_users

USERS.LOGIN (CREATE INDEX USERS__LOGIN ON USERS (LOGIN) 32
USERS.NAME 10
USERS.SECONDNAME 10
USERS.SURNAME 10

Db: fmf_xmlforms

FORMS.FORM_UUID 190 (CREATE INDEX FORMS__FORM_UUID ON FORMS (FORM_UUID)
FORMS.FORM_ORG_UUID 190

Solution: user only the first n letters: CREATE INDEX PATIENT_IDENTITY__NAME ON PATIENT_IDENTITY (NAME(10));

jeromecc commented 7 years ago

CREATE INDEX has a FULLTEXT option but it is available only for MyISAM in MySQL 5.5. Lots of distros still use MySQL 5.5 (eg Ubuntu 14.04, Debian Jessie).

I don't think we should break things for those widely used Linux distributions versions.

jeromecc commented 7 years ago

Name of indexes to drop: index.field.tableName + "__" + index.field.fieldName

jeromecc commented 7 years ago

Done for alerts database commit f1391775d8137f24a772cec6c60d5efc46152a9c

jeromecc commented 7 years ago

Done for category database with 479db0820b216060aece50d2ed307a9cf5341592