MySQL Bugs: #48721: New create_tablespace_priv column exists in mysql.user, after downgrade to 5.1

Bug #48721	New create_tablespace_priv column exists in mysql.user, after downgrade to 5.1
Submitted:	12 Nov 2009 9:03	Modified:	25 Nov 2009 22:39
Reporter:	Nidhi Shrotriya	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Privileges	Severity:	S3 (Non-critical)
Version:	5.5.0->5.1	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysql-next-mr

Description:
Before upgrade to 5.5 (In 5.1):
------------------------------

SELECT create_tablespace_priv FROM mysql.user;
ERROR 42S22: Unknown column 'create_tablespace_priv' in 'field list'

CREATE DATABASE privdb_tbspc;
USE privdb_tbspc;
CREATE USER 'create_tbspc1'@'localhost' IDENTIFIED BY 'create_tbspc1';
CREATE USER 'create_tbspc2'@'localhost' IDENTIFIED BY 'create_tbspc2';
SHOW GRANTS;
Grants for create_tbspc1@localhost
GRANT USAGE ON *.* TO 'create_tbspc1'@'localhost' IDENTIFIED BY PASSWORD '*522A027A804D27E678196D3C52C2057CEE5530C1'
SHOW GRANTS;
Grants for create_tbspc2@localhost
GRANT USAGE ON *.* TO 'create_tbspc2'@'localhost' IDENTIFIED BY PASSWORD '*DF1078BB67AD0B9BE638802F8693846100F21E65'

After upgrade to 5.5.0 (with GRANT and REVOKE of CREATE TABLESPACE privs):
-----------------------------
CREATE USER 'create_tbspc3'@'localhost' IDENTIFIED BY 'create_tbspc3';
CREATE USER 'create_tbspc4'@'localhost' IDENTIFIED BY 'create_tbspc4';
GRANT CREATE TABLESPACE ON *.* TO 'create_tbspc1'@'localhost','create_tbspc3'@'localhost' WITH GRANT OPTION;
GRANT CREATE TABLESPACE ON *.* TO 'create_tbspc2'@'localhost','create_tbspc4'@'localhost';
REVOKE CREATE TABLESPACE ON *.* FROM 'create_tbspc2'@'localhost','create_tbspc4'@'localhost';

SELECT grantee, privilege_type FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE privilege_type='CREATE TABLESPACE' AND grantee LIKE "%@'localhost'";
grantee privilege_type
'root'@'localhost'      CREATE TABLESPACE
'create_tbspc1'@'localhost'     CREATE TABLESPACE
'create_tbspc3'@'localhost'     CREATE TABLESPACE

SELECT user, create_tablespace_priv FROM mysql.user;
user    create_tablespace_priv
root    Y
root    Y
root    Y
create_tbspc1   Y
create_tbspc2   N
create_tbspc3   Y
create_tbspc4   N

After downgrade to 5.1:
---------------------------

I_S doesn't show any CREATE TABLESPACE privs. but grant table still shows the column (which shouldn't be seen for 5.1) and the privs.

SELECT grantee, privilege_type FROM INFORMATION_SCHEMA.USER_PRIVILEGES WHERE privilege_type='CREATE TABLESPACE' AND grantee LIKE "%@'localhost'";
grantee privilege_type

SELECT user, create_tablespace_priv FROM mysql.user;
user    create_tablespace_priv
root    Y
root    Y
root    Y
create_tbspc1   Y
create_tbspc2   N
create_tbspc3   Y
create_tbspc4   N

How to repeat:
As mentioned in how to repeat section.

Thank you for the bug report. Could you please describe the process you applied to downgrade back from 5.5 to 5.1?. Thanks in advance.

mysql-5.1>bin/mysql_install_db --no-defaults
mysql-5.1>bin/mysqld_safe --no-defaults &
mysql-5.1>bin/mysql --user=root --socket=/tmp/mysql.sock
SELECT create_tablespace_priv FROM mysql.user;
ERROR 42S22: Unknown column 'create_tablespace_priv' in 'field list'

mysql-5.1>bin/mysqladmin --user=root --socket=/tmp/mysql.sock shutdown

Live upgrade:
-------------------
mysql-next-mr>bin/mysqld_safe --no-defaults --datadir=/home/nidhi/mysql-builds/mysql-5.1/var &
mysql-next-mr>bin/mysql_upgrade --user=root --socket=/tmp/mysql.sock
mysql-next-mr>bin/mysql --user=root --socket=/tmp/mysql.sock
SELECT user, create_tablespace_priv FROM mysql.user;
user    create_tablespace_priv
root    Y
root    Y
root    Y
        N
        N

mysql-next-mr>bin/mysqladmin --user=root --socket=/tmp/mysql.sock shutdown

Live Downgrade:
-----------------
mysql-5.1>bin/mysqld_safe --no-defaults --datadir=$PWD/var &
mysql-5.1>bin/mysql --user=root --socket=/tmp/mysql.sock
SELECT user, create_tablespace_priv FROM mysql.user;
user    create_tablespace_priv
root    Y
root    Y
root    Y
        N
        N

Thank you for the report.

Verified as described: column exists even after run mysql_upgrade in 5.1.

Thought it could be closed if only present with 'Live downgrade' (overlooked that not really supported between major releases).

Then found this is present with 'Dump Downgrade' too. But again if we disregard the disrepancy, it might seem ok there.
-Importing dump means mysql.user would be created again with the new column(added in 5.4) too, the column automatically becomes present with the table in 5.1 too. So not sure it is a bug for that ?

I don't really see how this could be handled, at downgrade we don't run any script to fix such discrepancies either.

This is not a bug but expected behavior. When loading a table exported with mysqldump, the table is loaded as is with all its columns. There is no side effect from this behavior i.e. the 5.1 server is not affected by the extra column. Similar behavior exists between previous versions of the server as well