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:Microsoft Windows (2003 Server)
Assigned to: CPU Architecture:Any
Tags: CHECK;not enough memory

[15 Oct 2009 14:38] nicolas tricart
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.
[15 Oct 2009 14:48] MySQL Verification Team
Thank you for the bug report. What is approximately the table data size to run a test and to be repeatable?. Thanks in advance.
[15 Oct 2009 15:08] nicolas tricart
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.
[18 Nov 2009 7:09] Sveta Smirnova
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.
[18 Nov 2009 7:10] Sveta Smirnova
See bug #15089 for details.
[18 Nov 2009 8:59] nicolas tricart
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;
[18 Nov 2009 9:04] nicolas tricart
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 ???
[18 Nov 2009 9:19] Sveta Smirnova
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.