Mrthdrb

I have created new custom customer address attribute in M2 community edition 2.1.5 using sql setup.

public function install(ModuleDataSetupInterface $setup, ModuleContextInterface $context)


 /** @var CustomerSetup $customerSetup */
 $customerSetup = $this->customerSetupFactory->create(['setup' => $setup]);

 //install customer group attributes
 $setup->startSetup();
 if (version_compare($context->getVersion(), '1.0.1', '<')) 
 // insert default customer groups
 $setup->getConnection()->insert(
 $setup->getTable('customer_group'),
 ['customer_group_id' => 4, 'customer_group_code' => 'University / Research Lab Customers', 'tax_class_id' => 3]
 );
 $setup->getConnection()->insert(
 $setup->getTable('customer_group'),
 ['customer_group_id' => 5, 'customer_group_code' => 'Hospitals / Clinical Centers Customers', 'tax_class_id' => 3]
 );
 $setup->getConnection()->insert(
 $setup->getTable('customer_group'),
 ['customer_group_id' => 6, 'customer_group_code' => 'GMP – Pharma / Biotech Customers', 'tax_class_id' => 3]
 );

 $customerEntity = $customerSetup->getEavConfig()->getEntityType('customer');
 $attributeSetId = $customerEntity->getDefaultAttributeSetId();

 /** @var $attributeSet AttributeSet */
 $attributeSet = $this->attributeSetFactory->create();
 $attributeGroupId = $attributeSet->getDefaultGroupId($attributeSetId);


 $attributesInfo = [
 'contact_number' => [
 'label' => 'Contact Number',
 'type' => 'varchar',
 'input' => 'text',
 'input_validation' => 'numeric',
 'min_text_length' => '0',
 'max_text_length' => '10',
 'is_used_in_grid' => true,
 'is_visible_in_grid' => false,
 'is_filterable_in_grid' => true,
 'is_searchable_in_grid' => true,
 'is_visible' => true,
 'is_required' => false,
 'sort_order' => '999',
 'system' => 0,
 ],
 'customer_company' => [
 'label' => 'Customer Company',
 'type' => 'varchar',
 'input' => 'text',
 'is_used_in_grid' => true,
 'is_visible_in_grid' => false,
 'is_filterable_in_grid' => true,
 'is_searchable_in_grid' => true,
 'is_visible' => true,
 'is_required' => false,
 'sort_order' => '1000',
 'system' => 0,
 ]
 ];

 foreach ($attributesInfo as $attributeCode => $attributeParams) 
 $customerSetup->addAttribute(Customer::ENTITY, $attributeCode, $attributeParams);
 $attribute = $customerSetup->getEavConfig()->getAttribute(Customer::ENTITY, $attributeCode)
 ->addData([
 'attribute_set_id' => $attributeSetId,
 'attribute_group_id' => $attributeGroupId,
 'used_in_forms' => ['customer_account_create','customer_account_edit','adminhtml_customer']
 ]);
 $attribute->save();
 
 unset($attributesInfo);
 
 //create customer address attribute
 $attributesInfo = [
 'room_no' => [
 'label' => 'Room No',
 'type' => 'varchar',
 'input' => 'text',
 'is_used_in_grid' => true,
 'is_visible_in_grid' => false,
 'is_filterable_in_grid' => true,
 'is_searchable_in_grid' => true,
 'is_user_defined' => true,
 'is_visible' => true,
 'is_required' => false,
 'sort_order' => '999',
 'system' => 0,
 ],
 'building' => [
 'label' => 'Building',
 'type' => 'varchar',
 'input' => 'text',
 'is_used_in_grid' => true,
 'is_visible_in_grid' => false,
 'is_filterable_in_grid' => true,
 'is_searchable_in_grid' => true,
 'is_visible' => true,
 'is_required' => false,
 'is_user_defined' => true,
 'sort_order' => '1000',
 'system' => 0,
 ]
 ];

 foreach ($attributesInfo as $attributeCode => $attributeParams) 
 $customerSetup->addAttribute('customer_address', $attributeCode, $attributeParams);
 
 $setup->endSetup();

}

The customer address attribute is created, I have confirmed in database. But I am getting error after running

php bin/magento indexer:reindex

Below is the error:

 Customer Grid indexer process unknown error:
SQLSTATE[42000]: Syntax error or access violation: 1070 Too many key parts specified; max 16 parts allowed, 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' ,
 `contact_number` varchar(255) NULL COMMENT 'Contact_number' ,
 `customer_company` varchar(255) NULL COMMENT 'Customer_company' ,
 `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' ,
 `billing_building` varchar(255) NULL COMMENT 'Billing_building' ,
 `billing_room_no` varchar(255) NULL COMMENT 'Billing_room_no' ,
 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_5DD806E7D7383F9D4376BB41BEB968FC` (`name`, `email`, `created_in`, `taxvat`, `contact_number`, `customer_company`, `shipping_full`, `billing_full`, `billing_firstname`, `billing_lastname`, `billing_telephone`, `billing_postcode`, `billing_region`, `billing_city`, `billing_fax`, `billing_company`, `billing_building`, `billing_room_no`)
) COMMENT='customer_grid_flat' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci

In the database I tried by changing the indexer status from 'invalid' to 'valid' then also same error is coming.
Plz suggest me any solution.

Set is_used_in_grid, is_filterable_in_grid, is_searchable_in_grid to false

This is because MSSQL only allowed 16 fields that can be set as true. You have to choose which one you want to be shown in customer grid.