Upgrade error from 5.3.0 to 5.3.2

[MatLudlam]

Hi I am upgrading from 5.3.0 to 5.3.2 and get the error "Error: Error while rebuilding database. See log file for details." whilst doing so.

Here is my log with a few new lines added to aide readability:

[2018-07-18 10:03:25] Espo.ALERT: Rebuild database fault: 
PDOException: SQLSTATE[HY000]: General error: 1283 Column 'body_plain' cannot be part of FULLTEXT index in /var/www/html/espo/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:699 Stack trace: 
#0 /var/www/html/espo/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(699): PDO->query('CREATE FULLTEXT...') 
#1 /var/www/html/espo/application/Espo/Core/Utils/Database/Schema/Schema.php(196): Doctrine\DBAL\Connection->executeQuery('CREATE FULLTEXT...') 
#2 /var/www/html/espo/application/Espo/Core/DataManager.php(95): Espo\Core\Utils\Database\Schema\Schema->rebuild(NULL) 
#3 /var/www/html/espo/application/Espo/Core/DataManager.php(62): Espo\Core\DataManager->rebuildDatabase(NULL) 
#4 /var/www/html/espo/application/Espo/Core/Upgrades/Actions/Base.php(634): Espo\Core\DataManager->rebuild() 
#5 /var/www/html/espo/application/Espo/Core/Upgrades/Actions/Base/Install.php(95): Espo\Core\Upgrades\Actions\Base->systemRebuild() 
#6 /var/www/html/espo/application/Espo/Core/Upgrades/ActionManager.php(84): Espo\Core\Upgrades\Actions\Base\Install->run(Array) 
#7 /var/www/html/espo/application/Espo/Core/Upgrades/Base.php(85): Espo\Core\Upgrades\ActionManager->run(Array) 
#8 /var/www/html/espo/application/Espo/Controllers/Admin.php(96): Espo\Core\Upgrades\Base->install(Array) 
#9 /var/www/html/espo/application/Espo/Core/ControllerManager.php(118): Espo\Controllers\Admin->postActionRunUpgrade(Array, Object(stdClass), Object(Slim\Http\Request)) 
#10 /var/www/html/espo/application/Espo/Core/Application.php(227): Espo\Core\ControllerManager->process('Admin', 'runUpgrade', Array, Object(stdClass), Object(Slim\Http\Request)) 
#11 [internal function]: Espo\Core\Application->Espo\Core\{closure}() 
#12 /var/www/html/espo/vendor/slim/slim/Slim/Slim.php(1210): call_user_func_array(Object(Closure), Array) 
#13 /var/www/html/espo/vendor/slim/slim/Slim/Slim.php(1356): Slim\Slim->applyHook('slim.before.dis...') 
#14 /var/www/html/espo/vendor/slim/slim/Slim/Middleware/Flash.php(85): Slim\Slim->call() 
#15 /var/www/html/espo/vendor/slim/slim/Slim/Middleware/MethodOverride.php(92): Slim\Middleware\Flash->call() 
#16 /var/www/html/espo/application/Espo/Core/Utils/Api/Auth.php(121): Slim\Middleware\MethodOverride->call() 
#17 /var/www/html/espo/application/Espo/Core/Utils/Api/Slim.php(125): Espo\Core\Utils\Api\Auth->call() 
#18 /var/www/html/espo/application/Espo/Core/Application.php(88): Espo\Core\Utils\Api\Slim->run() 
#19 /var/www/html/espo/api/v1/index.php(33): Espo\Core\Application->run() 
#20 {main}  Next Doctrine\DBAL\DBALException: An exception occurred while executing 'CREATE FULLTEXT INDEX IDX_SYSTEM_FULL_TEXT_SEARCH ON `email` (name, body_plain, body)':  SQLSTATE[HY000]: General error: 1283 Column 'body_plain' cannot be part of FULLTEXT index in /var/www/html/espo/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:91 Stack trace: 
#0 /var/www/html/espo/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(702): Doctrine\DBAL\DBALException::driverExceptionDuringQuery(Object(PDOException), 'CREATE FULLTEXT...', Array) 
#1 /var/www/html/espo/application/Espo/Core/Utils/Database/Schema/Schema.php(196): Doctrine\DBAL\Connection->executeQuery('CREATE FULLTEXT...') 
#2 /var/www/html/espo/application/Espo/Core/DataManager.php(95): Espo\Core\Utils\Database\Schema\Schema->rebuild(NULL) 
#3 /var/www/html/espo/application/Espo/Core/DataManager.php(62): Espo\Core\DataManager->rebuildDatabase(NULL) 
#4 /var/www/html/espo/application/Espo/Core/Upgrades/Actions/Base.php(634): Espo\Core\DataManager->rebuild() 
#5 /var/www/html/espo/application/Espo/Core/Upgrades/Actions/Base/Install.php(95): Espo\Core\Upgrades\Actions\Base->systemRebuild() 
#6 /var/www/html/espo/application/Espo/Core/Upgrades/ActionManager.php(84): Espo\Core\Upgrades\Actions\Base\Install->run(Array) 
#7 /var/www/html/espo/application/Espo/Core/Upgrades/Base.php(85): Espo\Core\Upgrades\ActionManager->run(Array) 
#8 /var/www/html/espo/application/Espo/Controllers/Admin.php(96): Espo\Core\Upgrades\Base->install(Array) 
#9 /var/www/html/espo/application/Espo/Core/ControllerManager.php(118): Espo\Controllers\Admin->postActionRunUpgrade(Array, Object(stdClass), Object(Slim\Http\Request)) 
#10 /var/www/html/espo/application/Espo/Core/Application.php(227): Espo\Core\ControllerManager->process('Admin', 'runUpgrade', Array, Object(stdClass), Object(Slim\Http\Request)) 
#11 [internal function]: Espo\Core\Application->Espo\Core\{closure}() 
#12 /var/www/html/espo/vendor/slim/slim/Slim/Slim.php(1210): call_user_func_array(Object(Closure), Array) 
#13 /var/www/html/espo/vendor/slim/slim/Slim/Slim.php(1356): Slim\Slim->applyHook('slim.before.dis...') 
#14 /var/www/html/espo/vendor/slim/slim/Slim/Middleware/Flash.php(85): Slim\Slim->call() 
#15 /var/www/html/espo/vendor/slim/slim/Slim/Middleware/MethodOverride.php(92): Slim\Middleware\Flash->call() 
#16 /var/www/html/espo/application/Espo/Core/Utils/Api/Auth.php(121): Slim\Middleware\MethodOverride->call() 
#17 /var/www/html/espo/application/Espo/Core/Util... [] []
[2018-07-18 10:03:26] Espo.ERROR: API [POST]:/:controller/action/:action, Params:Array (     [controller] => Admin     [action] => runUpgrade ) , InputData: {"id":"5b4f10695dc87"} - Error while rebuilding database. See log file for details. [] []
[2018-07-18 10:03:26] Espo.ERROR: Display Error: Error while rebuilding database. See log file for details., Code: 500 URL: /espo/api/v1/Admin/action/runUpgrade [] []

Some other things that you may want (lightly edited to remove junk):

#mysql --version
mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

mysql> SHOW CREATE TABLE `email`;

| email | CREATE TABLE `email` (
  `id` varchar(24) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT '0',
  `from_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `from_string` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reply_to_string` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_replied` tinyint(1) NOT NULL DEFAULT '0',
  `message_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `message_id_internal` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `body_plain` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `body` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `is_html` tinyint(1) NOT NULL DEFAULT '1',
  `status` varchar(255) COLLATE utf8_unicode_ci DEFAULT 'Archived',
  `has_attachment` tinyint(1) NOT NULL DEFAULT '0',
  `date_sent` datetime DEFAULT NULL,
  `delivery_date` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  `is_system` tinyint(1) NOT NULL DEFAULT '0',
  `from_email_address_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `parent_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `parent_type` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_by_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `sent_by_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `modified_by_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `assigned_user_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `replied_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `account_id` varchar(24) COLLATE utf8_unicode_ci DEFAULT NULL,
  `address_name_map` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `IDX_E7927C74D445573A` (`from_email_address_id`),
  KEY `parent` (`parent_id`,`parent_type`),
  KEY `IDX_E7927C74B03A8386` (`created_by_id`),
  KEY `IDX_E7927C74A45BB98C` (`sent_by_id`),
  KEY `IDX_E7927C7499049ECE` (`modified_by_id`),
  KEY `IDX_E7927C74ADF66B1A` (`assigned_user_id`),
  KEY `IDX_E7927C74B4E994E0` (`replied_id`),
  KEY `IDX_E7927C749B6B5FBA` (`account_id`),
  KEY `IDX_EMAIL_DATE_SENT` (`date_sent`,`deleted`),
  KEY `IDX_EMAIL_DATE_SENT_STATUS` (`date_sent`,`status`,`deleted`),
  KEY `IDX_E7927C74537A1329` (`message_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

mysql> CREATE FULLTEXT INDEX IDX_SYSTEM_FULL_TEXT_SEARCH ON `email` (name, body_plain, body);
ERROR 1283 (HY000): Column 'body_plain' cannot be part of FULLTEXT index

I cannot understand why MySQL has this issue and causes the upgrade to file. Help, as always, appreciated.

Thanks.

[tarasm]

You have to change a column collation:

ALTER TABLE `email` CHANGE COLUMN `name` `name` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Did you move EspoCRM from another server?
Do you have configured a crontab?

[MatLudlam]

That changed worked thanks.

In answer to your questions:

This database has always been on this server, I don't think I have messed around with it. The database is large, 200K of email addresses, 200K of phone numbers, 140K contacts, 40K accounts and so on.

Yes crontab is running and working. Looking at the failed jobs there is one called "MysqlCharacter: jobConvertToMb4" that executed on the 12th of June and "Failed" that I suspect may be related (due to name). The other failed ones are "App: jobRebuild" and "NewVersionChecker" fro around the same time.

I am happy to move on, or help investigate further, let me know your preference.

Thanks.