Announcement

Collapse
No announcement yet.

Moving database, issues with mysql max_allowed_packet

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Moving database, issues with mysql max_allowed_packet

    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?

  • #2
    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

    Comment


    • #3
      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
      Real Web Host

      Comment


      • #4
        install mysqldumper and use it. one of the most brilliant pieces of free software for DB backup / restoration.
        www.MJWebhosting.com - (Vbulletin Forum Hosting)
        www.MercuryServer.com - (Our vBulletin forum Managed & hosted by MJWebhosting)

        Comment


        • #5
          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!

          Comment

          widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.
          Working...
          X