Compatibility MATRIX is not correct causing MISMATCH WITH DATABASE errors

[fcorluka]

In compatibility matrix some combinations are not correct, and when installed there are constant errors in SuiteCRM when trying R&R.
Problem is version of MYSQL 8. In this version INT can not be declared with value/size, so "int (11)" is not posible to create but many modules have this fileds, e.g. Cases, case_number.

This is causing a big list of SQL to execute but nothing is done.
In CRM everything works, but I can not be sure, when I have a real error because this list is to big to read it properly.
Also this means that at any point something greater can go wrong.

Tested on different version of SuiteCRM, all have this problem with MySQL 8.

Your Environment

• SuiteCRM Version used: 7.10.32, 7.11
• Browser name and version (e.g. Chrome Version 51.0.2704.63 (64-bit)): Firefox 88
• Environment name and version (e.g. MySQL, PHP 7): MySQL 8, PHP 7.4.3
• Operating System and version (e.g Ubuntu 16.04):Ubuntu Server 20.4 LTS

[tsmgeek]

Do you have strict mode enabled on MySQL, currently according to spec its deprecated and throws a warning but should still work.

Can you indicate specific SQL version you are using and confirm which DB manager you are using in config.php.

Maybe its actually failing due to warnings not being handled correctly.
Whichever way there is no point in having display length on TINYINT, SMALLINT, MEDIUMINT, INT or BIGINT which is why they ended up deprecating it with eventual view to make it fail.

https://dev.mysql.com/worklog/task/?id=13127

[tsmgeek]

SuiteCRM/include/database/MysqlManager.php

Lines 820 to 822 in d57e913

	if ($ref['colType'] == 'int' && !empty($fieldDef['len'])) {
	$ref['colType'] .= "(" . $fieldDef['len'] . ")";
	}

SuiteCRM/include/database/MysqlManager.php

Lines 820 to 822 in d57e913

	if ($ref['colType'] == 'int' && !empty($fieldDef['len'])) {
	$ref['colType'] .= "(" . $fieldDef['len'] . ")";
	}

This code should be changed to ignore length on TINYINT, SMALLINT, MEDIUMINT, INT or BIGINT column types.
I do not think we need to worry on MySQL < 8.

[fcorluka]

Hi @tsmgeek ,
these are my settings. At this point I do not know if strict mode is enabled or not.
Thank you for the code and explanation, but this should be in CRM installation and core code months ago.

If it says that 7.10.30 is working with MySQL 8, then it should work out of the box. I tried using MariaDB (version 10.3) and did not get this error.

'dbconfig' =>
array (
'db_host_name' => 'localhost',
'db_host_instance' => 'SQLEXPRESS',
'db_user_name' => 'username',
'db_password' => 'password,
'db_name' => 'crm-710',
'db_type' => 'mysql',
'db_port' => '',
'db_manager' => 'MysqliManager',
'collation' => 'utf8_general_ci',
'charset' => 'utf8',
),

[SuiteBot]

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

https://community.suitecrm.com/t/upgrade-to-7-12-1-some-dashlets-crash/83048/6