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 am trying to archive some files using mysql archive engine. I am using this query to insert file contents:

insert into test_table (id,arch) values (123,'FILE_CONTENT')

After inserting 2 records, I get "duplicate key" error from mysql for a key that doesn't exist in the table. I checked it like 5 times but the record is not there. I selected count(*) for the duplicate id and the result is 0.

I checked the same code with an innodb engine and it works fine. Can anyone tell me what the problem is with the archive engine?

CREATE TABLE `test_table` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `arch` mediumtext,
  PRIMARY KEY (`id`)
) ENGINE=ARCHIVE AUTO_INCREMENT=100175977 DEFAULT CHARSET=utf8mb4;
share|improve this question
    
For full clarfication, please post the output of SHOW CREATE TABLE test_table\G –  RolandoMySQLDBA Aug 6 '13 at 20:03
add comment

2 Answers 2

This sounds very unusual for a table using the ARCHIVE Storage Engine. Why? A duplicate key error is not characteristic for ARCHIVE Storage Engine since

  • Engine does not support the creation of indexes
  • Engine supports INSERTs and SELECTs

Surprisingly, there can be a key internally present. How?

According to the MySQL Documentation

The ARCHIVE engine supports the AUTO_INCREMENT column attribute. The AUTO_INCREMENT column can have either a unique or nonunique index. Attempting to create an index on any other column results in an error. The ARCHIVE engine also supports the AUTO_INCREMENT table option in CREATE TABLE and ALTER TABLE statements to specify the initial sequence value for a new table or reset the sequence value for an existing table, respectively.

Given this information, look back at the table and the query

insert into test_table (id,arch) values (123,'FILE_CONTENT')

If the id column has the AUTO_INCREMENT attribute, you should not specify id with a value. It would produce a normal 1062 error (Duplicate Key) for other Storage Engines.

SUGGESTIONS

Change the insert to a format that can handle the AUTO_INCREMENT attribute of id

insert into test_table (id,arch) values (0,'FILE_CONTENT')

or

insert into test_table (arch) values ('FILE_CONTENT')

Give it a Try !!!

UPDATE 2013-08-06 16:57 EST

If you are planning to do queries from the archive table, you need to get away from the ARCHIVE Storage Engine. Why? Again, according to the MySQL Documentation

Retrieval: On retrieval, rows are uncompressed on demand; there is no row cache. A SELECT operation performs a complete table scan: When a SELECT occurs, it finds out how many rows are currently available and reads that number of rows. SELECT is performed as a consistent read. Note that lots of SELECT statements during insertion can deteriorate the compression, unless only bulk or delayed inserts are used

Note that every SELECT against an ARCHIVE table is a full table scan. If you lookup id 123 with 1,000,000 rows, you gotta read 1,000,000 rows every time.

SUGGESTION

Convert the table to MyISAM. Then you can have a proper index on id plus the ability to create other indexes on other columns as needed.

CREATE TABLE test_table_myisam ENGINE=MyISAM as SELECT * FROM test_table WHERE 1=2;
ALTER TABLE test_table_myisam ADD PRIMARY KEY (id);
INSERT IGNORE INTO test_table_myisam SELECT * FROM test_table;
DROP TABLE test_table;
ALTER TABLE test_table_myisam RENAME test_table;
share|improve this answer
    
Actually I need the id as it is a unique id of my archived file. Is there any way to do that? –  AliBZ Aug 6 '13 at 20:49
    
I chose archive to actually archive my files. So you mean I use myisam tables and archive the data myself and put them in a "text" column? –  AliBZ Aug 6 '13 at 21:21
    
Yes. The index for an ARCHIVE is nothing more than the maintenance of the AUTO_INCREMENT attribute. Full table scans is all an ARCHIVE table can do for data retrieval. If you need fast lookups, go with MyISAM. As long as id is the only column to be indexed and you only lookup by id, InnoDB should be fine as well. If you go with InnoDB and you query by the text column, queries will hose the InnoDB Buffer Pool. Again, I'd go with MyISAM. –  RolandoMySQLDBA Aug 6 '13 at 21:43
add comment

I experienced this confusing error "Duplicate key" when I tried to insert ID's that were lower than the last ID in the database. Just doing a simple test: first insert record with ID 1, then with ID 5, both complete successful. But if you then try to insert with ID = 3, it gives the "Duplicate Key" error. I'm not sure whether there are ways to get around this, I just file archive tables with a query like "INSERT INTO archive_logs (SELECT * FROM logs ORDER BY ID)".

So in my experience, you can insert records with a given ID, you just have to insert them in ascending order.

share|improve this answer
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.