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:
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
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
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
The solution for this problem is simple: provide an explicit name for every MySQL index you create.