MySQL error upgrading to 4.2

Hi there,

What version are you updating from here?

You may have outstanding migrations if you have had incomplete updates in the past.

Does it do anything if you check in the UI for the schema updates?

I thought I was updating from 4.1 but perhaps some old updates didn’t finish completely.

If I try to check in the UI for schema I get a 500 server error.

Hi,

I had to play around with migrations to get it from Mautic 3 to 4 a bit too, because in previous versions of Mautic I have not executed update process completely, while I cannot remember the specific error I have a strong suspicion that your update error also comes from the same category of problems.

A general note on migrations in Mautic core: method isApplicable (or something very similar to that :)) is not really well implemented for all migrations thats why sometimes script ties to apply the migration even though you do not really need it.

About the error itself and how to work around it: A message from the log is saying that it cant renmae the index. You can triy to drop the index in question manually and then run the migration again. If that does not help see the steps below.

Upgrade process:

  • backup your mautic instance (source and database) as there is a possibility you will have to do the process multiple times
  • run php bin/console doctrine:migrations:migrate manually
  • migrations process will fail but while you run this command you can see exectly in which migration the problem is.
  • you now check the migration file of the failed migration, see what the migration is trying to do and adjust the database schema in a way so that migration can be applied (that usually means altering a table column)
  • after you manually update the schema just enough for the migration to apply schema changes successfully you run the doctrine migrations process again
  • this time it will either complete the migrations successfully or it will fail on the different migration, if it fails on different migration file you just inspect the failed migration fail again and repeat the process

Skipping migrations
After applying the process above you may come in a situation when during the process of manually updating the database schema for the migration you will actually update the schema in the same way as the migration file would if it would be applied successfully. When this happen you might want to skip the migration. You can do that by manually inserting a record in migrations table in your database.

Foreign keys
Some migrations might fail because of foreign key constraints, while FKs are important, you might encounter issues while trying to rescue the database, thats why you might want to turn off the foreign keys checks in the database before you start the migration, just remember to turn the checks back on when you are done.

I hope this helps, if you need further assistance let me know.

2 Likes

Thanks, I’ll give this a try and see how it goes. Backing up now…

I was able to complete the migrations using your guide!

I had to:

  1. drop the index causing the error
  2. manually complete another migration
  3. add a row to the ma_migrations table to mark another migration as complete, as it was getting stuck but had already been done.

So after trying several times and performing those fixes, it finally ran to completion.

Do I need to worry about re-creating the index that I dropped?

So now I’m getting an error when trying to view a segment because of the missing segment:

mautic.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing 'SELECT DATE_FORMAT(t.date_added, '%Y-%m-%d') AS date, COUNT(*) AS count FROM ma_lead_event_log t USE INDEX (ma_IDX_SEARCH) WHERE (t.object = ?) AND (t.bundle = ?) AND (t.action = ?) AND (t.object_id = ?) AND (t.date_added BETWEEN ? AND ?) GROUP BY DATE_FORMAT(t.date_added, '%Y-%m-%d') ORDER BY DATE_FORMAT(t.date_added, '%Y-%m-%d') ASC LIMIT 29' with params ["segment", "lead", "added", "52", "2022-02-01 00:00:00", "2022-03-01 23:59:59"]: SQLSTATE[42000]: Syntax error or access violation: 1176 Key 'ma_IDX_SEARCH' doesn't exist in table 't'" at /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 128 {"exception":"[object] (Doctrine\\DBAL\\Exception\\DriverException(code: 0): An exception occurred while executing 'SELECT DATE_FORMAT(t.date_added, '%Y-%m-%d') AS date, COUNT(*) AS count FROM ma_lead_event_log t USE INDEX (ma_IDX_SEARCH) WHERE (t.object = ?) AND (t.bundle = ?) AND (t.action = ?) AND (t.object_id = ?) AND (t.date_added BETWEEN ? AND ?) GROUP BY DATE_FORMAT(t.date_added, '%Y-%m-%d') ORDER BY DATE_FORMAT(t.date_added, '%Y-%m-%d') ASC LIMIT 29' with params [\"segment\", \"lead\", \"added\", \"52\", \"2022-02-01 00:00:00\", \"2022-03-01 23:59:59\"]:\n\nSQLSTATE[42000]: Syntax error or access violation: 1176 Key 'ma_IDX_SEARCH' doesn't exist in table 't' at /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128, Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1176 Key 'ma_IDX_SEARCH' doesn't exist in table 't' at /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1176 Key 'ma_IDX_SEARCH' doesn't exist in table 't' at /var/www/mautic/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:112)"} []

How can I re-create this index?

Try running the following command:

php bin/console doctrine:schema:update --dump-sql

It should display the statement for creating the index that is missing.

You can also try to run the migration that adds this index.

Thanks, before I saw your reply I did it by just looking at the error for the columns on the index and creating it that way:

CREATE INDEX ma_IDX_SEARCH ON ma_lead_event_log (object, bundle, action, object_id, date_added)

I can now view segments again.

3 Likes

I can see my PR Segment view optimization by escopecz · Pull Request #10523 · mautic/mautic · GitHub is to blame. It has 2 migrations in it for some reason (I’m not the author, I cherry-picked it from a colleague who left our team). I’m missing the full error in this forum thread. I can’t tell which of the 2 migration caused the problem. Can someone please provide full error that running the bin/console doctrine:migrations:migrate command outputs for you?

Update: From the commit messages it seems that the first migration was done without considering the table prefix. Since it was deployed to our development environment instances he created a second migration that fix the prefix. I should have caught that and merge those 2 migrations into one.

2 Likes

I also am struggling with updating.

When I run php bin/console doctrine:migrations:migrate I get this:

Migration 20201120122846 failed during Execution. Error An exception occurred while executing ’
CREATE TABLE mau4m_campaign_summary (
id INT UNSIGNED AUTO_INCREMENT NOT NULL,
campaign_id INT UNSIGNED DEFAULT NULL,
event_id INT UNSIGNED NOT NULL,
date_triggered DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime_immutable)’,
scheduled_count INT NOT NULL,
triggered_count INT NOT NULL,
non_action_path_taken_count INT NOT NULL,
failed_count INT NOT NULL,
log_counts_processed INT,
INDEX IDX_CEFB88B5F639F774 (campaign_id),
INDEX IDX_CEFB88B571F7E88B (event_id),
UNIQUE INDEX campaign_event_date_triggered (campaign_id, event_id, date_triggered),
PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC;
':

Table ‘mau4m_campaign_summary’ already exists

In AbstractMySQLDriver.php line 57:

An exception occurred while executing ’
CREATE TABLE mau4m_campaign_summary (
id INT UNSIGNED AUTO_INCREMENT NOT NULL,
campaign_id INT UNSIGNED DEFAULT NULL,
event_id INT UNSIGNED NOT NULL,
date_triggered DATETIME DEFAULT NULL COMMENT ‘(DC2Type:date
time_immutable)’,
scheduled_count INT NOT NULL,
triggered_count INT NOT NULL,
non_action_path_taken_count INT NOT NULL,
failed_count INT NOT NULL,
log_counts_processed INT,
INDEX IDX_CEFB88B5F639F774 (campaign_id),
INDEX IDX_CEFB88B571F7E88B (event_id),
UNIQUE INDEX campaign_event_date_triggered (campaign_id, ev
ent_id, date_triggered),
PRIMARY KEY(id)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci EN
GINE = InnoDB ROW_FORMAT = DYNAMIC;
':

Table ‘mau4m_campaign_summary’ already exists

In StatementError.php line 19:

Table ‘mau4m_campaign_summary’ already exists

doctrine:migrations:migrate [–write-sql [WRITE-SQL]] [–dry-run] [–query-time] [–allow-no-migration] [–all-or-nothing [ALL-OR-NOTHING]] [–configuration [CONFIGURATION]] [–db-configuration [DB-CONFIGURATION]] [–db DB] [–em EM] [–shard SHARD] [-h|–help] [-q|–quiet] [-v|vv|vvv|–verbose] [-V|–version] [–ansi] [–no-ansi] [-n|–no-interaction] [-e|–env ENV] [–no-debug] [–] []

Any idea what I can do to fix this?

Running s/update/schema gives me: An error occurred while updating the database. Check log for more details.

This is in the log:

[2022-03-03 14:09:06] mautic.NOTICE: Doctrine\DBAL\Exception\TableExistsException: An exception occurred while executing ’ CREATE TABLE mau4m_campaign_summary ( id INT UNSIGNED AUTO_INCREMENT NOT NULL, campaign_id INT UNSIGNED DEFAULT NULL, event_id INT UNSIGNED NOT NULL, date_triggered DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime_immutable)’, scheduled_count INT NOT NULL, triggered_count INT NOT NULL, non_action_path_taken_count INT NOT NULL, failed_count INT NOT NULL, log_counts_processed INT, INDEX IDX_CEFB88B5F639F774 (campaign_id), INDEX IDX_CEFB88B571F7E88B (event_id), UNIQUE INDEX campaign_event_date_triggered (campaign_id, event_id, date_triggered), PRIMARY KEY(id) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC; ': Table ‘mau4m_campaign_summary’ already exists (uncaught exception) at /home/golfasia/engage.golfasia.com/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 57 while running console command doctrine:migrations:migrate
[2022-03-03 14:09:06] mautic.WARNING: Command doctrine:migrations:migrate exited with status code 1
[2022-03-03 14:09:06] mautic.ERROR: [UPGRADE ERROR] Exit code 1; Mautic Migrations \ Migrating up to 20220111202917 from 20201105120328 \ ++ migrating 20201120122846 \ → CREATE TABLE mau4m_campaign_summary ( id INT UNSIGNED AUTO_INCREMENT NOT NULL, campaign_id INT UNSIGNED DEFAULT NULL, event_id INT UNSIGNED NOT NULL, date_triggered DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime_immutable)’, scheduled_count INT NOT NULL, triggered_count INT NOT NULL, non_action_path_taken_count INT NOT NULL, failed_count INT NOT NULL, log_counts_processed INT, INDEX IDX_CEFB88B5F639F774 (campaign_id), INDEX IDX_CEFB88B571F7E88B (event_id), UNIQUE INDEX campaign_event_date_triggered (campaign_id, event_id, date_triggered), PRIMARY KEY(id) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC; Migration 20201120122846 failed during Execution. Error An exception occurred while executing ’ CREATE TABLE mau4m_campaign_summary ( id INT UNSIGNED AUTO_INCREMENT NOT NULL, campaign_id INT UNSIGNED DEFAULT NULL, event_id INT UNSIGNED NOT NULL, date_triggered DATETIME DEFAULT NULL COMMENT ‘(DC2Type:datetime_immutable)’, scheduled_count INT NOT NULL, triggered_count INT NOT NULL, non_action_path_taken_count INT NOT NULL, failed_count INT NOT NULL, log_counts_processed INT, INDEX IDX_CEFB88B5F639F774 (campaign_id), INDEX IDX_CEFB88B571F7E88B (event_id), UNIQUE INDEX campaign_event_date_triggered (campaign_id, event_id, date_triggered), PRIMARY KEY(id) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE = InnoDB ROW_FORMAT = DYNAMIC; ': \ Table ‘mau4m_campaign_summary’ already exists \ In AbstractMySQLDriver.php line 57: \ An exception occurred while executing ’ CREATE TABLE mau4m_campaign_summary ( id INT UNSIGNED AUTO_INCREMENT NOT NULL, campaign_id INT UNSIGNED DEFAULT NULL, event_id INT UNSIGNED NOT NULL, date_triggered DATETIME DEFAULT NULL COMMENT ‘(DC2Type:date time_immutable)’, scheduled_count INT NOT NULL, triggered_count INT NOT NULL, non_action_path_taken_count INT NOT NULL, failed_count INT NOT NULL, log_counts_processed INT, INDEX IDX_CEFB88B5F639F774 (campaign_id), INDEX IDX_CEFB88B571F7E88B (event_id), UNIQUE INDEX campaign_event_date_triggered (campaign_id, ev ent_id, date_triggered), PRIMARY KEY(id) ) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci EN GINE = InnoDB ROW_FORMAT = DYNAMIC; ': \ Table ‘mau4m_campaign_summary’ already exists \ In StatementError.php line 19: \ Table ‘mau4m_campaign_summary’ already exists \ doctrine:migrations:migrate [–write-sql [WRITE-SQL]] [–dry-run] [–query-time] [–allow-no-migration] [–all-or-nothing [ALL-OR-NOTHING]] [–configuration [CONFIGURATION]] [–db-configuration [DB-CONFIGURATION]] [–db DB] [–em EM] [–shard SHARD] [-h|–help] [-q|–quiet] [-v|vv|vvv|–verbose] [-V|–version] [–ansi] [–no-ansi] [-n|–no-interaction] [-e|–env ENV] [–no-debug] [–] \

running php bin/console doctrine:migrations:status gives me:

== Configuration

>> Name:                                               Mautic Migrations
>> Database Driver:                                    mysqli
>> Database Host:                                      localhost
>> Database Name:                                      databasename-here
>> Configuration Source:                               manually configured
>> Version Table Name:                                 mau4m_migrations
>> Version Column Name:                                version
>> Migrations Namespace:                               Mautic\Migrations
>> Migrations Directory:                               /path/app/migrations
>> Previous Version:                                   2020-11-02 13:35:46 (20201102133546)
>> Current Version:                                    2020-11-05 12:03:28 (20201105120328)
>> Next Version:                                       2020-11-20 12:28:46 (20201120122846)
>> Latest Version:                                     2022-01-11 20:29:17 (20220111202917)
>> Executed Migrations:                                31
>> Executed Unavailable Migrations:                    0
>> Available Migrations:                               47
>> New Migrations:                                     16

Any help is appreciated

WARNING! You are about to execute a database migration that could result in schema changes and data loss. Are you sure you wish to continue? (y/n)Migrating up to 20220111202917 from 20210623071326

  ++ migrating 20201125155904

     -> ALTER TABLE `mlead_event_log` RENAME INDEX `IDX_SEARCH` TO `mIDX_SEARCH`
Migration 20201125155904 failed during Execution. Error An exception occurred while executing 'ALTER TABLE `mlead_event_log` RENAME INDEX `IDX_SEARCH` TO `mIDX_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 `mIDX_SEARCH`' at line 1

In AbstractMySQLDriver.php line 98:
                                                                               
  An exception occurred while executing 'ALTER TABLE `mlead_event_log` RENAME  
   INDEX `IDX_SEARCH` TO `mIDX_SEARCH`':                                       
                                                                               
  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 `mIDX_SEAR  
  CH`' at line 1                                                               
                                                                               

In Exception.php line 18:
                                                                               
  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 `mIDX_SEAR  
  CH`' at line 1                                                               
                                                                               

In PDOConnection.php line 132:
                                                                               
  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 `mIDX_SEAR  
  CH`' at line 1                                                               
                                                                               

doctrine:migrations:migrate [--write-sql [WRITE-SQL]] [--dry-run] [--query-time] [--allow-no-migration] [--all-or-nothing [ALL-OR-NOTHING]] [--configuration [CONFIGURATION]] [--db-configuration [DB-CONFIGURATION]] [--db DB] [--em EM] [--shard SHARD] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command> [<version>]

I manualy deleted IDX_SEARCH in phpmyadmin and run cron to migration. It helps. Thanks @mzagmajster!

This is different error than this topic is about. It’s problem with this migration:

which was added in Mautic 4.1. I suspect the problem might be caused by the migration failing in the middle, the table was created and now only the constraints are missing. I’d suggest to solve it by running

bin/console doctrine:schema:update --dump-sql | grep campaign_summary

This will print the SQL queries you have to run to fix the summary table and the migration command. Run those queries directly on the database either via command line, PhpMyAdmin, Adminer, SequelPro or similar UI.

2 Likes

I created fix for both issues with the campaign_summary as well as the lead_event_log index. Both are reproducible.

2 Likes

Thank you so much for this tip.

I ran the command and then got this output:

ALTER TABLE mau4m_campaign_summary ADD CONSTRAINT FK_CEFB88B5F639F774 FOREIGN KEY (campaign_id) REFERENCES mau4m_campaigns (id);
ALTER TABLE mau4m_campaign_summary ADD CONSTRAINT FK_CEFB88B571F7E88B FOREIGN KEY (event_id) REFERENCES mau4m_campaign_events (id) ON DELETE CASCADE;
DROP INDEX campaign_event_date_triggered ON mau4m_campaign_summary;
CREATE UNIQUE INDEX mau4m_campaign_event_date_triggered ON mau4m_campaign_summary (campaign_id, event_id, date_triggered);

I then went to phymyadmin and ran the query and got this:

ALTER TABLE mau4m_campaign_summary ADD CONSTRAINT FK_CEFB88B5F639F774 FOREIGN KEY (campaign_id) REFERENCES mau4m_campaigns (id)
MySQL said: Documentation

#1005 - Can't create table `db_maut926`.`mau4m_campaign_summary` (errno: 150 "Foreign key constraint is incorrectly formed")

I think I am getting close but still far away :slight_smile:

We manually updated the tables one by one and when i run /s/update/schema, I now get a green light, saying database has been updated. Also when i check the migration status, all is good.

However, now when I am opening an email to edit or load a customer detail, it won’t load at all (email) or it takes aaaages to load (customer detail page). What could be missing?

Thank you.

Check Troubleshooting | Mautic and if it’s not related to this thread, please start a new one so we don’t mix several issues together.

1 Like

I ran into a similar issue going from 4.1.x to 4.2. on MariaDB

The process failed at bin/console mautic:update:apply.

The log indicated :Error thrown while running command “doctrine:migrations:migrate --quiet --no-interaction”. Message: "An exception occurred while executing 'ALTER TABLE maup6_lead_event_log RENAME INDEX IDX_SEARCH TO maup6_IDX_SEARCH':

I manually created maup6_IDX_SEARCH using the same values as IDX_SEARCH, then deleted IDX_SEARCH.

I then reran at received a new error:

console.CRITICAL: Error thrown while running command “doctrine:migrations:migrate --quiet --no-interaction”. Message: “An exception occurred while executing ‘ALTER TABLE maup6_email_stats CHANGE COLUMN tokens tokens LONGTEXT CHARACTER SET ‘utf8mb4’ COLLATE ‘utf8mb4_unicode_ci’ NULL DEFAULT NULL COMMENT ‘(DC2Type:array)’;’: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘danianim_maut458.parcr.date_sent’ in ‘GENERATED ALWAYS’” {“exception”:"[object] (Doctrine\DBAL\Exception\InvalidFieldNameException(code: 0):

Reran apply and received the happy congratulations 4.2 message, but the site 403s.

then ran the dump sql script that returned 155 updates:

Took a back up, then ran the same script with --force which returned the FK errors above.

Not sure why my db is so far out of wack as every previous upgrade as executed successfully.

I can start stepping thru each update and debugging, but not sure that’s best approach.

Solved with commenting out

    <FilesMatch "\.php$">
        Require all denied
    </FilesMatch>

per this: 403 Error after Mautic 3.3.5 update - #5 by rcheesley

Where did you “solve” this @patrickeidemiller? Where did you “comment out” these lines? Looks like .htaccess. How is this connected with the SQL error?

I have the exact same problem as the start of this thread and don’t see a clear solution here.