Another failed upgrade (4.3.1) on stuck database migration, SQL syntax error

Support Mautic 4 Install/Upgrade Support
#1

Your software
My PHP version is : 7.4.29
My MySQL/MariaDB version is : 10.3.34-MariaDB, pdo_mysql

Updating/Installing Errors
I am: Updating to 4.3.1 hoping it would fix a failed 4.2.2 upgrade)
Upgrading/installing via : Command Line

Same error reported several times before:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQ
  L syntax; check the manual that corresponds to your MariaDB server version for the r
  ight syntax to use near 'INDEX `IDX_SEARCH` TO `mtc_IDX_SEARCH`' at line 1

Installation still completely dead, 403 in the browser - business on hold for the last three weeks. Clearing cache does not solve the problem.

None of these instructions/suggestions to ‘recover failed update’ work in this case.

What is the error in the SQL syntax and how can I fix it?

Error from the log; not sure if there are more clues in there:

[2022-05-28 12:42:01] 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/myuser/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
#2

I stumbled through a partial solution here, but still have to fix other issues.

#3

I also have this error after upgrading, so frustrating that this isn’t tested more before it’s released!

console doctrine:schema:update --force

Updating database schema…

In AbstractMySQLDriver.php line 128:

An exception occurred while executing ‘DROP INDEX IDX_1AE3441319EB6921 ON oauth2_user_client_xref’:

SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX IDX_1AE3441319EB6921; check that it exists

In Exception.php line 18:

SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX IDX_1AE3441319EB6921; check that it exists

In PDOConnection.php line 141:

SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX IDX_1AE3441319EB6921; check that it exists

1 Like
#4

@johnwick, I managed to get through the upgrade by temporarily turning off foreign_key_check and running the stalling SQL queries manually via PHPMyAdmin (or whatever you use):

SET foreign_key_checks = 0;
– run some queries
SET foreign_key_checks = 1;

Documented in this thread.

1 Like
#5

Thank you, I gave that an attempt and it didn’t work. An upgrade shouldn’t have to be this much of a headache. Luckily I’m still in the setup process of Mautic, so I’ll just turf this install and start fresh again, hoping this never happens again.

1 Like
Field recreation on upgrade to 4.3 causing too many keys
#6

I’m facing exactly this same error after an upgrade to 4.3.1.

When I try to run php bin/console doctrine:schema:update --force at the command prompt I get the error that the index IDX_5AE981B819EB6921 doesn’t exist.

If I manually create an index with that name (via the mysql command prompt), then it complains the index already exists. I can’t even --dump-sql at that point to see what it’s trying to do because the mere act of running doctrine:shcema:update --dump-sql apparently tries to create the index that doctrine:shcema:update --force tries to drop.

How have others gotten past this? this is a production instance that I’m not really in a position to blow away and rebuild.