max_allowed_packet

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

max_allowed_packet

Post 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
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post 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..
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post 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.
Post Reply