Page 1 of 1
PDO MySQL max_allowed_packet
Posted: Tue Mar 17, 2009 11:53 am
by inghamn
Is there a way to insert large amounts of text (~3M) into MySQL using PDO, without changing the my.cnf settings?
It seems like I should be able to do a
Code: Select all
$pdo->query('set max_allowed_packet=4*1024*1024');
But inserting after doing that still runs into the error:
Communication link failure: 1153 Got a packet bigger than 'max_allowed_packet' bytes
Here's what I've tried so far:
Code: Select all
$query = $pdo->prepare('set max_allowed_packet=4*1024*1024');
$query->execute();
$query = $pdo->prepare('insert entries values(?,now(),?,?,?)');
$query->bindParam(1,$this->id);
$query->bindParam(2,$script);
$query->bindParam(3,$type);
$query->bindParam(4,$message,PDO::PARAM_LOB);
$query->execute();
But without setting the max_allowed_packet on the server in /etc/my.cnf, I continue to get the error.
Is there some mystical PDO parameter or configuration I'm missing?
Re: PDO MySQL max_allowed_packet
Posted: Tue Mar 17, 2009 5:05 pm
by VladSun
inghamn wrote:Code: Select all
$pdo->query('set max_allowed_packet=4*1024*1024');
Isn't it:
[sql]SET @@max_allowed_packet:=4*1024*1024[/sql]
Re: PDO MySQL max_allowed_packet
Posted: Wed Mar 18, 2009 8:29 am
by inghamn

No, the @@ are used when selecting the variables.
When using SET, you just use the variable names directly.
Re: PDO MySQL max_allowed_packet
Posted: Wed Mar 18, 2009 11:31 am
by VladSun
inghamn wrote:
No, the @@ are used when selecting the variables.
When using SET, you just use the variable names directly.
Are you sure?
http://dev.mysql.com/doc/refman/5.1/en/set-option.html
Examples:
SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;
Also:
http://dev.mysql.com/doc/refman/5.1/en/ ... wed_packet
As of MySQL 5.1.31, the session value of this variable is read only. Before 5.1.31, setting the session value is allowed but has no effect.
Re: PDO MySQL max_allowed_packet
Posted: Wed Mar 18, 2009 2:26 pm
by inghamn
VladSun wrote:
Are you sure?
Yes, the @@ when setting is optional. The default is to set the session variable:
To indicate explicitly that a variable is a global variable, precede its name by GLOBAL or @@global.. The SUPER privilege is required to set global variables.
To indicate explicitly that a variable is a session variable, precede its name by SESSION, @@session., or @@. Setting a session variable requires no special privilege, but a client can change only its own session variables, not those of any other client.
LOCAL and @@local. are synonyms for SESSION and @@session..
If no modifier is present, SET changes the session variable.
VladSun wrote:
Also:
http://dev.mysql.com/doc/refman/5.1/en/ ... wed_packet
As of MySQL 5.1.31, the session value of this variable is read only. Before 5.1.31, setting the session value is allowed but has no effect.
SONOFA...that's it! I'm on 5.0.77 - haven't jumped to the 5.1 line because I haven't needed any of the clustering features. But true enough, you're reading from the 5.1 documentation and it does, in fact, mention that, oh yeah, in 5.0 settining max_allowed_packet as a session variable is ignored and does nothing. Never mind that,
in the 5.0 documentation, this is never mentioned.
You can compare the documentation yourself:
http://dev.mysql.com/doc/refman/5.1/en/ ... wed_packet
http://dev.mysql.com/doc/refman/5.0/en/ ... wed_packet
This sucks, I guess I have to upgrade to 5.1 and try it all again.
Re: PDO MySQL max_allowed_packet
Posted: Wed Mar 18, 2009 2:31 pm
by VladSun
Yes, I meant "Are you sure you don't want to set it to global and it's only for SELECT statement"

I can see in the examples I've posted that "SET var" is ok ... but for sessions
Can't you set it in mysql config files instead of upgrading?
Re: PDO MySQL max_allowed_packet
Posted: Thu Mar 19, 2009 7:45 am
by inghamn
VladSun wrote:
Can't you set it in mysql config files instead of upgrading?
Heh, well yeah - I'm running with it set in my.cnf right now. But I only need a 4M max_allowed_packet for one query in one application on the server. I don't quite like the idea of leaving that value large the rest of the time.
But I do have it working by setting it globally right now. I'm going to try upgrading and then test to see if I can get it working just for the session.
Re: PDO MySQL max_allowed_packet
Posted: Thu Mar 19, 2009 8:41 am
by inghamn
Argh! MySQL 5.1.32 refuses to look in /usr/local/mysql, I had been avoiding 5.1 releases because of wierdness like this:
Starting MySQL
* Couldn't find MySQL manager (/usr/bin/mysqlmanager) or server (/usr/bin/mysqld_safe)
I did the configure make and install like I normally do:
Code: Select all
./configure --prefix=/usr/local/mysql \
--enable-assembler \
--enable-local-infile \
--with-charset=utf8 \
--with-extra-charsets=none \
--with-mysqld-user=mysql \
--with-mysqlmanager=no \
--without-docs \
--without-man \
--with-plugins=archive,heap,innobase,myisam
But cannot get the mysql.server script to work right