Outstanding queries after upgrade to 4.4.5

Product Support Mautic 4 Install/Upgrade Support
1

Your software
My Mautic version is: 3.2.4 upgrading to 4.4.5
My PHP version is:7.4.9
My Database type and version is: MySQL 5.7.31

My problem is:
Upgrading from 3.2.4 to 4.4.5 seems to go fine and says that the upgrade has completed successfully, however, when I check to see if there are outstanding queries to bring the update up to date using console doctrine:schema:update --dump-sql, I see the following:

DROP INDEX IDX_1AE3441319EB6921 ON oauth2_user_client_xref

  • DROP INDEX IDX_6480052EF639F774 ON campaign_leadlist_xref
  • DROP INDEX IDX_3048A8B2F639F774 ON campaign_form_xref
  • DROP INDEX IDX_5995213DF639F774 ON campaign_leads
  • DROP INDEX IDX_2E24F01CA832C1C9 ON email_list_xref
  • DROP INDEX IDX_CA315778A832C1C9 ON email_assets_xref
  • ALTER TABLE form_fields CHANGE parent_id parent_id VARCHAR(191) DEFAULT NULL
  • ALTER TABLE lead_fields CHANGE column_is_not_created column_is_not_created TINYINT(1) DEFAULT ‘0’ NOT NULL, CHANGE original_is_published_value original_is_published_value TINYINT(1) DEFAULT ‘0’ NOT NULL
  • DROP INDEX googleplus_search ON leads
  • DROP INDEX address2_search ON leads
  • DROP INDEX state_search ON leads
  • DROP INDEX country_search ON leads
  • DROP INDEX timezone_search ON leads
  • DROP INDEX address1_search ON leads
  • DROP INDEX city_search ON leads
  • DROP INDEX zipcode_search ON leads
  • ALTER TABLE leads DROP googleplus, CHANGE fax fax VARCHAR(191) DEFAULT NULL, CHANGE preferred_locale preferred_locale VARCHAR(191) DEFAULT NULL, CHANGE website website VARCHAR(191) DEFAULT NULL, CHANGE facebook facebook VARCHAR(191) DEFAULT NULL, CHANGE foursquare foursquare VARCHAR(191) DEFAULT NULL, CHANGE instagram instagram VARCHAR(191) DEFAULT NULL, CHANGE linkedin linkedin VARCHAR(191) DEFAULT NULL, CHANGE skype skype VARCHAR(191) DEFAULT NULL, CHANGE twitter twitter VARCHAR(191) DEFAULT NULL, CHANGE fromfirst fromfirst VARCHAR(191) DEFAULT NULL, CHANGE fromlast fromlast VARCHAR(191) DEFAULT NULL, CHANGE iqfromtext iqfromtext VARCHAR(191) DEFAULT NULL, CHANGE jobtitle jobtitle VARCHAR(191) DEFAULT NULL, CHANGE businessline businessline VARCHAR(191) DEFAULT NULL, CHANGE owneralias owneralias VARCHAR(191) DEFAULT NULL, CHANGE ownerfullname ownerfullname VARCHAR(191) DEFAULT NULL, CHANGE eventname eventname VARCHAR(191) DEFAULT NULL, CHANGE eventdate eventdate VARCHAR(191) DEFAULT NULL, CHANGE awardyears awardyears VARCHAR(191) DEFAULT NULL, CHANGE awardvalue awardvalue VARCHAR(191) DEFAULT NULL, CHANGE garvyaward garvyaward VARCHAR(191) DEFAULT NULL, CHANGE teamname teamname VARCHAR(191) DEFAULT NULL, CHANGE ownertitle ownertitle VARCHAR(191) DEFAULT NULL, CHANGE school school VARCHAR(191) DEFAULT NULL
  • CREATE INDEX website_search ON leads (website)
  • DROP INDEX IDX_9EED7E6655458D ON lead_ips_xref
  • DROP INDEX IDX_F2E51EB655458D ON lead_tags_xref
  • DROP INDEX IDX_F5F47C7CB9FC8874 ON lead_lists_leads
  • DROP INDEX companycity_search ON companies
  • DROP INDEX companyzipcode_search ON companies
  • DROP INDEX companyname_search ON companies
  • DROP INDEX business_line_search ON companies
  • DROP INDEX companyaddress1_search ON companies
  • DROP INDEX companyindustry_search ON companies
  • DROP INDEX companyemail_search ON companies
  • DROP INDEX companyphone_search ON companies
  • DROP INDEX companystate_search ON companies
  • DROP INDEX companycountry_search ON companies
  • DROP INDEX organization_search ON companies
  • DROP INDEX companyaddress2_search ON companies
  • ALTER TABLE companies DROP organization, DROP business_line, CHANGE companywebsite companywebsite VARCHAR(191) DEFAULT NULL, CHANGE companyfax companyfax VARCHAR(191) DEFAULT NULL
  • DROP INDEX IDX_F4190AB6979B1AD6 ON companies_leads
  • ALTER TABLE push_notifications CHANGE name name VARCHAR(191) NOT NULL, CHANGE heading heading LONGTEXT NOT NULL, CHANGE message message LONGTEXT NOT NULL
  • DROP INDEX IDX_473919EFEF1A9D84 ON push_notification_list_xref
  • CREATE INDEX page_hit_url ON page_hits (url(128))
  • DROP INDEX IDX_2F81A41DB42D874D ON channel_url_trackables
  • DROP INDEX IDX_6DF94A56C028CEA2 ON point_lead_action_log
  • DROP INDEX IDX_C2A3BDBA71F7E88B ON point_lead_event_log
  • DROP INDEX IDX_B032FC2EBD5C7E60 ON sms_message_list_xref
  • DROP INDEX IDX_A506AFBE2298D193 ON stage_lead_action_log
  • DROP INDEX webhook_id_date ON webhook_queue
  • DROP INDEX IDX_45207A4A4CE1C902 ON monitoring_leads

if I try force those updates using php console doctrine:schema:update --force, I am getting the following errors:

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 ‘IDX_1AE3441319EB6921’; check that column/key exists

In Exception.php line 18:
SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP ‘IDX_1AE3441319EB6921’; check that column/key exists

In PDOConnection.php line 141:
SQLSTATE[42000]: Syntax error or access violation: 1091 Can’t DROP ‘IDX_1AE3441319EB6921’; check that column/key exists

Any thoughts on what I should be doing or how is should make sure the schema is up to date. I’m reluctant to make this upgrade live if it may be unstable.

Thanks in advance for any help you can offer.

2

I have seen this a lot. It happens, because client_id in the oauth2_user_client_xref table is different format then in the id in the oauth_client table. Somewhere the 2 values got messed up, one is 10 the other one 11 long. I usually use some dirty thricks to overcome this issue, I would love to see an elegant long lasting solution. @escopecz ?
This migration is between Mautic 2.16.5 and M3.

3

Thanks for the reply. Any tips on those dirty tricks? Just want to be sure everything is updated completely before switching over to the 4.4.5. Thanks!

4

Hi, again: I’m not sure it’s the right way to do it, but what I did is synched the field lenght so the error for the oauth2 tables would disappear. After that it ran properly for me.
Not sure if this is the right way.

5

Do you get the error, that that Index doesn’t exists?

6

I am also seeing this on trying to upgrade. I actually tested a fresh install of 4.4.5 and 4.4.6 and even fresh installs have the same issue.

In AbstractMySQLDriver.php line 128:

An exception occurred while executing ‘DROP INDEX IDX_DED9EA1819EB6921 ON m
auea_oauth2_user_client_xref’:

Can’t DROP INDEX IDX_DED9EA1819EB6921; check that it exists

In StatementError.php line 21:

Can’t DROP INDEX IDX_DED9EA1819EB6921; check that it exists

7

I have seen the exact same outstanding queries report when running Mautic updates from 4.3.0 onwards. I tested this on a local install last night. Although the updates always complete - all migrations are completed - the same outstanding queries are listed when running:

console doctrine:schema:update --dump-sql

Can we therefore assume that it is the schema report mechanism that is buggy and that the Mautic upgrades have completed successfully? Running the queries manually through phpMyAdmin proves that the queries reported are false.

If this is the case then can we please acknowledge this bug so people are not wasting their time trying to fix a non-existent fault with their database every time they update?

2 Likes
8

Any update on the “right way” to handle these table references?

And FYI, I have similar problem on a 4.4.13.
I checked that the
client_id in oauth2_user_client_xref matched
the id in oauth2_clients.

And they are of the same type and length.

DESCRIBE oauth2_clients;
±--------------------±-----------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±--------------------±-----------------±-----±----±--------±---------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |

DESCRIBE oauth2_user_client_xref;
±----------±-----------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±----------±-----------------±-----±----±--------±------+
| client_id | int(10) unsigned | NO | PRI | NULL | |

9

Continued:

Assume the out of sync mapping is related to my issue:

Command:
php bin/console doctrine:schema:validate

Output:
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.

10

I had same “can’t drop index” errors and my “mautic expert” hosting company aslo could NOT solve it.. even with all ChatGPT + Claude AI help, and i also did all commands as they told and searched all forums, etc. and the “can’t drop index” came up even after full composer install, and even on full empty and new database on mautic 6.0.5 with very new almost 100% compozer fresh install also. So please solve that because it seems that bug is comiing up again and again in different forms (indexes) with every upgrade when someone migrated through from an earlier non compozer mautic.. :frowning: (for chema update)

And that problem also for doctrine:schema:validate:

”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.

“
So and if I delete the indexes manually from SQL it creates them again, or want to delete again, and if it needs them because cannot delete but wants to delete, and i create them for doctrine to be able to delete the index it says it cannot create because it is duplicate or similar 
 so every time i do manually in SQL what it want to do it wants then to do the opposite, or drags in a loop, like the primary key deleting or ignoring globally also do NOT solves it, and it is a NIGHTMARE!

SO it is SURELY not “just a single migration file” or SURELY NOT “just an update or just some version specific error” it is a HUGE core BUG, maybe in doctrine itself i think now
 so maybe not just mautic specific, and i saw this came up again and again since mautic 2 to mautic 6 , with my 6.0.5 also, so it is surely not just “1 bad written files” for some “bad indexes” but the whole index generating mechanism itself, or the core of the whole index generating / checking algoritm i am now sure of this!!! so please ask the doctrine developers themselfes also to fix that, because this was also not just on mautic forums! the “can’t drop index” error!!! Thanks! :))