| Bug #33978 | optimize table query on archive table leads to loss of data in text/blob column | ||
|---|---|---|---|
| Submitted: | 22 Jan 2008 10:55 | Modified: | 6 Feb 2008 9:17 |
| Reporter: | Jayant Kumar | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Archive storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.1.22 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | archive storage engine, BLOB, data loss, optimize table, text | ||
[22 Jan 2008 11:58]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[25 Jan 2008 13:13]
Sergey Vojtovich
Hi Valeriy,
I failed to repeat this problem on FC6-32bit using latest 5.1 sources (5.1.24bk). I used BUILD/compile-pentium-debug-max and BUILD/compile-pentium-max scripts to build mysqld.
Could you provide additional information and re-verify this bug? Following is result set that I got:
create table test_arch (id int, val varchar(255), txt text);
alter table test_arch engine=ARCHIVE;
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
insert into test_arch values('1', 'thi is the summary', ' this is the actual text... blah,blah');
insert into test_arch values('2', 'thi iz tze zummary', ' this iz tze actual tezt... blah,blah, 123, 12344');
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
optimize table test_arch;
Table Op Msg_type Msg_text
test.test_arch optimize status OK
select id, val, txt 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
[6 Feb 2008 9:17]
Valeriy Kravchuk
I was not able to repeat this bug with latest 5.1.24-BK also. openxs@linux:~/mysql-5.1> bk changes | head ChangeSet@1.2512, 2008-01-27 14:31:49-07:00, tsmith@ramayana.hindu.god +2 -0 Merge ramayana.hindu.god:/home/tsmith/m/bk/maint/50 into ramayana.hindu.god:/home/tsmith/m/bk/maint/51 MERGE: 1.1810.3470.10 So, I assume it is fixed somehow.
Description: 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. How to repeat: 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)