MySQL Forums
Forum List  »  Partitioning

UNABLE TO INSERT DATA IN ARCHIVE ENGINE HAVING TABLE WITH 1015 PARTITIONS
Posted by: Ijaz Rashid
Date: January 17, 2007 08:34AM

##################
PROBLEM
##################
I've created a table using system variables and schema below successfully. But when i started inserting records in table, I recieved table crashed error below.
If i use less number of partitions, every thing works fine. PROBLEM occcurs when partition number exceeds 500 partitions.

ERROR : "Table is crashed and need's to be repaired"
mysql> CHECK TABLE AR;
(0 error, 1001 Warnings)
mysql> SHOW WARNINGS;
Out of resources when opening file 'C:\...\TEMP\p_2007011600#sp01' (Errcode: 24)



##################
System Variables
##################
OPEN_FILES_LIMIT=2048
TABLE_CACHE=3000
THREAD_CACHE=80
BULK_INSERT_BUFFER_SIZE=32MB.

##################
Table Schema
##################
DROP TABLE IF EXISTS AR;
CREATE TABLE AR (TagID smallint NOT NULL, Value smallint NOT NULL, Time timestamp NOT NULL) ENGINE=ARCHIVE
PARTITION BY RANGE (unix_timestamp(time))
SUBPARTITION BY HASH(TagID)
SUBPARTITIONS 202
(
PARTITION p_2007011600 VALUES LESS THAN (unix_timestamp('2007-01-16 00:00:00')),
PARTITION p_2007011700 VALUES LESS THAN (unix_timestamp('2007-01-17 00:00:00')),
PARTITION p_2007011800 VALUES LESS THAN (unix_timestamp('2007-01-18 00:00:00')),
PARTITION p_2007011900 VALUES LESS THAN (unix_timestamp('2007-01-19 00:00:00')),
PARTITION p_2007012000 VALUES LESS THAN MAXVALUE
);

##################
INSERT STMT
##################
insert into AR values (1,1,'07-01-02 00:00:01'),
(2,2,'07-01-02 00:00:01'),
.....
(35000,1,'07-01-02 00:00:01')

Thank you for help,
IR

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.