Skip to main content
added 67 characters in body
Source Link
  • Deleting the module altogether from the Magento installation
  • Deleting the module altogether from the Magento installation
Source Link

Magento 2 DataPatch has created persistent SQL error

Thanks ahead of time for your help in understanding some of the mechanics behind the problem I am experiencing.

Original Cause

I created a DataPatch which is pretty much a copy/paste from the AddCustomerExampleAttribute class in the following page of the Magento 2 developer documentation.

https://devdocs.magento.com/guides/v2.4/extension-dev-guide/attributes.html

However, I made a mistake. I included attribute options I shouldn't have. Now, I'm not sure exactly which attribute option caused this issue (perhaps 'searchable'?), but I ended up getting the following error:

SQLSTATE[HY000]: General error: 1283 Column 'my_added_custom_id' cannot be part of FULLTEXT index, query was: CREATE TABLE IF NOT EXISTS `customer_grid_flat` (
  `entity_id` int UNSIGNED NOT NULL COMMENT 'Entity ID' ,
  `name` text NULL COMMENT 'Name' ,
  `email` varchar(255) NULL COMMENT 'Email' ,
  `group_id` int NULL COMMENT 'Group_id' ,
  `created_at` timestamp NULL default NULL COMMENT 'Created_at' ,
  `website_id` int NULL COMMENT 'Website_id' ,
  `confirmation` varchar(255) NULL COMMENT 'Confirmation' ,
  `created_in` text NULL COMMENT 'Created_in' ,
  `dob` date NULL COMMENT 'Dob' ,
  `gender` int NULL COMMENT 'Gender' ,
  `taxvat` varchar(255) NULL COMMENT 'Taxvat' ,
  `lock_expires` timestamp NULL default NULL COMMENT 'Lock_expires' ,
  `my_added_custom_id` int NULL COMMENT 'my_added_custom_id' ,
  `shipping_full` text NULL COMMENT 'Shipping_full' ,
  `billing_full` text NULL COMMENT 'Billing_full' ,
  `billing_firstname` varchar(255) NULL COMMENT 'Billing_firstname' ,
  `billing_lastname` varchar(255) NULL COMMENT 'Billing_lastname' ,
  `billing_telephone` varchar(255) NULL COMMENT 'Billing_telephone' ,
  `billing_postcode` varchar(255) NULL COMMENT 'Billing_postcode' ,
  `billing_country_id` varchar(255) NULL COMMENT 'Billing_country_id' ,
  `billing_region` varchar(255) NULL COMMENT 'Billing_region' ,
  `billing_region_id` int NULL COMMENT 'Billing_region_id' ,
  `billing_street` varchar(255) NULL COMMENT 'Billing_street' ,
  `billing_city` varchar(255) NULL COMMENT 'Billing_city' ,
  `billing_fax` varchar(255) NULL COMMENT 'Billing_fax' ,
  `billing_vat_id` varchar(255) NULL COMMENT 'Billing_vat_id' ,
  `billing_company` varchar(255) NULL COMMENT 'Billing_company' ,
  PRIMARY KEY (`entity_id`),
  INDEX `CUSTOMER_GRID_FLAT_GROUP_ID` (`group_id`),
  INDEX `CUSTOMER_GRID_FLAT_CREATED_AT` (`created_at`),
  INDEX `CUSTOMER_GRID_FLAT_WEBSITE_ID` (`website_id`),
  INDEX `CUSTOMER_GRID_FLAT_CONFIRMATION` (`confirmation`),
  INDEX `CUSTOMER_GRID_FLAT_DOB` (`dob`),
  INDEX `CUSTOMER_GRID_FLAT_GENDER` (`gender`),
  INDEX `CUSTOMER_GRID_FLAT_BILLING_COUNTRY_ID` (`billing_country_id`),
  FULLTEXT `FTI_4A6003713F01926B1BCFC4AAD4625182` (`name`, `email`, `created_in`, `taxvat`, `my_added_custom_id`, `shipping_full`, `billing_full`, `billing_firstname`, `billing_lastname`, `billing_telephone`, `billing_postcode`, `billing_region`, `billing_city`, `billing_fax`, `billing_company`)
) COMMENT='customer_grid_flat' ENGINE=innodb charset=utf8 COLLATE=utf8_general_ci

Obviously a valid SQL error. An int column shouldn't be part of a fulltext index. Easy fix, change the attribute options in the DataPatch.

Current Roadblock

I made changes to the DataPatch, but the error message persists. Crucially here, I changed the name of the column / attribute, but the error message still displays the original (now old) column name. It's still trying run the same (now old) SQL for the DataPatch.

The same error message is shown if I try either of the following two commands

php bin/magento setup:upgrade

php bin/magento indexer:reindex customer_grid

So, it appears that somewhere Magento 2 is holding on to the SQL for the original DataPatch.

Questions

Is there a queue of unapplied queries that Magento keeps somewhere? I tried searching the Magento installation codebase, but did not find my old attribute column name.

How can this old query be removed so that I can retry a new DataPatch with different attribute options?

Alternatively, is there a way I can override the existing DataPatch so new SQL is generated?

Attempts to Resolve

I have tried each of the following

  • Clearing the cache

php bin/magento cache:clean

  • Reset the index

php bin/magento indexer:reset customer_grid

  • Disabling the module (the problem occurs with the module disabled)

php bin/magento module:disable MyOrganization_MyModule

  • Uninstalling the module (using the --non-composer because the module is in app/code)

php bin/magento module:uninstall --non-composer MyOrganization_MyModule

Wrap Up

Thanks again for taking a look. I've been searching and seeing plenty of answers that pertain to getting the SQL to succeed, but I don't need or want this SQL to succeed. It was the result of a mistake.