Tag Archives: import

Obscure mysql bug number 1153 (08S01) (my packet’s too big)

Ok, here’s one you don’t see every day.

Importing a mysql dumpfile results in “ERROR 1153 (08S01) at line 265: Got a packet bigger than ‘max_allowed_packet’ bytes”

Errm.

Log in to a mysql console,

enter the magic commands:

set global net_buffer_length=1000000;

set global max_allowed_packet=1000000000;

Modify your import to include the command:

–max_allowed_packet=100M

so you have something like

mysql –max_allowed_packet=100M -u user -p db_name < dump.sql

Try again

Your mileage may vary ( do they say kilometerage elsewhere?) so increase the allowed sizes if you have to.

Getting data out of MySQL (and then back in)

There seems to be lots of ways to get your databases out of a MySQL database and an even more bewildering array of ways to get it back in.  I have found that the following commands seem to be the best way to do it (for me):

Out

mysqldump –databases database_name -u username -p > dumpfile.sql

In

mysql -u username -p database_name < dumpfile.sql

Find the reference here.

15/02/10: Here is another variation on getting stuff out:

mysqldump db_name table1 table2 table3 -u username -p > dumpfile.sql