Suitecrm: Index hits Key threshold in MySQL5.6/UTF-8

[samus-aran]

Issue

A forum users has discovered that installing SuiteCRM 7.11.6 (>7.11.5 are not affected) produces a failure due to the combination of the fields to create a DB index.
It appears that the issue is due to MySQL 5.6 hitting the threshold of 1000 characters (MyISAM) or 767 (InnoDB). MySQL 5.7 has a higher threshold which is 3072. However on top of that threshold character set also contribute to this.. in this case it's UTF8 which adds more characters rather than if it was e.g. latin1.

This issue was introduced by fix provided to resolve this issue #7351 and it wasn't picked up by tests - most tests run on MySQL 5.7 but our MySQL 5.6 environment also didn't fail.

Expected Behavior

1. Installation should not fail on MySQL 5.6+

Actual Behavior

1. Installation fails (appears) on MySQL 5.6 and using UTF-8

Possible Fix

Either:

1. Revert original fix by reducing the character limit from 255 to suggested 100
   or
2. Cap the character set within the index itself (needs to confirm this is doable).

Steps to Reproduce

Can't replicate this locally or on travis (tho suspect that on travis the install is different)
Please see error logs of user's post.
https://suitecrm.com/suitecrm/forum/announcements/26612-suitecrm-7-11-6-7-10-18-7-8-31-maintenance-patch-now-available#86505

Context

Your Environment

• SuiteCRM Version used: 7.11.6
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): See Post
• Environment name and version (e.g. MySQL, PHP 7): MySQL 5.6
• Operating System and version (e.g Ubuntu 16.04): WAMP

@Abuelodelanada Are you able to have a wee look at this?

[Abuelodelanada]

@samus-aran I'll take a look

[Abuelodelanada]

Executing the following SQL:

CREATE TABLE users (
    id char(36) NOT NULL ,
    user_name varchar(60) NULL ,
    user_hash varchar(255) NULL ,
    system_generated_password bool NULL ,
    pwd_last_changed datetime NULL ,
    authenticate_id varchar(100) NULL ,
    sugar_login bool DEFAULT '1' NULL ,
    first_name varchar(255) NULL ,
    last_name varchar(255) NULL ,
    is_admin bool DEFAULT '0' NULL ,
    external_auth_only bool DEFAULT '0' NULL ,
    receive_notifications bool DEFAULT '1' NULL ,
    description text NULL ,
    date_entered datetime NULL ,
    date_modified datetime NULL ,
    modified_user_id char(36) NULL ,
    created_by char(36) NULL ,
    title varchar(50) NULL ,
    photo varchar(255) NULL ,
    department varchar(50) NULL ,
    phone_home varchar(50) NULL ,
    phone_mobile varchar(50) NULL ,
    phone_work varchar(50) NULL ,
    phone_other varchar(50) NULL ,
    phone_fax varchar(50) NULL ,
    status varchar(100) NULL ,
    address_street varchar(150) NULL ,
    address_city varchar(100) NULL ,
    address_state varchar(100) NULL ,
    address_country varchar(100) NULL ,
    address_postalcode varchar(20) NULL ,
    deleted bool NULL ,
    portal_only bool DEFAULT '0' NULL ,
    show_on_employees bool DEFAULT '1' NULL ,
    employee_status varchar(100) NULL ,
    messenger_id varchar(100) NULL ,
    messenger_type varchar(100) NULL ,
    reports_to_id char(36) NULL ,
    is_group bool NULL ,
    factor_auth bool NULL ,
    factor_auth_interface varchar(255) NULL ,
    PRIMARY KEY (id),
    KEY idx_user_name (user_name, is_group, status, last_name, first_name,id)
) CHARACTER SET utf8 COLLATE utf8_general_ci;

Log:

• Using: Server version: 5.6.44 MySQL Community Server (GPL) I cannot reproduce the bug
• Using Server version: 5.7.26-0ubuntu0.19.04.1 (Ubuntu) I cannot reproduce the bug
• Using Server version: 5.7.25-28 Percona Server (GPL), Release '28', Revision 'c335905' I cannot reproduce the bug.

[Abuelodelanada]

@samus-aran

Despite of the fact that I cannot reproduce the bug in MySQL 5.6, I think that we could modify the vardefs.php of Users like this:

@@ -762,8 +762,8 @@ $dictionary['User'] = array(
                 'user_name',
                 'is_group',
                 'status',
-                'last_name',
-                'first_name',
+                'last_name (30)',
+                'first_name (30)',
                 'id'
             )
         ),

With this we still have a varchar(255) for first_name and last_name, but we will use prefix indexes (with the long of old definition). This will even improve the performance of the index.
The users table (and its indexes) will be like this:

CREATE TABLE `users` (
  `id` char(36) NOT NULL,
  `user_name` varchar(60) DEFAULT NULL,
  `user_hash` varchar(255) DEFAULT NULL,
  `system_generated_password` tinyint(1) DEFAULT NULL,
  `pwd_last_changed` datetime DEFAULT NULL,
  `authenticate_id` varchar(100) DEFAULT NULL,
  `sugar_login` tinyint(1) DEFAULT '1',
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `is_admin` tinyint(1) DEFAULT '0',
  `external_auth_only` tinyint(1) DEFAULT '0',
  `receive_notifications` tinyint(1) DEFAULT '1',
  `description` text,
  `date_entered` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  `modified_user_id` char(36) DEFAULT NULL,
  `created_by` char(36) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `department` varchar(50) DEFAULT NULL,
  `phone_home` varchar(50) DEFAULT NULL,
  `phone_mobile` varchar(50) DEFAULT NULL,
  `phone_work` varchar(50) DEFAULT NULL,
  `phone_other` varchar(50) DEFAULT NULL,
  `phone_fax` varchar(50) DEFAULT NULL,
  `status` varchar(100) DEFAULT NULL,
  `address_street` varchar(150) DEFAULT NULL,
  `address_city` varchar(100) DEFAULT NULL,
  `address_state` varchar(100) DEFAULT NULL,
  `address_country` varchar(100) DEFAULT NULL,
  `address_postalcode` varchar(20) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT NULL,
  `portal_only` tinyint(1) DEFAULT '0',
  `show_on_employees` tinyint(1) DEFAULT '1',
  `employee_status` varchar(100) DEFAULT NULL,
  `messenger_id` varchar(100) DEFAULT NULL,
  `messenger_type` varchar(100) DEFAULT NULL,
  `reports_to_id` char(36) DEFAULT NULL,
  `is_group` tinyint(1) DEFAULT NULL,
  `factor_auth` tinyint(1) DEFAULT NULL,
  `factor_auth_interface` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user_name` (`user_name`,`is_group`,`status`,`last_name`(30),`first_name`(30),`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If you agree, I'll make a PR to test it.

[pgorod]

Shouldn't we make it smaller than 100? Indexes should be short and fast to evaluate. There is nothing past the 30th or 40th character of a last_name field that is going to be relevant for indexing, especially when the full index key includes other fields.

Note that the change in first_name and last_name field length probably increased the index key sizes in many other tables. Even if they didn't crash with an error, the performance is impacted negatively. If this new PR works well, we should probably do it for more tables, would you agree?

I hope SuiteCRM correctly takes those field limitation values from the vardefs and into the SQL commands. And I hope that Studio and other parts of the app don't get confused with this change in the vardefs, we should probably do a bit more extensive testing, not just check the index creation.

[Abuelodelanada]

Hi @pgorod

Before we extended last_name and first_name to 255, the length was 100, and the index too.... so in terms of index performance this PR is the same because we use prefix indexes.

Note that the change in first_name and last_name field length probably increased the index key sizes in many other tables

No, the indexes are per table. We are not using first_name and last_name as foreign keys in other tables.

As far as I know the indices key in vardefs are only used when executing installation and QuickRebuildAndRepair and in Studio we cannot create or modify indexes.

[pgorod]

Ok, thanks for the clarifications!

[Abuelodelanada]

Sorry @pgorod

"Before we extended last_name and first_name to 255, the length was 100"

...No, the length was 30. So the prefix index could be even smaller ;-)