The problem was not in fact MySQL. We use the DRb Ruby library, which gives a very similar error under similar circumstances: too large packet 26814732
. Due to familiarity with the MySQL issue referenced here, I didn't read the error message well enough and didn't notice it is slightly different from Packet too Large
.
Rather than killing the process, it would be safer if you did it within MySQL:
$ mysqladmin processlist -u root -p
Enter password:
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
| 174 | root | localhost | example | Sleep | 297 | | |
| 407 | root | localhost | | Query | 0 | | show processlist |
+-----+------+-----------+-------------------+---------+------+-------+------------------+
The query with id 174 is the one blocking deletion of the 'example' database, so before you kill any processes first let MySQL try to terminate the query:
$ mysqladmin kill 174
Run the processlist
command above again to confirm that it was killed.
If this doesn't work, then you could perhaps look at killing the errant process, but before that you might try restarting the MySQL server.
You can also run commands like 'SHOW FULL PROCESSLIST' and 'KILL 174' in the MySQL shell, for example if you only have the MySQL client installed. The main point is to avoid killing the process using 'kill' in the shell unless absolutely necessary.
Generally speaking you can use either mysql
or mysqladmin
. You shouldn't need to be running commands like this that often though; once you start killing queries regularly something is definitely wrong and you'd be better off fixing that problem (killing the query process is just treating the symptom).
Best Answer
Something tells me you are either using a version of MySQL prior to 5.0, you called an older version of mysqldump, or you messed with the settings of the dump.
What usually blows up a mysqldump past the size of its dataset is the option --skip-extended-insert.
In older versions of MySQL, there was no extended insert. That means each and every row in a table had an INSERT command to itself. If a table had 2,000 rows, the mysqldump output will have 2,000 INSERT commands. That's a whole lot of commas, parentheses, single quotes, and "INSERT INTO" tags to place in a dump file.
In newer versions of MySQL, --extended-insert was added to group together dozens (or even hundreds) of rows in a single INSERT. SO, instead of...
You could have this:
For mysqldump, --opt includes --extended-insert. If you use --skip-opt when doing mysqldump, it disables --skip-extended-insert. Here are the options of mysqldump that affect extended insert:
CAVEAT
Run these at the Linux command line
If these do not match the version of mysql you are running on the server, or multiple versions of MySQL exist on the same machine, get that straightened out. Until then, make sure you call the correct version of mysqldump and don't use --skip-opt.