Re: 4.0.18 restore dump file 'max_allowed_packet' error
Luke Vanderfluit wrote: Hi. I've got mysql 4.0.18 installed on a sun X4100 running solaris. This is just a 32 bit version of mysql. I've reverted back to this version after trying mysql 5, 4.0.26 and 4.0.18 64bit. Those versions were all unstable on 64bit, that is, the server would just go away for no apparent reason. This would ofter rear it's head when importing a dump file, the dump file I need to import is around 10 GIG, but also at other unpredictable times. I have this same database running on another machine running solaris, with no problems, except speed/performance. This other machine is the one that produces the dump file I'm trying to import. It does that with the following command: /usr/local/bin/mysqldump --opt --complete-insert --max_allowed_packet=32M rt3 | bzip2 -9 rt3.out-`date +\%Y\%m\%d-\%H`.bz2 I'm trying to restore the file on the new machine and I'm getting a 'max_allowed_packet' error: ERROR 1153 at line 162: Got a packet bigger than 'max_allowed_packet I've tried different settings for this in my.cnf, from 32 up to 1024M, and I still get the error. Is there anything I can do to remedy this? Kind regards. Yes in principle rules are: http://www.mysql.com/news-and-events/newsletter/2003-08/a000216.html but you *must* enforce them on server too (not just on mysqldump): http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html so either run second mysql with: mysqld --max_allowed_packet=32M or edit your my.cnf and restart mysqld: You can also use an option file to set max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file: [mysqld] max_allowed_packet=32M Before MySQL 4.0, use this syntax instead: [mysqld] set-variable = max_allowed_packet=32M or (if you're trying from mysql console then run it with) mysql --max_allowed_packet=32M HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.18 restore dump file 'max_allowed_packet' error
Luke Vanderfluit [EMAIL PROTECTED] wrote on 03/22/2006 08:29:02 PM: Hi. I've got mysql 4.0.18 installed on a sun X4100 running solaris. This is just a 32 bit version of mysql. I've reverted back to this version after trying mysql 5, 4.0.26 and 4.0.18 64bit. Those versions were all unstable on 64bit, that is, the server would just go away for no apparent reason. This would ofter rear it's head when importing a dump file, the dump file I need to import is around 10 GIG, but also at other unpredictable times. I have this same database running on another machine running solaris, with no problems, except speed/performance. This other machine is the one that produces the dump file I'm trying to import. It does that with the following command: /usr/local/bin/mysqldump --opt --complete-insert --max_allowed_packet=32M rt3 | bzip2 -9 rt3.out-`date +\%Y\%m\%d-\%H`.bz2 I'm trying to restore the file on the new machine and I'm getting a 'max_allowed_packet' error: ERROR 1153 at line 162: Got a packet bigger than 'max_allowed_packet I've tried different settings for this in my.cnf, from 32 up to 1024M, and I still get the error. Is there anything I can do to remedy this? Kind regards. -- Luke Not exactly sure if this has changed in a recent version but I believe that the MySQL protocol only allows for packets up to 16M. That has to do with the size of the part of the packet that handles that value. Yep, here it is: http://dev.mysql.com/doc/internals/en/the-packet-header.html 7.4. The Packet Header Bytes Name - 3 Packet Length 1 Packet Number Packet Length: The length, in bytes, of the packet that follows the Packet Header. There may be some special values in the most significant byte. Since 2**24 = 16MB, the maximum packet length is 16MB. You are going to need to re-dump your file or you will need to split your larger packets (probably INSERT statements) into smaller chunks. More troubleshooting and information: http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html However, here it says the limit is 1GB: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html I don't know which document to trust. Try changing it to use 16M chunks and see if that helps you work around the issue. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
4.0.18 restore dump file 'max_allowed_packet' error
Hi. I've got mysql 4.0.18 installed on a sun X4100 running solaris. This is just a 32 bit version of mysql. I've reverted back to this version after trying mysql 5, 4.0.26 and 4.0.18 64bit. Those versions were all unstable on 64bit, that is, the server would just go away for no apparent reason. This would ofter rear it's head when importing a dump file, the dump file I need to import is around 10 GIG, but also at other unpredictable times. I have this same database running on another machine running solaris, with no problems, except speed/performance. This other machine is the one that produces the dump file I'm trying to import. It does that with the following command: /usr/local/bin/mysqldump --opt --complete-insert --max_allowed_packet=32M rt3 | bzip2 -9 rt3.out-`date +\%Y\%m\%d-\%H`.bz2 I'm trying to restore the file on the new machine and I'm getting a 'max_allowed_packet' error: ERROR 1153 at line 162: Got a packet bigger than 'max_allowed_packet I've tried different settings for this in my.cnf, from 32 up to 1024M, and I still get the error. Is there anything I can do to remedy this? Kind regards. -- Luke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
