Problem upgrading to Mautic 3 -- Can't drop index 'live_tweet_text_index';

Your software
My PHP version is : 7.2.24
My MySQL/MariaDB version is (delete as applicable): mysql Ver 15.1 Distrib 10.3.28-MariaDB

Updating/Installing Errors
I am (delete as applicable): Updating
Upgrading/installing via (delete as applicable) : Command Line

These errors are showing in the installer :

→ DROP INDEX live_tweet_text_index ON live_tweets
Migration 20200805185714 failed during Execution. Error An exception occurred while executing ‘DROP INDEX live_tweet_text_index ON live_tweets’:

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

In AbstractMySQLDriver.php line 106:

An exception occurred while executing ‘DROP INDEX live_tweet_text_index ON live_tweets’:

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

In PDOConnection.php line 80:

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

In PDOConnection.php line 75:

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

These errors are showing in the Mautic log :

[2021-06-30 01:56:23] mautic.NOTICE: Doctrine\DBAL\Exception\DriverException: An exception occurred while executing ‘DROP INDEX live_tweet_text_index ON live_tweets’: SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX live_tweet_text_index; check that it exists (uncaught exception) at /var/www/html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 106 while running console command doctrine:migrations:migrate
[2021-06-30 01:56:23] mautic.WARNING: Command doctrine:migrations:migrate exited with status code 1

[2021-06-30 01:56:23] console.ERROR: Error thrown while running command “doctrine:migrations:migrate --no-interaction --env=prod --no-debug”. Message: “An exception occurred while executing ‘DROP INDEX live_tweet_text_index ON live_tweets’: SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX live_tweet_text_index; check that it exists” {“exception”:"[object] (Doctrine\DBAL\Exception\DriverException(code: 0): An exception occurred while executing ‘DROP INDEX live_tweet_text_index ON live_tweets’:\n\nSQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX live_tweet_text_index; check that it exists at /var/www/html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:106, Doctrine\DBAL\Driver\PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX live_tweet_text_index; check that it exists at /var/www/html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:80, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX live_tweet_text_index; check that it exists at /var/www/html/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:75)",“command”:“doctrine:migrations:migrate --no-interaction --env=prod --no-debug”,“message”:“An exception occurred while executing ‘DROP INDEX live_tweet_text_index ON live_tweets’:\n\nSQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX live_tweet_text_index; check that it exists”}

These errors are showing in the upgrade_log.txt file (located in the root of your Mautic instance when an upgrade has been attempted - ensure you remove or redact any sensitive data such as domain names in the file path) :

 -> DROP INDEX live_tweet_text_index ON live_tweets

Migration 20200805185714 failed during Execution. Error An exception occurred while executing ‘DROP INDEX live_tweet_text_index ON live_tweets’:

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

In AbstractMySQLDriver.php line 106:

An exception occurred while executing ‘DROP INDEX live_tweet_text_index ON live_tweets’:

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

In PDOConnection.php line 80:

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

In PDOConnection.php line 75:

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

Your problem
My problem is :

I’m trying upgrade from mautic 2.16.5 to mautic 3 using SSH. Everything goes well until I run into the issues listed above. I’m not sure how to fix them. Any advice?

Steps I have tried to fix the problem :

1 Like

Hello,

Maybe you have already solved this issue but posting here for others who may encounter it. I upgraded mautic recently and had exactly the same issue when trying to go from v2.16.5 to v3.2.4, via command line.

PHP 7.3.31-1
Ver 15.1 Distrib 10.3.31-MariaDB

What it worked for me was manually creating the index in mautic database i.e `CREATE INDEX IF NOT EXISTS mautic_tweet_text_index ON mautic_tweets (text); '. Then when I tried the upgrade, it finished successfully.

Cheers
Aulona