Too many keys specified

[choules]

We have just upgraded our MunkiReport instance and ran into problems migrating the database tables for this module.

Migrating: 2021_03_31_000001_power_big_sur_columns

In Connection.php line 664:
                                                                                                                                                                        
  SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed (SQL: alter table `power` add index `power_charging_voltage_ind  
  ex`(`charging_voltage`))                                                                                                                                              
                                                                                                                                                                        

In Exception.php line 18:
                                                                                                        
  SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed  
                                                                                                        

In PDOStatement.php line 117:
                                                                                                        
  SQLSTATE[42000]: Syntax error or access violation: 1069 Too many keys specified; max 64 keys allowed  
                                                                                                        

I have checked on the migrations and the current database scheme. We are actually creating an index on every column, and – at least for our MySQL version in use – this leads to more than the default maximum of 64 indexes per table.

After manually deleting some of the obviously unimportant indexes, the migration could be processed correctly.

This seems to be the same issue which was addressed with commit 962abfa. Obviously we were affected by this issue one migration earlier than other users. I would recommend the following:

• Update the migration 2021_03_31_000001_power_big_sur_columns.php and remove any indexes
• Add a new migration which removes most of the indexes

To decide, what the relevant indexes are, we should decide which of the columns in the Listing "Power Report" should be sortable, because having an index in those columns would speed up the generation of these listings.

If you agree on my suggestions, I would be glad to provide a corresponding pull request.

BTW: I highly recommend @ksassnowski's talk on Database Indexing at the Laracon EU 2018:
https://www.youtube.com/watch?v=HubezKbFL7E