MySQL Forums - Archive Storage Engine

Recover archive table (1 reply)

Ryan Hatfield — Tue, 11 Feb 2020 18:41:50 +0000

I have an InnoDB table that I use for logging. Every few months I create a new one, rename the old one, and convert the old one into an archive using the Archive storage engine. I've been doing this for around 8 years without issue.

This last time however I got a warning stating "Engine error 1" or "Engine error -1" (can't remember which but it was definitely a 1). When I select on the new archive table I get no results. If I perform a check table it takes several minutes and comes back stating the table is corrupt. The size of the ARZ file is roughly what I would expect based on the the size of previous archive tables and relative number of records. So I know the data is there. When I perform a CHECK TABLE it tells me it is corrupt. When I attempt a repair (I've tried all options) it truncates it each time.

I'm on MySQL 5.7 and the only information I can find for my case is an old file called archive_reader written by Twitter over 13 years ago and not updated in more than 9.5 years. I have my doubts this will help me given it was written for MySQL 5.1.

Is there anything that I can use to recover this data? It isn't mission critical but I have a gap now of about 6 months that I might one day need to reference (debugging, legal, etc).

Archive Some Data from Huge Table (>1TB) (3 replies)

Ravi Rai — Wed, 08 May 2019 05:35:30 +0000

Hi All,

I need to archive some data(Based on condition) from a huge table(size > 1TB).
My Table structure is as follows. This table is having 2 foreign keys and also have TEXT type column.
MYSQL Version - 5.6

CREATE TABLE `d_highmark_reports` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`loan_app_reference_id` BIGINT(20) NOT NULL,
`client_id` BIGINT(20) NOT NULL,
`date_of_report_upload` DATE NULL DEFAULT NULL,
`highmark_status` INT(11) NULL DEFAULT NULL,
`request` TEXT NULL,
`response` MEDIUMTEXT NULL,
`raw_response` LONGTEXT NULL,
`is_online` TINYINT(1) NULL DEFAULT '0',
`request_id` VARCHAR(20) NULL DEFAULT NULL,
`errors` TEXT NULL,
`date_of_online_request` DATE NULL DEFAULT NULL,
`process_def_key` VARCHAR(100) NULL DEFAULT NULL,
`active` TINYINT(1) NOT NULL DEFAULT '1',
`eligibility_rule_status` INT(2) NULL DEFAULT '0',
`is_processed_by_batch` TINYINT(1) NULL DEFAULT '0',
PRIMARY KEY (`id`),
INDEX `fk_loan_app_reference_id` (`loan_app_reference_id`),
INDEX `fk_client_id` (`client_id`),
CONSTRAINT `fk_client_id` FOREIGN KEY (`client_id`) REFERENCES `m_client` (`id`),
CONSTRAINT `fk_d_highmark_reports_loan_app_reference_id` FOREIGN KEY (`loan_app_reference_id`) REFERENCES `m_loan_app_reference` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=24002
;

Can't write; duplicate key in table '#sql-950b_207' (no replies)

Fabien CELAIA — Tue, 07 Mar 2017 08:47:58 +0000

I'm using MySQL 5.7.17 on RH7

I'm facing any problems converting some innodb tables to archive...

1) duplicate key... without duplicate key

CREATE TABLE `MYTABLE` (
`ID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`NAME_FR` varchar(50) DEFAULT NULL,
`NAME_DE` varchar(50) DEFAULT NULL,
`NAME_IT` varchar(50) DEFAULT NULL,
`NAME_EN` varchar(50) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

6 lines without duplicate

ALTER TABLE MYTABLE engine=archive ;

Lookup Error - MySQL Database Error: Can't write; duplicate key in table '#sql-950b_476'

2) I'm creating the same table, without AUTO_INCREMENT

ALTER TABLE MYTABLE engine=archive ;

Lookup Error - MySQL Database Error: Got error -1 from storage engine

MySQL 5.5.47 aborts with signal 11 while accessing table with archive engine beyond row# 2724397 (no replies)

Bénoît Segond von Banchet — Tue, 05 Apr 2016 19:37:26 +0000

I have a table with 5635816 rows. I can access rows 0-2724397, but when I try to access the table beyond that point MySQL aborts with signal 11.
I have found no way to access or recover the rows beyond that point.
Any help in accessing or recovering the data is welcome.

large .arn file filling up whole hard drive (no replies)

Florian Enzinger — Wed, 28 Jan 2015 13:28:20 +0000

Hello,

I recently ran into an issue whit an archive table of about 1gb. When I try to get data from this table a large .arn file with up to 800gb is created on the server, using almost all space available.
This happens running mysql5.5.4 on ubuntu. I also have the same setup on another server (mysql5.0.38 on ubuntu) where I don't have this issue (thoug the archive there is ~8gb). On mysql5.0.38 there is no .arn but a .arm file that has only 19kb.

This is what the table looks like:

CREATE TABLE `importsuccess` (
`backup_id` int(10) unsigned NOT NULL,
`serial` varchar(36) NOT NULL,
`identifier` varchar(14) NOT NULL,
`sn_id` int(10) unsigned NOT NULL,
`sn` longtext NOT NULL,
`parsetime` bigint(20) unsigned NOT NULL DEFAULT '0',
`ts_import` datetime NOT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=utf8 PACK_KEYS=0;

Thanks for any help,
Flo

what version of MySQL has Archive storage engine support built in? (no replies)

rmn190 rmn190 — Wed, 14 May 2014 09:23:30 +0000

As far as I know, the archive storage engine is built in source code only. So, if I want to use this engine, I should build it from source code, and it is a problem since I have to build so many times on different OS platform.

So, my question is, what version has it built in? or any plan about that.

Huge and growing archive with ARN extension (1 reply)

Joakim Byström — Wed, 22 Jan 2014 06:34:41 +0000

Dear all,

I got disk full and it turned out to be a large .ARN file from an archive table. When I cleared some space, the file continued to grow until the space was consumed. I tried to find what process is running the optimization and creating the .ARN (with intention to kill it) with the mysql command

mysql> show full processlist\G

but nothing regarding the archive or optimization showed up. Any suggestions? It might not be an archive issue, but large ARN-files seem very unusual from a web search...

-rw-rw---- 1 mysql mysql 153G 2014-01-04 17:45 acu_data_io_value_archive.ARN
-rw-rw---- 1 mysql mysql 47M 2013-07-02 00:15 acu_data_io_value_archive.ARZ
-rw-rw---- 1 mysql mysql 8.5K 2013-03-25 11:40 acu_data_io_value_archive.frm

best regards,
Joakim Byström

Archive Engine and Direct Access to Indices (no replies)

Andrew Vehlies — Mon, 25 Jun 2012 18:03:40 +0000

Hi,
I'm part of a project that right now is using Archive tables to hold log data. We are finding an increasing need to access data from these tables, but read speeds can be very slow due to the scanning of the whole tables. I know there is an index on the AUTO_INCREMENT value (`contact_id` in this case). Since there is an index, wouldn't a WHERE `contact_id` = xx clause be a quick lookup? Does it still do a complete table scan even though it's searching on an indexed field?

I believe I read on the Wikipedia page for the Archive storage engine that there were plans to add more indeces onto the Archive-typed tables. Is there any update on this? Perhaps it's something I can add to the type if it's no longer being worked on.

Select returns no rows (no replies)

Dan Kelly — Fri, 30 Dec 2011 14:14:49 +0000

I have an archive engine table that I have copied from our production server back to our lab. I performed this copy by stopping the MySQL server and copying the file system directories. On the lab system, I updated the my.ini file to point to the correct data and InnoDB directories.

MySQL starts fine, however, when performing a SELECT on this table, no results are returned. The statistics, however, appear to be correct:

+------------+---------+---------------+------------+
| table_name | engine | total_size_kb | table_rows |
+------------+---------+---------------+------------+
| bulkdata | ARCHIVE | 5382180.75 | 260991277 |
+------------+---------+---------------+------------+
1 row in set (0.00 sec)

When I perform a check table, it shows the table as corrupt:
mysql> check table dotnetlogger.bulkdata;

+-----------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+-------+----------+----------+
| dotnetlogger.bulkdata | check | error | Corrupt |
+-----------------------+-------+----------+----------+
1 row in set (0.00 sec)

I then repair the table, which apparently works:

mysql> repair table dotnetlogger.bulkdata;
+-----------------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+--------+----------+----------+
| dotnetlogger.bulkdata | repair | status | OK |
+-----------------------+--------+----------+----------+
1 row in set (0.03 sec)

After repair, it appears to have lost all my data:

+------------+---------+---------------+------------+
| table_name | engine | total_size_kb | table_rows |
+------------+---------+---------------+------------+
| bulkdata | ARCHIVE | 0.09 | 0 |
+------------+---------+---------------+------------+
1 row in set (0.00 sec)

On our production system, the data is always readable. I am wondering what gets corrupted during the copy operation. Is there a better way to copy archive data in its compressed form? How can I recover this table?

Thank you.

How to enable ARCHIVE Engine in 5.0.77? (3 replies)

Shashidhar Y — Wed, 04 Jan 2012 18:01:01 +0000

Hello guys,

I have got a very big problem on my neck.

we are using mysql 5.0.77 and couple of days back we moved our tables (which have historical data) from MYISAM engine to ARCHIVE. It went well, in fact I see files for these archive table (ARZ, ARM etc.,) in my file system. Things were good.

Couple of days back when we try to do database backup with the script, we noticed some of the table files didn't find by mysql. Diving bit deeper we came to know is all these tables are ARCHIVE tables. But we do see all table related file does exist in database folder. After doing some more investigation we came to know ARCHIVE engine is disabled on this server (This server went for schedule restart just 10 days back).

The "Show engines" command clearly shows below lines

*************************** 7. row ***************************
 Engine: ARCHIVE
Support: NO
Comment: Archive storage engine

also this ...

mysql> show variables like 'have_archive'\G
*************************** 1. row ***************************
Variable_name: have_archive
        Value: NO
1 row in set (0.13 sec)

I don't know how this ENGINE is disabled on the fly. But my goal is to make ARCHIVE table's visible and accessible.

Can any one help me how to enable ARCHIVE engine on my server (5.0.77)?

replication with archive engine (no replies)

Darin Shintani — Wed, 10 Aug 2011 21:48:13 +0000

This scenario is on v 5.1.48

I have a master db with a table using myisam. I have the same table using the archive engine on a slave db. My understanding is that the archive engine does not allow UPDATE and DELETE statements. When I run a DELETE statement against the master db table, the slave tries to execute that DELETE statement, but receives an Error 1031 (Table storage engine doesn't have this option) and the slave stops replicating. Shouldn't the slave filter the statements? Did I miss some configuration?

I was able to get around it by adding slave-skip-errors=1031, however, that does not seem to be the correct solution.

Thanks,
Darin

Partition returned CORRUPT error (1 reply)

Shafi ahmed — Tue, 28 Jun 2011 08:16:18 +0000

Dear Folks,
From the application,foll critical error reported by the user

error : Partition p530 returned error

error : Corrupt

Findings:
No errors from the database error.log
Current mysql version: 5.1.32
table of size 1.2G contains 800 partitions ALL SITS on ARCHIVE ENGINE.
database sits on Linux box with enough memory/hardware.

The current status is OK

+-------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------------------+-------+----------+----------+
| beaserv.poll_data | check | status | OK |
+-------------------+-------+----------+----------+
1 row in set (50.10 sec)

Kindly pls advise asap what could be the reason for error,any possible bug ?

Thanks
Shafi

Replication Innodb table to archive table (no replies)

Owen C — Thu, 19 May 2011 02:33:13 +0000

Is it possible to replicate a simple innodb table with int(11) primary key into a slave database where the same table uses archive engine. I've been trying to do this with 5.1 and auto_increment support but it keeps giving me a duplicate key error and never starts replicating.

CREATE TABLE `transactions` (
`transaction_id` int(11) NOT NULL AUTO_INCREMENT
...
PRIMARY KEY (`transaction_id`),
KEY `idx1_transactions` (`completed_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1862714345 DEFAULT CHARSET=utf8

CREATE TABLE `transactions` (
`transaction_id` int(11) NOT NULL AUTO_INCREMENT
...
PRIMARY KEY (`transaction_id`),
KEY `idx1_transactions` (`completed_date`)
ENGINE=ARCHIVE AUTO_INCREMENT=1862714345 DEFAULT CHARSET=utf8

Thanks,
Owen

MySQL Archive process (no replies)

pravat nayak — Mon, 14 Mar 2011 06:34:27 +0000

Hi Friends,

I am a new user of mysql.I have a requirement to set archival process of data in mysql5.1.

So please let me know how can i take data to archive in my production database.
My database having 37GB of data currently and it is increasing 3gb appx per day.

Thanks
&
Regards,
Pravat

Unique key issue with Archive Engine (1 reply)

Rameshkumar Venkatachalam — Thu, 17 Feb 2011 14:18:24 +0000

Hi,

I am having issue with Archive engine when i create table with Unique keys.

Could any one please tell me if you come across any such issues?

Here is my code.

CREATE TABLE IF NOT EXISTS SAMPLE (
SESSION_ID VARCHAR(15) NOT NULL ,
NAME VARCHAR(12) NOT NULL ,
COST VARCHAR(50) NULL ,
TYPE TINYINT(1) NOT NULL ,
CODE VARCHAR(5) NOT NULL ,
STATUS VARCHAR(15) NOT NULL,
DATE TIMESTAMP DEFAULT 0 ,
SERVICE_ID VARCHAR(20) NOT NULL
)
ENGINE=ARCHIVE partition by range(UNIX_TIMESTAMP(DATE))(partition p20110104 values less than(UNIX_TIMESTAMP('2011-01-05')), partition p20110105 values less than maxvalue);

ALTER TABLE SAMPLE ADD CONSTRAINT US_SAMPLE UNIQUE (SESSION_ID, NAME, COST, TYPE, CODE, STATUS, DATE, SERVICE_ID);

Thanks
-Ramesh-

Archive Storage Engine Config Parameters (no replies)

Sujit S — Fri, 11 Feb 2011 05:41:30 +0000

Hi All,

Does Archive Storage Engine support config parameters to change the read and log buffer size (something similar to innodb config parameters). We were moving one of our table from innodb to archive. The insert operation into the archive table shows a very big improvement. However the select operation requires the same amount of time. Since archive engine uses compressed data we were hoping to see some improvement in the select operation as well. Since this is not the case, we were thinking that increasing the read buffer size might help.

Please let me know your comments. Is there some other way to improve select performance on archive engine.

Thanks and Regards
Sujit

P.S. The table is partitioned by date , is not indexed and all the queries on it do a full table scan.

Archive table crashed and not recovering (4 replies)

Syed Zaeem Abbas — Sat, 21 May 2011 22:40:21 +0000

Dear All,

I am using MySQL 5.1.47 (64 bit) on CentOS 5.5(64bit). I have a database with archive storage engine. One of table is crashed in the database and it couldn't be repaired using 'repair table' command. Following error I can see in mysql error log file

Error 'Incorrect key file for table 'tbldatarecords'; try to repair it'

Any suggestion about hoe to repair / fix it.

Thanks a lot for support

Enable / Install / Activate Archive Engine on MySQL 5.0.77 (1 reply)

Pinak Doshi — Mon, 20 Dec 2010 14:59:36 +0000

Archive table frequently crashing (1 reply)

Mani C — Tue, 23 Nov 2010 07:56:12 +0000

Hi All,

We are running Mysqsl 5.1.34-log community server (on linux) and we have a ARCHIVE table which has around 122280263 records now. There will be more inserts to this table on daily basis.

For reporting also, we use the same table. Data on this table is split across 370 partitions. One partition or the other frequently gets corrupted and we are restoring data as and when they get corrupted.

Server has 2 CPUs and 3G RAM.

Which parameters in my.cnf are corresponds to archive table performance? We would like to tune them and see if we get a solution for this problem.

Thanks,
Mani

Diff between key_buffer n key_buffer_size ? and why it is needed ? (no replies)

Naveen Kumar Sarihaddula — Tue, 08 Jun 2010 05:41:48 +0000

key_buffer n key_buffer_size ? and why it is needed ?

Unable to repair/rebuild archive table with partition (no replies)

Andreas Thorstensson — Mon, 15 Mar 2010 10:18:35 +0000

Date and Time type in archive table (no replies)

dianbau Zhu — Wed, 23 Dec 2009 07:29:35 +0000

Hi,
I have two tables whose DDLs are:
test_datetime:
CREATE TABLE `test_datetime` (
`if_id` int(10) unsigned NOT NULL,
`coll_type_id` tinyint(3) unsigned NOT NULL,
`value_diff` bigint(20) unsigned DEFAULT NULL,
`rate` int(10) unsigned DEFAULT NULL,
`coll_ts` datetime NOT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (if_id) PARTITIONS 1024 */;

test_time
CREATE TABLE `test_time` (
`if_id` int(10) unsigned NOT NULL,
`coll_type_id` tinyint(3) unsigned NOT NULL,
`value_diff` bigint(20) unsigned DEFAULT NULL,
`rate` int(10) unsigned DEFAULT NULL,
`coll_ts` time NOT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (if_id) PARTITIONS 1024 */;

Then, i insert the same data (25,000,000 records) into these two tables except that the datetime data is convert to time data by time(coll_ts) before insert into the second table(test_time).

Last, i get the DATA_LENGTHs of these two tables from information_schema.tables, they are 118917198(test_datetime) and 121706132.

I am so surprised that i think '0000-00-00 00:00:00' is longer than '00:00:00', so is there anyone who can tell me why the table length with time column is greater than the table with datetime column.

Thanks and best regards,

Dianbo

.zip data corrupted when exported by select dumpfile (no replies)

Ricardo Faria — Thu, 03 Dec 2009 00:42:56 +0000

I have an InnoDB table storing a compress file (.zip) into two columns: Data (longblob) and Filename (text). When a export the file via select into dumpfile... I am not able to extract the file at command line. All the compress programs alert the file is corrupted, but I am able to extract them inside the windows. I read somewhere something related to the drop of a last zero of the binary file.
Is anyone have an idea how to solve such a problem?
tks.
R.

Enable deletion for archive engine. (2 replies)

David Drouin — Fri, 06 Nov 2009 14:52:28 +0000

The archive engine should obviously support deletion. It's somewhat ridiculous that it's prevented - seems almost like spin on a lack of an ability than a feature. There are often situations where you would like to archive data for a few years, a decade maybe but not necessarily "forever". So for those use cases (the majority) we're still left wanting. Live -> Archive -> Delete.

Wouldn't it be far more reasonable to have the ability to toggle whether deletes are allowed for a particular table perhaps upon creation only - or to be clearer supply effectively a copy of the archive engine with deletion disabled called perhaps the audit storage engine to facilitate this where needed?

Archive Engine File Limit (1 reply)

6ky 8ngel — Wed, 24 Jun 2009 15:35:20 +0000

Hi,

I just want to know the archive engine file limit for the database.

Thanks

Failed creating Archive table (1 reply)

Jonas Nylund — Tue, 14 Apr 2009 19:00:11 +0000

Hi,

Im trying to create this tabel with storage engine Archive. But it failes and i cant find any good information about why.

Its failing with this error message:
ERROR 1005 (HY000): Can't create table 'apricot.RPT_TR_JN' (errno: -1)
mysql>

This is my table definition. Im running Mysql 5.1.33 on CenOS.

mysql> CREATE TABLE `RPT_TR_JN` (
-> `TIME_STAMP` datetime DEFAULT NULL,
-> `RECORD_SOURCE` int(11) DEFAULT NULL,
-> `SUBSCRIBER_ID` varchar(64) DEFAULT NULL,
-> `PACKAGE_ID` smallint(6) DEFAULT NULL,
-> `SERVICE_ID` int(11) DEFAULT NULL,
-> `PROTOCOL_ID` smallint(6) DEFAULT NULL,
-> `SAMPLE_SIZE` int(11) DEFAULT NULL,
-> `PEER_IP` bigint(20) DEFAULT NULL,
-> `PEER_PORT` int(11) DEFAULT NULL,
-> `ACCESS_String` varchar(1024) DEFAULT NULL,
-> `INFO_String` varchar(1024) DEFAULT NULL,
-> `SOURCE_IP` bigint(20) DEFAULT NULL,
-> `SOURCE_PORT` int(11) DEFAULT NULL,
-> `INITIATING_SIDE` tinyint(4) DEFAULT NULL,
-> `END_TIME` int(11) NOT NULL,
-> `MILISEC_DURATION` bigint(20) DEFAULT NULL,
-> `TIME_FRAME` tinyint(4) DEFAULT NULL,
-> `UPSTREAM_VOLUME` bigint(20) DEFAULT NULL,
-> `DOWNSTREAM_VOLUME` bigint(20) DEFAULT NULL,
-> `SUBS_CNT_ID` int(11) DEFAULT NULL,
-> `GLBL_CNT_ID` int(11) DEFAULT NULL,
-> `PKG_USG_CNT_ID` int(11) DEFAULT NULL,
-> `IP_PROTOCOL` smallint(6) DEFAULT NULL,
-> `PROTOCOL_SIGNATURE` int(11) DEFAULT NULL,
-> `ZONE_ID` int(11) DEFAULT NULL,
-> `FLAVOR_ID` int(11) DEFAULT NULL,
-> `FLOW_CLOSE_MODE` smallint(6) DEFAULT NULL,
-> KEY `RPT_TR_I1` (`END_TIME`)
-> ) ENGINE=Archive DEFAULT CHARSET=latin1;

Delete old records (no replies)

Joss BH — Fri, 27 Feb 2009 07:13:26 +0000

Hi All,

I am required to store large amount of textual data. the information source is CSV files holding CDRs (call details records).
The data is accessed infrequently for data mining but needs to be kept for few months, this leads me to using the Archive engine with partitions per interval (month, week, according to table scan performance).

The only issue I need to figure out is how to remove old data after a retention period has expired, as I know 'Delete' is not supported by this engine.
Conversion to another engine is also impractical as after decompression the data is too large to fit on disk.
What is the best practice for such scenario? dropping the partition for the expired interval?

ARCHIVE crash after power failure (no replies)

Piotr Czachur — Mon, 02 Feb 2009 09:25:20 +0000

Hello!

Do you guys use ARCHIVE on production systems? I want to warn you, that last week after power failure, the only one ARCHIVE table I was using, caused my server to be unavailable.
2 CPUs were working 100% while MySQL was trying to repair that table. It was impossible to run any query, because server was so busy. In result data in table was corrupted, despite `check table` successful result.
What's more surprising, in some rows, I have found /var/lib/mysql dir listing, and what is also very surprising, select * from this table is causing MySQL do die. Details on this bug can be founf here: http://bugs.mysql.com/bug.php?id=29250

ARCHIVE table very slow... (1 reply)

Marcin Piatek — Wed, 21 Jan 2009 21:41:32 +0000

Hi
I'm testing the ARCHIVE storage engine and i vase some performance problems...

Test table consist of 30 columns:
- 1 blob,
- 1 bigint (primary index),
- 24 int,
- 1 datetime,
- 3 float.

Currently I've got 13000000 records inside, they use 13GB on MyISAM and only 3GB on ARCHIVE.

When i was testing random selects from this table I've got results on selecting 1 row:
MyISAM : 25ms average
ARCHIVE: 47615ms average.

Am I doing something wrong or does ARCHIVE cannot search for given KEY and must read whole table?

Archive SELECT Speed vs. MyISAM (1 reply)

Jamie Thompson — Tue, 25 Nov 2008 16:59:51 +0000

What is the speed difference between the archive storage engine and MyISAM, as the Archive compresses the row data on insert, based on SELECT statements?

Is there much in it?

Many thanks.