[Maria-discuss] ERROR 1071 with mysql_upgrade
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.column_stats OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.global_priv OK mysql.gtid_slave_pos OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic 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 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
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@mariadb.org
Am 16.07.19 um 21:13 schrieb Sergei Golubchik:
you can run mysql_fix_privilege_tables.sql manually. Like with mysql -uroot -p -vvv < mysql_fix_privilege_tables.sql
how would that solve the issue of a obviously incompatible change in 10.4?
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.
Hi, Reindl! On Jul 16, Reindl Harald wrote:
Am 16.07.19 um 21:13 schrieb Sergei Golubchik:
you can run mysql_fix_privilege_tables.sql manually. Like with mysql -uroot -p -vvv < mysql_fix_privilege_tables.sql
how would that solve the issue of a obviously incompatible change in 10.4?
The question in the email was "How do I debug this?" and I tried to answer it. My reply might help to find the statement that causes the error, aborting the upgrade. Then we could look at why it fails, whether it's something in 10.4, in mysql_upgrade, or some local change that the user did, or may be something caused by FreeBSD patches.
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.
Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org
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/9a7d96e8326377b92406c09fdcb8bd60c45f9... 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@mariadb.org>:
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@mariadb.org
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Le 17 juil. 2019 à 11:22, Erik Cederstrand <erik@cederstrand.dk> a écrit :
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/9a7d96e8326377b92406c09fdcb8bd60c45f9...
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?
Hi Erik! It seems this table should be in InnoDB format, not MyISAM (same for innodb_table_stats). HTH, Jocelyn
Thanks, Jocelyn!
Den 17. jul. 2019 kl. 11.28 skrev jocelyn fournier <jocelyn.fournier@gmail.com>: It seems this table should be in InnoDB format, not MyISAM (same for innodb_table_stats).
I tried altering the engine to InnoDB but was not allowed. Hmm. Then I decided to just drop the table to have mysql_upgrade create it again. Now mysql_upgrade complains that the tablespace for that table exists and I need to drop it. But: MariaDB [mysql]> ALTER TABLE innodb_index_stats DROP TABLESPACE; ERROR 1146 (42S02): Table 'mysql.innodb_index_stats' doesn't exist [mysql]> DROP TABLE innodb_index_stats; ERROR 1051 (42S02): Unknown table 'mysql.innodb_index_stats' MariaDB [mysql]> CREATE TABLE `innodb_index_stats` ( -> `database_name` varchar(64) COLLATE utf8_bin NOT NULL, -> `table_name` varchar(199) 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=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; ERROR 1050 (42S01): Table '`mysql`.`innodb_index_stats`' already exists I tried stopping the server, deleting all files in /var/db/mysql/mysql/innodb_index_stats.* and restarting, but I still see the same behaviour above. Kind regards, Erik
Den 17. jul. 2019 kl. 11.48 skrev Erik Cederstrand <erik@cederstrand.dk>:
Thanks, Jocelyn!
Den 17. jul. 2019 kl. 11.28 skrev jocelyn fournier <jocelyn.fournier@gmail.com>: It seems this table should be in InnoDB format, not MyISAM (same for innodb_table_stats).
I tried altering the engine to InnoDB but was not allowed. Hmm.
Then I decided to just drop the table to have mysql_upgrade create it again. Now mysql_upgrade complains that the tablespace for that table exists and I need to drop it. But:
MariaDB [mysql]> ALTER TABLE innodb_index_stats DROP TABLESPACE; ERROR 1146 (42S02): Table 'mysql.innodb_index_stats' doesn't exist [mysql]> DROP TABLE innodb_index_stats; ERROR 1051 (42S02): Unknown table 'mysql.innodb_index_stats' MariaDB [mysql]> CREATE TABLE `innodb_index_stats` ( -> `database_name` varchar(64) COLLATE utf8_bin NOT NULL, -> `table_name` varchar(199) 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=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0; ERROR 1050 (42S01): Table '`mysql`.`innodb_index_stats`' already exists
I tried stopping the server, deleting all files in /var/db/mysql/mysql/innodb_index_stats.* and restarting, but I still see the same behaviour above.
Answering my own question: I ended up solving my problem with the following: * stop the server * copy in /var/db/mysql/mysql/innodb_* files from a different 10.4 server where these tables were already InnoDB * start the server * drop the tables * re-run mysql_upgrade Thanks for the hints, everyone! Kind regards, Erik
participants (4)
-
Erik Cederstrand -
jocelyn fournier -
Reindl Harald -
Sergei Golubchik