Re: UNABLE TO INSERT DATA IN ARCHIVE ENGINE HAVING TABLE WITH 1015 PARTITIONS

[Mikael Ronström]

Hi,

Ijaz Rashid Wrote:
-------------------------------------------------------
> ##################
> 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)
>

perror 24 shows Too many open files, you need to extend even
beyond 2048 that you've set below.

Rgrds Mikael


>
>
> ##################
> 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