Linux Centos 7.1

Thierry-B.,
what exactly is this bug report about, the error messages you were getting upon mysql_upgrade, or the fragments of the code that you found inappropriate?

If it's about the errors, please quote the exact output that you got. Errors about system tables, including stat tables, can indeed appear after switching from one version to another, but mysql_upgrade is supposed to fix them.

If if was about the code, please quote the code fragment(s) that you are referring to, and explain the nature of the problem that you see in them.

Thanks.

Elena,

The text written below is not directed against you

That's not really a bug in fact. But JIRA doesn't purpose anything else than "bug, epic or task".

Anyway,

First point :
Inside the perl script mysql_install_db I see some strange concept approach.

line 23 to 27

set @orig_storage_engine=@@storage_engine;
set storage_engine=myisam;

set @have_innodb= (select count(engine) from information_schema.engines where engine='INNODB' and support != 'NO');
SET @innodb_or_myisam=IF(@have_innodb <> 0, 'InnoDB', 'MyISAM');

Is It a choice ? or an historical laziness ?

Why not let the default storage engine to build the mysql database, except if this one is blackhole !

Second point :

line 106 to 130

SET @create_innodb_table_stats="CREATE TABLE IF NOT EXISTS innodb_table_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
n_rows BIGINT UNSIGNED NOT NULL,
clustered_index_size BIGINT UNSIGNED NOT NULL,
sum_of_other_index_sizes BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (database_name, table_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0";

SET @create_innodb_index_stats="CREATE TABLE IF NOT EXISTS innodb_index_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(64) NOT NULL,
index_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
/* there are at least:
stat_name='size'
stat_name='n_leaf_pages'
stat_name='n_diff_pfx%' */
stat_name VARCHAR(64) NOT NULL,
stat_value BIGINT UNSIGNED NOT NULL,
sample_size BIGINT UNSIGNED,
stat_description VARCHAR(1024) NOT NULL,
PRIMARY KEY (database_name, table_name, index_name, stat_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0";

Well, this tables are not transactionnal, they are used to store datas only. To my knowledge it has no integrity constraint. But the script fix directly INNODB without explaination. why not use @innodb_myisam variable
That is arbitrary ! And Aria ? why not Aria engine too, by example ?

Third point :
mysql_upgrade is hard coded with exactly the same code. Arrgggg, where is the knife to open my arms ! Who is the coder ?!

The result of this, is when mysql_upgrade trying to control the differences, It said , Error.
And that normal ! when you observe this code. The stuff who isn't normal, this is arbitrary code with some fixed engine on tables.

Best regards, Elena

Thierry-B.,

Yes, I understand you are not being antagonistic, my comment had the literal meaning: to respond appropriately, we really needed to know what exactly you meant while filing the issue. It is much clearer now, thanks.

I will write a detailed response later, we are having a MariaDB development meeting now, so things are a bit hectic here.

Regarding issue types, "bug" and "task" normally cover the range of issues we want to use JIRA for (and epic is rarely used as an umbrella for smaller tasks). If you don't find either of them suitable, most likely what you want is a discussion, which is better to have on the public MariaDB mailing list maria-discuss@lists.launchpad.net – you will have a wider audience and more opinions there.

Thierry-B.,

So, back to your questions.

First point :
...
Is It a choice ? or an historical laziness ?
Why not let the default storage engine to build the mysql database

It is historical, but not exactly laziness. The legacy limitation that system tables must be MyISAM is deeply ingrained in MySQL/MariaDB, and it's not easy to get rid of.
As you might know, Oracle MySQL quite resolutely moves away from MyISAM towards InnoDB, but even their upcoming GA still has MyISAM for system tables (unless they changed it in the very last release which is not available yet, but it's hard to believe – such changes cannot be done between RC and GA). The limitation is still mentioned in MySQL manual as well:

Do not convert MySQL system tables in the mysql database (such as user or host) to the InnoDB type. This is an unsupported operation. The system tables must always be of the MyISAM type.

You can try to alter a system table manually on a test instance and see how it goes.

So all in all, it will happen some day, but we are not there yet.

Second point :
...
SET @create_innodb_table_stats
...
Well, this tables are not transactionnal, they are used to store datas only. To my knowledge it has no integrity constraint. But the script fix directly INNODB without explaination. why not use @innodb_myisam variable
That is arbitrary ! And Aria ? why not Aria engine too, by example ?

InnoDB stat tables are used only for InnoDB, and inside InnoDB. The engine expects to find them in their dictionary and cannot store statistics if the tables belong to any other engine. I suppose one could claim that the tables should be universal, but it just does not make sense. They are meaningless if InnoDB is disabled, they are only useful when InnoDB is in use, and it just makes no point to introduce inter-engine communication for such functionality. If you have good arguments for that, I suppose you could file a feature request for Oracle InnoDB.

Third point :
mysql_upgrade is hard coded with exactly the same code.

Actually, it is not, or I don't understand the nature of your complaint. Both mysql_upgrade and mysql_install_db use SQL-ish scripts which, in turn, are put up together during compilation from templates that reside in scripts/* folder, but there is no code duplication or SQL hard code in either of the tools. If you still think otherwise, please provide examples.

The result of this, is when mysql_upgrade trying to control the differences, It said , Error.
And that normal ! when you observe this code. The stuff who isn't normal, this is arbitrary code with some fixed engine on tables.

I hope the above explains why mysql_upgrade was not happy about your converting all system tables to Aria. If it does not, please let me know.

Given my last point, I just retail inspector the code that generates table creation and update it.
I agree with you, The Cmake build automatically a file "mysql_fix_privilege_tables_sql.c" with some SQL files that will be included in mysql_upgrade.
You're right, it is not a duplicate, the code has the same origin: the creation of the MySQL table system, also used by mysql_install_db
.
Now I understand much better why it is like that. I would have time to philosophize about this, but it is not at our level he plays.

Thank you very much for your time, Elena.

PS: After the migration of the "mysql" database of innodb_*_stats tables from the InnoDB format to Aria, statistics no longer stored. after restoring the structure to Innodb, everything returned to normal. That's good, but here shows a nesting (too) high in my opinion