Suitecrm: Repair Required just after Fresh Install 7.11.7

[Ashish-Outright]

Issue

Just after a fresh install, when I had a quick repair, It appended execute section. where this code is appearning

/* Table : users /
/COLUMNS/
/ INDEXES /
/INDEX MISMATCH WITH DATABASE - idx_user_name - ROW idx_user_name index [0] => 'user_name' [1] => 'is_group' [2] => 'status' [3] => 'last_name' [4] => 'first_name' [5] => 'id' /
/ VARDEF - idx_user_name - ROW idx_user_name index [0] => 'user_name' [1] => 'is_group' [2] => 'status' [3] => 'last_name (30)' [4] => 'first_name (30)' [5] => 'id' */
ALTER TABLE users DROP INDEX idx_user_name, ADD INDEX idx_user_name (user_name,is_group,status,last_name (30),first_name (30),id);

Expected Behavior

First repair must be clean repair, should not require anything to execute, that must be managed during installation time automatically.

Actual Behavior

At SuiteCRM Log:
08/01/19 08:49:36 [8300][-none-][FATAL] User update error: Temp User is not retrieved at ID 1, boolean given
08/01/19 08:49:36 [8300][-none-][FATAL] Email address save error
08/01/19 08:50:05 [8300][1][FATAL] Chart class not found.
08/01/19 08:50:07 [8300][1][FATAL] Chart class not found.

Possible Fix

Steps to Reproduce

1. click on admin
2. click on quick repair and rebuild
3. scroll down to bottom

Context

Your Environment

• SuiteCRM Version used: 7.11.7
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Chrome
• Environment name and version (e.g. MySQL, PHP 7):

PHP Version 5.6.38
MySQL Version 5.7.23

--

• Operating System and version (e.g Ubuntu 16.04):
  Windows , Using WAMP

[pgorod]

But if you click to execute, it executes correctly?

[Ashish-Outright]

No, it is appending again and again, so execute is failing..

[pgorod]

Can you get us the MySQL error message, saying why it's failing?

Is it possible that you had applied the manual fix before, and now it can't find the wrong index to delete, because it is already deleted?

[Ashish-Outright]

I have no manual fix before, just tried a fresh install and went to repair and seen those messages.
Those message are stuck and not going even after executing..

Nothing in mysql log.

[pgorod]

And in web server log (php_errors.log or errors.log, as defined in php.ini)?

[Ashish-Outright]

did not found anything in those logs

[lazka]

Same with 7.10.19. Nothing in the logs.

[ebogaard]

It seems the new index isn't detected properly.
When looking at the original issue, #7509, there is some messing about with the field length of first_name and last_name. It seems they setteled on 100, but in the db, the fields are (still) defined as 255, and the index uses 30 as field length. A bit messy.

I'm not sure what's the root cause of the index not being detected properly, but it seems at least the vardefs for these two fields need to be fixed. Maybe that solves it all.

I'd like to vote for including a fix for this in #7700

[connorshea]

Yep, seeing this on 7.10.19 as well.

[Abuelodelanada]

Yep, seeing this on 7.10.19 as well.

What MySQL version are you using?

[connorshea]

5.7

[jr-clark]

This issue also occurs after upgrading from 7.11.16 -> 7.11.17. The sql executes and the system indicates that everything has been synchronized, but subsequent repairs require the code be run again.

[Abuelodelanada]

I found the bug.

The problem is in the method get_indices() in include/database/MysqlManager.php, because executes a SHOW INDEX FROM users. If you execute the query the result is:

(root@localhost) [suitecrm71020]► SHOW INDEX FROM users;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY       |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | idx_user_name |            1 | user_name   | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | idx_user_name |            2 | is_group    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | idx_user_name |            3 | status      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | idx_user_name |            4 | last_name   | A         |           0 |       30 | NULL   | YES  | BTREE      |         |               |
| users |          1 | idx_user_name |            5 | first_name  | A         |           0 |       30 | NULL   | YES  | BTREE      |         |               |
| users |          1 | idx_user_name |            6 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
7 rows in set (0.00 sec)

And the value that get_indices returns is:

array (size=3)
  'name' => string 'idx_user_name' (length=13)
  'type' => string 'index' (length=5)
  'fields' => 
    array (size=6)
      0 => string 'user_name' (length=9)
      1 => string 'is_group' (length=8)
      2 => string 'status' (length=6)
      3 => string 'last_name' (length=9)
      4 => string 'first_name' (length=10)
      5 => string 'id' (length=2)

As we can see, the method is not taking into account the field Sub_part, so the method compareVarDefs() compare this array against the one defined in vardefs.php:

array (size=3)
  'name' => string 'idx_user_name' (length=13)
  'type' => string 'index' (length=5)
  'fields' => 
    array (size=6)
      0 => string 'user_name' (length=9)
      1 => string 'is_group' (length=8)
      2 => string 'status' (length=6)
      3 => string 'last_name (30)' (length=14)
      4 => string 'first_name (30)' (length=15)
      5 => string 'id' (length=2)

determines that they are not equal. So, the QuickRepair deletes and creates the index every time.

I will be working on the fix in the following days.

[Abuelodelanada]

Hi @connorshea @Ashish-Outright @lazka @jr-clark

Please, can you take a look at PR #7746 ?

[meierwitt]

Hi, is there a fix for 7.11.7 too? I will test.

[Abuelodelanada]

Hi, is there a fix for 7.11.7 too? I will test.

Hi @meierwitt

As far as I understand, if the PR that fixes this issue is merged, will eventually reach 7.11.x

Take a look at: https://docs.suitecrm.com/community/contributing-code/bugs/

[holdusback]

As you mentionned me in :

https://suitecrm.com/suitecrm/forum/suitecrm-7-0-discussion/27256-quick-repair-keeps-saying-differences-found-between-database-and-vardefs#88654

I can say that your fix, actually fix my issue, C/C every piece of code on the 3 file, run a qr&r and the issue is gone 👍