MySQL Forums
Forum List  »  Archive Storage Engine

text/blob columns lost on optimizing archive table
Posted by: Jayant Kumar
Date: January 22, 2008 03:06AM

Hi,

I created an archive table and populated it with some data - text, varchar and int. Then i optimized the table. After which i could get the int and varchar data back on running select, but if the text/blob data is lost.

Please suggest a solution for this problem...

following are the set of commands that i ran on mysql-5.1.22-rc.

mysql> create table test_arch (id int, val varchar(255), txt text);
Query OK, 0 rows affected (0.04 sec)

mysql> alter table test_arch engine=ARCHIVE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test_arch;
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------+
| test_arch | CREATE TABLE `test_arch` (
`id` int(11) DEFAULT NULL,
`val` varchar(255) DEFAULT NULL,
`txt` text
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 |
+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> insert into test_arch values('1', 'thi is the summary', ' this is the actual text... blah,blah');
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_arch values('2', 'thi iz tze zummary', ' this iz tze actual tezt... blah,blah, 123, 12344');
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_arch;
+------+--------------------+---------------------------------------------------+
| id | val | txt |
+------+--------------------+---------------------------------------------------+
| 1 | thi is the summary | this is the actual text... blah,blah |
| 2 | thi iz tze zummary | this iz tze actual tezt... blah,blah, 123, 12344 |
+------+--------------------+---------------------------------------------------+
2 rows in set (0.01 sec)

mysql> optimize table test_arch;
+------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+------------------+----------+----------+----------+
| newrdx.test_arch | optimize | status | OK |
+------------------+----------+----------+----------+
1 row in set (0.00 sec)

mysql> select id, val, txt from test_arch;
+------+--------------------+---------------------------------------------------+
| id | val | txt |
+------+--------------------+---------------------------------------------------+
| 1 | thi is the summary | ry% ry% ry% ry% ry% ry% ry% ry% ry% r |
| 2 | thi iz tze zummary | ry1 ry1 ry1 ry1 ry1 ry1 ry1 ry1 ry1 ry1 ry1 ry1 r |
+------+--------------------+---------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select id, val, txt from test_arch\G
*************************** 1. row ***************************
id: 1
val: thi is the summary
txt: ry%
*************************** 2. row ***************************
id: 2
val: thi iz tze zummary
txt: ry1
2 rows in set (0.00 sec)

Which is quite strange, considering the fact that data being archived should not be lost...

Am i missing anything ??

Options: ReplyQuote


Subject
Views
Written By
Posted
text/blob columns lost on optimizing archive table
9651
January 22, 2008 03:06AM


Sorry, you can't reply to this topic. It has been closed.

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.