Moving database, issues with mysql max_allowed_packet

[ChriSsY]

I'm currently trying to move my forum to a new hosting provider, but am having trouble importing the SQL.

I currently have my attachments stored in the filesystem but am still receiving mysql max_allowed_packet error when importing the database via SSH.

I contacted my host about increasing this limit and received this response:


I can say at least that the most common
cause of this error is importing a database backup file containing large
binary data. The packet setting reflects a database policy here wherein
binary data is not permitted to be stored in MySQL databases.

If your data is only plain text but there are a lot of rows in single
INSERT statements, you might try redoing the mysqldump without
--extended-insert. This is part of --opt which is on by default. You
could try using this to get the benefit of everything in --opt except for
--extended-insert.

--skip-opt --add-drop-table --add-locks --create-options --disable-keys
--lock-tables --quick --set-charset

Does anyone have any advice?

[choppers4life]

Edit my.cnf and add
[mysqld_safe]
nice = -5
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M

Then restart mysql

Service mysql restart

[express]

Seems, this is a shared host so setting the my.cnf for you might not be a option, have you tried asking the host to import the database for you?

Also try to empty the postindex table if it has anything in it then dump the db again and try to restore it you can always rebuild search after or not use the postindex option.

Also how are you trying to inport it? SSH, or phpmyadmin, if it is a big db phpmyadmin may not be a option for you.

Joey

[encryption]

install mysqldumper and use it. one of the most brilliant pieces of free software for DB backup / restoration.

[ChriSsY]

Thanks for the input guys, turns out it was an easier option to just switch hosts.

http://www.pair.com/policies/dbresource.html

Thanks anyway!