Skip navigation links
4.2.3.5 Option Defaults, Options Expecting Values, and the = Sign »
Section Navigation      [Toggle]

4.2.3.4. Using Options to Set Program Variables

Many MySQL programs have internal variables that can be set at runtime using the SET statement. See Section 12.4.4, “SET Syntax”, and Section 5.1.6, “Using System Variables”.

Most of these program variables also can be set at server startup by using the same syntax that applies to specifying program options. For example, mysql has a max_allowed_packet variable that controls the maximum size of its communication buffer. To set the max_allowed_packet variable for mysql to a value of 16MB, use either of the following commands: 

shell> mysql --max_allowed_packet=16777216
shell> mysql --max_allowed_packet=16M

The first command specifies the value in bytes. The second specifies the value in megabytes. For variables that take a numeric value, the value can be given with a suffix of K, M, or G (either uppercase or lowercase) to indicate a multiplier of 1024, 10242 or 10243. (For example, when used to set max_allowed_packet, the suffixes indicate units of kilobytes, megabytes, or gigabytes.)

In an option file, variable settings are given without the leading dashes: 

[mysql]
max_allowed_packet=16777216

Or: 

[mysql]
max_allowed_packet=16M

If you like, underscores in a variable name can be specified as dashes. The following option groups are equivalent. Both set the size of the server's key buffer to 512MB: 

[mysqld]
key_buffer_size=512M

[mysqld]
key-buffer-size=512M

A variable can be specified by writing it in full or as any unambiguous prefix. For example, the max_allowed_packet variable can be set for mysql as --max_a, but not as --max because the latter is ambiguous: 

shell> mysql --max=1000000
mysql: ambiguous option '--max=1000000' (max_allowed_packet, max_join_size)

Be aware that the use of variable prefixes can cause problems in the event that new variables are implemented for a program. A prefix that is unambiguous now might become ambiguous in the future.

Suffixes for specifying a value multiplier can be used when setting a variable at server startup, but not to set the value with SET at runtime. On the other hand, with SET you can assign a variable's value using an expression, which is not true when you set a variable at server startup. For example, the first of the following lines is legal at server startup, but the second is not: 

shell> mysql --max_allowed_packet=16M
shell> mysql --max_allowed_packet=16*1024*1024

Conversely, the second of the following lines is legal at runtime, but the first is not: 

mysql> SET GLOBAL max_allowed_packet=16M;
mysql> SET GLOBAL max_allowed_packet=16*1024*1024;

Note

Before MySQL 4.0.2, the only syntax for setting program variables was --set-variable=option=value (or set-variable=option=value in option files). Underscores cannot be given as dashes, and the variable name must be specified in full. This syntax is deprecated and was removed in MySQL 5.5.3.


User Comments

Posted by Devin Butts on November 7 2008 3:26pm[Delete] [Edit]

To find out if your option variable syntax is correct, go into MySQL and try it, such as shown below.

mysql> show variables like 'max%' ;

+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| max_allowed_packet    | 1048576    |
...

mysql> set max_allowed_packet = 1500000;
Query OK, 0 rows affected (0.03 sec)

mysql> show variables like 'max%' ;
+-----------------------+------------+
| Variable_name         | Value      |
+-----------------------+------------+
| max_allowed_packet    | 1499136    |

Posted by Devin Butts on November 7 2008 4:11pm[Delete] [Edit]

I had problems setting max_allowed_packet. My provider would not increase the value from 16M in the my.cnf file. I found the following work arounds... These may or may not work for you, but hope it gives some ideas about things to try... Replace the [] with the proper info...

To Backup the Database, shell command:
mysqldump --add-drop-table --extended-insert -O net_buffer_length=10M -O max_allowed_packet=1G -h [nameofserver] -u [username] -p[password] [nameofdatabase] | bzip2 -c > [outfilename].sql.bz2

To Restore the Database, shell command (if you used the command above, unzip the file first):
mysql -h [nameofserver] -u [username] -p[password] --max_allowed_packet=1073741824 [nameofdatabase] < [outfilename].sql

The max_allowed_packet had no effect on the restore above, but did work on the mysqldump. I had to use these commands and go into MySQL to get the restore completed:

mysql -h [nameofserver] -u [username] -p[password] [nameofdatabase]
set max_allowed_packet=1073741824;
\. [outfilename].sql

Depending on the file, even the above steps of manually setting the max_allowed_packet size didn’t work. I believe turning off –extended-insert above might help with large packet size problems. I finally ended up writing a program to divide up the big SQL output file into separate files for each table, which finally allowed me to get the file restored. Either way, it seems important to do a test backup and restore to ensure that the parameters used to create the backup file will create a file that can be restored.

Good luck!

Add your own comment.