Flite Careers

Avoiding MySQL ERROR 1069 by Explicitly Naming Indexes

Since I recently wrote about both MySQL error 1071 and error 1070 I decided to continue the pattern with a quick note on MySQL error 1069. In case you’ve never seen it before, this is MySQL error 1069:

1

ERROR 1069 (42000): Too many keys specified; max 64 keys allowed

I can’t think of a valid use case that requires more than 64 indexes on a MySQL table, but it’s possible to get this error by inadvertantly adding lots of duplicate indexes to a table. This can happen if you don’t explicitly name your indexes.

Read on for examples…

If I try to add the same named index twice, the second time it fails, so I do not end up with a duplicate index:

1
2
3
4
5
6

mysql> alter table sakila.film add index release_year (release_year);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sakila.film add index release_year (release_year);
ERROR 1061 (42000): Duplicate key name 'release_year'

If I do the same thing without naming the index, then a duplicate index is created and I get a warning, not an error:

1
2
3
4
5
6
7
8
9

mysql> alter table sakila.film add index (release_year);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sakila.film add index (release_year);
Query OK, 0 rows affected, 1 warning (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 1

Note (Code 1831): Duplicate index 'release_year_2' defined on the table 'sakila.film'. This is deprecated and will be disallowed in a future release.

Now imagine an automated script that tries to add the same index over and over again, and it doesn’t take long to get error 1069. For the sake of brevity I will simulate this using common_schema QueryScript:

1
2
3
4
5
6
7

mysql> call common_schema.run("
    ">   foreach($i: 1:100)
    ">   {
    ">     alter table sakila.film add index (release_year);
    ">   }
    "> ");
ERROR 1069 (42000): Too many keys specified; max 64 keys allowed

The solution for this problem is simple: provide an explicit name for every MySQL index you create.

MySQL
Comments
Back to Top

Posted by Ike Walker

Comments