MySQL Bugs: #48085: CHECK TABLE makes "not enough memory" if done on many tables

Bug #48085	CHECK TABLE makes "not enough memory" if done on many tables
Submitted:	15 Oct 2009 14:38	Modified:	18 Nov 2009 9:20
Reporter:	nicolas tricart	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.39	OS:	Windows (2003 Server)
Assigned to:		CPU Architecture:	Any
Tags:	CHECK;not enough memory

Description:
I have a database with many big tables (about 900) created as :
CREATE TABLE `docblob_xxxx` (\n  `DOCBLOB_ID` int(10) unsigned NOT NULL DEFAULT '0',\n  `DOCBLOB_BLOB` longblob NOT NULL,\n  PRIMARY KEY (`DOCBLOB_ID`)\n) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=14360 AVG_ROW_LENGTH=598145

If I do a "CHECK TABLE docblob_1323", the table is OK; 
But if I run a script that checks all the tables, docblob_1323 is not good, we have an error "Not enough memory (18328575) for blob at 17004916"; after this error message about 80 % of the table checked after have a similar error.

mysqlcheck have the same result.

REPAIR command destroy all data in the table if we dont re-start mysql service after the checks;

We made a complete dump and restore and the problem is the same.

How to repeat:
Very difficult to repeat but we tested on different server and we have the same problem with this specific database; No problem with MySQL 4.0.

Thank you for the bug report. What is approximately the table data size to run a test and to be repeatable?. Thanks in advance.

I sent you files with data size of the table been corrupt and size of .MYD of other tables. I hope this is what you expect ?

I am not sure data size are responsible, I have many databases with more data sizes and/or more tables, the problem seems to append on specific data.

Thank you for the feedback.

This can happen because MySQL keeps BLOB fields in memory for faster access. Please add FLUSH TABLES before every CHECK TABLE statement and inform us if it solves the problem.

See bug #15089 for details.

I had FLUSH TABLE before every CHECK TABLE and it solved the problem;

Is myisamchk.exe will work in a future version ?

Thank you for your help;

It looks like I have the same problem then Bug#15089;

Does that mean I have to do a Flush table after every querry to a blob column as you told me to before CHECK TABLE ???

Thank you for the feedback.

Closing as duplicate of bug #15089.

Not, you should not do FLUSH TABLES after every query accessing BLOB value, but you should be aware about this problem and tune your application accordingly.