Magento 2 DataPatch has created persistent SQL error

Question

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

• Deleting the module altogether from the Magento installation

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.

Answer 1

In your database there is table “patch_list”. Find your module patch in that table and drop that row. Then when you run setup:upgrade command magento will apply the patch again. If you use the same attribute but with the correct settings I think it will overwrite your initial mistake and solve the issue.

Unlike the declarative schema approach, patches will only be applied once. A list of applied patches is stored in the patch_list database table. An unapplied patch will be applied when running the setup:upgrade from the Magento CLI.

https://devdocs.magento.com/guides/v2.4/extension-dev-guide/declarative-schema/data-patches.html

Answer 2

I was able to fix my own issue. The key was understanding a bit of what is happening when creating an attribute in Magento's EAV system.

When making the following call (from the Magento 2 developer documentation), the system will populate Magento's EAV tables.

$customerSetup->addAttribute(Customer::ENTITY, 'attribute_code', [
  // Attribute options (list of options can be found below)
]);

It is from the data in these tables that Magento attempts to create the customer_grid_flat table. Specifically, I was able to find the configuration for the attribute options in the following tables:

eav_attribute

customer_eav_attribute

I manually changed the customer_eav_attribute table to modify the values for the columns is_used_in_grid is_visible_in_grid is_filterable_in_grid and is_searchable_in_grid from a 1 to 0. One of these configuration option changes fixed the issue I was experiencing.

---

Edit: It may also be possible create a new patch that will remove the previously created attribute (see below). This has worked for me after fixing my error using the database method above. I did not try this while my error was occurring.

$customerSetup->removeAttribute(Customer::ENTITY, 'my_added_custom_id');