Thesqlcheck: unknown variable ‘max_allowed_packet=100M’

MySQL

I get the following error when I try to use mysqlcheck, mysqlrepair or mysqlanalyze:

mysqlcheck: unknown variable 'max_allowed_packet=100M'

I am trying to find whether there are any corrupt tables or indexes.

Background:

MySQL Ver 14.14 Distrib 5.5.24, for Linux (x86_64) using readline 5.1
I used the command mysqlcheck -u xxxx -h xxx.xxx.xx.xx -d xxxx -p xxxxx

my.cnf

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
default-character-set = utf8
max_allowed_packet = 100M

Under [client]

Best Answer

According to this bug report (resolved as "not a bug"), you shouldn't put settings for mysqld in the [client] section of your .ini file.

Put the max_allowed_packet option in the [mysqld] section instead.

See Using Option Files for more details.

Related Solutions

Mysql – ERROR 1034 (HY000) : Wrong aligned block, while importing in thesql

At first glance, I would say you have some corruption. But, this an import. That scares me. Take your mysqldump and run this:

head -20 SQLDump.sql

Does the dump contain DROP TABLE and CREATE TABLE ? My guess would be no.

Did you change the table structure, i.e.,

added columns to the table
removed columns from the table
changed charset/collation to utf8

after doing that mysqldump ? My guess would be yes.

If neither of these are the case, then your table has some corruption in the .frm.

Try installing MySQL on another DB Server and loading the SQLDump there. If nothing errors out, the mysqldump is fine. This verifies some corruption.

Try dropping the problem table, create it from scratch. Then, load it back. You could run myisamchk against the MyISAM table.

Mysql – /usr/libexec/thesqld: Normal shutdown, but the team doesn’t do that

Linux's Viewpoint

You should run this Linux command

history | grep mysqladmin

This will let you see if anyone ran a shutdown from within the server. Note that this will not let you see remote mysqladmin shutdowns. Perhaps running tcpdump and locating the word mysqladmin or shutdown might help.

MySQL's Viewpoint

Shutdown commands do not exist from command line utilities. Therefore, binary logs will not have shutdowns recorded. You are on the right track when you said;

One possibility I'm thinking is someone (who has SUPER privilege) turned off the binary log then run SHUTDOWN and turned it on again.

Look over the users with that privilege. Run this query:

SELECT user,host FROM mysql.user WHERE shutdown_priv='Y';

You may see something like this:

mysql> select user,host from mysql.user where shutdown_priv='Y';
+-------+--------------+
| user  | host         |
+-------+--------------+
| root  | localhost    |
| root  | 127.0.0.1    |
| root  | ::1          |
| root  | 10.240.163.% |
| root  | 10.48.19.%   |
| user1 | %            |
+-------+--------------+
6 rows in set (0.00 sec)

mysql>

You will see all the users that have shutdown privilege. SUPER privilege does not have the shutdown privilege. There is a separate privilege called SHUTDOWN.

You can quickly revoke that privilege. For example, note the user user1@'%'. It has remote shutdown privileges. You can yank it with

REVOKE SHUTDOWN ON *.* FROM user1.'%';

UPDATE mysql.user SET shutdown_priv='N'
WHERE user='user1' AND host='%';
FLUSH PRIVILEGES;

That takes care of the remote shutdown privileges.

You should also address securing user connections by removing SHUTDOWN privilege from every user that does not need to have it. Perhaps you can do this:

UPDATE mysql.user SET shutdown_priv='N'
WHERE CONCAT(user,'@',host) NOT IN
('root@localhost','root@127.0.0.1');
FLUSH PRIVILEGES;

That way, only root@localhost and root@127.0.0.1 can do mysqladmin shutdown within the localhost. I would definitely have the root@127.0.0.1 on hand because there are times when mysqld loses connection to the mysql.sock file and prevents root@localhost from connecting to issue a shutdown. With the root@127.0.0.1, you can issue this:

mysqladmin -uroot -p -h127.0.0.1 --protocol=tcp

to issue a mysqladmin shutdown without needed the socket file.

Give it a Try !!!