Linux

Could you please attach the schema dump of mysql database which you're trying to upgrade? No data, just the schema.
Please also attach your cnf file(s) from 10.3 and 10.4.

Same issue upgrading. I cannot publish the schema, but I can give access to Elena so she can see the issue
mysql_upgrade does not fix it, and it should
please contact me via email

Version: '10.4.12-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
2020-04-11 11:22:01 8 [ERROR] Incorrect definition of table mysql.proc: expected column 'sql_mode' at position 14 to have type set('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','IGNORE_BAD_TABLE_OPTIONS','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','POSTGRESQL','ORACLE','MSSQL','DB2','MAXDB','NO_KEY_OPTIONS','NO_TABLE_OPTIONS','NO_FIELD_OPTIONS','MYSQL323','MYSQL40','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','INVA
2020-04-11 11:22:02 11 [Warning] ./asterisk/c2.frm is inconsistent: engine typecode 44, engine name ROCKSDB (45)
2020-04-11 11:22:02 11 [Warning] ./asterisk/c3.frm is inconsistent: engine typecode 44, engine name ROCKSDB (45)
2020-04-11 11:22:02 11 [Warning] ./asterisk/c4.frm is inconsistent: engine typecode 44, engine name ROCKSDB (45)
2020-04-11 11:22:02 11 [Warning] ./asterisk/c5.frm is inconsistent: engine typecode 44, engine name ROCKSDB (45)
2020-04-11 11:22:02 11 [Warning] ./asterisk/c6.frm is inconsistent: engine typecode 44, engine name ROCKSDB (45)
2020-04-11 11:22:02 11 [Warning] ./asterisk/c7.frm is inconsistent: engine typecode 44, engine name ROCKSDB (45)
2020-04-11 11:22:02 11 [Warning] ./asterisk/c8.frm is inconsistent: engine typecode 44, engine name ROCKSDB (45)
2020-04-11 11:22:02 11 [Warning] ./asterisk/c9.frm is inconsistent: engine typecode 44, engine name ROCKSDB (45)
2020-04-11 11:22:02 12 [Warning] 'user' entry 'root@production3' ignored in --skip-name-resolve mode.
2020-04-11 11:22:02 12 [Warning] 'proxies_priv' entry '@% root@production3' ignored in --skip-name-resolve mode.

I need to emphasize that we need a path to upgrade, not dump and reload, which is impossible for very large databases and imposes a downtime that we cannot afford.

I cannot publish the schema, but I can give access to Elena so she can see the issue

Sorry, that's not the kind of case which we investigate on users' machines, at least not at this point.
I was only asking about mysql schema, not the entire instance. If your mysql database (structure, not data!) is intact, as it should be, it cannot be confidential, since it's the same for everyone. If it has been modified/customized, then that's why the upgrade fails.

I misunderstood, I thought you needed the SQL DDL of my user-database,
where the error is.
Of course, I can upload it. Please post here the command to extract it and
I will upload it right away.

On Mon, Apr 13, 2020 at 6:59 PM Elena Stepanova (Jira) <jira@mariadb.org>

I need somebody to post the exact command to dump the MySQL schema.
But the issue is evident in all machines upgraded, so I feel this to be unnecessary.

mysqldump -uroot -p --no-data --skip-lock-tables --databases mysql performance_schema > 1.dump

Along with the resulting 1.dump, please also paste or attach your configuration file(s).

If the problem were generic, we would have had thousands of reports about it. Currently this is the only one, so there must be something specific to your database or configuration (I suppose we can rule out environment, if it's reproducible for you on all machines).

Thanks for the dump.
While it doesn't match the story to the teeth, I think I've eventually managed to figure out most of what had happened.

You must have had this database from around 10.1 or so.
Some time then, in order to get rid of InnoDB, you altered a few system tables which had InnoDB for an engine. Importantly, you changed innodb_table_stats and innodb_index_stats tables to MyISAM.
In 10.2+, the tables' structures were modified, particularly one of columns was enlarged. For innodb_index_stats this column is a part of an index, and the new column length makes the index too long for MyISAM (it is still fine with InnoDB).
mysql_upgrade script takes into account the possibility that InnoDB doesn't exist in the system, and doesn't attempt to touch its tables then, but it cannot guess that tables which belong to InnoDB by nature can have some other engine, so this error is not anticipated.

At least by doing it this way (altering the tables to MyISAM on 10.1 and then running upgrade on any higher version), I get the same error. The line numbers differ depending on the version, with 10.4 it matches yours exactly.

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

The failing statement is this:

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

What I couldn't quite figure out is where mysql.innodb_index_stats table disappeared after the upgrade, as it isn't present in your dump. Another table, mysql.innodb_table_stats, is there, and it is indeed MyISAM, but innodb_index_stats, which causes the failure, isn't there at all. Maybe it disappeared during further failed attempts to upgrade, or maybe you dropped it later.

You should have seen this error in 10.2 already, but maybe you ran upgrade without InnoDB. And 10.3 apparently skipped mysql_upgrade, otherwise you wouldn't have the error about mysql.proc in the log on 10.4.

So mainly it's an issue of the customized configuration. But I want to keep this report open, because after dealing with it, there are at least two complaints I fully agree with.

First, mysql_upgrade needs to provide a much better message. I don't yet know how exactly it can be achieved, as it just runs the SQL script, but it's badly needed. Printing the failing statement would be very helpful, while the line number isn't at all, it doesn't match line numbers in either mysql_fix_privilege_tables.sql or scripts/mysql_system_tables_fix.sql or scripts/mysql_fix_privilege_tables_sql.c.

Secondly, while mysql_upgrade claims that it encountered a FATAL error and aborted, in fact it does a lot of stuff afterwards. That's why the general log is useless too, the failed statement is nowhere near the end of it.

So, I'm going to modify the summary of this bug to indicate that it's about mysql_upgrade failing without proper diagnostics.

For resolving the original issue, once you decide to customize you system tables, you have to stay consistent about it. In this case, inconsistency was in changing InnoDB-specific tables to MyISAM rather than dropping them, and then switching InnoDB back on. I think it's rather fortunate that it complained so early, I don't even know how InnoDB would behave when its system tables turned out to be outside the engine.

Dear Elena
Your must create a product that does not depend on the existence of any particular engine, and it still works and upgrades fine. There must be a "legal" way to get rid of Innodb that does cause these issues. I use rocksdb and feel it works much better.

philip_38, I think that the InnoDB persistent statistics tables were a mistake that was made in MySQL 5.6. In MariaDB Server 10.6.5, MDEV-25919 should finally fix most trouble related to them, but I do not think that it will fix these upgrade issues.

I hope that in some future, we will be able to deprecate and remove the InnoDB internal statistics. To my understanding, that would require making the construction of engine-independent statistics more efficient.

Question: I could find a way to fix this in Google. I erased by mistake the directory '#rocksdb', and now I have all the *.frm. I don't care about the data, but I want to recover the create table definitions from the *.frm. Is there a way? I plain Mysql there was a utility but it depends on the full server been installed. Does MariaDB offers any way to recreate the table structure from a *.from (Rocksdb)

philip_38, we don't have such a tool, although it should be possible to write one rather easily.

What you can do now is to select from information_schema tables. Depending on what columns you select, it will or won't work with frm files only. For example, these two queries will work:

select * from information_schema.columns where table_name='t2';

select table_catalog, table_schema, table_name, non_unique, index_schema, index_name, seq_in_index, column_name, collation, sub_part, packed, nullable, comment, index_comment, ignored from information_schema.statistics where table_name='t2';

But two remaining columns from information_schema.statistics will not work.

I believe these two queries as above should be enough to reconstruct all columns and indexes, though.