maria-discuss team mailing list archive

[Erik Cederstrand <erik@xxxxxxxxxxxxxx>]

Thanks!

I couldn't find the mysql_fix_privilege_tables.sql installed anywhere on my system, so I ended up patching mysql_upgrade to print the SQL commands it's executing. There are ca. 200 commands executed at once, so it took a while to pinpoint the failing statement. It would be cool if triple-verbose mysql_upgrade could print the exact statement that matches the error that it prints in https://github.com/MariaDB/server/blob/9a7d96e8326377b92406c09fdcb8bd60c45f901c/client/mysql_upgrade.c#L1063

Anyway, the failing query is:

alter table innodb_index_stats modify last_update timestamp not null default current_timestamp on update current_timestamp, modify table_name varchar(199);

which throws "Specified key was too long; max key length is 1000 bytes".

Currently, my innodb_index_stats table is defined as:

CREATE TABLE `innodb_index_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `index_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `stat_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `stat_value` bigint(20) unsigned NOT NULL,
  `sample_size` bigint(20) unsigned DEFAULT NULL,
  `stat_description` varchar(1024) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`,`index_name`,`stat_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;

Does that ring a bell?

Kind regards,
Erik

> Den 16. jul. 2019 kl. 21.13 skrev Sergei Golubchik <serg@xxxxxxxxxxx>:
> 
> Hi, Erik!
> 
> you can run mysql_fix_privilege_tables.sql manually. Like with
> mysql -uroot -p -vvv < mysql_fix_privilege_tables.sql
> 
> Presuming FreeBSD installs this file somewhere. If it doesn't, you can
> extract it from mysql_upgrade with strings, I suppose.
> 
> On Jul 16, Erik Cederstrand wrote:
>> Hi,
>> 
>> I'm trying to upgrade a MariaDB 10.2 server to 10.4.6 on FreeBSD.
>> 
>> When running the mysql_update command, it dies with:
>> 
>> $ mysql_upgrade 
>> Phase 1/7: Checking and upgrading mysql database
>> Processing databases
>> mysql
> ...
>> mysql.transaction_registry                         OK
>> Phase 2/7: Installing used storage engines... Skipped
>> Phase 3/7: Fixing views
>> mysql.user                                         OK
>> Phase 4/7: Running 'mysql_fix_privilege_tables'
>> ERROR 1071 (42000) at line 437: Specified key was too long; max key length is 1000 bytes
>> FATAL ERROR: Upgrade failed
>> 
>> Adding verbose option does not give more hints, and the failing SQL
>> statement doesn't seem to be logged with full query logging turned on.
>> 
>> How do I debug this? I can't even see which table or column it's
>> complaining about. I don't remember fiddling with collation or
>> character sets on system tables.
>> 
>> Kind regards,
>> Erik
> 
> Regards,
> Sergei
> VP of MariaDB Server Engineering
> and security@xxxxxxxxxxx
> 
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp