Page 1 of 1

max_allowed_packet

Posted: Tue May 30, 2006 8:53 am
by s.dot
I am running a one-time cleanup script to delete a bunch of rows that are no longer in use.

My query looks like this

Code: Select all

mysql_query("DELETE FROM `privatemessages` WHERE `id` NOT IN($ids)") or die(mysql_error());
$ids is 192,411 ids

MySQL gives me this error

Code: Select all

Got a packet bigger than 'max_allowed_packet'
So I researched it, and came up with this
If you are using the mysql client program, its default max_allowed_packet variable is 16MB. To set a larger value, start mysql like this:

shell> mysql --max_allowed_packet=32M

That sets the packet size to 32MB.

The server's default max_allowed_packet value is 1MB. You can increase this if the server needs to handle big queries (for example, if you are working with big BLOB columns). For example, to set the variable to 16MB, start the server like this:

shell> mysqld --max_allowed_packet=16M
I am not too good with using SSH, so I want to make sure if I execute one of those commands, that I can revert back to what it is now. (I assume 1 mb)

Which one of those would I want to use? mysql or mysqld

Posted: Tue May 30, 2006 9:58 am
by onion2k

Code: Select all

mysql_query("SET global max_allowed_packet=33554432;") or die(mysql_error());
mysql_query("DELETE FROM `privatemessages` WHERE `id` NOT IN($ids)") or die(mysql_error());
In theory it should update the max_allowed_packet variable on the fly.. No idea if it'll work from PHP. Other SET things like "SET NAMES utf8;" work though, so there's a chance..

Posted: Tue May 30, 2006 10:07 am
by feyd
could always break the array into smaller chunks with array_chunk()

I'd also suggest using LOW_PRIORITY so the deletion process doesn't eat the processor(s) or lock up the table unnecessarily.

Posted: Tue May 30, 2006 10:20 am
by RobertGonzalez
max_allowed_packet is a MySQL server startup variable. That means that you can change it, restart mysql, and it will take effect. After you are done with it, change it back, restart the MySQL server and you should be golden.

Another thing you can do (it's kind hackish, but works pretty good) is to use SQLYog to hit your MySQL server. It will actually job out the queries so they never exceeed the max allowed packet size. It is a little slow, but it should work. I think it also 'remembers' the query location, so if you get disconnected from the server during the transaction, it can pick up where it left off. I used it to insert a 55,000 record query and it did it over a wireless DSL connection in about fifteen minutes.

Posted: Tue May 30, 2006 7:22 pm
by s.dot
feyd wrote:could always break the array into smaller chunks with array_chunk()

I'd also suggest using LOW_PRIORITY so the deletion process doesn't eat the processor(s) or lock up the table unnecessarily.
Chunking it won't work. Cuz after it runs the first chunk, it will delete everything that's not in that chunk, thus deleting everything that would be in the other chunks.

The sql query through php, didn't work.. I assume because it's a startup option like everah said.

So I'll have to play with it on SSH :-S

Posted: Tue May 30, 2006 7:58 pm
by RobertGonzalez
Hey scottayy, if you have access, you can always edit the my.conf file and change the max_alowed_packets entry. The stop and start the mysql server for the change to take effect. After you're done, edit the my.conf file back to original and stop and restart the mysql server again. Boddabing, you're back in business.

Posted: Tue May 30, 2006 10:40 pm
by Weirdan
scottayy wrote: Chunking it won't work. Cuz after it runs the first chunk, it will delete everything that's not in that chunk, thus deleting everything that would be in the other chunks.
it's somewhat tricky, but it can be done:

Code: Select all

// NOT TESTED!!
$ids = array(123,345 /*, ... */, 567);

list($upper_id, $lower_id) = mysql_fetch_row(mysql_query("select max(id), min(id) from `privatemessages`"));
$chunk_length = 1024;
$c_lower_id = $lower_id;
$c_upper_id = $lower_id + $chunk_length; // not inclusive
$tmp = array();
foreach($ids as $id) {
   if($id > $c_upper_id) { // process_previous chunk
        mysql_query("delete from `privatemessages` where `id`>= $c_lower_id and `id` < $c_upper_id and `id` not in(" . implode(',', $tmp) . ")";
        // reset state
        $tmp = array();
        $c_lower_id = $c_upper_id;
        $c_upper_id = $c_lower_id + $chunk_length;
   } else {
        $tmp[] = $id;
   }
}
this will not delete the messages added while this script is running, though.