MySQL Bugs: #15946: Error: 'Incorrect argument type' when using suffix in set command

Bug #15946	Error: 'Incorrect argument type' when using suffix in set command
Submitted:	23 Dec 2005 0:36	Modified:	23 Dec 2005 8:50
Reporter:	Ronald Bradford	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.15	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
When attempting to set a variable via mysql client of with the set comment within a session using the 'M' suffix, results in an error and the the variable is not set.  In this example, using bytes does work.

mysql>SET MAX_ALLOWED_PACKET = 16M;
ERROR 1232 (42000): Incorrect argument type to variable 'max_allowed_packet'

This particular example is also explicitly documented as the syntax within the 5.0 documentation at http://dev.mysql.com/doc/refman/5.0/en/program-variables.html

I can also reproduce this problem with versions 4.1.10a-standard and 4.0.13-standard

How to repeat:
$ mysql
Your MySQL connection id is 59 to server version: 5.0.15-standard
mysql> show variables like 'max_all%';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

$ mysql --max_allowed_packet=16M
Your MySQL connection id is 61 to server version: 5.0.15-standard
mysql> show variables like 'max_all%';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql> SET MAX_ALLOWED_PACKET = 16M;
ERROR 1232 (42000): Incorrect argument type to variable 'max_allowed_packet'
mysql> SET MAX_ALLOWED_PACKET = 104857600;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_all%';
+--------------------+-----------+
| Variable_name      | Value     |
+--------------------+-----------+
| max_allowed_packet | 104857600 |
+--------------------+-----------+
1 row in set (0.00 sec)

Suggested fix:
I'm hoping somebody at MySQL will help me out here.

Specifying the argument in my.cnf provide the following output.
[mysqld]
max_allowed_packet=16M

Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql> show variables like 'max_a%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 16776192 |
+--------------------+----------+
1 row in set (0.00 sec)

Thank you for a problem report. Sorry, but it is not a bug, rather a misinterpretation of the manual. The page you quoted (http://dev.mysql.com/doc/refman/5.0/en/program-variables.html) explains how to set values in command line and in options files. Dynamic changes (by SET command) are described on the other page, http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html. And it clearly describes max_allowed_packet as numeric dynamic variable. Then:

"Variables that are marked as numeric take a numeric value."

16M is not a numeric value in SQL context. So, it is not a bug, but intended and documented behaviour, although slightly misleading. 

You may add a feature request asking for support of K, M, G suffixes for (some) numeric values.