I have a InnoDB table that holds large BLOB objects containing PDF
files, some of which are up to 100 megs.

I have max_allowed_packet set to 512MB for mysql. I back up with these
options:

mysqldump --max-allowed-packet=512M \
--quick \
-uuserid -ppassw0rd mydb \
and yet I get this error. What gives???

mysql is 5.1.

thanks

i

On 2011-02-17 18:50, Ignoramus5243 wrote:
Can you post the table definition?


/Lennart

[...]

On 2011-02-17, Lennart Jonsson <erik.lennart.jonsson (AT) gmail (DOT) com> wrote:

CREATE TABLE $SILENT_TABLE_CREATION manuals (
id INTEGER UNIQUE AUTO_INCREMENT,
name VARCHAR( 64 ), # TM-123-4567-89
title BLOB, # FOOBAR-005B OPERATOR'S MANUAL AND PARTS LIST
original_data $PDF_BLOB_TYPE,# %PDF... ... ... ...
processed_data $PDF_BLOB_TYPE,# %PDF... ... ... ...
author VARCHAR( 32 ), # USG for govt
source VARCHAR( 32 ), # logsa
status VARCHAR( 16 ), # failed|downloaded|watermarked

category1 INTEGER,
category2 INTEGER,
category3 INTEGER,
category4 INTEGER,

last_update DATETIME,
INDEX index_by_name( name ),
UNIQUE INDEX index_by_name_and_author( name, author )

) ENGINE=$DEFAULT_ENGINE;

On 17-02-11 19:36, Ignoramus5243 wrote:
can you post the output of

SELECT
length(title),
length(original_data),
length(processed_data),
length(title)+length(original_data)+length(process ed_data),
FROM manuals
ORDER BY length(title)+length(original_data)+length(process ed_data) DESC
LIMIT 5

I'll bet the largest one is >512Mb ?

--
Luuk

On 2011-02-17, Luuk <Luuk (AT) invalid (DOT) lan> wrote:
You got it!

length(title) length(original_data) length(processed_data)
length(title)+length(original_data)+length(process ed_data)
99 293169173 293139129 586308401
146 172871136 173402308 346273590
511 150428397 151113925 301542833
512 145139999 145938507 291079018
506 144851994 145596382 290448882
You have mail in /var/mail/evil1

I increased the max allowed packet some more and am re-running.

Thank you!!!!!!!!!!!!!!!!!

i

On Thu, 17 Feb 2011 11:50:40 -0600, Ignoramus5243 wrote:
You may also want to look at --hex-blob as well, even though it will
make your output even bigger. It would be annoying to have solved this
problem only to end up with something that couldn't be imported back
in...

--
8. After I kidnap the beautiful princess, we will be married
immediately in a quiet civil ceremony, not a lavish spectacle in
three weeks' time during which the final phase of my plan will be
carried out. --Peter Anspach's Evil Overlord list

On 2011-02-17, Peter H. Coffin <hellsop (AT) ninehells (DOT) com> wrote:
What happened is that after I increased the limit, mysqldump croaked
with a "cannot allocate memory" error -- even though I have 4GB of RAM
and 5 GB of swap.

This is quite disappointing.

In any case, what I will do is I will delete the top 3 (by size)
records and I will try again.

Just that -- deleting three records with size over 145 megs -- took 18
minutes.

I am coming to a sad conclusion that mysql simply was not designed to
deal with medium large records. These are not even that big.

Anyway, with the top 3 records gone, I should fit within 512M. I am
trying to run backup again.

Good point on suggesting to try to restore from a backup -- I should
take nothing for granted.

i