Bug #15520 "Optimize table" wants INSERT privs?
Submitted: 6 Dec 2005 14:26 Modified: 3 May 2006 17:56
Reporter: Matthias Urlichs Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:all OS:Any (all)
Assigned to: Paul DuBois CPU Architecture:Any

[6 Dec 2005 14:26] Matthias Urlichs
Description:
mysql> optimize table cat1;
ERROR 1142 (42000): INSERT command denied to user 'smurf'@'localhost' for table 'cat1'

I have a couple of issues with this:
- optimize is potentially disruptive. In any case it's slow on big tables. Allowing everybody who can insert records to deadlock the table for half an hour isn't helpful.
- you can't package it into a transaction. When feeding batches into mysql remotely, one helpful strategy is to bracket the whole thing with a BEGIN/COMMIT. That won't work when everybody can issue statements like this one.

- the fact that OTIMIZE requires INSERT privilege is not documented.

How to repeat:
-

Suggested fix:
use the ALTER privilege.
[6 Dec 2005 14:54] Valeriy Kravchuk
Thank you for a problem report. Please, provide a complete test case (starting with CREATE TABLE..., with all grants performed). 

Are you sure that table cat1 really exists?
[6 Dec 2005 15:11] Matthias Urlichs
Come on, please -- if table "cat1" wouldn't exist, I would get a quite different error message.

But since you insist ..:

root@mysql> revoke insert on smurf.* from smurf@localhost;
smurf@mysql> optimize table cat1;
ERROR 1142 (42000): INSERT command denied to user 'smurf'@'localhost' for table 'cat1'
root@mysql> grant insert on smurf.* to smurf@localhost;
smurf@mysql> optimize table cat1;
+------------+----------+----------+----------+
| Table      | Op       | Msg_type | Msg_text |
+------------+----------+----------+----------+
| smurf.cat1 | optimize | status   | OK       |
+------------+----------+----------+----------+
1 row in set (2.50 sec)

mysql> show create table cat1;
       Table: cat1
Create Table: CREATE TABLE `cat1` (
  `id` int(11) NOT NULL auto_increment,
  `multi` tinyint(1) NOT NULL default '0',
  `name` varchar(100) NOT NULL default '',
  `depcat` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `depcat` (`depcat`),
  CONSTRAINT `cat1_ibfk_1` FOREIGN KEY (`depcat`) REFERENCES `cat2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
[6 Dec 2005 15:12] Matthias Urlichs
Oh yes -- whether any other permissions exist on that table does not matter; I tried both with an initial "REVOKE ALL" and a "GRANT ALL".
[6 Dec 2005 15:29] Valeriy Kravchuk
Thank you for the additional information. I was able to repeat the problem you described on 5.0.15-nt (used just as an example, because you marked version as "all"):

C:\Documents and Settings\openxs>mysql -uroot -p -P3307 test
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `cat1` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `multi` tinyint(1) NOT NULL default '0',
    ->   `name` varchar(100) NOT NULL default '',
    ->   `depcat` int(11) default NULL,
    ->   PRIMARY KEY  (`id`),
    ->   UNIQUE KEY `name` (`name`),
    ->   KEY `depcat` (`depcat`));
Query OK, 0 rows affected (0.68 sec)

mysql> exit;
Bye

C:\Documents and Settings\openxs>mysql -uuser1 -p -P3307 test
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 5.0.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show tables like 'cat%';
+-----------------------+
| Tables_in_test (cat%) |
+-----------------------+
| cat1                  |
+-----------------------+
1 row in set (0.01 sec)

mysql> optimize table cat2;
ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'cat2'

So, your "Come on, please -- if table "cat1" wouldn't exist, I would get a quite different error message." is not really appropriate... Surely I asked because of some reason. But let's go on:

mysql> optimize table cat1;
ERROR 1142 (42000): INSERT command denied to user 'user1'@'localhost' for table 'cat1'
mysql> exit
Bye

C:\Documents and Settings\openxs>mysql -uroot -p -P3307 test
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 5.0.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant insert on cat1 to user1;
Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye

C:\Documents and Settings\openxs>mysql -uuser1 -p -P3307 test
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.15-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> optimize table cat1;
+-----------+----------+----------+----------+
| Table     | Op       | Msg_type | Msg_text |
+-----------+----------+----------+----------+
| test.cat1 | optimize | status   | OK       |
+-----------+----------+----------+----------+
1 row in set (0.44 sec)

So, yes, INSERT is needed for InnoDB tables. But I think it is just a documentation request. This should be documented in http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html and, surely, in http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html.
[6 Dec 2005 15:37] Matthias Urlichs
Well, I disagree about the Documentation-Only part -- OPTIMIZE is not transaction-friendly, and potentially disruptive. It should therefore not be available to users with simple select/insert/update/delete privileges.
[3 May 2006 17:56] Paul DuBois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Also noted in manual:
SELECT/INSERT are required for ANALYZE TABLE and
REPAIR TABLE as well.