MySQL Bugs: #73636: Mention mysql.proxies_priv explicitly in "Downgrading to 5.5"

Bug #73636	Mention mysql.proxies_priv explicitly in "Downgrading to 5.5"
Submitted:	19 Aug 2014 9:11	Modified:	4 Apr 2015 20:15
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
http://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html

mentioned that tables containing TIMESTAMP columns created with 5.6 need to be dumped and restored due to 5.5 not understanding the sub-second extensions to this data type

The system table mysql.proxies_priv is one of the tables affected by this,
and if it was created by 5.6 a downgrade will lead to 

  [ERROR] /usr/local/mysql-5.5.34/bin/mysqld: Incorrect information in file: './mysql/proxies_priv.frm'
  [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect information in file: './mysql/proxies_priv.frm'

on 5.5 server startup unless --skip-grant-tables is used

So instructions on how to deal with this specific table would be nice

How to repeat:
try downgrade fresh 5.6 installation to 5.5, this will be the 2nd problem you'll run into after recreating the mysql.host table ...

Suggested fix:
mention this table explicitly, and maybe also add the following instructions how to identify tables that need to be dumped and restored:

* start 5.5 mysqld with --skip-grant-tables
* run 

  mysqldump ... --no-data --force --all-databases 2>&1 > /dev/null | grep "LOCK TABLES" | grep -o "'.*'"

this will produce a list of all tables with frm format extensions that 5.5 doesn't understand ...

Hello Hartmut,

Thank you for the bug report.
Verified as described.

140821  6:53:34 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
140821  6:53:34 [Note] Server socket created on IP: '0.0.0.0'.
140821  6:53:34 [ERROR] /data/ushastry/server/mysql-5.5.40/bin/mysqld: Incorrect information in file: './mysql/proxies_priv.frm'
140821  6:53:34 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect information in file: './mysql/proxies_priv.frm'
140821 06:53:34 mysqld_safe mysqld from pid file /tmp/master/cluster-repo.pid ended

Thanks,
Umesh

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.

Added the following text to
http://dev.mysql.com/doc/refman/5.6/en/downgrading-to-previous-series.html:

The following query identifies tables and columns that may be affected by this problem. Some of them are system tables in the mysql database (such ascolumns_priv and proxies_priv). This means that mysql is one of the databases you must dump and reload, or server startup may fail after downgrading.

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('TIME','DATETIME','TIMESTAMP')
ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME;