You can easily import data from .SQL files into MySQL database using plain mysql command. But sometimes when you try to import a file’s contents into MySQL database you may get the error ‘ERROR 1153 …: Got a packet bigger than ‘max_allowed_packet’ bytes’. In this article, we will learn how to fix this error. This may happen because when MySQL imports data it runs many INSERT statements to populate your database, and these insert statements have a lot of data. In this article, we will learn how to MySQL error 1153.
How to Fix MySQL Error 1153 – Got a packet bigger than ‘max_allowed_packet’ bytes
In most cases, this is fixed by increasing the value of max_packet_allowed parameter on client or server or both. You can increase the parameter value of max_allowed_packet to 100Mb during data import on MySQL client by running the following command. Replace <database> and <filename.sql> with the names of your database and filename with SQL queries.
# mysql --max_allowed_packet=100M -u root -p <database> < <filename.sql>
In this case, the max_allowed_packet value will be temporarily increased during import and afterwards will be reverted back to its old value.
If you want to permanently increase this value, then open my.cnf file in text editor.
# vi /etc/mysql/my.cnf
Add the following line to it under mysqld section.
max_allowed_packet=100M
Save and close the file. Restart MySQL server to apply changes.
You can also log into MySQL console and run the following command to set max_allowed_packet size for the duration of the session.
set global net_buffer_length=1000000; set global max_allowed_packet=1000000000;
In this article, we have learnt how to fix MySQL error 1153. If you want to increase value of max_packet_allowed only for a specific query, include –max_allowed_packet option in your SQL query. If you want to set it for the duration of your entire session, log into MySQL and use SET GLOBAL queries as shown above. If you want to permanently increase the value of max_packet_allowed open my.cnf file and add ‘max_packet_allowed=…’ under mysqld section. You can use any of the above methods depending on your requirement.
Also read:
Create Temporary Table from SELECT Query
How to Select Multiple Columns in Python Pandas
How to Sort Python List By Multiple Elements
How to Remove Duplicates in Python Pandas
How to Check if String is Number in Python
Thanks.