Bug #73634 mysql.host table not mentioned on "Downgrading to MySQL 5.5"
Submitted: 19 Aug 2014 9:01 Modified: 5 Oct 2015 18:23
Reporter: Hartmut Holzgraefe Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: Daniel Price CPU Architecture:Any

[19 Aug 2014 9:01] Hartmut Holzgraefe
Description:
The mysql.host table has been listed as "obsolete" in the manual ever since the 3.23/4.x version, and finally been removed in 5.6.7, but MySQL 5.5 still seems to check for it on startup and refuses to start if it is missing.

So when trying to downgrade a fresh MySQL 5.6 (>= 5.6.7) installation to 5.5
mysql startup will fail with:

  [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

How to repeat:
Create fresh 5.6 instance, then try to downgrade it to 5.5

Suggested fix:
Mention this on http://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html and provide 5.5 CREATE TABLE statement for mysql.host table
[19 Aug 2014 9:20] Umesh Shastry
Hello Hartmut,

Thank you for the bug report.
Verified as described.

Downgraded 5.6.21->5.5.40
=========================

140821  5:40:29 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist
140821 05:40:29 mysqld_safe mysqld from pid file /tmp/master/cluster-repo.pid ended

Thanks,
Umesh
[5 Oct 2015 18:21] Daniel Price
Posted by developer:
 
SHOW CREATE TABLE for mysql.host table (5.5.46):

CREATE TABLE `host` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`Db`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Host privileges;  Merged with database privileges';
[5 Oct 2015 18:23] Daniel Price
Posted by developer:
 
The "Downgrading to MySQL 5.5" documentation has been updated. The change should appear online within 24 hours.

"The mysql.host table was removed in MySQL 5.6.7. When downgrading to a
previous release, startup on the downgraded server fails with an error if
the mysql.host table is not present. You can recreate the table manually
or restore it from a backup taken prior to upgrading to MySQL 5.6.7 or
higher."

Thank you for the bug report.