Same happens while upgrading from MySQL 5.6.5 to MySQL 5.6.10 or to MySQL 5.6.12.
... or to MySQL 5.6.13.
But for upgrade from MySQL 5.6.10 to 5.6.13 it works all right, so I presume it should be okay for further versions of MariaDB 10.0 as wel.
To my understanding, as a general rule, MySQL doesn't guarantee live upgrade from a non-GA version. While I haven't found mentioning of this specific problem in the manual, it does say this:
"For upgrades between versions of a MySQL release series that has reached General Availability status, you can move the MySQL format files and data files between different versions on systems with the same architecture. For upgrades to a version of a MySQL release series that is in development status, that is not necessarily true. Use of development releases is at your own risk."
I suppose what we have here is an example of "not true".
We can try to report it to MySQL, but I'm pretty sure that it will be said to be "not a bug", due to the reason above.
It's an upstream bug, but as Sergei suggested, it can be fixed just by adding an ALTER to the fix_privilege_tables.sql. I tried the following draft change:
— scripts/mysql_system_tables_fix.sql 2013-07-04 13:01:36 +0000
+++ scripts/mysql_system_tables_fix.sql 2013-09-15 16:39:27 +0000
@@ -648,5 +648,12 @@
- This should not be needed, but gives us some extra testing that the above
- changes was correct
+set @have_innodb= (select count(engine) from information_schema.engines where engine='INNODB' and support != 'NO');
+SET @innodb_index_stats_fk= (select count
from information_schema.referential_constraints where constraint_schema='mysql' and table_name = 'innodb_index_stats' and referenced_table_name = 'innodb_table_stats' and constraint_name = 'innodb_index_stats_ibfk_1');
+SET @str=IF(@innodb_index_stats_fk > 0 and @have_innodb > 0, "ALTER TABLE mysql.innodb_index_stats DROP FOREIGN KEY `innodb_index_stats_ibfk_1`", "SET @dummy = 0");
+PREPARE stmt FROM @str;
+EXECUTE stmt;
+DROP PREPARE stmt;
+
flush privileges;
It seems to work, although in an attempt to make it look/work similarly to other InnoDB-related changes, I ended up with an ugly construct.
Jan,
Could you please take a look, and unless you see any danger in the change, make it look all pretty and neat and apply to 10.0?
Thanks.
I do not fully understand why there is a foreign key to that table or is this a incorrect message ? Where that foreign key is created ?
There used to be a foreign key in the older version (created by install/upgrade). Now it's not there and shouldn't be. But when we do live upgrade from the older version to the current one, the foreign key is not dropped.
Disclaimer: bellow is an educated guess, I didn't try to verify any of that.
I suppose, that
1. innodb_table_stats is created from scripts/mysql_system_tables_fix.sql
2. there is a function in the innodb code that verifies that the table has the correct definition
3. in earlier 5.6 versions this tables used to have a foreign key
4. later it was removed
5. if you upgrade, your innodb_table_stats is created by the earlier mariadb version
6. so a newer version will complain that the table is incorrectly defined
7. an ALTER TABLE in the mysql_fix_privilege_tables.sql will fix that
IIRC the table is created in mysql_system_tables.sql (not _fix).
I can't say anything about the function in the innodb code, but as described earlier, I did try to add DROP <fk> to mysql_system_tables_fix.sql and it seemed to have fixed the problem, so the guess seems to be correct.
Fixed as suggested.