0

I have been given a database dump that I am trying to rebuild. When I import I get the following error:

Too many key parts specified; max 16 parts allowed

There are indeed more than 16 key parts in the dump.

I have read and understand the limitations of InnoDB, what I don't understand is how the original database was able to create a key with more than 16 parts.

Here is the relevent part of the dump:

CREATE TABLE `v1_view` (    
  `view_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `view_date` date NOT NULL,
  `account_id` int(9) unsigned NOT NULL DEFAULT '0',
  ....
  PRIMARY KEY (`view_id`),
  UNIQUE KEY `viewUnique` (`view_date`,`account_id`,`player_id`,`video_id`,`order_id`,`orderlist_id`,`feed_id`,`domain_id`,`location_id`,`city_id`,`region_id`,`dma_id`,`country_id`,`continent_id`,`browser_id`,`platform_id`,`os_id`,`size_id`,`autoplay`,`volume`,`pacing`,`ad_spend`,`ad_revenue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A couple of thoughts I had:

  • It was created with an older version of MySQL that allowed more key parts
  • It is using a different ENGINE even though the dump says InnoDB

Not sure how valid either of those are. I don't have direct access to the original DB but can probably get more info if needed.

How is it that the original database has a unique key with so many parts? How do I replicate this database?

1

Somehow this was a VIEW that turned into a TABLE?

I checked; the max of 16 columns per index has existed for a 'long time' and applies to 'all' engines. Reference. I can't explain your specific question.

Also, I am thinking there are worse problems than the failing UNIQUE.

It is unreasonable to normalize something like "location", while also including all the fields in this table:

location_id,city_id,region_id,dma_id,country_id,continent_id

That is, there should be a different table with all of those columns, but only location_id in this table.

I recommend you manually remove the UNIQUE line, load the data, then clean up messes such as location_id.

  • Thanks Rick, I guess I could have dug in a little further but it seems like this key is an anomaly. I agree the structure is odd as well. There are lots of redundancies like the location. One step at a time cleaning this up I guess :) – Shawn Northrop Nov 17 '16 at 16:52
  • Also not sure what a VIEW is per se but I think it was just a TABLE that happened to be called v1_view and not necessarily a VIEW – Shawn Northrop Nov 17 '16 at 16:54
0

Four(4) years ago, someone came across the same issue but with 64 parts

I tried out the test script from Limit on Number of Indexes on MySQL Table on my Mac running MySQL 5.7.12. It reached the limit of 64.

It is possible that compound indexes make this problem worse, reaching imposed limits.

The unique index you see has 23 columns. Ugh !!!

What was suggested at first by RickJames is all you can do for now: Change UNIQUE KEY in the script to just KEY. You can reload from there.

Perhaps you could make dma_id unique worldwide. Then, ditch country_id,continent_id from the index. Be careful with cities which sit in multiple DMAs (because of multiple ZipCodes)

  • The 64 is the limit on the number of indexes per table. The 16 that the question is about is the limit on the number of columns in an index. – ypercubeᵀᴹ Nov 17 '16 at 22:10

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.