I am now going to fix my failed Mautic upgrade by just going over the crappy upgrade process again and hammering the same commands that you’re supposed to use:
php bin/console doctrine:migration:migrate goes through with the following error message:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error i
n your SQL syntax; check the manual that corresponds to your MariaDB server
version for the right syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_S
EARCH`' at line 1
php bin/console doctrine:schema:update --force goes through with the following errors:
SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a for
eign key constraint 'FK_818C32519EB6921'
php bin/console cache:clear goes through without errors, but nothing is fixed.
Trying to run the latest update again. After php bin/console mautic:update:apply --finish I get this error:
Step 1 [>---------------------------] Migrating database schema...
An error occurred while updating the database. Check log for more details.
My Mautic installation is now completely dead/unreachable. From the daily log in the Mautic installation directory /var/logs:
[2022-05-21 14:42:52] console.CRITICAL: Error thrown while running command "'doctrine:migration:migrate'". Message: "An exception occurred while executing 'ALTER TABLE `mtc_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`': 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 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1" {"exception":"[object] (Doctrine\\DBAL\\Exception\\SyntaxErrorException(code: 0): An exception occurred while executing 'ALTER TABLE `mtc_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`':\n\nSQLSTATE[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 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1 at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:98, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42000): 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 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1 at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42000): 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 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1 at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:132)","command":"'doctrine:migration:migrate'","message":"An exception occurred while executing 'ALTER TABLE `mtc_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`':\n\nSQLSTATE[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 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1"} []
[2022-05-21 14:47:24] console.CRITICAL: Error thrown while running command "doctrine:schema:update --force". Message: "An exception occurred while executing 'ALTER TABLE mtc_oauth2_accesstokens CHANGE client_id client_id INT UNSIGNED NOT NULL, CHANGE user_id user_id INT UNSIGNED DEFAULT NULL, CHANGE token token VARCHAR(191) NOT NULL, CHANGE scope scope VARCHAR(191) DEFAULT NULL': SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_818C32519EB6921'" {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing 'ALTER TABLE mtc_oauth2_accesstokens CHANGE client_id client_id INT UNSIGNED NOT NULL, CHANGE user_id user_id INT UNSIGNED DEFAULT NULL, CHANGE token token VARCHAR(191) NOT NULL, CHANGE scope scope VARCHAR(191) DEFAULT NULL':\n\nSQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_818C32519EB6921' at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: HY000): SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_818C32519EB6921' at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: HY000): SQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_818C32519EB6921' at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:132)","command":"doctrine:schema:update --force","message":"An exception occurred while executing 'ALTER TABLE mtc_oauth2_accesstokens CHANGE client_id client_id INT UNSIGNED NOT NULL, CHANGE user_id user_id INT UNSIGNED DEFAULT NULL, CHANGE token token VARCHAR(191) NOT NULL, CHANGE scope scope VARCHAR(191) DEFAULT NULL':\n\nSQLSTATE[HY000]: General error: 1832 Cannot change column 'client_id': used in a foreign key constraint 'FK_818C32519EB6921'"} []
[2022-05-21 14:53:29] console.CRITICAL: Error thrown while running command "doctrine:migrations:migrate --quiet --no-interaction". Message: "An exception occurred while executing 'ALTER TABLE `mtc_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`': 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 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1" {"exception":"[object] (Doctrine\\DBAL\\Exception\\SyntaxErrorException(code: 0): An exception occurred while executing 'ALTER TABLE `mtc_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`':\n\nSQLSTATE[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 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1 at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:98, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42000): 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 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1 at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42000): 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 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1 at /home/bizpartn/public_html/mrktng/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:132)","command":"doctrine:migrations:migrate --quiet --no-interaction","message":"An exception occurred while executing 'ALTER TABLE `mtc_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`':\n\nSQLSTATE[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 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1"} []
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 'INDEX IDX_SEARCH TO mtc_IDX_SEARCH’ at line 1" {“exception”:"[object] (Doctrine\DBAL\Exception\SyntaxErrorException(code: 0)
Apparently a way to fix it is to manually drop the problem-causing table from the database, run upgrade again and then presumably (?) manually recreate it later.
What/where is the table I should drop? I don’t see IDX_SEARCH, with or without prefix, in my database (via PHPMyAdmin).
The SQL the upgrade script is trying to run is apparently this:
ALTER TABLE mtc_lead_event_log CHANGE id id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, CHANGE lead_id lead_id BIGINT UNSIGNED DEFAULT NULL, CHANGE user_name user_name VARCHAR(191) DEFAULT NULL, CHANGE bundle bundle VARCHAR(191) DEFAULT NULL, CHANGE object object VARCHAR(191) DEFAULT NULL, CHANGE action action VARCHAR(191) DEFAULT NULL;
DROP INDEX idx_search ON mtc_lead_event_log;
CREATE INDEX mtc_IDX_SEARCH ON mtc_lead_event_log (bundle, object, action, object_id, date_added);
If I try to run that directly via phpmyadmin I get this error message:
ALTER TABLE mtc_lead_event_log CHANGE id id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, CHANGE lead_id lead_id BIGINT UNSIGNED DEFAULT NULL, CHANGE user_name user_name VARCHAR(191) DEFAULT NULL, CHANGE bundle bundle VARCHAR(191) DEFAULT NULL, CHANGE object object VARCHAR(191) DEFAULT NULL, CHANGE action action VARCHAR(191) DEFAULT NULL
And:
MySQL said: Documentation
#1832 - Cannot change column 'lead_id': used in a foreign key constraint 'FK_78B7E97955458D'
Clearing the cache still did not fix anything. Mautic installation still completely dead with 403.