Mautic 6.0.0 to 6.0.2 Update Can't DROP INDEX `mtc_company_match` Error

Product Support Mautic 6 Install/Upgrade Support
1

I originally downloaded the Mautic 6.0.0 zip from the Mautic GitHub release page and successfully installed it manually without using composer. Mautic 6.0.0 was working.

To upgrade to Mautic 6.0.2 I have run the following commands as documented.

php bin/console mautic:update:find

It identified that I was running Mautic 6.0.0 and there was an update available to Mautic 6.0.2.

I then applied the update with:
php bin/console mautic:update:apply

However when I run the next command it failed with the following errors
php bin/console mautic:update:apply --finish

In ExceptionConverter.php line 117:
An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX mtc_company_match; check that it exists

In Exception.php line 28:
SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX mtc_company_match; check that it exists
In Connection.php line 71:
SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX mtc_company_match; check that it exists

When I run the following: I get some info on the database migration status.
php bin/console doctrine:migration:status

Versions | Previous | Mautic\Migrations\Version20210112162046 |
| | Current | Mautic\Migrations\Version20210211081531 |
| | Next | Mautic\Migrations\Version20190326190241 |
| | Latest | Mautic\Migrations\Versionzz20230929183000 |
|----------------------------------------------------------------------------------------------------------------------|
| Migrations | Executed | 5 |
| | Executed Unavailable | 0 |
| | Available | 63 |
| | New | 58

The above states that Mautic\Migrations\Versionzz20230929183000 is the latest ?

However in theapps/migrations directory there is :
Version20250207035735.php and Versionzz20230929183000.php at the end of the list

The 2023 version is last in the list because zz is appended to the end of the word version i.e. Versionzz20230929183000. And as such will always be the last file in the list.

Can a developer please confirm that I’m my case the wrong php file version is being used for the database migration causing the “Can’t DROP INDEX `mtc_company_match’” error as defined above ?

Please note that Versionzz20230929183000.php is distributed in the Mautic 6.0.0 zip

Has anyone successfully used this technique upgrading Mautic from 6.0.0 to 6.0.2 ?

Thanks.

2

Hello,

I have checked the mtc_migrations table and when I attempted to upgrade from Mautic 6.0.0 to 6.0.2, according to the database it recorded a number of data migrations ?

mtc_migrations
mtc_migrations913×394 55.1 KB

Surely when Mautic 6.0.0 was installed it had the latest database schema ? Why would the upgrade run older migrations and not the 2025 one ?

Is this by design or is there a bug in database migrations ?

Thanks.

3

Hello,

Migration 6.0.0 to 6.0.2
I restored my database and the files in public_html back to pre migration.
I then ran the migration again using the same procedure.
Same error as above.

Migration 6.0.1 to 6.0.2
I spun up a test instance of 6.0.1 and populated it with some test data.
I then ran the migration procedure on this test instance.
Same error as above.

In both cases index mtc_company_match exists in the database so either the code can’t drop the index or possibly our web platform or database is unable to drop the index. Or the code is wrongly trying to drop the index. Only the developers can confirm what should happen.

I have to conclude that the migration leaves the database in an unknown state as it doesn’t complete cleanly. And there is no way to manually check what changes have been made.

May be the developers should consider documenting the database changes separately outside of the code so that if the database does suffer a problem any database administrator can easily resolve the database problems manually. This is how all commercial software is developed and all good open source projects as well.

WARNING - If you are running 6.0.0, backup your database and files before trying to update to the latest edition and try it on a test instance before running it on a production system. I’ve been unable to get it to work on either so far.

I believe a version upgrade that fails leaving the database in an unknown state is a critical problem but so far no developer has responded to my posts which is disappointing.

I hope the developers look at this problem and come up with a quick fix asap. I’m trying to quickly install the security fixes in 6.0.2 and have spent man days trying to get version upgrades working which should work by default.

Thanks.

4

Hello,

I have spun up a Mautic 6.0.2 test instance that has no added data i.e. a default new install database.

When I run the following command to determine outstanding migrations it thinks there are 63 which is a shock on a new install.
php bin/console doctrine:migration:status

Migration Status
Migration Status909×189 23.9 KB

The commands and procedures were taken from the the following documentation

Will you please investigate and resolve these database migration inconsistencies on Mautic 6.x as a matter of urgency.

Thanks.

5

You are not alone.
Have the same problem.
Anyone can help?

6

I am having a similar issue, in that my new (from scratch, using Composer) installation of 6.0.1 likewise lists 63 new migrations, including the one with the zz suffix from 2023.

I managed to kill a 5.2.5 installation through upgrading, and was expecting a fresh installation to be “migration-free”…

bin/console mautic:update:apply --finish results in:
Step 2 [============================] Success! You are now running version 6.0.2 of Mautic.

But then bin/console doctrine:migration:migrate --no-interaction
results in:
[OK] Successfully migrated to version: Mautic\Migrations\Versionzz20230929183000

gives (the same errors I was having with 5.2.5):

[WARNING] Not passing the “–complete” option to “doctrine:schema:update” is deprecated and will not be supported when using doctrine/dbal 4

Updating database schema…

In ExceptionConverter.php line 117:

An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX IDX_1AE3441319EB6921; check that it exists

In Exception.php line 28:

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

In Connection.php line 33:

SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP INDEX IDX_1AE3441319EB6921; check that it exists
doctrine:schema:update [–em EM] [–complete] [–dump-sql] [-f|–force]
bin/console doctrine:schema:update --no-interaction --force

I believe the issue lies with unnecessary migrations being applied, but I don’t know enough to track it down exactly.

7

Perhaps not useful, but still want to say that I had no problem in my case, which is likely due to the fact that I have a very minimal database. Also, I am in the dev environment. Well, it might not be totally useless. At the least, we know that it is specific to some settings.

8

Just to add that I also had no problem in the prod environment. I will try again with the database filled with the default test fixtures.

9

OK, I have the same error message. It was a fresh installation, simply added the default fixtures, which are used for the acceptance tests, and I have the same error message.

10

I checked which migrations created the error message. In my case, they were Version20211020114811.php and Version20230621074925.php. When I remove them
and run php bin/console mautic:update:apply --finish again, I get

Step    2 [============================] Success! You are now running version 6.0.2 of Mautic.

That might not be a good solution, because I don’t know what these migrations do.

11

I have the same question.

12

I looked at the code. The migrations to apply are computed in Doctrine\Migrations\Version\SortedMigrationPlanCalculator::getPlanUntilVersion():

        $availableMigrations = $this->getMigrations(); // migrations are sorted at this point
        $executedMigrations  = $this->metadataStorage->getExecutedMigrations();

The method getMigrations() returned the 63 migrations found in app\migrations. However, the method getExecutedMigrations() seems error prone to me. It uses the table test_migrations. It’s error prone, because `test_" is a prefix for tables used in tests. In my case, this table contained only 19 migrations, but all of the 63 migrations, except one or two, must have been already executed. I only started to look at the code. It is just a first impression.

13

I directly added the two migrations Version20211020114811 and Version20230621074925 in table test_migrations (using arbitrary times in the past) and it worked.

14

Hello dominic.mayers,

Thank you for your efforts so far.

Marking the two migrations you have identified as causing the problem as completed is a short term workaround. However ignoring the migration files completely is risky as they must be there for a reason and must do something important to the database or why else are they there ?

Doctrine creates these migration files after developers make changes to the database schema during code development.

I’m also concerned that the 2025 one that does not run and of course why was the versionzz migration renamed. I would have expected the migrations to work on a newly installed instance of Mautic with little data and without error.

I use the mtc_ prefix on database tables on all instancies if that has some bearing on the problem as many installers may not use a prefix.

I hope you find the root cause of the problem.

Thanks.

15

Finding the root cause is the whole point of what I wrote. It seems very clear that Mautic keeps the old migrations for different reasons than applying them when we do an update. This is apparent in the snippet that I copied/pasted above. It is also what AI says on the subject (but, of course, it has to be confirmed on the original sources). The root cause seems to be that the record of the old migrations that should not be applied is incorrect. It may not be related, but I find it weird that the table used to store these old migrations uses the prefix “test_”, at the least in my case. It will be good to see if that strange prefix is used in every one case or only in my case. By the way, in my case, the recent migrations are being applied and even added to the list of already applied migrations.

16

It is a good point. I will use a prefix for my tables and see if it affects the prefix used for the (old) migrations table. It is named test_migrations in my case, but I do not use any prefix for.my database tables.

17

@andrew_c3. this documentation says that the table migrations “tracks which database migrations have been applied”. The problem occurs perhaps only in those cases where the table does not exist or has been corrupted. BTW, I realize now that I had switched to a table prefix test_. So, there was nothing weird in the fact that the table was named test_migrations. Also, if the recent migrations are not applied, it is most likely due to the fact that an exception is raised before the code reach them. They are also not apply in my case when there is an error message, but when I add the two migrations mentioned above to the migrations table, they are applied. I don’t know why the other old migrations do not create an issue, but it maybe that it is just that applying them twice creates no issue.

18

I also found this explanation :

Whenever you run doctrine:migrations:status, the code reads the files in app/migrations and counts them as Available Migrations. Alongside this, it will read the table migrations in the MySQL database, which shows what migrations you have already applied (either through ordinary Mautic upgrade or, in rare cases of troubleshooting, manually with doctrine:migrations:migrate ). Any migration files that do not show up in the migrations table are labeled as New Migration and will be applied if you ever run doctrine:migrations:migrate

19

Hello dominic.mayers,

Yes I agree with your description of how Doctrine migrations work as I’ve just done some Symfony/Doctrine training on the very subject as I need to get up to speed with this platform.

To recap,

I was originally upgrading an early production 6.0.0 to 6.0.2 instance and this error occurred.
I restored from backups and repeated and the problem re-occurred.

I then spun up a separate test 6.0.1 instance with no data and attempted to upgrade to 6.0.2 and the same error occurred on the test instance. So I’m not convinced it database corruption problem.

And others have posted above with a similar experience.

What is the next step ?

Do we add the the two migrations you have identified to the migrations table with an arbitrary date/time in the past and complete the migration and then continue running ?

Or are your investigations still on going ?

Thanks.

20

If none of the migrations in app/migrations need to be applied again, in Ubuntu (and perhaps other Linux), at the command line, you could execute (after replacing database with your actual database name and the prefix prefix_ with your actual prefix):

for m in $(php bin/console doctrine:migrations:list | grep "not migrated" | egrep -o  "Mautic[\\A-Za-z0-9]*\ " | sed -e 's/\\/\\\\/g' ); do sudo mysql database  -e "INSERT INTO prefix_migrations VALUES ('$m', CURDATE(),'999' );"; done;

It does the following, which you could do separately if you wish:

  • It executes php bin/console doctrine:migrations:list to get the list of migrations and their status.
  • It selects (using grep) those that have the status “not migrated”.
  • It extracts (using egrep) the name of these migrations.
  • It escape the backslash in these names (using sed).
  • For each of these names as $m, it executes the mysql query
    “INSERT INTO prefix_migrations VALUES (‘$m’, CURDATE(),‘999’ );”