MySQL error upgrading to 4.2

Product Support Mautic 4 Install/Upgrade Support
1

Your software
My PHP version is : 7.4
My MySQL/MariaDB version is (delete as applicable): Ver 15.1 Distrib 10.3.32-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 :
An error occurred while updating the database. Check log for more details.

These errors are showing in the Mautic log :
mautic.NOTICE: Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing 'ALTER TABLE `ma_lead_event_log` RENAME INDEX `IDX_SEARCH` TO `ma_IDX_SEARCH`': SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds

Your problem
My problem is :
Trying to upgrade to 4.2

Attempting to upgrade to version 4.2 and it is unable to complete the process.

What I’ve tried
When I run php bin/console mautic:update:apply --finish I get the error above.

Also - when I check the migrations with php bin/console doctrine:migrations:status I see that I appear to be far behind in migrations, considering there’s been a few updates since the last migration is listed and it says there are 10 migrations available:

image

When I try to run the migrations, I get the same error as when I run php bin/console mautic:update:apply --finish (the error above).

How can I complete the update to 4.2?

Also as a side note - Mautic thinks I am running 4.2 already.

2

Are you updating from the web? Starting with v5.0, you will only be able to update from CLI but get notification from web.

Try out this and see if it helps

3

No I am updating via command line.

4

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?

5

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.

6

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
7

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

8

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?

9

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?

10

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.

11

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
12

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
13

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

14 
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>]
15

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

16

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
17

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

2 Likes
18

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:

19

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.

20

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