Magento 2: setup:upgrade error with customer_grid_flat index

Question

I tried to run setup:upgrade command from Magento source but it's always get error:

SQLSTATE[42000]: Syntax error or access violation: 1170 BLOB/TEXT column 'created_in' used in key specification without a key length, 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' ,
  `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_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_CONFIRMATION` (`confirmation`),
  INDEX `CUSTOMER_GRID_FLAT_CREATED_IN` (`created_in`),
  INDEX `CUSTOMER_GRID_FLAT_DOB` (`dob`),
  INDEX `CUSTOMER_GRID_FLAT_GENDER` (`gender`),
  INDEX `CUSTOMER_GRID_FLAT_LOCK_EXPIRES` (`lock_expires`),
  INDEX `CUSTOMER_GRID_FLAT_BILLING_COUNTRY_ID` (`billing_country_id`),
  FULLTEXT `FTI_AC9FBFAE9FBD3A0B02DBA986349612A6` (`name`, `email`, `taxvat`, `shipping_full`, `billing_full`, `billing_firstname`, `billing_lastname`, `billing_telephone`, `billing_postcode`, `billing_region`, `billing_street`, `billing_city`, `billing_fax`, `billing_vat_id`, `billing_company`)
) COMMENT='customer_grid_flat' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci

I tried to create this table manually from my PHPMyAdmin, change created_in from text to varchar but it still get this error. After research some post in here, I tried updated from customer_eav_attribute table with is_used_in_grid, is_visible_in_grid, is_filterable_in_grid, is_searchable_in_grid to 0. But it's still not work.

Can you give me a solution to fix this?

Thank you very much!

Answer 1

try below way,

try to change is_filterable_in_grid to be value 0

Answer 2

For anyone who might face the same issue in the future ,if the environment is not production you can delete the attribute/column and readd it ,for me it resolved the issue