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