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?