Re: Why partition can not support auto_increment field now?

[Scott Noyes]

Glenn,

What you're seeing is a documented feature.

"For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups."

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

To get the behavior you expected, just swap the fields in your primary key:

CONSTRAINT PK_t1 PRIMARY KEY (id, currTime)

Or, since auto_increment doesn't actually require a PRIMARY or even a UNIQUE key, don't include the id in the primary key:

CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT,
currTime DATETIME,
PRIMARY KEY (currTime),
INDEX(id)
) ENGINE = myISAM
PARTITION BY LINEAR KEY(currTime)...