| Bug #17010 | Table Maintenance (analyze, optimize, ...) | ||
|---|---|---|---|
| Submitted: | 1 Feb 2006 15:58 | Modified: | 3 Jul 2006 14:16 |
| Reporter: | Krzysztof Klimczak | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S4 (Feature request) |
| Version: | 5.1.5-max | OS: | Windows (Windows NT) |
| Assigned to: | CPU Architecture: | Any | |
[1 Feb 2006 16:13]
MySQL Verification Team
On Linux current source server not presents this issue:
miguel@hegel:~/dbs/5.1> 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.6-alpha-debug
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.04 sec)
mysql> INSERT INTO employees2 VALUES (
-> '1',
-> 'aaa',
-> 'AAA',
-> '2006-02-01'
-> );
Query OK, 1 row affected (0.01 sec)
mysql> DELETE FROM employees2;
Query OK, 1 row affected (0.00 sec)
mysql> OPTIMIZE TABLE employees2;
+-----------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------+----------+----------+----------+
| test.employees2 | optimize | status | OK |
+-----------------+----------+----------+----------+
1 row in set (0.01 sec)
mysql> REPAIR TABLE employees2;
+-----------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------+--------+----------+----------+
| test.employees2 | repair | status | OK |
+-----------------+--------+----------+----------+
1 row in set (0.03 sec)
mysql>
[1 Feb 2006 19:12]
MySQL Verification Team
Thank you for the bug report.
[1 Feb 2006 19:13]
MySQL Verification Team
Forgot to paste the results:
Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
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.5-alpha-nt-max
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.13 sec)
mysql>
mysql> INSERT INTO employees2 VALUES (
-> '1',
-> 'aaa',
-> 'AAA',
-> '2006-02-01'
-> );
Query OK, 1 row affected (0.01 sec)
mysql> DELETE FROM employees2;
Query OK, 1 row affected (0.00 sec)
mysql> OPTIMIZE TABLE employees2;
+-----------------+----------+----------+-----------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------+----------+----------+-----------------------------------------------------------+
| test.employees2 | optimize | note | The storage engine for the table doesn't support optimize |
+-----------------+----------+----------+-----------------------------------------------------------+
1 row in set (0.03 sec)
mysql>
[1 Feb 2006 20:08]
Krzysztof Klimczak
I see it only on Windows systems (NT 4.0 and XP). Commands INSERT and DELETE are unimportands. I've checkind preserve of RANGE partitioning. On Windows this functionality is not working well in my opinion (or i'm not understending it). When the RANGE function for YEAR(hired) stored data in partitions i've checking the size of *.MYD files (because "ALTER TABLE employee DROP PARTITIONS p1" on windows not working too - Bug #16530). For example: ============================================ mysql > DROP TABLE IF EXISTS employees; CREATE TABLE IF NOT EXISTS employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT ) PARTITION BY RANGE ( YEAR(hired) ) ( PARTITION p03 VALUES LESS THAN (2004), PARTITION p04 VALUES LESS THAN (2005), PARTITION p05 VALUES LESS THAN (2006), PARTITION p06 VALUES LESS THAN (2007), PARTITION oth VALUES LESS THAN MAXVALUE ); ---- c:>dir *.MYD 2006-02-01 20:35 0 employees_oth.MYD 2006-02-01 20:35 0 employees_p03.MYD 2006-02-01 20:35 0 employees_p04.MYD 2006-02-01 20:35 0 employees_p05.MYD 2006-02-01 20:35 0 employees_p06.MYD mysql> INSERT INTO employees VALUES (1,'fname1','lname1','2004-01-01','',1,1); INSERT INTO employees VALUES (2,'fname2','lname1','2005-01-01','',1,1); INSERT INTO employees VALUES (3,'fname3','lname1','2005-01-01','',1,1); INSERT INTO employees VALUES (4,'fname4','lname1','2006-01-01','',1,1); INSERT INTO employees VALUES (5,'fname5','lname1','2006-01-01','',1,1); INSERT INTO employees VALUES (6,'fname6','lname1','2006-01-01','',1,1); INSERT INTO employees VALUES (7,'fname7','lname1','2007-01-01','',1,1); INSERT INTO employees VALUES (8,'fname8','lname1','2007-01-01','',1,1); INSERT INTO employees VALUES (9,'fname9','lname1','2007-01-01','',1,1); INSERT INTO employees VALUES (10,'fname0','lname1','2007-01-01','',1,1); SELECT YEAR(hired), COUNT(*) FROM employees GROUP BY 1; +-------------+----------+ | YEAR(hired) | COUNT(*) | +-------------+----------+ | 2004 | 1 | | 2005 | 2 | | 2006 | 3 | | 2007 | 4 | +-------------+----------+ 4 rows in set (0.00 sec) c:> dir *.MYD 2006-02-01 20:36 144 employees_oth.MYD 2006-02-01 20:35 0 employees_p03.MYD 2006-02-01 20:36 108 employees_p04.MYD 2006-02-01 20:36 108 employees_p05.MYD 2006-02-01 20:35 0 employees_p06.MYD ============================================ Size of p03 is OK. Record id=1 should be stored in partition p04 in my opinion. One and only in this partition. In p05 should be stored 2 records (id=2 and id=3). Why size of p04 is the same as size of p05? And why p06 is empty? Base on the size of partition its like: p03 has 0 records (OK) p04 has 3 records (3 x 36 b = 108 b) (NOT OK) p05 has 3 records (NOT OK) p06 has 0 records (NOT OK) oth has 4 records (OK) Thank you.
[3 Jun 2006 14:16]
Valeriy Kravchuk
Please, try to repeat with a newer version, 5.1.9-beta, and inform about the results.
[3 Jul 2006 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
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.02 sec) mysql> INSERT INTO employees2 VALUES ( -> '1', -> 'aaa', -> 'AAA', -> '2006-02-01' -> ); Query OK, 1 row affected (0.00 sec) mysql> DELETE FROM employees2; Query OK, 1 row affected (0.00 sec) mysql> OPTIMIZE TABLE employees2; +------------------+----------+----------+-----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------+----------+----------+-----------------------------------------------------------+ | audit.employees2 | optimize | note | The storage engine for the table doesn't support optimize | +------------------+----------+----------+-----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> ANALYZE TABLE employees2; +------------------+---------+----------+----------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------+---------+----------+----------------------------------------------------------+ | audit.employees2 | analyze | note | The storage engine for the table doesn't support analyze | +------------------+---------+----------+----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> REPAIR TABLE employees2; +------------------+--------+----------+---------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +------------------+--------+----------+---------------------------------------------------------+ | audit.employees2 | repair | note | The storage engine for the table doesn't support repair | +------------------+--------+----------+---------------------------------------------------------+ 1 row in set (0.01 sec) How to repeat: Every time