Bug #4214 Table corruption with myisampack and large BLOB objects
Submitted: 18 Jun 2004 22:15 Modified: 8 Sep 2005 17:09
Reporter: Miguel Solorzano Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.1.2 OS:Microsoft Windows (Windows)
Assigned to: Ramil Kalimullin CPU Architecture:Any

[18 Jun 2004 22:15] Miguel Solorzano
Description:
We are having a problem with table corruption when a large BLOB is inserted into a table column and the table is compressed using myisampack.

When we insert a large BLOB into a table, the BLOB can be selected from the table before the table is compressed. However, when the table is compressed using myisampack and then running myisamchk after compression, an error is returned and the table is corrupt.
When we then run myisamchk without the -q option, it removes the row containing the large BLOB and then the table works correctly.

We are using the following versions of myisampack and myisamchk:
myisampack Ver 1.22 for Win95/Win98 on i32
myisamchk Ver 2.7 for Win95/Win98 at i32

How to repeat:
C:\mysql\bin>mysql -uroot cat
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.2-alpha-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE masimage1 (
    -> GRAPHICCONTROLNUMBER varchar(25) NOT NULL default '',
    -> IMAGETYPE char(1) NOT NULL default '',
    -> GRAPHICUPDATEDATE date NOT NULL default "1900-01-01",
    -> IMAGE longblob NOT NULL,
    -> PRIMARY KEY (GRAPHICCONTROLNUMBER,IMAGETYPE)
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql> insert into masimage1 values ("33333-id","1","2004-06-17",
    -> load_file("c:/mysql/bin/image.bmp"));
Query OK, 1 row affected (4.35 sec)

mysql> insert into masimage1 values ("33334-id","1","2004-06-17",
    -> load_file("c:/mysql/bin/image2.bmp"));
Query OK, 1 row affected (1.10 sec)

C:\mysql\data\cat>myisampack -w -v masimage1.MYI
Compressing masimage1.MYD: (2 records)
- Calculating statistics

normal:      2  empty-space:       0  empty-zero:         0  empty-fill:   0
pre-space:   0  end-space:         1  intervall-fields:   0  zero:         0
Original trees:  4  After join: 1
- Compressing file
Min record length: 2708142   Max length: 6444597   Mean total length: 4576702
84.74%
Remember to run myisamchk -rq on compressed tables

C:\mysql\data\cat>myisamchk -rq --analyze --sort-index -v masimage1.MYI
- check record delete-chain
- recovering (with sort) MyISAM-table 'masimage1.MYI'
Data records: 2
- Fixing index 1
  - Searching for keys, allocating buffer for 5 keys
Found wrong record at 650
MyISAM-table 'masimage1.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the
--quick (-q) flag
[25 Jun 2004 14:27] Sergei Golubchik
Miguel, do you mean that *any* large blob will do, or I need your specific image.bmp and image2.bmp files to repeat the corruption ?
[25 Jun 2004 15:16] Miguel Solorzano
Yes it was necessary to insert 2 rows (I noticed that the customer did
the same) the reason was that the myisampack gives a message that the
table is very small for to compress with only one row.
[26 Jun 2004 22:15] Sergei Golubchik
Yes, myisampack currently do not support blobs larger than 2^24 (3 bytes for length).
Simplified test case:

CREATE TABLE t1 ( GRAPHICCONTROLNUMBER varchar(25) NOT NULL default '', IMAGETYPE char(1) NOT NULL default '', GRAPHICUPDATEDATE date NOT NULL default "1900-01-01", IMAGE longblob NOT NULL, PRIMARY KEY (GRAPHICCONTROLNUMBER,IMAGETYPE));
insert into t1 values ("33333-id","1","2004-06-17",repeat("a",16777216)), ("33334-id","1","2004-06-17",'');

myisampack it - myisamchk will fail.
Put 16777215 as the length - myisampack, myisamchk will work ok.

see save_pack_length() in myisampack.c and mi_packrec.c

Let's see what we can do here...
[8 Jul 2005 18:46] Michael Pechner
I am have issues as well with a table with a text instead of a blob field in it.

When I run myisamchk -r as recommended, my table gets trashed. All data gone.
I am running on FreeBSD not windows.
[5 Sep 2005 11:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/29317
[5 Sep 2005 14:06] Ramil Kalimullin
fixed in 4.1.15
[8 Sep 2005 17:09] Paul Dubois
Noted in 4.1.15, 5.0.13 changelogs.