Bug #70152 mysql_upgrade fails on a server with disabled InnoDB
Submitted: 26 Aug 2013 14:07 Modified: 2 Oct 2013 17:41
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.6.13 OS:Any
Assigned to: CPU Architecture:Any

[26 Aug 2013 14:07] Elena Stepanova
Description:
If MySQL 5.6 server is started without InnoDB on an old datadir, mysql_upgrade fails with

Running 'mysql_fix_privilege_tables'...
ERROR 1146 (42S02) at line 1885: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1146 (42S02) at line 1886: Table 'mysql.innodb_index_stats' doesn't exist
FATAL ERROR: Upgrade failed

How to repeat:
- create a datadir with 5.5 server;
- start 5.6 server with 
  --skip-innodb --default-storage-engine=myisam --default-tmp-storage-engine=myisam;
- run mysql_upgrade;
- observe the errors

Suggested fix:
In mysql_fix_privilege_tables, there is a logic that carefully avoids creating InnoDB stat tables if InnoDB is not enabled; however, later there is unconditional ALTER TABLE for these tables, which apparently causes the errors. 

Either create the tables despite the missing engine, or skip ALTER if the engine is missing. Probably the latter makes more sense, because it will also cover the case when the tables were already there, created with InnoDB, but now InnoDB is disabled.
[26 Aug 2013 17:58] MySQL Verification Team
Hello Elena,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh
[26 Aug 2013 18:00] MySQL Verification Team
// How to repeat

Follow the steps provided by reporter..

// 
[root@cluster-repo mysql-5.6.13]# bin/mysql_upgrade -uroot -p
Enter password:
Looking for 'mysql' as: bin/mysql
Looking for 'mysqlcheck' as: bin/mysqlcheck
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
Running 'mysqlcheck with default connection arguments
Warning: Using a password on the command line interface can be insecure.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.host                                         OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.servers                                      OK
mysql.slow_log                                     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.user                                         OK
Running 'mysql_fix_privilege_tables'...
Warning: Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1885: Table 'mysql.innodb_table_stats' doesn't exist
ERROR 1146 (42S02) at line 1886: Table 'mysql.innodb_index_stats' doesn't exist
FATAL ERROR: Upgrade failed
[2 Oct 2013 17:41] Paul DuBois
Noted in 5.6.15, 5.7.3 changelogs.

If asked to upgrade a server that was running without InnoDB enabled,
mysql_upgrade issued complaints about InnoDB tables not existing
(tables that will not exist unless InnoDB is available).
[4 Dec 2013 11:32] Laurynas Biveinis
5.6$ bzr log -r 5452
------------------------------------------------------------
revno: 5452
committer: mayank prasad <mayank.prasad@oracle.com>
branch nick: 5.6
timestamp: Tue 2013-09-17 10:09:17 +0530
message:
  Bug #17361912 : MYSQL_UPGRADE FAILS ON A SERVER WITH DISABLED INNODB
  
  Issue : 
   While doing an upgrade, when server was started without innodb (i.e. --skip-innodb),
   an error was shown thrown mentioning 2 innodb tables doesn't exist.
  
  Cause :
   In mysql_system_tables_fix.sql (which finally gets appeneded to 
   mysql_fix_privilege_tables.sql), statements were there to alter these two tables,
   and these statements were executed without checking if InnoDB is available or not.
  
  Fix :
   Added a check so that these alter satements are executed on InnoDB tables only
   when InnoDB is available.
[4 Dec 2013 11:33] Laurynas Biveinis
5.6$ bzr log -r 5454
------------------------------------------------------------
revno: 5454
committer: mayank prasad <mayank.prasad@oracle.com>
branch nick: 5.6
timestamp: Tue 2013-09-17 12:31:53 +0530
message:
  Bug #17361912 	MYSQL_UPGRADE FAILS ON A SERVER WITH DISABLED INNODB
  
  Follow up patch for some cosmetics changes.