Home > Mysql > Got a packet bigger than ‘max_allowed_packet’ bytes

Got a packet bigger than ‘max_allowed_packet’ bytes

While restoring a database file with a size of about 800MB I got the following error,

[root@heuristics ~]# mysql -uadmin -p`cat /etc/mysqlpass` databasename --max_allowed_packet=1G < ~/dbbackup.sql
ERROR 1153 (08S01) at line 24832: Got a packet bigger than 'max_allowed_packet' bytes
[root@heuristics ~]# 

The system was having a RAM of 1GB. Even setting the max_allowed_packet size to 1GB didn’t help. On running the mysql restoration task with the verbose option(-v) it was evident that this issue was caused by too many rows for a mysql insert statement. In my case an insert statement with about 16lakh rows was causing this issue.

[root@heuristics ~]# mysql -v -uadmin -p`cat /etc/mysqlpass` databasename --max_allowed_packet=1G < ~/dbbackup.sql
....
.....
.....
(33239, 'tr', 'video', 'phpfox', '3.0.0', 'load_more_suggestions', 'Load More Suggestions', 'Load More Suggestions', 1323096814),
(33240, 'tr', 'video', 'phpfox', '3.0.0', 'user_setting_can_feature_videos_', 'Can feature videos?', 'Can feature videos?', 1323336926),
(33241, 'tr', 'video', 'phpfox', '3.0.0', 'can_feature_videos', 'Can feature videos?', 'Can feature videos?', 1323337001),
(33242, 'tr', 'youface', 'phpfox', '2.0.7', 'module_youface', 'youface', 'youface', 1299569545)

ERROR 1153 (08S01) at line 24832: Got a packet bigger than 'max_allowed_packet' bytes

[root@heuristics ~]# 

On reaching the 33242’th row of the insert statement the restoration fails with the above error. This insert statement has the following syntax.

--------------------------------
INSERT INTO TABLENAME (field1,field2,field3) VALUES
('row1','value2','value3'),
('row2','value2','value3'),
('row3','value2','value3'),
('row4','value2','value3'),
('row5','value2','value3');
-------------------------------- 

In order to solve this issue, split the sql backup file with one not having the insert statement and one with the problem insert statement.

sed -n '/INSERT INTO `testing` VALUES/,/[);]/p' ~/dbbackup.sql > ~/insert.sql
sed '/INSERT INTO `testing` VALUES/,/[);]/d' ~/dbbackup.sql > ~/no_insert.sql

Restore the ~/no_insert.sql sql backup file containing table structures first.

mysql -v -uadmin -p`cat /etc/mysqlpass` databasename --max_allowed_packet=1G < ~/no_insert.sql

Now for the ~/insert.sql backup file, we will add an INSERT statement after every 1000th row and restore them independently. The following script can be used for achieving this.
NOTE: Please replace the sql insert statement with your own:)

#!/bin/bash

sql_insert_file=~/no_insert.sql
line_count=$(wc -l ${sql_insert_file}|awk '{print $1}')

for((i=1,j=i+999;j<=${line_count};i=i+1000,j=i+999))
do
	sed -n "${i},${j}p" ${sql_insert_file} > ~/test.sql
	head -1 ~/test.sql|grep ^"INSERT INTO"  > /dev/null
	if [ $? -ne 0 ];then
		echo "INSERT INTO \`tablename\` (\`view_id\`, \`user_id\`, \`ip_address\`, \`protocal\`, \`cache_data\`, \`time_stamp\`) VALUES" > ~/test.sql
		sed -n "${i},${j}p" ${sql_insert_file} >> ~/test.sql
	fi

	#Replace the last character in file "," with ";"
	sed -i '$s/.$/;/' ~/test.sql

	mysql -v -uadmin -p`cat /etc/mysqlpass` databasename --max_allowed_packet=1G < ~/test.sql

done

if [ $i -lt $line_count ];then 
	k=$(($i+1))
	sed -n "${k},${line_count}p" ${sql_insert_file} > ~/test.sql
	head -1 ~/test.sql|grep ^"INSERT INTO"  > /dev/null
		if [ $? -ne 0 ];then
			echo "INSERT INTO \`tablename\` (\`view_id\`, \`user_id\`, \`ip_address\`, \`protocal\`, \`cache_data\`, \`time_stamp\`) VALUES" > ~/test.sql
			sed -n "${i},${j}p" ${sql_insert_file} >> ~/test.sql
		fi

	#Replace the last character in file "," with ";"
	sed -i '$s/.$/;/' ~/test.sql

	mysql -v -uadmin -p`cat /etc/mysqlpass` databasename --max_allowed_packet=1G < ~/test.sql	
fi
Advertisements
Categories: Mysql
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: