• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3843
  • 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
 
UmeshCommented:
## 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
 
UmeshCommented:
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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
 
UmeshCommented:
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
 
UmeshCommented:
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
 
UmeshCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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