This knowledge base article describes an issue where Strategy report generates a SQL to create an index, for an intermediate SQL pass, on 17+ columns in MySQL which fails the execution with following error: Too many key parts specified; max 16 parts allowed.
SYMPTOM:
Strategy report generates a SQL to create an index, for an intermediate SQL pass, on 17+ columns in MySQL which fails the execution with following error: Too many key parts specified; max 16 parts allowed.
STEPS TO REPRODUCE:
1. The data source used must be MySQL.
2. Create a report with 17+ attributes and metrics such that it generates intermediate SQL pass. For ex:
create table TEMP1
(Col1 INT, Col2 INT, Col3 INT, Col4 INT, Col5 INT, Col6 INT, Col7 INT, Col8 INT, Col9 INT, Col10 INT, Col11 INT, Col12 INT, Col13 INT, Col14 INT, Col15 INT, Col16 INT, Col17 INT, SALES FLOAT)
insert into TEMP1 ...
create index IDX_TEMP1 (Col1 INT, Col2 INT, Col3 INT, Col4 INT, Col5 INT, Col6 INT, Col7 INT, Col8 INT, Col9 INT, Col10 INT, Col11 INT, Col12 INT, Col13 INT, Col14 INT, Col15 INT, Col16 INT, Col17 INT)
3. When the report SQL is executed against the MySQL data source then it reports an error: Too many key parts specified; max 16 parts allowed.
CAUSE:
This is a known defect and is only evident when 17+ columns are used to build index. MySQL does not allow to build index on more than 16 columns.
ACTION/RESOLUTION:
This defect is addressed in Strategy 11.0 and higher where Strategy generates the index only upto 16 columns even if intermediate SQL pass contains more than 16 columns. This fix also requires that the Strategy Metadata is updated on 11.0 release.
table TEMP1
(Col1 INT, Col2 INT, Col3 INT, Col4 INT, Col5 INT, Col6 INT, Col7 INT, Col8 INT, Col9 INT, Col10 INT, Col11 INT, Col12 INT, Col13 INT, Col14 INT, Col15 INT, Col16 INT, Col17 INT, SALES FLOAT)
insert into TEMP1 ...
create index IDX_TEMP1 (Col1 INT, Col2 INT, Col3 INT, Col4 INT, Col5 INT, Col6 INT, Col7 INT, Col8 INT, Col9 INT, Col10 INT, Col11 INT, Col12 INT, Col13 INT, Col14 INT, Col15 INT, Col16 INT)
Strategy reference numbers for this technical note are KB442276 and DE94796.
