Installing Fresh Copy of 4.3.1, Still has Mysql Bugs

Environment

Linux 7.9
PHP 7.4
MariaDB 10.4.25

I got so fed up with the MySQL errors from upgrading 4.2 to 4.3.1 that I pulled the nuclear operation and deleted my previous 4.2.2 instance. Installing 4.3.1 did not fix the issue.

I installed using the composer method as instructed by:

After installing, I ran the commands

console doctrine:migration:migrate

console doctrine:schema:update --force

and got the following output and the same errors again.

[admin@server1 newsletter]$ console doctrine:migration:migrate

                    Mautic Migrations


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)y
Migrating up to 20220111202917 from 0

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20180821144204

     -> CREATE INDEX webhook_id_date ON webhook_queue (webhook_id, date_added)

  ++ migrated (took 312.5ms, used 46.5M memory)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20190704154940

Migration 20190704154940 was executed but did not result in any SQL statements.

  ++ migrated (took 0.90000000000009ms, used 46.5M memory)

  ++ migrating 20190724110039

Migration 20190724110039 was executed but did not result in any SQL statements.

  ++ migrated (took 532.2ms, used 46.5M memory)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: The online_status column on the users table has already been removed.)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20200220172041

     -> UPDATE `categories` SET bundle = 'messages' WHERE bundle = '0';

  ++ migrated (took 0.69999999999982ms, used 48.5M memory)

  ++ migrating 20200227110431

     -> CREATE INDEX dnc_channel_id_search ON lead_donotcontact (channel_id)

  ++ migrated (took 15.2ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20200422144300 (Migrate mautic_lead_fields.properties to simple array)

Migration 20200422144300 was executed but did not result in any SQL statements.

  ++ migrated (took 1.0999999999995ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: column_value is already the correct type.)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: The last_built_date column has already been added to the lead_lists table.)

  ++ migrating 20200815153711

     -> UPDATE `leads` SET `state` = 'Bragança' WHERE `state` = 'Braganca'
     -> UPDATE `leads` SET `state` = 'Coimbra' WHERE `state` = 'Colmbra'
     -> UPDATE `leads` SET `state` = 'Évora' WHERE `state` = 'Ovora'
     -> UPDATE `leads` SET `state` = 'Santarém' WHERE `state` = 'Santarem'
     -> UPDATE `leads` SET `state` = 'Setúbal' WHERE `state` = 'Setubal'
     -> UPDATE `leads` SET `state` = 'Região Autónoma dos Açores' WHERE `state` = 'Regiao Autonoma dos Acores'
     -> UPDATE `leads` SET `state` = 'Região Autónoma da Madeira' WHERE `state` = 'Regiao Autonoma da Madeira'

  ++ migrated (took 2.8000000000002ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20201015084627

     -> UPDATE lead_fields SET label = 'Preferred Timezone' WHERE alias = 'timezone';

  ++ migrated (took 1ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Index IDX_SEARCH already exists. Skipping the migration)

  SS skipped (Reason: This instance does not use prefix, so this migration does not apply. Skipping the migration)

  ++ migrating 20201130201631

Migration 20201130201631 was executed but did not result in any SQL statements.

  ++ migrated (took 1.3000000000002ms, used 48.5M memory)

  ++ migrating 20201207114926

     -> UPDATE lead_fields SET is_unique_identifer = 0 WHERE object = 'company';
     -> UPDATE lead_fields SET is_unique_identifer = 1 WHERE object = 'company' and alias in ('companyname');

  ++ migrated (took 1.3999999999996ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20201228041109

     -> ALTER TABLE assets MODIFY remote_path LONGTEXT
     -> ALTER TABLE assets MODIFY original_file_name LONGTEXT

  ++ migrated (took 6.9000000000005ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20210502162314


  SS skipped (Reason: No db_server_version parameter found in config/local.php, skipping this migration)

  ++ migrating 20210520100503

     -> ALTER TABLE `email_stats` CHANGE COLUMN `tokens` `tokens` LONGTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NULL DEFAULT NULL COMMENT '(DC2Type:array)';

  ++ migrated (took 5ms, used 48.5M memory)

  ++ migrating 20210609191822

Migration 20210609191822 was executed but did not result in any SQL statements.

  ++ migrated (took 499.6ms, used 48.5M memory)

  ++ migrating 20210614151138

     -> ALTER TABLE webhooks MODIFY webhook_url LONGTEXT NOT NULL

  ++ migrated (took 3.1999999999989ms, used 48.5M memory)

  ++ migrating 20210623071326

     -> ALTER TABLE forms MODIFY post_action_property LONGTEXT

  ++ migrated (took 4ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  ------------------------

  ++ finished in 8853.7ms
  ++ used 48.5M memory
  ++ 47 migrations executed
  ++ 18 sql queries
[dsnetadmin@newbeach newsletter]$ 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


doctrine:schema:update [--em EM] [--complete] [--dump-sql] [-f|--force] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>

[admin@server1 newsletter]$

4.3.1 failed quality control and should NOT have been released yet.

Any suggestions to fix this that don’t require me to deep dive into code?

Did you delete just the source or did you delete source + database. Error above makes me believe that you only deleted the source.

The error above also makes me believe that did not fully migrate mautic instance from one of the previous versions.

Try to follow this guide to resolve migration issues: MySQL error upgrading to 4.2 - #6 by mzagmajster

I wiped out the subdirectory folder and the database and installed fresh, creating a new database, then used the composer method.

Before you ran the migrations did you check the status of migrations after fresh install.

I just re-installed again, this time using the prefix indicated in the errors “IDX_” under an entirely new subdomain.

After installation, setting up the user and SMTP account and having logged into the dashboard, I run the following commands.

console doctrine:migration:migrate

and

console doctrine:schema:update --force

On the --force command the errors popup about the index error.


[admin@server hello]$ console doctrine:migration:migrate

                    Mautic Migrations


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)y
Migrating up to 20220111202917 from 0

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20180821144204

     -> CREATE INDEX IDXwebhook_id_date ON IDXwebhook_queue (webhook_id, date_added)

  ++ migrated (took 285.8ms, used 46.5M memory)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20190704154940

Migration 20190704154940 was executed but did not result in any SQL statements.

  ++ migrated (took 1.0999999999999ms, used 46.5M memory)

  ++ migrating 20190724110039

Migration 20190724110039 was executed but did not result in any SQL statements.

  ++ migrated (took 621.6ms, used 46.5M memory)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: The online_status column on the IDXusers table has already been removed.)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20200220172041

     -> UPDATE `IDXcategories` SET bundle = 'messages' WHERE bundle = '0';

  ++ migrated (took 0.89999999999964ms, used 48.5M memory)

  ++ migrating 20200227110431

     -> CREATE INDEX IDXdnc_channel_id_search ON IDXlead_donotcontact (channel_id)

  ++ migrated (took 8.6000000000004ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20200422144300 (Migrate mautic_lead_fields.properties to simple array)

Migration 20200422144300 was executed but did not result in any SQL statements.

  ++ migrated (took 1.2999999999993ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: column_value is already the correct type.)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: The last_built_date column has already been added to the IDXlead_lists table.)

  ++ migrating 20200815153711

     -> UPDATE `IDXleads` SET `state` = 'Bragança' WHERE `state` = 'Braganca'
     -> UPDATE `IDXleads` SET `state` = 'Coimbra' WHERE `state` = 'Colmbra'
     -> UPDATE `IDXleads` SET `state` = 'Évora' WHERE `state` = 'Ovora'
     -> UPDATE `IDXleads` SET `state` = 'Santarém' WHERE `state` = 'Santarem'
     -> UPDATE `IDXleads` SET `state` = 'Setúbal' WHERE `state` = 'Setubal'
     -> UPDATE `IDXleads` SET `state` = 'Região Autónoma dos Açores' WHERE `state` = 'Regiao Autonoma dos Acores'
     -> UPDATE `IDXleads` SET `state` = 'Região Autónoma da Madeira' WHERE `state` = 'Regiao Autonoma da Madeira'

  ++ migrated (took 13.299999999999ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20201015084627

     -> UPDATE IDXlead_fields SET label = 'Preferred Timezone' WHERE alias = 'timezone';

  ++ migrated (took 2.5999999999985ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20201120122846

     ->
            CREATE TABLE IF NOT EXISTS IDXcampaign_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_DE481DF1F639F774 (campaign_id),
                INDEX IDX_DE481DF171F7E88B (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;
     -> ALTER TABLE IDXcampaign_summary ADD CONSTRAINT FK_66B190B2F639F774 FOREIGN KEY (campaign_id) REFERENCES IDXcampaigns (id)
     -> ALTER TABLE IDXcampaign_summary ADD CONSTRAINT FK_66B190B271F7E88B FOREIGN KEY (event_id) REFERENCES IDXcampaign_events (id) ON DELETE CASCADE

  ++ migrated (took 3341.7ms, used 48.5M memory)

  ++ migrating 20201123070813

     -> ALTER TABLE `IDXlead_event_log` ADD INDEX `IDX_SEARCH` (`bundle`,`object`,`action`,`object_id`,`date_added`)
     -> ALTER TABLE IDXcampaign_summary DROP FOREIGN KEY FK_DE481DF171F7E88B
     -> ALTER TABLE IDXcampaign_summary DROP FOREIGN KEY FK_DE481DF1F639F774

  ++ migrated (took 1047.9ms, used 48.5M memory)

  SS skipped (Reason: Index IDXIDX_SEARCH already exists. Skipping the migration)

  ++ migrating 20201130201631

Migration 20201130201631 was executed but did not result in any SQL statements.

  ++ migrated (took 0.70000000000073ms, used 48.5M memory)

  ++ migrating 20201207114926

     -> UPDATE IDXlead_fields SET is_unique_identifer = 0 WHERE object = 'company';
     -> UPDATE IDXlead_fields SET is_unique_identifer = 1 WHERE object = 'company' and alias in ('companyname');

  ++ migrated (took 1.2000000000007ms, used 48.5M memory)

  ++ migrating 20201207140911

     -> CREATE INDEX campaign_trigger_date_order ON IDXcampaign_lead_event_log (trigger_date)

  ++ migrated (took 378.7ms, used 48.5M memory)

  ++ migrating 20201228041109

     -> ALTER TABLE IDXassets MODIFY remote_path LONGTEXT
     -> ALTER TABLE IDXassets MODIFY original_file_name LONGTEXT

  ++ migrated (took 9.7999999999993ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  SS skipped (Reason: Schema includes this migration)

  ++ migrating 20210502162314


  SS skipped (Reason: No db_server_version parameter found in config/local.php, skipping this migration)

  ++ migrating 20210520100503

     -> ALTER TABLE `IDXemail_stats` CHANGE COLUMN `tokens` `tokens` LONGTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NULL DEFAULT NULL COMMENT '(DC2Type:array)';

  ++ migrated (took 3.5ms, used 48.5M memory)

  ++ migrating 20210609191822

Migration 20210609191822 was executed but did not result in any SQL statements.

  ++ migrated (took 520.1ms, used 48.5M memory)

  ++ migrating 20210614151138

     -> ALTER TABLE IDXwebhooks MODIFY webhook_url LONGTEXT NOT NULL

  ++ migrated (took 2.8000000000029ms, used 48.5M memory)

  ++ migrating 20210623071326

     -> ALTER TABLE IDXforms MODIFY post_action_property LONGTEXT

  ++ migrated (took 7.5ms, used 48.5M memory)

  SS skipped (Reason: Schema includes this migration)

  ------------------------

  ++ finished in 18408.2ms
  ++ used 48.5M memory
  ++ 47 migrations executed
  ++ 25 sql queries

[admin@server hello]$ console doctrine:schema:update --force

 Updating database schema...


In AbstractMySQLDriver.php line 128:

  An exception occurred while executing 'DROP INDEX IDX_7A6B5A5919EB6921 ON IDXoauth2_user_client_xref':

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


In Exception.php line 18:

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


In PDOConnection.php line 141:

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


doctrine:schema:update [--em EM] [--complete] [--dump-sql] [-f|--force] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>

[admin@server hello]$

I looked in the database and IDX_7A6B5A5919EB6921 does not exist. So I created that index, and ran the [admin@server hello]$ console doctrine:schema:update --force again and it did delete the index that I created. It then complains about that index on another table.

There is a serious bug if this is happening on a brand new install.