Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've created a table using the following query,

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
)   
    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
);

Then, i've inserted the data to the table using the partition information and the query as follows,

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);

Now, I'm trying to select the records from specific partition, and my query was,

SELECT * FROM employees PARTITION (p1);

But i'm keep on getting the same error as,

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p1) LIMIT 0, 30' at line 1

FYI, initially i was using InnoDB database engine, now i've changed it to MyISAM, but nothing helped me out.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

The syntax you are proposing (being able to explicitly select from a partition list) does not exist until MySQL 5.6.2:

Starting in MySQL 5.6.2, SELECT supports explicit partition selection using the PARTITION keyword with a list of partitions or subpartitions (or both)... [src]

And indeed, your example comes from the MySQL 5.6 manual. I am assuming you are running MySQL 5.5 or even 5.1, but not 5.6.

share|improve this answer
    
You're right..I'm using mysql version 5.5.16. Thanks! –  Vijin Paulraj Aug 25 '12 at 14:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.