MySQL Bugs: #1012: mysqladmin dies when max_allowed

Bug #1012	mysqladmin dies when max_allowed_packet is set
Submitted:	7 Aug 2003 10:39	Modified:	13 Aug 2003 2:45
Reporter:	Martin Mokrejs	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.14	OS:	Linux (Linux 2.4.22-pre7)
Assigned to:	Sergei Golubchik	CPU Architecture:	Any

Description:
Hi,
  mysql(1) client binary works fine when there's set:

[mysql]
set-variable    = max_allowed_packet=1G

  We need it because we need to import larger amounts of data into rows. That always worked somehow fine (except perl DBI, where we always hit max_allowed_packet boundary), but that's another story. At least the mysql(1) client accepts more than 16M. ;)

  Unfortunately, mysqldmin(1) dies with:

$ mysqladmin shutdown
mysqladmin: ERROR: unknown variable 'max_allowed_packet=1G'
$

  Why is it reading section [mysql] at all? We have to remove that definition, but then mysql(1) client cannot pickup larger amounts of data. :( This efectively blocks us from using that option.

  Second. The documenation is getting outdated. "mysqladmin --help" says that

  -O, --set-variable=name 
                      Change the value of a variable. Please note that this
                      option is deprecated; you can set variables directly with
                      --variable-name=value.

  This is not related to the syntax of my.cnf files, right? So there still should be used

set-variable   = max_allowed_packet=1G

  if I got it right? ;) If so, please make the HTML documentation in distribution clear that only the command-line options changed. Thanks.

How to repeat:
$ mysqladmin shutdown
mysqladmin: ERROR: unknown variable 'max_allowed_packet=1G'
$

Jani, I assume this is your area?

It happens also with mysqladmin(1).

mokrejs@vrapenec$ mysqladmin -hkulan processlist
mysqladmin: ERROR: unknown variable 'max_allowed_packet=1G'
mokrejs@vrapenec$

Sorry, I thought I reported that originally for another client binary having the same problem. So I say twice: I cannot use mysqladmin now. ;)))

works perfectly for me with the latest mysqladmin. That is it does not read [mysql] group.
And the list of groups to read (mysqladmin, client) hasn't changed for years. At least it wasn't changed since we started to use bitkeeper.

It does not understand max_allowed_packet option, true. You can still put it in [client] section if you want to affect all clients. Then you can prepend it with loose- (loose-max-allowed-packet=) so clients that don't know this option will ignore it.

Also, set-variable syntax is outdated, and it concerns config files too. Config file contains the same set of command line options but without -- prefix. So you can write in config file

max-allowed-packet=1G

It's in fact, recommended syntax. The old one is of course supported too (as you know).

vrapenec mysql-debug-4.0.14-pc-linux-i686 # bin/mysqld
030811  0:24:32  Warning: Asked for 196608 thread stack, but got 126976
bin/mysqld: ready for connections.
Version: '4.0.14-debug-debug'  socket: '/tmp/mysql.sock'  port: 3306

And the client does:

mokrejs@vrapenec$ /usr/local/mysql-debug-4.0.14-pc-linux-i686/bin/mysqladmin
/usr/local/mysql-debug-4.0.14-pc-linux-i686/bin/mysqladmin: ERROR: unknown variable 'max_allowed_packet=1G'
mokrejs@vrapenec$ md5sum /usr/local/mysql-debug-4.0.14-pc-linux-i686/bin/mysqladmin
a9b907f16caf203c12ca7ae78e813b67  /usr/local/mysql-debug-4.0.14-pc-linux-i686/bin/mysqladmin
mokrejs@vrapenec$ 

Do you need acces to my machine to repeat that??? Thanks for the note about *undocumented* loose option. :)

Are you sure the clients ignore loose-max_allowed_packet=1G ?
As you can see below, maybe they do on the first hit, but they do die anyway!

mokrejs@vrapenec$ mysqladmin shutdown
mysqladmin: ERROR: unknown variable 'max_allowed_packet=1G'
mokrejs@vrapenec$ mysqladmin shutdown
mysqladmin: WARNING: unknown variable 'max_allowed_packet=1G'
mysqladmin: WARNING: unknown variable 'max_allowed_packet=1G'
mokrejs@vrapenec$

mokrejs@vrapenec$ mysqladmin shutdown
mysqladmin: WARNING: unknown variable 'max_allowed_packet=1G'
mysqladmin: WARNING: unknown variable 'max_allowed_packet=1G'
mokrejs@vrapenec$ grep max_allowed_packet /etc/my.cnf
loose-max_allowed_packet=1G
# they are dynamically enlarged up to max_allowed_packet when needed.
loose-max_allowed_packet=1G
loose-max_allowed_packet=1G
loose-max_allowed_packet=1G
mokrejs@vrapenec$ grep max_allowed_packet /etc/mysql/my.cnf
loose-max_allowed_packet=1G
loose-max_allowed_packet=1G
loose-max_allowed_packet=1G
mokrejs@vrapenec$ 
mokrejs@vrapenec$ grep max_allowed_packet ~/.my.cnf        
loose-max_allowed_packet=1G
loose-max_allowed_packet=1G
mokrejs@vrapenec$

First - I reminded our doc team about this --loose prefix. It'll decumented soon.

THen - are you sure about location of my.cnf files that the client reads ?

Try /usr/local/mysql-debug-4.0.14-pc-linux-i686/bin/my_print_defaults mysqladmin client

I know why you couldn't repeat that. After I've restarted mysqld my clients can connect, they really issued a warning about max_allowed_packet but continue working. But that means, that actually the server was closing connection.
Please, set for client, mysqladmin, mysqld max_allowed_packet=1G, restart mysqld, try mysqladmin to connect. It will fail. Edit config file for server and modify those max_allowed_packet to loose-max_allowed_packet, restart mysqld and connect again using mysqladmin. You will succeeed.

But, to answer your question:

mokrejs@vrapenec$ /usr/bin/my_print_defaults mysqladmin client
--port=3306
--socket=/var/run/mysqld/mysqld.sock
--loose-max_allowed_packet=1G
--compress
--compress
--host=127.0.0.1
--user=pedant
--password=abc#abc
--port=3306
--loose-max_allowed_packet=1G
--compress
mokrejs@vrapenec$ /usr/local/mysql-debug-4.0.14-pc-linux-i686/bin/my_print_defaults mysqladmin client
--port=3306
--socket=/var/run/mysqld/mysqld.sock
--loose-max_allowed_packet=1G
--compress
--host=127.0.0.1
--user=pedant
--password=abc
--port=3306
--loose-max_allowed_packet=1G
--compress
mokrejs@vrapenec$ 

I have 4.0.13 in /usr/bin and also 4.0.14 available, as you can see. 4.0.14 uses /etc/my.cnf, 4.0.13 coming from gentoo uses /etc/mysql/my.cnf. Howeerver, I take care of both config files and there's no problem with them.

I see another bug. Compare the password lines in the output. The 4.0.14  doesn't display the password after `#' character.

Martin, I don't understand it.

Original bugreport was that mysqladmin read [mysql] group, wasn't it ?
Now you say "Please, set for [client], [mysqladmin], [mysqld] max_allowed_packet=1G"
How it is related ?

I tried your procedure - and only got expected results.
With max_allowed_packet=1G in [client] or [mysqladmin] section, mysqladmin doesn't work. After adding loose- it works. [mysqld] section or server restart don't change anything for me.

OK Sergei, my misunderstanding. I really wanted to report that mysqladmin dies when

[client]
max_allowed_packet=1G

I didn't know I should put into 

[mysql]
max_allowed_packet=1G

instead. Then I hope the so called "client" - i.e. mysql(1) will read it from the [mysql] section, which mysqladmin never reads.

Am I clear now? Then it's really not a bug, but I newer saw a note that I cannot raise max_allowed_packet for mysqladmin. ;) Maybe doc update would help stupids like me? Thanks!