Results 1 to 4 of 4
  1. #1

    Red face error when i dump sql data!!

    hello everybody,
    i have some problem with my server which is :

    i have a sql file 1.3GB yes GB
    when i try to dump it into my database by SSH i getting a error massege says : "ERROR 1153 at line 1941: Got a packet bigger than 'max_allowed_packet'"

    i did this to change "'max_allowed_packet'" :
    pico /etc/my.cnf
    max_allowed_packet = 2000M
    and save file and
    mysqld restart

    but no thing change when i try to dump the massege will come back

    i have a big table in my sql file the size of it is 1.2 GB
    the dump stop in 4000 records and quit dumping.

    please let me know if you have any help for that issue.

    thanks

  2. #2
    Join Date
    Sep 2004
    Location
    Chicago, IL
    Posts
    213
    are you exporting to a file or another db? Try increasing the timeout in my.cnf
    Ben Lenard, MS, MBA
    TechMinds 4 Hire, Inc - (866) 214-1285 x 2001
    http://www.tm4h.com

  3. #3
    Also, you could split the big SQL file. Especially if the bulk of it is inserts. IE put the create table into one SQL and split that big puppy.

    Something like this:

    Code:
    split -l 2000 bigdump.sql littledump
    This will create multiple files that have a prefix of littledump and contain a maximum of 2000 lines from bigdump.sql.

    Then just run each of the littledump files and you're good to go.

  4. #4
    Join Date
    May 2005
    Posts
    61
    Try to dump it using this my.cnf which has high values.


    [mysqld]
    safe-show-database
    set-variable = key_buffer=1280M
    set-variable = max_allowed_packet=2000M
    set-variable = table_cache=5000
    set-variable = sort_buffer=10M
    set-variable = join_buffer=128K
    set-variable = record_buffer=1M
    set-variable = net_buffer_length=16K
    set-variable = myisam_sort_buffer_size=6M
    set-variable = thread_cache=4
    set-variable = wait_timeout=120
    set-variable = max_connections=800
    set-variable = max_user_connections=800
    set-variable = query_cache_size=8M
    set-variable = query_cache_type=1
    set-variable = wait_timeout=60
    set-variable = thread_concurrency=2
    set-variable = thread_cache_size=300

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •