Installing Fresh Copy of 4.3.1, Still has Mysql Bugs

johnwick · June 12, 2022, 12:31am

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?

mzagmajster · June 12, 2022, 7:45pm

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

johnwick · June 12, 2022, 9:24pm

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

mzagmajster · June 13, 2022, 1:12pm

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

johnwick · June 14, 2022, 11:06pm

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.

plato39 · October 7, 2023, 3:36pm

I just did a completely new install of 4.4.10 - on a different domain, so there is no chance of any old files / data anywhere. Exactly the same errors occurs.

I mentioned this in a previous post - when trying to upgrade any Mautic instance above 3.x.x these errors always show up. What I didn’t expect was to see them on the latest release on a clean install.

They have not caused any problems on a production install I’ve had going for many months.

plato39 · October 7, 2023, 3:40pm

As not everyone reports this I am beginning to wonder if these errors are caused by server environment - like using MariaDB for example?

Could it be that Mautic is not fully compatible with MariaDB greater than 10.2 ? I am using 10.3.X and all the posts about this error seem to have higher versions of MariaDB.

mzagmajster · October 8, 2023, 5:56pm

Hi,

I am using mariadb 10.3 and its working fine. It says here: < Requirements - Mautic Community > 10.2 is just minimal version.

When you install fresh instance of mautic you do not have to run migrate command since schema is already created when you install mautic.

Regards, M.

plato39 · October 8, 2023, 7:08pm

Hi

Yes, you are right. I did a local fresh install using MAMP and MySQL 5.7 and the error still shows.

But, on a fresh install, if you run the status command it tells you there are migrations pending. And after you run the migrations and use the validate command it tells you the schema is out of sync.

Have you tried running validate on your install?

mzagmajster · October 8, 2023, 8:42pm

It shows there are migrations to execute because migration files on disk (I think location is: app/migrations) do not have reference in the migrations table (in mariadb).

So you are fine not executing migrations when you install fresh instance of mautic. Even if you do run them, all migrations should be written in a way that they get skipped if there is nothing to migrate. Thats why in the above post someone got message SS skipped (Reason: Schema includes this migration) . Writing checks that cover all the cases in the migration file is a big task, that is why I would not suggest to not run the migrate command on a fresh install in the first place.

And do not ever execute this in production env.:

php bin/console doctrine:schema:update --force

I know there is a lot of content out there suggesting otherwise, but by executing the command above you are asking for troubles.

Running this:

php bin/console doctrine:schema:validate

outputs the error referring to DynamicContentLeadData entity for me, this is something that should in my opinion get looked at, but at the same time, I do not think its a big issue.

What is the output of your schema:validate command?

Regards, M.

plato39 · October 9, 2023, 6:20am

Thanks for the info.

When I run: php bin/console doctrine:schema:validate I get the DynamicContentLeadData error and the database schema is not in sync error. I see this on clean installs and on updated, production instances.

Mapping
-------

 [FAIL] The entity-class Mautic\DynamicContentBundle\Entity\DynamicContentLeadData mapping is invalid:
 * The association Mautic\DynamicContentBundle\Entity\DynamicContentLeadData#dynamicContent refers to the inverse side field Mautic\DynamicContentBundle\Entity\DynamicContent#id which is not defined as association.
 * The association Mautic\DynamicContentBundle\Entity\DynamicContentLeadData#dynamicContent refers to the inverse side field Mautic\DynamicContentBundle\Entity\DynamicContent#id which does not exist.


Database
--------


 [ERROR] The database schema is not in sync with the current mapping file.

Are you also seeing this?

mzagmajster · October 9, 2023, 11:22am

Yes, this issue has been around for a while and should be investigated. But from the practical perspective, I do not think this is a show stopper.

You can raise an issue on GH if it does not exist or even better create a PR that fixes this issue, at this point I would advise it to create a PR against Mautic 5, since Mautic 4 is gonna be replaced by Mautic 5 soon .

Anyway I hope it helps.

plato39 · October 10, 2023, 8:02am

Thanks @mzagmajster this was very helpful.

Hopeful this thread will also be helpful to others who are confused / anxious about these errors.

andrewchildsza · December 14, 2023, 7:24am

Getting the same error here on a recently upgraded instance to 4.4.10.

Topic		Replies	Views
Error during upgrade from 4.1.1 to 4.1.2 Mautic 4 Install/Upgrade Support	3	716	March 7, 2022
Outstanding queries after upgrading Mautic 4.3.0 and later Product Support	11	330	March 14, 2023
MySQL error upgrading to 4.2 Mautic 4 Install/Upgrade Support	24	5838	May 23, 2022
DB error when upgrading from 4.1.2 to 4.4.2 Mautic 4 Install/Upgrade Support	6	758	October 17, 2022
Mautic 2.6.14 to Mautic 3 error: DB migration ALTER TABLE Mautic 3 - Install/Upgrade Support	9	1312	June 9, 2021

Installing Fresh Copy of 4.3.1, Still has Mysql Bugs

Related topics