Upgrading from 7.3.4 to 7.4.6 SQL syntax Error

[rouhu]

When trying to do Database Rebuilt (even before actual upgrade) I get error:
[2023-07-19 10:01:31] ALERT: Rebuild database fault: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Unique ON users_external'

I am using MariaDB 10.9.7. Any advice for troubleshooting would be greatly appreciated.

[Vadym]

Hi rouhu,

Check data/config.php version parameter. Is it v7.3.4?
Do you upgrade your instance by running the command below?

https://docs.espocrm.com/administration/commands/#upgrade

[rouhu]

I did it in app upgrade page. But the CLI upgrade does not work either with different error:

[espocrm@srv1 public_html]$ php command.php upgrade
PHP Parse error: syntax error, unexpected ')', expecting '|' or variable (T_VARIABLE) in /home/espocrm/public_html/vendor/laminas/laminas-mail/src/Protocol/Smtp.php on line 329
[espocrm@srv1 public_html]$

[Vadym]

Hi rouhu,

Please provide screenshot of Administration -> System Requirements to investigate this issue further.

[rouhu]

Administration System Requirements
PHP Settings
PHP Version 8.1.17 Success
json On Success
openssl On Success
pdo_mysql On Success
mbstring On Success
zip On Success
gd On Success
iconv On Success
curl On Success
xml On Success
xmlwriter On Success
exif On Success
max_execution_time 600 Success
max_input_time 600 Success
memory_limit 256M Success
post_max_size 100M Success
upload_max_filesize 100M Success
Database Settings
MariaDB version 10.9.7-MariaDB Success
Host Name localhost Success
Database Name espocrm Success
User Name espocrm Success
Permissions
/home/espocrm/public_html/data Writable Success
/home/espocrm/public_html/application/Espo/Modules Writable Success
/home/espocrm/public_html/client/custom Writable Success
/home/espocrm/public_html/client/modules Writable Success
/home/espocrm/public_html/custom/Espo/Custom Writable Success

[item]

what is result of terminal command : php -v
?

[rouhu]

php -v is 7.4.33

But then with php81 the rebuilt still fails.

[espocrm@srv1 public_html]$ php81 command.php upgrade
Current version is 7.3.4.
EspoCRM will be upgraded to version 7.4.6 now. Enter [Y] to continue.
Y
This may take a while. Do not close the terminal.
Downloading...
Upgrading...
Initialization...
Reverting...
Error: Rebuild is failed. Fix all errors before upgrade.
[espocrm@srv1 public_html]$

Error Log:
[2023-08-04 10:17:36] ERROR: Database rebuild failure, details: Error while rebuilding database. See log file for details.. [] []
[2023-08-04 10:17:38] ALERT: Rebuild database fault: PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Unique ON users_external' at line 1 in /home/espocrm/public_html/vendor/doctrine/dbal/src/Driver/PDO/Connection.php:72 Stack trace: #0 /home/espocrm/public_html/vendor/doctrine/dbal/src/Driver/PDO/Connection.php(72): PDO->query() #1 /home/espocrm/public_html/vendor/doctrine/dbal/src/Connection.php(1049): Doctrine\DBAL\Driver\PDO\Connection->query() #2 /home/espocrm/public_html/application/Espo/Core/Utils/Database/Schema/Schema.php(218): Doctrine\DBAL\Connection->executeQuery() #3 /home/espocrm/public_html/application/Espo/Core/Utils/Database/Schema/SchemaProxy.php(58): Espo\Core\Utils\Database\Schema\Schema->rebuild() #4 /home/espocrm/public_html/application/Espo/Core/DataManager.php(145): Espo\Core\Utils\Database\Schema\SchemaProxy->rebuild() #5 /home/espocrm/public_html/application/Espo/Core/DataManager.php(110): Espo\Core\DataManager->rebuildDatabase() #6 /home/espocrm/public_html/application/Espo/Core/Upgrades/Actions/Base.php(1033): Espo\Core\DataManager->rebuild() #7 /home/espocrm/public_html/application/Espo/Core/Upgrades/Actions/Base.php(285): Espo\Core\Upgrades\Actions\Base->systemRebuild() #8 /home/espocrm/public_html/application/Espo/Core/Upgrades/Actions/Base/Install.php(347): Espo\Core\Upgrades\Actions\Base->throwErrorAndRemovePackage() #9 /home/espocrm/public_html/application/Espo/Core/Upgrades/Actions/Base/Install.php(122): Espo\Core\Upgrades\Actions\Base\Install->throwErrorAndRemovePackage() #10 /home/espocrm/public_html/application/Espo/Core/Upgrades/Base.php(193): Espo\Core\Upgrades\Actions\Base\Install->stepInit() #11 /home/espocrm/public_html/application/Espo/Core/Upgrades/Base.php(168): Espo\Core\Upgrades\Base->runActionStep() #12 /home/espocrm/public_html/application/Espo/Core/Console/Commands/UpgradeStep.php(88): Espo\Core\Upgrades\Base->runInstallStep() #13 /home/espocrm/public_html/application/Espo/Core/Console/Commands/UpgradeStep.php(65): Espo\Core\Console\Commands\UpgradeStep->runUpgradeStep() #14 /home/espocrm/public_html/application/Espo/Core/Console/CommandManager.php(95): Espo\Core\Console\Commands\UpgradeStep->run() #15 /home/espocrm/public_html/application/Espo/Core/ApplicationRunners/Command.php(51): Espo\Core\Console\CommandManager->run() #16 /home/espocrm/public_html/application/Espo/Core/Application/RunnerRunner.php(87): Espo\Core\ApplicationRunners\Command->run() #17 /home/espocrm/public_html/application/Espo/Core/Application.php(78): Espo\Core\Application\RunnerRunner->run() #18 /home/espocrm/public_html/command.php(35): Espo\Core\Application->run() #19 {main} Next Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'Unique ON users_external' at line 1 in /home/espocrm/public_html/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:30 Stack trace: #0 /home/espocrm/public_html/vendor/doctrine/dbal/src/Driver/PDO/Connection.php(77): Doctrine\DBAL\Driver\PDO\Exception::new() #1 /home/espocrm/public_html/vendor/doctrine/dbal/src/Connection.php(1049): Doctrine\DBAL\Driver\PDO\Connection->query() #2 /home/espocrm/public_html/application/Espo/Core/Utils/Database/Schema/Schema.php(218): Doctrine\DBAL\Connection->executeQuery() #3 /home/espocrm/public_html/application/Espo/Core/Utils/Database/Schema/SchemaProxy.php(58): Espo\Core\Utils\Database\Schema\Schema->rebuild() #4 /home/espocrm/public_html/application/Espo/Core/DataManager.php(145): Espo\Core\Utils\Database\Schema\SchemaProxy->rebuild() #5 /home/espocrm/public_html/application/Espo/Core/DataManager.php(110): Espo\Core\DataManager->rebuildDatabase() #6 /home/espocrm/public_html/application/Espo/Core/Upgrades/Actions/Base.php(1033): Espo\Core\DataManager->rebuild() #7 /home/espocrm/public_html/application/Espo/Core/Upgrades/Actions/Base.php(285): Espo\Core\Upgrades\Actions\Base->systemRebuild() #8 /home/espocrm/public_html/application/Espo/Core/Upgrades/Actions/Base/Install.php(347): Espo\Core\Upgrades\Actions\Base->throwErrorAndRemovePackage() #9 /home/espocrm/public_html/application/Espo/Core/Upgrades/Actions/Base/Install.php(122): Espo\Core\Upgrades\Actions\Base\Install->throwErrorAndRemovePackage() #10 /home/espocrm/public_html/application/Espo/Core/Upgrades/Base.php(193): Espo\Core\Upgrades\Actions\Base\Install->stepInit() #11 /home/espocrm/public_html/application/Espo/Core/Upgrades/Base.php(168): Espo\Core\Upgrades\Base->runActionStep() #12 /home/espocrm/public_html/application/Espo/Core/Console/Commands/UpgradeStep.php(88): Espo\Core\Upgrades\Base->runInstallStep() #13 /home/espocrm/public_html/application/Espo/Core/Console/Commands/UpgradeStep.php(65): Espo\Core\Console\Commands\UpgradeStep->runUpgradeStep() #14 /h... [] []
[2023-08-04 10:17:38] ERROR: Database rebuild failure, details: Error while rebuilding database. See log file for details.. [] []
[2023-08-04 10:17:38] ERROR: Upgrade Error: Error: Rebuild is failed. Fix all errors before upgrade. [] []

[shalmaxb]

I think, this version does not run with PHP 7.x

If you do not succeed in any scenario, you might consider to install a fresh espoCRM in another domain. Before that, make a backup of the web folders and an export of the database.

Then you install a new instance of espoCRM. When ready you import the database.

From the web folders upload the following folders:

data
custom
client/custom

If your new database has different credentials than the old one, you have to configure the config-internal.php at the values for database (user, password).

Then you make a rebuild and the app should work. If not, see the log-file, if there is any hint. Because of possible server issues, have a look into the server logs as well, if possible.

It might take much more time to search the culprit in your old installation and even finding does not guarantee, that the system will work correctly afterwards.

[rouhu]

I got to the bottom of this. I needed to make the "Name" field unique in a custom entity. So I created unique index for that column. I removed the unique index and then cli upgrade worked. Would there by any other way to make field unique in a custom entity ?

[shalmaxb]

In the entityDefs of that field add

"unique": true

If it is the "name" field, addittional line

"isCustom": true

This used to work, but I don`t know, if still.