Menu
My application download mails over IMAP and stores them in a MySQL database. Earlier I was supporting mails size upto 10 MB and hence a 'mediumtext' column to store the mail content was enough. Now I need to support mails upto 30MB. So I changed the datatype for the column to 'largetext'. Yesterday a mail with size 25 MB was stored. After that whenever I execute mysqldump command it throws error:
mysqldump: Error 2020: Got packet bigger than 'max_allowed_packet' bytes when dumping table `ib_mailbox_backup` at row: 3369
Row 3369 contains the 25 MB mail.
In MySQL config I increased the 'max_allowed_packet' from 64M to 512M and it still fails with the same error. Executing the mysqldump command on the same machine where MySQL server is running. How do I solve this?
478
Open the "my. ini" file under the MySQL server install directory. Search for the "max_allowed_packet" parameter.
In a nutshell, max_allowed_packet is the maximum size of a MySQL network protocol packet that the server can create or read. It has a default value of 1MB (<= 5.6. 5) or 4MB (>= 5.6. 6) and a maximum size of 1GB.
Mysqldump with Transactions The --single-transaction flag will start a transaction before running. Rather than lock the entire database, this will let mysqldump read the database in the current state at the time of the transaction, making for a consistent data dump.
--max_allowed_packet=512M to your mysqldump command.max_allowed_packet=512M to [mysqldump] section of your my.cnf (thanks @Varun)Note: it will not work if it is not under the [mysqldump] section...
82
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
