MySQL Got packet bigger than 'max_allowed_packet'

[owen@accountis]

Hi

We are using mysql 5.0.42 and are getting this error when trying to
insert records of over 5M. We have set max_allowed_packet as per the
advice on http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html
to be 1G but to no avail althought this did allow the direct sql to
suceed. Does django ignore the max_allowed_packet setting in my.cnf?
if so how can it be increased?

File "/home/accountis/dhl.be/eipp/branches/release_2.1/AccountisHub/
AccountisHubApp/models.py", line 972, in insertDocument
a.save()
File "/home/accountis/nhs/python/lib/python2.4/site-packages/
Django-0.95-py2.4.egg/django/db/models/base.py", line 203, in save
File "/home/accountis/nhs/python/lib/python2.4/site-packages/
Django-0.95-py2.4.egg/django/db/backends/util.py", line 12, in execute
File "/home/accountis/nhs/python/lib/python2.4/site-packages/
Django-0.95-py2.4.egg/django/db/backends/mysql/base.py", line 35, in
execute
File "/home/accountis/dhl.be/python/lib/python2.4/site-packages/
MySQLdb/cursors.py", line 163, in execute
self.errorhandler(self, exc, value)
File "/home/accountis/dhl.be/python/lib/python2.4/site-packages/
MySQLdb/connections.py", line 35, in defaulterrorhandler
raise errorclass, errorvalue
OperationalError: (2020, "Got packet bigger than 'max_allowed_packet'")

[Martin Winkler]

Am Tue, 03 Jul 2007 15:07:14 -0000
schrieb "owen@accountis" <owen2m...@googlemail.com>:

> We are using mysql 5.0.42 and are getting this error when trying to
> insert records of over 5M.

I may sound nosy, and apologize for that. But don't you think that you
have a software design bug when one record in a table exceeds 5 MB?

If you have binary data, why don't you use FileField and ImageField?
That way you'd store the large data in the filesystem and only have a
reference to the file in the database.

Just my 2 eurocent

Martin

[Carl Karsten]

Same reason text data isn't stored in a text file. :)

I will admit there are pros and cons to where you store data, but I would not
shy away from storing it in the db.

"The largest possible packet that can be transmitted to or from a MySQL 5.1
server or client is 1GB."

just need to make some adjustments:

http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html

Carl K

[Martin Winkler]

Am Tue, 3 Jul 2007 17:34:33 +0200
schrieb Martin Winkler <m...@agamisystems.eu>:

> If you have binary data, why don't you use FileField and ImageField?
> That way you'd store the large data in the filesystem and only have a
> reference to the file in the database.

Maybe I should clarify the problems as far as I am aware of them:

Whenever you work with the result of MyModel.objects.filter()
or .all() or even .get(), django loads the complete record(s) including
all fields into memory. So if you want to present just a list of titles
of 20 of your records, all these 20 records will be loaded into memory -
which might be a HUGE amount of data. (As long as you don't play around
with specialized Q() objects, as far as I know)

Furthermore Django is not really meant to serve huge amounts of (more
or less) static data. That's what your webserver is for. Your webserver
might be able to continue a download which stopped in the middle at
the correct location etc.

So for me it makes much more sense to store the binary data in the
filesystem because it has much less overhead. The only disadvantage I
can see here is that for backups you not only have to save your
database, but also one directory (including subdirs) of your file
system.

Martin

[owen@marshall]

Thanks Martn and Carl.

I would really like to avoid having to re design the architecture of
the application at this stage (its a case of the requirements changing
after roll out), althougth thanks for the comments re serving large
data volumes.
I have made the adjustments suggested in http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html
prior to making these loading the insert directly into mysql failed,
afterwards it suceeded but the insert through django still fails hence
my question - Does django ignore the max_allowed_packet setting in
my.cnf?
if so how can it be increased so that django uses it?

Thanks again

Owen

[Carl Karsten]

owen@marshall wrote:
> Thanks Martn and Carl.
>
> I would really like to avoid having to re design the architecture of
> the application at this stage (its a case of the requirements changing
> after roll out), althougth thanks for the comments re serving large
> data volumes.
> I have made the adjustments suggested in http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html
> prior to making these loading the insert directly into mysql failed,
> afterwards it suceeded but the insert through django still fails hence
> my question - Does django ignore the max_allowed_packet setting in
> my.cnf?
> if so how can it be increased so that django uses it?

django uses python's mysqldb module, which uses mysql's client library.

My guess is the my.conf you edited is not the one being used.

I would write a little 5 line .py that just connects and inserts a 5m wad of
data. my guess is it will fail.

I tried to track down exactly where the .conf file got read, and ran out of
time. But I did see that you can specify it as part of the connection:

89 read_default_file
90 file from which default client values are read

http://mysql-python.svn.sourceforge.net/viewvc/mysql-python/trunk/MySQLdb/MySQLdb/connections.py?view=markup

I rushed this post and just psoted what I had so far, so let me know if you need
more details.

Carl K

[owen@marshall]

Hello

I wrote a test case using mysldb with direct python calls and using
the django model classes to insert various sized binary data into a
blob field. First I changed the value of max_allowed_packets in my.cnf
to prove/disprove that mysqldb/django are using it. Conclusion they
do use my.cnf and setting it to 1k will always cause the error. So I
set it to 32M and tried loading the large data.
Both the mysqldb direct call and django model class routes fail at
some point between 10M and 25M with the max packet error. Seems like a
bug in mysqldb as the same mysql insert works up to 30M. I'm going to
see if there is a later version to try.

Thanks for the help

Owen

On Jul 4, 4:27 pm, Carl Karsten <c...@personnelware.com> wrote:
> owen@marshall wrote:
> > Thanks Martn and Carl.
>
> > I would really like to avoid having to re design the architecture of
> > the application at this stage (its a case of the requirements changing
> > after roll out), althougth thanks for the comments re serving large
> > data volumes.

> > I have made the adjustments suggested inhttp://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html

> > prior to making these loading the insert directly into mysql failed,
> > afterwards it suceeded but the insert through django still fails hence
> > my question - Does django ignore the max_allowed_packet setting in
> > my.cnf?
> > if so how can it be increased so that django uses it?
>
> django uses python's mysqldb module, which uses mysql's client library.
>
> My guess is the my.conf you edited is not the one being used.
>
> I would write a little 5 line .py that just connects and inserts a 5m wad of
> data. my guess is it will fail.
>
> I tried to track down exactly where the .conf file got read, and ran out of
> time. But I did see that you can specify it as part of the connection:
>
> 89 read_default_file
> 90 file from which default client values are read
>

> http://mysql-python.svn.sourceforge.net/viewvc/mysql-python/trunk/MyS...

[owen@marshall]

Hi

fixed the problem mysql for python 1.2.2 did the trick
http://sourceforge.net/project/showfiles.php?group_id=22307&package_id=15775

Thanks again
Owen

On Jul 8, 9:33 pm, "owen@marshall" <owen2marsh...@googlemail.com>
wrote: