Large Blobs and MySQL..File is stuck

bastien

Having a real issue here with storing files in the table where the blob file size is voer 16Meg. Anything under we can get in and out with no problems. Anything over 16M is a problem. We can and have chunked the data thru a loop to get it into the table. But once in there, getting it out is proving problematical.

The error is 'exceeded max_allowed_packets'. Yet the max_allowed_packets setting is 32M.

PHP is set to allow 32M files in upload as well.

We have tried with both

ASP / Mysql 4.0.x / mySQL Connector 3.51
PHP 4.3.x / MySQL 4.0.x

Is there a mysql client setting that needs to be changed somewhere?

Is there something in the connector that can be adjusted?

AstroTeg

Hint: let the file system do what it does best and store files. Let the database do what it does best and store data.

Take the files out of the database and store them locally. In your table(s), have a field keep track of the path and file name where you stored the file. The benefit is: file system automatically handles the caching, you work around blobs and their limits.

bastien

That would be fine it we had a simple system. Currently with multiple app servers and multiple db servers and a load balancer. There is no guarantee that users will end up on the same server to be able to access this file if its on the file system. The app servers are not networked for security reasons.

chyan

have you try to change your data type to LONGBLOB?

tsinka

Hi,

that should work. Please post my.cnf

Thomas

bastien

it is a longblob....

using my.ini

[mysqld]
basedir=C:/mysql
#bind-address=192.168.100.10
datadir=e:/mysql/data
#language=C:/mysql/share/your language directory
#slow query log#=
#tmpdir#=
#port=3306
key_buffer=32M
max_allowed_packet=32M
max_connections=2000
[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-nt.exe

QueryInterval=10

tsinka

Ok,

first of all execute

SHOW VARIABLES;

and check if the value of max_allowed_packet is correct.

I don't know exactly which version of libmysql.dll comes with php4 but if it is one of the 3.x libraries then the limit might still be 16M.

Try to replace libmysql.dll in system32 with the one that comes with MySQL 4.0.X (not sure if that works) and check if that solves the problem.

Thomas

chyan

maybe your max_allowed_packet had not been set to 32M

check your my.ini:

in line where max_allowed_packed exist you wrote it:

max_allowed_packet=32M

it must be

set-variable=max_allowed_packet=32M

in order to get it work

bastien

Originally posted by chyan
maybe your max_allowed_packet had not been set to 32M

check your my.ini:

in line where max_allowed_packed exist you wrote it:

max_allowed_packet=32M

it must be

set-variable=max_allowed_packet=32M

in order to get it work

no, it does not have to be set-variable=max_allowed_packet=32M

here is a portion of the system variables

key_buffer_size 33554432
large_files_support ON
local_infile ON
lower_case_table_names ON
max_allowed_packet 33553408

you'll see the max_allowed_packet is 32M

tsinka

Hi,

there should be an option in the MyODBC connection screen:

Don't set any packet limit for results and parameters.

Check if that option is checked and try it again with MyODBC.

Seems like the MyODBC driver itself sets a limit, too.

Thomas

Sneef

I have the same problem... anything over 16MB won't go in.

You probably get the same error message "MySQL server has gone away" if you have the query exit(mysql_error()), right?