Ask Your Question
0

mariadb: ERROR 1071 Specified key was too long; max key length is 1000 bytes

asked 2019-02-27 17:50:00 -0600

mariadb-10.3.12-10.fc29.x86_64

I do not know anything about the database, but it is being used under Mythtv. There has been a warning about needing to upgrade the database for a while but when I attempt to run the command to do that it errors out. I have done the normal search for trying to find a solution but have failed to find one. I did submit a bug report to Mythtv but it has had no action. The maria site also does not seem to provide any support.

mysql_upgrade -v -p
Enter password: 
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
mysql.user                                         OK
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Processing databases
information_schema
mysql
mythconverg
performance_schema
roundcube
test
Phase 4/7: Running 'mysql_fix_privilege_tables'
ERROR 1071 (42000) at line 643: Specified key was too long; max key length is 1000 bytes
FATAL ERROR: Upgrade failed
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-02-28 10:04:48 -0600

Simple solution: make the index smaller.

Example:

Schlüsselname   Typ Unique  Gepackt Spalte  Kardinalität    Kollation   Null    Kommentar
domain  BTREE   Ja  Nein    domain (100)    380 A   Nein    
                                            webserver           380 A   Nein

You see the (100) behind "domain" thats the length of data from the field, that should be used to comparison for this index.

The sum of all fields in one index must be below 1000 chars. Your index is bigger than 1000 chars atm.

Go to your database mangement software, open the table structure and edit the length of all fields in your index to a sum less than 1000 safe it.

restart the upgrade command. Solved.

edit flag offensive delete link more

Comments

The answer seems to have missed the very first sentence in the description of the issue. I have no knowledge or background with the maria database or any other database so I haven't a clue about how to implement the above fix. I do know that there are 113 tables in the database. I managed to dig out how to connect to the database and show the tables.

I might dig out how to accomplish the fix above but it would take a pretty long time. I also have the concern that since I know nothing about this could it end up breaking the application?

dhighley gravatar imagedhighley ( 2019-03-06 11:57:57 -0600 )edit

Ok, do you know PHPMYAdmin ? Thats a management software for mysql databases via a webserver. If you prefer a GUI and a standalone programm you can use this: https://dev.mysql.com/downloads/workb...

Set one of this tools up for your database ( servername, dbname, dbuser and dbpassword needed ) and navigate to your table it complaining about. Get to "Structure" .. edit the index and make one fieldlength smaller.

rdtcustomercare gravatar imagerdtcustomercare ( 2019-03-12 09:36:20 -0600 )edit

Thank you for the help. I downloaded the rpm and installed it but it crashed when I tried running it. As far as knowing which database has the issue the error diagnostics does not provide the information. It seems to refer to a line in a file that does not appear to exist.

dhighley gravatar imagedhighley ( 2019-03-23 21:57:38 -0600 )edit

Can you post the message?

rdtcustomercare gravatar imagerdtcustomercare ( 2019-03-24 17:45:25 -0600 )edit

I'm assuming your asking for the diagnostic message which is posted above in the original posting.

dhighley gravatar imagedhighley ( 2019-04-02 16:05:48 -0600 )edit

Question Tools

1 follower

Stats

Asked: 2019-02-27 17:47:38 -0600

Seen: 161 times

Last updated: Feb 28 '19