MySQL Bugs: #5058: Column privileges are not reloaded properly when server is started again

Bug #5058	Column privileges are not reloaded properly when server is started again
Submitted:	14 Aug 2004 20:59	Modified:	27 Aug 2004 12:22
Reporter:	Nick Kotsonis	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.3-beta	OS:	Linux (linux)
Assigned to:	Per-Erik Martin	CPU Architecture:	Any

Description:
When column privileges are set for a user on a table, works OK at this session. If the server is stopped and started again, the column privileges that are set for the tables are not restored properly. Only one of these is restored and displayed in the command SHOW GRANTS FOR 'user';

How to repeat:
GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'somepass';
GRANT SELECT (
Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
Execute_priv, Repl_slave_priv, Repl_client_priv
) ON mysql.user TO 'pma'@'localhost';
.
.
.

mysql> show grants for pma@localhost;

Grants for pma@localhost
------------------------------
.
.
.
GRANT SELECT (Create_priv, Delete_priv, Drop_priv, Execute_priv, Host, Select_priv, Super_priv, Lock_tables_priv, User, Insert_priv, Grant_priv, Shutdown_priv, Show_db_priv, References_priv, Alter_priv, File_priv, Reload_priv, Update_priv, Process_priv, Create_tmp_table_priv, Index_priv, Repl_slave_priv, Repl_client_priv) ON `mysql`.`user` TO 'pma'@'localhost' 
.
.
.
6 rows in set (0.00 sec)

>>>> THIS IS OK. THE GRANTS THAT WE GAVE WERE REGISTERED <<<<

now stop and start the server again.

mysqladmin shutdown
safe_mysqld &

mysql> show grants for pma@localhost;

Grants for pma@localhost
--------------------------------
.
.
.
GRANT SELECT (Alter_priv) ON `mysql`.`user` TO 'pma'@'localhost'
.
.
6 rows in set (0.00 sec)

>>>> THIS IS THE FAULT <<<<

I was able to verify this on linux (2.4.21 #12 SMP) and windows 2000 (SMP) using this script:

create database if not exists bugs;

use bugs;

CREATE TABLE if not exists `foo` (
  `howmanyvalues` bigint(20) default NULL,
  `avalue` int(11) default NULL,
  KEY `foo_howmanyvalues_idx` (`howmanyvalues`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

grant usage on *.* to bug5058@localhost identified by 'blah';

GRANT SELECT (howmanyvalues, avalue) ON bugs.foo TO bug5058@localhost;

show grants for bug5058@localhost;

-- restart mysqld
-- i used mysqld_multi stop 1
-- mysqld_multi start 1

show grants for bug5058@localhost;
-- I got GRANT SELECT (avalue) ON `bugs`.`foo` TO 'bug5058'@'localhost'

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

I can't repeat either of the test cases in the current 4.1 source version.
Perhaps there's some detail missing from the instructions on how to repeat
the error?

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Dear User,

This has been fixed in our source repository which means that the forthcoming 4.1.4 releaes will 
not have this bug.  I was unable to repeat the problem using the 4.1.4 built from the bitkeeper 
sources.

Thanks for the bug report!

I have not only reproduced the fault on the source version compiled by me, but also on the binaries package for linux (x86, libc6).

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

I'm sorry, but I still can't repeat any of this. I get the same, expected, results after
a restart of the server. So if this happens for you, there must be vital information
missing from the description on how to repeat it.

Here's the result when doing this on the 4.1 source build on a RH 7.3 singel CPU,
using "mysqld_safe" and "mysqladmin shutdown" to start/stop the server:

mysql> GRANT USAGE ON mysql.* TO 'pma'@'localhost' IDENTIFIED BY 'somepass';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT (
    -> Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv,
    -> Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv,
    -> File_priv, Grant_priv, References_priv, Index_priv, Alter_priv,
    -> Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv,
    -> Execute_priv, Repl_slave_priv, Repl_client_priv
    -> ) ON mysql.user TO 'pma'@'localhost';
Query OK, 0 rows affected (0.02 sec)

mysql> show grants for pma@localhost;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for pma@localhost                                                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'pma'@'localhost' IDENTIFIED BY PASSWORD '*13883BDDBE566ECECC0501CDE9B293303116521A'                                                                                                                                                                                                                                                                 |
| GRANT SELECT (Select_priv, Index_priv, Update_priv, Drop_priv, Host, Reload_priv, Super_priv, Grant_priv, Repl_client_priv, Insert_priv, Delete_priv, Create_tmp_table_priv, Execute_priv, References_priv, Alter_priv, File_priv, Process_priv, Create_priv, Shutdown_priv, User, Show_db_priv, Repl_slave_priv, Lock_tables_priv) ON `mysql`.`user` TO 'pma'@'localhost' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for pma@localhost;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for pma@localhost                                                                                                                                                                                                                                                                                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'pma'@'localhost' IDENTIFIED BY PASSWORD '*13883BDDBE566ECECC0501CDE9B293303116521A'                                                                                                                                                                                                                                                                 |
| GRANT SELECT (Create_priv, Delete_priv, Drop_priv, Execute_priv, Host, Select_priv, Super_priv, Lock_tables_priv, User, Insert_priv, Grant_priv, Shutdown_priv, Show_db_priv, References_priv, Alter_priv, File_priv, Reload_priv, Update_priv, Process_priv, Create_tmp_table_priv, Index_priv, Repl_slave_priv, Repl_client_priv) ON `mysql`.`user` TO 'pma'@'localhost' |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> create database if not exists bugs;
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> use bugs;
Database changed
mysql> 
mysql> CREATE TABLE if not exists `foo` (
    ->   `howmanyvalues` bigint(20) default NULL,
    ->   `avalue` int(11) default NULL,
    ->   KEY `foo_howmanyvalues_idx` (`howmanyvalues`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> grant usage on *.* to bug5058@localhost identified by 'blah';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT SELECT (howmanyvalues, avalue) ON bugs.foo TO bug5058@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> show grants for bug5058@localhost;
+----------------------------------------------------------------------------------------------------------------+
| Grants for bug5058@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bug5058'@'localhost' IDENTIFIED BY PASSWORD '*0380BEA27363E56C37F0BFDA438F429080848051' |
| GRANT SELECT (howmanyvalues, avalue) ON `bugs`.`foo` TO 'bug5058'@'localhost'                                  |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> 
mysql> show grants for bug5058@localhost;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: bugs

+----------------------------------------------------------------------------------------------------------------+
| Grants for bug5058@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bug5058'@'localhost' IDENTIFIED BY PASSWORD '*0380BEA27363E56C37F0BFDA438F429080848051' |
| GRANT SELECT (howmanyvalues, avalue) ON `bugs`.`foo` TO 'bug5058'@'localhost'                                  |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql>

An additional note: I first missed that this was reported for SMP systems.

I now tried the same thing on a linux 2.4.26 #4 SMP, but I still can't repeat it,
it's working fine.