Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to increase max_allowed_packet variable size for mysql client which is using remote server. I've googled it and the answers I could find only discussed changing the variable for server.

My client program is MySql Workbench for Windows 7.

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

According to MySQL Documentation on max_allowed_packet

Some programs such as mysql and mysqldump enable you to change the client-side value by setting max_allowed_packet on the command line or in an option file.

On the command line, to set it to 512M just run mysql client with this:

C:\>mysql -u... -p...

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.5.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> set max_allowed_packet=1024 * 1024 * 512;
ERROR 1621 (HY000): SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value
mysql> set global max_allowed_packet=1024 * 1024 * 512;
Query OK, 0 rows affected (0.00 sec)

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

mysql> exit
Bye

C:\>mysql -u... -p...
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.5.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

You have to set it globally. You cannot set it locally.

You need the SUPER privilege to set any global variable.

share|improve this answer
    
when i try this command i got error like ERROR 1621 (HY000): SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value –  MONTYHS Feb 17 at 14:58
    
@MONTYHS Please read my answer real carefully. I already ran set max_allowed_packet=1024 * 1024 * 512;, showed the error message, ran set global max_allowed_packet=1024 * 1024 * 512; successfully, and then said You have to set it globally. You cannot set it locally. . Therefore, my answer is thorough and complete and has been that way since May 3, 2012. –  RolandoMySQLDBA Feb 17 at 16:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.