• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4175
  • Last Modified:

MySQL table keeps crashing - not enough memory for blob

I have a table that keeps on crashing (iif_uploads). It's main function is to house uploaded reports. Repaired it 3 times already, crashes every time within an hour (possibly when someone uploads a new report). I have to repair it by doing a "myisamchk e:\sqldata\mysql\iif_intranet\iif_uploads.myi -r". The database server is run on Windows.
moz-screenshot-1.jpg
moz-screenshot-2.jpg
moz-screenshot-3.jpg
0
smueller72
Asked:
smueller72
  • 6
  • 5
1 Solution
 
smueller72Author Commented:
Forgot my main question -- why would this table keep crashing? It had been working correctly for months without any problems, up until a few days ago.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
## Backup table & run myisamchk on it with --max-record-length parameter.. (make sure table is not being used.. you may want to bring down MySQL during this operation)

--max-record-length=len

Skip rows larger than the given length if myisamchk cannot allocate memory to hold them. currently this is 1MB (1048576)..try if your blob column is bigger than this..
myisamchk --max-record-length=1048576 -r -f e:\sqldata\mysql\iif_intranet\iif_uploads.myi

Open in new window

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
smueller72Author Commented:
I will try that.

I tried running an OPTIMIZE query on the table and received the following:
Table                     Op        Msg_type  Msg_text                                                                  
------------------------  --------  --------  --------------------------------------------------------------------------
iif_intranet.iif_uploads  optimize  error     Not enough memory for blob at 1300185844 (need 22886386)                  
iif_intranet.iif_uploads  optimize  error     Keyblock size at page -1 is not correct.  Block length: 254  key length: 2
iif_intranet.iif_uploads  optimize  status    Operation failed     

Open in new window

0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Ok.. pls let me know once you run

myisamchk --max-record-length=1048576 -r -f e:\sqldata\mysql\iif_intranet\iif_uploads.myi
0
 
smueller72Author Commented:
Actually, that fix will not apply as it looks as that is for normal 'BLOB' fields. The field in this database is a LONGBLOB field -- just about every record in the database is larger than 1MB.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
But you can use more than 1MB to skip right? Pls post the output of below & table strucure.. I can't see in attached image..

SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
0
 
smueller72Author Commented:
myisamchk is correctly running just using:
myisamchk e:\sqldata\mysql\iif_intranet\iif_uploads.myi -r
(it doens't need the max-record-length param because it is being correctly repaired)

The problem is with mysql crashing upon someone uploading something to the database -- not that it is not properly being repaired (because it is).
0
 
smueller72Author Commented:
I managed to successfully alter the field to MEDIUMBLOB from LONGBLOB -- and optimized table. We'll see if that works.
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Ok.. let me know how it goes..

Also, I suggest you to put this under mysqld group my.ini file

myisam-recover=FORCE,BACKUP
0
 
smueller72Author Commented:
The table has now been up for more than 12 hours so I am assuming the LONGBLOB caused the problem. Thank you so much for the suggestion for myisam-recover --- I've been looking for something like this for a long time. Definitely implementing it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now