Magento2: customer_grid reindex error after added 3 custom attributes

[cs221313]

Preconditions

1. Magento 2.1.6, created 3 custom attributes in customer: organization, position, department

Steps to reproduce

1. run bin/magento indexer:reindex

Expected result

1. it should give me the success information

Actual result

1.Following is what I got

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' ,
department varchar(255) NULL COMMENT 'Department' ,
organization varchar(255) NULL COMMENT 'Organization' ,
position varchar(255) NULL COMMENT 'Position' ,
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' ,
shipping_full text NULL COMMENT 'Shipping_full' ,
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_A0DB95C08D547A63BEEEE719DCFB0A10 (name, email, created_in, taxvat, department, organization, position, billing_full, billing_firstname, billing_lastname, billing_telephone, billing_postcode, billing_region, billing_city, billing_fax, billing_company, shipping_full)
) COMMENT='customer_grid_flat' ENGINE=INNODB charset=utf8 COLLATE=utf8_general_ci

[cs221313]

This issue was solved. Just set is_used_in_grid as false.

[ajzele]

How is this a "solved" issue? I have the same situation, created 5 extra fields, which I want to use in customer grid. Setting is_used_in_grid as false means I don't get to use them at all.

[cs221313]

MSSQL only allowed 16 fields could be set as true. You have to choose which one you want to be shown in customer grid.

[tuyennn]

This issue was caused by your mysql version. I guess this is 5.6, try to upgrade to Mariadb or 5.7
Here the ref

[deb00t]

This issue was caused by your mysql version. I guess this is 5.6, try to upgrade to Mariadb or 5.7
Still causing problems in latest 5.7, fyi

[aschrammel]

Same issue here on MySQL5.7.25-1 (Debian)
I totally agree with @ajzele - this issue can't be resolved, as setting is_used_in_grid to false is no option here!

[m2-assistant[bot]]

Hi @engcom-backlog-nazar. Thank you for working on this issue.
In order to make sure that issue has enough information and ready for development, please read and check the following instruction: :point_down:

• [ ] 1. Verify that issue has all the required information. (Preconditions, Steps to reproduce, Expected result, Actual result).
  Details

  If the issue has a valid description, the label Issue: Format is valid will be added to the issue automatically. Please, edit issue description if needed, until label Issue: Format is valid appears.

• [ ] 2. Verify that issue has a meaningful description and provides enough information to reproduce the issue. If the report is valid, add Issue: Clear Description label to the issue by yourself.

• [ ] 3. Add Component: XXXXX label(s) to the ticket, indicating the components it may be related to.

• [ ] 4. Verify that the issue is reproducible on 2.3-develop branch

  Details

  - Add the comment @magento-engcom-team give me 2.3-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.3-develop branch, please, add the label Reproduced on 2.3.x.
  - If the issue is not reproducible, add your comment that issue is not reproducible and close the issue and _stop verification process here_!
  

• [ ] 5. Verify that the issue is reproducible on 2.2-develop branch.

  Details

  - Add the comment @magento-engcom-team give me 2.2-develop instance to deploy test instance on Magento infrastructure.
  - If the issue is reproducible on 2.2-develop branch, please add the label Reproduced on 2.2.x

• [ ] 6. Add label Issue: Confirmed once verification is complete.

• [ ] 7. Make sure that automatic system confirms that report has been added to the backlog.

[ghost]

HI @aschrammel i'm not able to reproduce following steps on 2.3-develop branch, seems like this issue not present here, please try upgrading.

[nelero]

Hi,
no it's not closed in 2.3.4.
16 fields limit