MySQL DBA - Tips and Techniques Magazine

13 Nov 2014

Got a packet bigger than 'max_allowed_packet' bytes

mysql> show slave status\G

*************************** 1. row ***************************

               Slave_IO_State:

                  Master_Host: mk-mysqlcluster-2-miscdb-rw.uk.intranet

                  Master_User: replusr_miscdb

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000009

          Read_Master_Log_Pos: 5506942

               Relay_Log_File: mysql-relay.000516

                Relay_Log_Pos: 72730

        Relay_Master_Log_File: mysql-bin.000009

             Slave_IO_Running: No

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB: sc3_test_database

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 5506847

              Relay_Log_Space: 73121

              Until_Condition: None

               Until_Log_File:

                Until_Log_Pos: 0

           Master_SSL_Allowed: No

           Master_SSL_CA_File:

           Master_SSL_CA_Path:

              Master_SSL_Cert:

            Master_SSL_Cipher:

               Master_SSL_Key:

        Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 1153

                Last_IO_Error: Got a packet bigger than 'max_allowed_packet' bytes

               Last_SQL_Errno: 0

               Last_SQL_Error:

1 row in set (0.00 sec)

 

mysql> exit

 

 

 

SOLUTION:

1) Increase max_allowed_packet to 16M, by default its 1MB

 

 

my.cnf

 

 

[mysqldump]

max_allowed_packet                                    = 16M

 

2) bounce database.

 

3) had to rebuild slave as well, as slave wasn't replicating even afterwards.

 

1 comment:

  1. This should not be needed for 5.6.6 and later

    http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html#option_mysqld_slave-max-allowed-packet

    ReplyDelete