7.10.1 – recurring database sync script when running Quick Repair and Rebuild

[davenz]

Issue

After upgrading to 7.10.1, performing a Quick Repair and Rebuild will prompt to execute a database sync script:

Differences found between database and vardefs

The following script will sync the database structure with the structure defined in the vardefs. You have the option of exporting this script and then running it against your database using external database management tools, or to allow the administration module to run the script.

NOTE: any changes you make to the script in the textbox will be reflected in the exported or executed code.

The script contents are:

/* Table : oauth2clients */
/*COLUMNS*/
/*MISMATCH WITH DATABASE - duration_value -  ROW [name] => 'duration_value'  [type] => 'int'  [len] => '11'  */
/* VARDEF - duration_value -  ROW[name] => 'duration_value'  [type] => 'integer'  [required] => ''  [reportable] => ''  [api-visible] => ''  [inline_edit] => ''  [dbType] => 'integer'  [len] => ''  */
/*MISMATCH WITH DATABASE - duration_amount -  ROW [name] => 'duration_amount'  [type] => 'int'  [len] => '11'  */
/* VARDEF - duration_amount -  ROW[name] => 'duration_amount'  [vname] => 'LBL_DURATION_AMOUNT'  [type] => 'integer'  [required] => ''  [reportable] => ''  [api-visible] => ''  [inline_edit] => ''  [dbType] => 'integer'  [len] => ''  */
/* INDEXES */
ALTER TABLE oauth2clients   modify COLUMN duration_value integer  NULL ,  modify COLUMN duration_amount integer  NULL ;

Expected Behavior

If the script is needed, then it executes successfully once.

Actual Behavior

The script will apparently execute without error, but upon running a subsequent Quick Repair and Rebuild the same prompt to run the same script will appear again.

Possible Fix

Steps to Reproduce

1. Go to Admin > Repair > Quick Repair and Rebuild
2. Observe the script execution prompt, and confirm
3. Go to Admin > Repair > Quick Repair and Rebuild
4. The script execution prompt appears again

Context

Your Environment

• SuiteCRM Version used: 7.10.1, upgraded from 7.9.8 with no customisations
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)):
• Environment name and version (e.g. MySQL, PHP 7):
• Operating System and version (e.g Ubuntu 16.04): Ubuntu 16.04 running a stock LAMP stack

[pgorod]

Seems to happen on several different kinds of OS/DB/PHP, see thread.

[likhobory]

I have the same problem on 7.10.1
The issue has been resolved after permission changing for mysql folder from 600 to 700.

[pgorod]

@likhobory what do you mean by "sql folder"? The folder where the database is in your file system, like /var/lib/mysql/suitecrm/?

[erevodifosin]

My /var/lib/mysql has 755, all the db folders have 700, and the frm files in the db folders have 660.
I haven't modify them ever. I am experiencing the same issue.

[likhobory]

like /var/lib/mysql/suitecrm/?

Yes

[pgorod]

@likhobory I wonder why you didn't have execute permissions there. I also have them in my install, just like @erevodifosin.

But it also doesn't make much sense that these rights solve the problem for you, but not for him... and I'm not getting the problem, so I can't test.

[ApatheticCosmos]

I'm also seeing this issue after upgrading from 7.8.11 to 7.10.1. Every time I run a quick repair, it says it needs this SQL to be run. I've tried running the SQL directly, which gives the response:
0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0
The repair still says it needs to apply the SQL.

[pgorod]

@gymad I see a fix for this issue included in a commit for an unrelated PR, probably by mistake:
https://github.com/salesagility/SuiteCRM/pull/5428/files

Meanwhile, the other people here can by testing it :-) just apply these changes.

[ApatheticCosmos]

@pgorod I've tested these changes and it does get rid of the SQL issue.

[itsgudenuf]

@pgorod I can confirm, the patch fixed this issue on my installation too.

[SuiteBot]

This issue has been mentioned on SuiteCRM. There might be relevant details there:

https://community.suitecrm.com/t/mismatch-with-database/86937/4