1

I am using MySQL Workbench CE to migrate one schema from a server to another, all table is migrated successfully, except one table with blob data.

The error message is :

Found record bigger than max_allowed_packet.

I have set the max_allowed_packet to 1M on target server, and restarted MySQL, but still got the same error.

And I checked data in that table:

select max(OCTET_LENGTH(file_Data)) from attachment;
------
680960
select sum(OCTET_LENGTH(file_Data)) from attachment;
------
5165578

How can this be fixed?

2
  • @RickJames bigger than sum of all blob columns or max ?
    – WestFarmer
    Jul 9 '15 at 2:05
  • Try 2M, in case it is using hex.
    – Rick James
    Jul 9 '15 at 2:05
3

You can see max_allowed_packet to 1G in your session

SET max_allowed_packet = 1073741824;

You can do so because MySQL will not immediately allocate it. A packet can grow. Please note how it mentions this in the MySQL Documentation:

The packet message buffer is initialized to net_buffer_length bytes, but can grow up to max_allowed_packet bytes when needed. This value by default is small, to catch large (possibly incorrect) packets.

You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

You should go ahead and add max_allowed_packet to my.cnf

[mysqld]
max_allowed_packet=1G

That way it is globally available for all incoming connections when you restart mysql. You don't have to restart mysql right now. You can just run:

SET GLOBAL max_allowed_packet = 1073741824;

Give it a Try !!!

1
  • thanks sir, one more little question why max_allowed_packet = 1M is not enough, while the max length of my blob column is 680960 which is lower than 1M, 1M = 1024 * 1024 = 1048576, did I calculate it right ?
    – WestFarmer
    Jul 9 '15 at 2:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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