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

Product 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
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.

7

There is always so much pain and wasted time trying to update Mautic instances.

8

have you fixed this issue? It still happens when i updated to mautic 5.1.1

9

A client of mine still has this issue, when having a fresh 5.1 install on his server. So we are still seeing this issue in the wild.

Anyone has any solution? Would love to get him up and running with Mautic, but these issues are currently blocking project progress :frowning: