3

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.

0

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.

0

MySQL supports indexing and re-indexing data automatically for a full-text search enabled column. In MySQL, we can define FULL-TEXT for column have data type TEXT, VARCHAR, CHAR for MySql version 5.6 or later.

Above query is running while customer_grid indexing. During this indexing, Magento creates a table customer_grid_flat and apply the FULLTEXT indexes to the TEXT, VARCHAR, CHAR columns. So in MySQL version 5.6 if the number of column increases i.e. more than 16 it would throw an error. Below is the FULLTEXT section in the query

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`)

We will resolve this in the following way

  1. Set is_used_in_grid, is_filterable_in_grid, is_searchable_in_grid to 0 in the customer_eav_attribute table like billing_room_no to minimize the attribute showing in customer grid.
  2. Update your MYSQL version to 5.7 or later or Mria DB to allow FULL-TEXT indexes more than 16.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.