The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-16. For the latest, interactive version please visit https://mariadb.com/kb/.

Index with up to 24 columns

Hello there,

A couple years ago (like 2012), we switched from MySQL to MariaDb 5.5 because MariaDb could handle indices with more than 16 columns, which MySQL could not. Our production has always ran well, but today MariaDb 5.5 is getting old, and won't be supported anymore in a few months.

As a consequence, we'd like to upgrade to MariaDb's latest version, so we started to upgrade from 5.5 to 10.0, then from 10.0 to 10.1, but we failed at upgrading from 10.1 to 10.2 (and therefore 10.3) because the following error occurs: "Table 'yyy' uses an extension that doesn't exist in this MariaDB version".

I must precise that table 'yyy' has the same schema as table 'xxx' (which has an UNIQUE index of up to 24 columns), except some of its columns are VARCHAR(255) instead of VARCHAR(120). Both tables are MyISAM.

We didn't get any issue with this on MariaDb 10.0 and 10.1, the upgrade was transparent until 10.2. But we couldn't get any further information online about this error.

Any ideas?

Thank you much, Ben

Answer Answered by Ian Gilfillan in this comment.

How did you get the second table working on the older versions? Running the above on MariaDB 5.5 in my environment returns 

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

If this is related to your error, you can overcome this by using a portion of a column for an index, for example UNIQUE KEY `unique_stats` ... `tracker_int`(10), But am still interested in more details on your setup as to how it worked in the first place.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.