Bug #16530 DROP PARTITION fails with OS permissions error

Submitted: 16 Jan 2006 10:19 Modified: 8 Feb 2006 19:08
Reporter: Jon Stephens Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.4-max OS:Windows (Windows 2000 Server)
Assigned to: CPU Architecture:Any

[16 Jan 2006 10:19] Jon Stephens
Description:
mysql> CREATE TABLE employees2 (
    ->   id INT NOT NULL,
    ->   fname VARCHAR(50) NOT NULL,
    ->   lname VARCHAR(50) NOT NULL,
    ->   hired DATE NOT NULL
    -> )
    -> PARTITION BY RANGE( YEAR(hired) ) (
    ->   PARTITION p1 VALUES LESS THAN (1991),
    ->   PARTITION p2 VALUES LESS THAN (1996),
    ->   PARTITION p3 VALUES LESS THAN (2001),
    ->   PARTITION p4 VALUES LESS THAN (2005),
    ->   PARTITION p5 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.08 sec)

[inserted 10,000 rows using script]

mysql> SELECT COUNT(*) FROM employees2;
+----------+
| COUNT(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(*) FROM employees2 WHERE YEAR(hired) < 1991;
+----------+
| COUNT(*) |
+----------+
|     2286 |
+----------+
1 row in set (0.03 sec)                 

mysql> ALTER TABLE employees2 DROP PARTITION p1;
ERROR 6 (HY000): Error on delete of '.\part\employees2_p1.MYI' (Errcode: 13)

DB and table created by MySQL root. Table populated by script running running as MySQL root. Attempt to drop partition as MySQL root.

I checked permissions on the files and all parent directories in question. Owner is Administrators. All permissions (including Delete permissions) enabled for Everyone.

Server is installed in C:\mysql\bin.
Data directory is M:\mydata (different disk).

How to repeat:
See Description.

Suggested fix:
Allow MySQL user with appropriate permissions on the table to delete a partition.
[16 Jan 2006 10:27] Jon Stephens
Performed same test using 5.1.6-bk built 2006-01-16 on SuSE Linux 9.3, worked without issues.
[8 Feb 2006 19:08] MySQL Verification Team
This issue isn't more repeatable with current Windows source server:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.7-beta-nt-max-log

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

mysql> CREATE TABLE employees2 (
    -> id INT NOT NULL,
    -> fname VARCHAR(50) NOT NULL,
    -> lname VARCHAR(50) NOT NULL,
    -> hired DATE NOT NULL
    -> )
    -> PARTITION BY RANGE( YEAR(hired) ) (
    -> PARTITION p1 VALUES LESS THAN (1991),
    -> PARTITION p2 VALUES LESS THAN (1996),
    -> PARTITION p3 VALUES LESS THAN (2001),
    -> PARTITION p4 VALUES LESS THAN (2005),
    -> PARTITION p5 VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> ALTER TABLE employees2 DROP PARTITION p1;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table employees2\G
*************************** 1. row ***************************
       Table: employees2
Create Table: CREATE TABLE `employees2` (
  `id` int(11) NOT NULL,
  `fname` varchar(50) NOT NULL,
  `lname` varchar(50) NOT NULL,
  `hired` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE ( YEAR(hired)) (PARTITION p2 VALUES LESS THAN (1
996) ENGINE = MyISAM, PARTITION p3 VALUES LESS THAN (2001) ENGINE = MyISAM, PARTITION p4 VALUES LESS THAN
(2005) ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN MAXVALUE ENGINE = MyISAM)
1 row in set (0.01 sec)

mysql>

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.054 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.