Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.18, 10.0(EOL), 10.1(EOL)
-
10.1.19, 10.1.20, 10.2.4-1, 10.2.4-2, 10.1.22, 10.1.23, 10.3.1-1, 10.1.29, 10.1.30, 10.1.31
Description
When a user uses partition selection in a SELECT query to only query a particular partition of a MyISAM table, many users would expect the server to only open the data files for that particular partition. In reality, the server opens all data files for the table.
This bug is related to partition selection in SELECT statements, as defined here:
http://dev.mysql.com/doc/refman/5.6/en/partitioning-selection.html
Let's say that we create the following table:
CREATE TABLE employees (
|
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
fname VARCHAR(25) NOT NULL,
|
lname VARCHAR(25) NOT NULL,
|
store_id INT NOT NULL,
|
department_id INT NOT NULL
|
) ENGINE=MyISAM
|
PARTITION BY RANGE(id) (
|
PARTITION p0 VALUES LESS THAN (5),
|
PARTITION p1 VALUES LESS THAN (10),
|
PARTITION p2 VALUES LESS THAN (15),
|
PARTITION p3 VALUES LESS THAN MAXVALUE
|
);
|
 |
INSERT INTO employees VALUES
|
('', 'Bob', 'Taylor', 3, 2), ('', 'Frank', 'Williams', 1, 2),
|
('', 'Ellen', 'Johnson', 3, 4), ('', 'Jim', 'Smith', 2, 4),
|
('', 'Mary', 'Jones', 1, 1), ('', 'Linda', 'Black', 2, 3),
|
('', 'Ed', 'Jones', 2, 1), ('', 'June', 'Wilson', 3, 1),
|
('', 'Andy', 'Smith', 1, 3), ('', 'Lou', 'Waters', 2, 4),
|
('', 'Jill', 'Stone', 1, 4), ('', 'Roger', 'White', 3, 2),
|
('', 'Howard', 'Andrews', 1, 2), ('', 'Fred', 'Goldberg', 3, 3),
|
('', 'Barbara', 'Brown', 2, 3), ('', 'Alice', 'Rogers', 2, 2),
|
('', 'Mark', 'Morgan', 3, 3), ('', 'Karen', 'Cole', 3, 2);
|
And then we execute the following to ensure that the table's data files are closed:
FLUSH TABLES;
|
And then we can confirm that the table's data files are not open:
$ sudo lsof -p `pidof mysqld` | grep employees
|
And then let's say that we execute the following query:
SELECT * FROM employees PARTITION (p1);
|
Most users would expect that the server would only open the data files for partition p1. In reality, it opens all of them:
$ sudo lsof -p `pidof mysqld` | grep employees
|
mysqld 825 mysql 18u REG 202,2 2048 50336245 /var/lib/mysql/db1/employees#P#p0.MYI
|
mysqld 825 mysql 20u REG 202,2 120 50336246 /var/lib/mysql/db1/employees#P#p0.MYD
|
mysqld 825 mysql 23u REG 202,2 2048 50336247 /var/lib/mysql/db1/employees#P#p1.MYI
|
mysqld 825 mysql 24u REG 202,2 140 50336248 /var/lib/mysql/db1/employees#P#p1.MYD
|
mysqld 825 mysql 25u REG 202,2 2048 50336249 /var/lib/mysql/db1/employees#P#p2.MYI
|
mysqld 825 mysql 26u REG 202,2 148 50336250 /var/lib/mysql/db1/employees#P#p2.MYD
|
mysqld 825 mysql 27u REG 202,2 2048 50336251 /var/lib/mysql/db1/employees#P#p3.MYI
|
mysqld 825 mysql 28u REG 202,2 120 50336252 /var/lib/mysql/db1/employees#P#p3.MYD
|
Attachments
Issue Links
- causes
-
-
- Closed
-
- relates to
-
MDEV-20250 do not open partitions excluded by automatic partition pruning
-
- In Progress
-
-
-
- Closed
-
- links to