1

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.

4

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.

  • You're right..I'm using mysql version 5.5.16. Thanks! – Vijin Paulraj Aug 25 '12 at 14:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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