MySQL Bugs: #23603: Partitions: reorganize means delete

Bug #23603	Partitions: reorganize means delete
Submitted:	24 Oct 2006 23:44	Modified:	14 Feb 2008 16:42
Reporter:	Peter Gulutzan	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.13-beta-debug	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
If I reorganize a partition, I can delete its rows.

I'm inclined to believe that this should not happen
at all. I didn't notice this point mentioned in the
reference manual. An error message "you can't do it"
would be one good solution.

On the other hand, if it's intended behaviour, then
ALTER TABLE REORGANIZE PARTITION should require more
privileges, just as ALTER TABLE DROP PARTITION does.
(Currently, a user who only has ALTER privilege can
reorganize the partition and thus drop rows.)

How to repeat:
As user root, say:

mysql> create table r (r int) partition by list (r) (partition p1 values in (1));
Query OK, 0 rows affected (0.92 sec)

mysql> insert into r values (1);
Query OK, 1 row affected (0.56 sec)

mysql> grant alter on r to morgan@localhost;
Query OK, 0 rows affected (0.27 sec)

mysql> select * from r;
+------+
| r    |
+------+
|    1 |
+------+
1 row in set (0.59 sec)

As user morgan@localhost, say:

mysql> alter table r reorganize partition p1 into (partition p1 values in (0));
Query OK, 0 rows affected (0.94 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table r drop partition p1; /* morgan can't delete this way */
ERROR 1142 (42000): DROP command denied to user 'morgan'@'localhost' for table 'r'

As user root, say:

mysql> select * from r;
Empty set (2.19 sec)

Thank you for the report.

Verified as described on 32-bit Linux using last BK sources.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22220

ChangeSet@1.2478, 2007-03-18 22:55:02+04:00, holyfoot@mysql.com +4 -0
  Bug #23603 Partitions: reorganize means delete
  added code to raise error in case when there's no partition
  for the existing value(s)

That it is possible to delete records using ALTER TABLE was intentional. So this is more of a
change of behaviour rather than a bug fix. I suggest asking a group of e.g. Peter Gulutzan,
Timour and probably some more.

If it isn't possible to change partition limits and even remove data as part of the reorganize means
there is functionality missing. The user might need exactly that functionality in some cases.

Do the following:
1) REORGANIZE means delete should still be allowed
=> Requires more privileges to reorganize as Peter mentioned
2) Add new flag to ALTER TABLE that specifies that DELETE of REORGANIZE should
not be allowed (syntax flag)
3) Move this to Documenting category and add comments for documentation of this new
functionality.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38226

ChangeSet@1.2627, 2007-11-21 18:55:33+01:00, mattiasj@witty.local +11 -0
  Bug#23603: Partitions: reoganize means delete
  
  Problem was that a user with ALTER priv (and without
  DROP priv) could do a REORGANIZE PARTITION that could
  delete rows.
  
  Added option "WITH NO DELETE" for use with
  REORGANIZE PARTITION for non delete operations
  (do not require DROP priv).
  Requiring DROP priv if "WITH NO DELETE" is NOT used.

This behavior was done intentionally and there will be no change in privileges for this. (no change in server, patch will not be pushed). Also see Bug#32980