CODEWITHSUNDEEP
mysqldatabase
Ahmet Karakaya
Ahmet Karakaya

Reputation: 10139

max_allowed_packet could not be set in MySQL 5.5.25

I could not set "max_allowed_packet" variable in MYSQL command prompt though I can set this value by inserting "max_allowed_packet=100M" line into my.ini file. Why I am having following warning?

mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql> warnings
Show warnings enabled.
mysql> set global max_allowed_packet=123456000;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect max_allowed_packet value: '123456000'
mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in set (0.00 sec)

mysql>

I have alse tried smaller size as following.

mysql> set global max_allowed_packet=123456;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect max_allowed_packet value: '123456'
mysql>

mysql> set session max_allowed_packet=123456;
ERROR 1621 (HY000): SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value
Warning (Code 1292): Truncated incorrect max_allowed_packet value: '123456'
Error (Code 1621): SESSION variable 'max_allowed_packet' is read-only. Use SET GLOBAL to assign the value

MySQL Version

    mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 1.1.8                        |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.25                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86                          |
| version_compile_os      | Win64                        |
+-------------------------+------------------------------+
7 rows in set (0.00 sec)

mysql>

Upvotes: 6

Views: 21825

Answers (3)

RahulDeep Attri
RahulDeep Attri

Reputation: 418

The answer1 and answer2 are good check them out. Adding to those, when we start MySQL server we get a global deafult max_allowed_packet value and when a connection to server is initialized this global deafult max_allowed_packet value is copied to a local max_allowed_packet variable (which is read only) and used by connection for its operations.

So basically after every server restart you need to enter

set global max_allowed_packet=1048576000;

Drop all the connections and start new connections, so that this new global value is reflected and now you would be able to insert blob.

Upvotes: 4

pfrenssen
pfrenssen

Reputation: 5515

I noticed that you have to use multiples of 1024 for the values to be accepted:

A value of 2048001 fails:

mysql> set global max_allowed_packet=2048001;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 1292): Truncated incorrect max_allowed_packet value: '2048001'

But a value of 2048000 works:

mysql> set global max_allowed_packet=2048000;
Query OK, 0 rows affected (0.00 sec)

The manual says the following, but it seems that the 'rounding down' does not work as designed:

The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

Upvotes: 8

Ahmet Karakaya
Ahmet Karakaya

Reputation: 10139

http://bugs.mysql.com/bug.php?id=22891 and http://bugs.mysql.com/bug.php?id=32223 where the conclusion was that max_allowed_packet should be a startup parameter only.

Upvotes: 4

Related Questions