ERROR 1153 at line 50377: Got a packet bigger than 'max_allowed_packet'

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • SaN-DeeP
    Senior Member
    • Dec 2003
    • 1352
    • 1.1.x

    ERROR 1153 at line 50377: Got a packet bigger than 'max_allowed_packet'

    Trying to restore a database using ssh., where i get the above error.
    DB Size = 650 MB
    Control Panel = plesk
    System = Unix

    Any quick help is apprecaited

    Regards,
    TechArena - TechArena Community - Technology News - Tools Download - Tech Video - Gamer Guide - Hardware Review
  • SaN-DeeP
    Senior Member
    • Dec 2003
    • 1352
    • 1.1.x

    #2
    I tried to change the max_allowed_packet size as well

    getting this error in SSH

    [root@vps etc]# mysqldump -p set-variable = max_allowed_packet = 32M
    Enter password:
    mysqldump: Got error: 1045: Access denied for user: 'root@localhost' (Using password: YES) when trying to connect
    [root@vps etc]#

    Some of my Stats, generated with a script under this forums:
    Code:
    Http processes currently running = Mysql processes currently running = 
    Netstat information summary
    
    
    +---------------------------+-----------------+
    | Variable_name             | Value           |
    +---------------------------+-----------------+
    | Aborted_clients           | 12              |
    | Aborted_connects          | 16              |
    | Bytes_received            | 265567817       |
    | Bytes_sent                | 2555422         |
    | Com_admin_commands        | 0               |
    | Com_alter_table           | 0               |
    | Com_analyze               | 0               |
    | Com_backup_table          | 0               |
    | Com_begin                 | 3               |
    | Com_change_db             | 358             |
    | Com_change_master         | 0               |
    | Com_check                 | 0               |
    | Com_commit                | 3               |
    | Com_create_db             | 2               |
    | Com_create_function       | 0               |
    | Com_create_index          | 0               |
    | Com_create_table          | 142             |
    | Com_delete                | 56              |
    | Com_delete_multi          | 0               |
    | Com_drop_db               | 1               |
    | Com_drop_function         | 0               |
    | Com_drop_index            | 0               |
    | Com_drop_table            | 1               |
    | Com_flush                 | 3               |
    | Com_grant                 | 0               |
    | Com_ha_close              | 0               |
    | Com_ha_open               | 0               |
    | Com_ha_read               | 0               |
    | Com_insert                | 97698           |
    | Com_insert_select         | 0               |
    | Com_kill                  | 0               |
    | Com_load                  | 0               |
    | Com_load_master_data      | 0               |
    | Com_load_master_table     | 0               |
    | Com_lock_tables           | 0               |
    | Com_optimize              | 0               |
    | Com_purge                 | 0               |
    | Com_rename_table          | 0               |
    | Com_repair                | 0               |
    | Com_replace               | 207             |
    | Com_replace_select        | 2               |
    | Com_reset                 | 0               |
    | Com_restore_table         | 0               |
    | Com_revoke                | 0               |
    | Com_rollback              | 0               |
    | Com_savepoint             | 0               |
    | Com_select                | 3089            |
    | Com_set_option            | 191             |
    | Com_show_binlog_events    | 0               |
    | Com_show_binlogs          | 0               |
    | Com_show_create           | 191             |
    | Com_show_databases        | 12              |
    | Com_show_fields           | 190             |
    | Com_show_grants           | 1               |
    | Com_show_keys             | 0               |
    | Com_show_logs             | 0               |
    | Com_show_master_status    | 0               |
    | Com_show_new_master       | 0               |
    | Com_show_open_tables      | 0               |
    | Com_show_processlist      | 0               |
    | Com_show_slave_hosts      | 0               |
    | Com_show_slave_status     | 0               |
    | Com_show_status           | 2               |
    | Com_show_innodb_status    | 0               |
    | Com_show_tables           | 203             |
    | Com_show_variables        | 4               |
    | Com_slave_start           | 0               |
    | Com_slave_stop            | 0               |
    | Com_truncate              | 0               |
    | Com_unlock_tables         | 0               |
    | Com_update                | 223             |
    | Connections               | 1495            |
    | Created_tmp_disk_tables   | 3               |
    | Created_tmp_tables        | 135             |
    | Created_tmp_files         | 332             |
    | Delayed_insert_threads    | 0               |
    | Delayed_writes            | 0               |
    | Delayed_errors            | 0               |
    | Flush_commands            | 3               |
    | Handler_commit            | 3               |
    | Handler_delete            | 2               |
    | Handler_read_first        | 1283            |
    | Handler_read_key          | 9628            |
    | Handler_read_next         | 2627            |
    | Handler_read_prev         | 0               |
    | Handler_read_rnd          | 72              |
    | Handler_read_rnd_next     | 21562           |
    | Handler_rollback          | 386             |
    | Handler_update            | 18              |
    | Handler_write             | 97987           |
    | Key_blocks_used           | 1690            |
    | Key_read_requests         | 320537          |
    | Key_reads                 | 12              |
    | Key_write_requests        | 149074          |
    | Key_writes                | 150795          |
    | Max_used_connections      | 3               |
    | Not_flushed_key_blocks    | 0               |
    | Not_flushed_delayed_rows  | 0               |
    | Open_tables               | 64              | 100% of table_cache in use
    | Open_files                | 94              |
    | Open_streams              | 0               |
    | Opened_tables             | 882             |
    | Questions                 | 114205          |
    | Qcache_queries_in_cache   | 0               |
    | Qcache_inserts            | 0               |
    | Qcache_hits               | 0               |
    | Qcache_lowmem_prunes      | 0               |
    | Qcache_not_cached         | 0               |
    | Qcache_free_memory        | 0               |
    | Qcache_free_blocks        | 0               |
    | Qcache_total_blocks       | 0               |
    | Rpl_status                | NULL            |
    | Select_full_join          | 3               |
    | Select_full_range_join    | 0               |
    | Select_range              | 51              |
    | Select_range_check        | 0               |
    | Select_scan               | 1090            |
    | Slave_open_temp_tables    | 0               |
    | Slave_running             | OFF             |
    | Slow_launch_threads       | 0               |
    | Slow_queries              | 0               | (execution time > 10 secs)
    | Sort_merge_passes         | 0               |
    | Sort_range                | 13              |
    | Sort_rows                 | 72              |
    | Sort_scan                 | 151             |
    | Table_locks_immediate     | 102341          |
    | Table_locks_waited        | 0               |
    | Threads_cached            | 0               |
    | Threads_created           | 1494            |
    | Threads_connected         | 1               |
    | Threads_running           | 1               |
    | Uptime                    | 336821          | 3 days 21 hrs 33 mins 41 secs
    +---------------------------+-----------------+
    
    
    Key Reads/Key Read Requests = 0.000037  (Cache hit = 99.999963%)
    Key Writes/Key Write Requests = 1.011545
    Connections/second = 0.004 (/hour = 15.979)
    KB received/second = 0.770 (/hour = 2771.902)
    KB sent/second = 0.007 (/hour = 26.667)
    Temporary Tables Created/second = 0.000 (/hour = 1.443)
    Opened Tables/second = 0.003 (/hour = 9.427)
    Slow Queries/second = 0.000 (/hour = 0.000)
    % of slow queries = 0.000%
    Queries/second = 0.339 (/hour = 1220.642)
    TechArena - TechArena Community - Technology News - Tools Download - Tech Video - Gamer Guide - Hardware Review

    Comment

    • SaN-DeeP
      Senior Member
      • Dec 2003
      • 1352
      • 1.1.x

      #3
      mysqladmin variables

      Code:
      --------------------------------------------------------+
      | Variable_name                   | Value                                                                                                                                                                                                             |
      +---------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | back_log                        | 50                                                                                                                                                                                                                |
      | basedir                         | /usr/                                                                                                                                                                                                             |
      | binlog_cache_size               | 32768                                                                                                                                                                                                             |
      | bulk_insert_buffer_size         | 8388608                                                                                                                                                                                                           |
      | character_set                   | latin1                                                                                                                                                                                                            |
      | character_sets                  | latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin1_de latin2 latin5 sjis swe7 tis620 ujis usa7 win1250 win1251ukr win1251 |
      | concurrent_insert               | ON                                                                                                                                                                                                                |
      | connect_timeout                 | 5                                                                                                                                                                                                                 |
      | convert_character_set           |                                                                                                                                                                                                                   |
      | datadir                         | /var/lib/mysql/                                                                                                                                                                                                   |
      | default_week_format             | 0                                                                                                                                                                                                                 |
      | delay_key_write                 | ON                                                                                                                                                                                                                |
      | delayed_insert_limit            | 100                                                                                                                                                                                                               |
      | delayed_insert_timeout          | 300                                                                                                                                                                                                               |
      | delayed_queue_size              | 1000                                                                                                                                                                                                              |
      | flush                           | OFF                                                                                                                                                                                                               |
      | flush_time                      | 0                                                                                                                                                                                                                 |
      | ft_boolean_syntax               | + -><()~*:""&|                                                                                                                                                                                                    |
      | ft_min_word_len                 | 4                                                                                                                                                                                                                 |
      | ft_max_word_len                 | 254                                                                                                                                                                                                               |
      | ft_max_word_len_for_sort        | 20                                                                                                                                                                                                                |
      | ft_stopword_file                | (built-in)                                                                                                                                                                                                        |
      | have_bdb                        | NO                                                                                                                                                                                                                |
      | have_crypt                      | YES                                                                                                                                                                                                               |
      | have_innodb                     | YES                                                                                                                                                                                                               |
      | have_isam                       | YES                                                                                                                                                                                                               |
      | have_raid                       | NO                                                                                                                                                                                                                |
      | have_symlink                    | YES                                                                                                                                                                                                               |
      | have_openssl                    | NO                                                                                                                                                                                                                |
      | have_query_cache                | YES                                                                                                                                                                                                               |
      | init_file                       |                                                                                                                                                                                                                   |
      | innodb_additional_mem_pool_size | 1048576                                                                                                                                                                                                           |
      | innodb_buffer_pool_size         | 8388608                                                                                                                                                                                                           |
      | innodb_data_file_path           | ibdata1:10M:autoextend                                                                                                                                                                                            |
      | innodb_data_home_dir            |                                                                                                                                                                                                                   |
      | innodb_file_io_threads          | 4                                                                                                                                                                                                                 |
      | innodb_force_recovery           | 0                                                                                                                                                                                                                 |
      | innodb_thread_concurrency       | 8                                                                                                                                                                                                                 |
      | innodb_flush_log_at_trx_commit  | 1                                                                                                                                                                                                                 |
      | innodb_fast_shutdown            | ON                                                                                                                                                                                                                |
      | innodb_flush_method             |                                                                                                                                                                                                                   |
      | innodb_lock_wait_timeout        | 50                                                                                                                                                                                                                |
      | innodb_log_arch_dir             | ./                                                                                                                                                                                                                |
      | innodb_log_archive              | OFF                                                                                                                                                                                                               |
      | innodb_log_buffer_size          | 1048576                                                                                                                                                                                                           |
      | innodb_log_file_size            | 5242880                                                                                                                                                                                                           |
      | innodb_log_files_in_group       | 2                                                                                                                                                                                                                 |
      | innodb_log_group_home_dir       | ./                                                                                                                                                                                                                |
      | innodb_mirrored_log_groups      | 1                                                                                                                                                                                                                 |
      | innodb_max_dirty_pages_pct      | 90                                                                                                                                                                                                                |
      | interactive_timeout             | 28800                                                                                                                                                                                                             |
      | join_buffer_size                | 131072                                                                                                                                                                                                            |
      | key_buffer_size                 | 8388600                                                                                                                                                                                                           |
      | language                        | /usr/share/mysql/english/                                                                                                                                                                                         |
      | large_files_support             | ON                                                                                                                                                                                                                |
      | license                         | GPL                                                                                                                                                                                                               |
      | local_infile                    | ON                                                                                                                                                                                                                |
      | locked_in_memory                | OFF                                                                                                                                                                                                               |
      | log                             | OFF                                                                                                                                                                                                               |
      | log_update                      | OFF                                                                                                                                                                                                               |
      | log_bin                         | OFF                                                                                                                                                                                                               |
      | log_slave_updates               | OFF                                                                                                                                                                                                               |
      | log_slow_queries                | OFF                                                                                                                                                                                                               |
      | log_warnings                    | 1                                                                                                                                                                                                                 |
      | long_query_time                 | 10                                                                                                                                                                                                                |
      | low_priority_updates            | OFF                                                                                                                                                                                                               |
      | lower_case_file_system          | OFF                                                                                                                                                                                                               |
      | lower_case_table_names          | 0                                                                                                                                                                                                                 |
      | max_allowed_packet              | 1048576                                                                                                                                                                                                           |
      | max_binlog_cache_size           | 4294967295                                                                                                                                                                                                        |
      | max_binlog_size                 | 1073741824                                                                                                                                                                                                        |
      | max_connections                 | 100                                                                                                                                                                                                               |
      | max_connect_errors              | 10                                                                                                                                                                                                                |
      | max_delayed_threads             | 20                                                                                                                                                                                                                |
      | max_insert_delayed_threads      | 20                                                                                                                                                                                                                |
      | max_heap_table_size             | 16777216                                                                                                                                                                                                          |
      | max_join_size                   | 4294967295                                                                                                                                                                                                        |
      | max_relay_log_size              | 0                                                                                                                                                                                                                 |
      | max_seeks_for_key               | 4294967295                                                                                                                                                                                                        |
      | max_sort_length                 | 1024                                                                                                                                                                                                              |
      | max_user_connections            | 0                                                                                                                                                                                                                 |
      | max_tmp_tables                  | 32                                                                                                                                                                                                                |
      | max_write_lock_count            | 4294967295                                                                                                                                                                                                        |
      | myisam_max_extra_sort_file_size | 268435456                                                                                                                                                                                                         |
      | myisam_max_sort_file_size       | 2147483647                                                                                                                                                                                                        |
      | myisam_repair_threads           | 1                                                                                                                                                                                                                 |
      | myisam_recover_options          | OFF                                                                                                                                                                                                               |
      | myisam_sort_buffer_size         | 8388608                                                                                                                                                                                                           |
      | net_buffer_length               | 16384                                                                                                                                                                                                             |
      | net_read_timeout                | 30                                                                                                                                                                                                                |
      | net_retry_count                 | 10                                                                                                                                                                                                                |
      | net_write_timeout               | 60                                                                                                                                                                                                                |
      | new                             | OFF                                                                                                                                                                                                               |
      | open_files_limit                | 1024                                                                                                                                                                                                              |
      | pid_file                        | /var/run/mysqld/mysqld.pid                                                                                                                                                                                        |
      | log_error                       |                                                                                                                                                                                                                   |
      | port                            | 0                                                                                                                                                                                                                 |
      | protocol_version                | 10                                                                                                                                                                                                                |
      | query_alloc_block_size          | 8192                                                                                                                                                                                                              |
      | query_cache_limit               | 1048576                                                                                                                                                                                                           |
      | query_cache_size                | 0                                                                                                                                                                                                                 |
      | query_cache_type                | ON                                                                                                                                                                                                                |
      | query_prealloc_size             | 8192                                                                                                                                                                                                              |
      | range_alloc_block_size          | 2048                                                                                                                                                                                                              |
      | read_buffer_size                | 131072                                                                                                                                                                                                            |
      | read_only                       | OFF                                                                                                                                                                                                               |
      | read_rnd_buffer_size            | 262144                                                                                                                                                                                                          |
      | rpl_recovery_rank               | 0                                                                                                                                                                                                                 |
      | server_id                       | 0                                                                                                                                                                                                                 |
      | slave_net_timeout               | 3600                                                                                                                                                                                                              |
      | skip_external_locking           | ON                                                                                                                                                                                                                |
      | skip_networking                 | ON                                                                                                                                                                                                                |
      | skip_show_database              | OFF                                                                                                                                                                                                               |
      | slow_launch_time                | 2                                                                                                                                                                                                                 |
      | socket                          | /var/lib/mysql/mysql.sock                                                                                                                                                                                         |
      | sort_buffer_size                | 2097144                                                                                                                                                                                                           |
      | sql_mode                        | 0                                                                                                                                                                                                                 |
      | table_cache                     | 64                                                                                                                                                                                                                |
      | table_type                      | MYISAM                                                                                                                                                                                                            |
      | thread_cache_size               | 0                                                                                                                                                                                                                 |
      | thread_stack                    | 196608                                                                                    
      | tx_isolation                    | REPEATABLE-READ
      
      | timezone                        | EST
      
      | tmp_table_size                  | 33554432
      
      | tmpdir                          | /tmp/
      
      | transaction_alloc_block_size    | 8192
      
      | transaction_prealloc_size       | 4096
      
      | version                         | 4.0.21
      
      | version_comment                 | Source distribution
      
      | version_compile_os              | pc-linux-gnu
      
      | wait_timeout                    | 28800
      
      +---------------------------------+---------------------------------------------
      TechArena - TechArena Community - Technology News - Tools Download - Tech Video - Gamer Guide - Hardware Review

      Comment

      • Steve Machol
        Former Customer Support Manager
        • Jul 2000
        • 154488

        #4
        I think this should be:

        mysqldump -u DBUSER -p --set-variable var=max_allowed_packet = 32M > backup.sql

        Note, that is for the dump only. There is no equivalent optiion during the restore. You will have to manually edit my.cnf.
        Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
        Change CKEditor Colors to Match Style (for 4.1.4 and above)

        Steve Machol Photography


        Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


        Comment

        • SaN-DeeP
          Senior Member
          • Dec 2003
          • 1352
          • 1.1.x

          #5
          how long does it takes to restore the database ?

          any idea ? on this
          its been 3 hours already and my restore is still continuing via ssh
          I am checking the imported database from phpmyadmin

          Is this normal or something messup ? can i make the process faster someway ?

          My postindex has 5,109,804 records and around 115 mb in size, which is causing this huge delay i assume.

          Regards,
          TechArena - TechArena Community - Technology News - Tools Download - Tech Video - Gamer Guide - Hardware Review

          Comment

          • Steve Machol
            Former Customer Support Manager
            • Jul 2000
            • 154488

            #6
            How large is the total db?
            Steve Machol, former vBulletin Customer Support Manager (and NOT retired!)
            Change CKEditor Colors to Match Style (for 4.1.4 and above)

            Steve Machol Photography


            Mankind is the only creature smart enough to know its own history, and dumb enough to ignore it.


            Comment

            • Anfiniti Tidus
              New Member
              • Dec 2004
              • 4
              • 3.0.4

              #7
              Here is a tip for future reference:

              If your MySQL installation supports it, you can add the -e flag to your mysqldump command, which will make database restoration ALOT faster, especially on large db's.

              Comment

              • SaN-DeeP
                Senior Member
                • Dec 2003
                • 1352
                • 1.1.x

                #8
                Originally posted by Steve Machol
                How large is the total db?
                DB is 650 MB in size, and it took me 5 hours to restore the same.. thats a huge time.
                posts and postindex tables took the max time..
                TechArena - TechArena Community - Technology News - Tools Download - Tech Video - Gamer Guide - Hardware Review

                Comment

                • wintech2005
                  New Member
                  • Jan 2005
                  • 9
                  • 3.0.5

                  #9
                  Go to your my.cnf file (/etc/my.cnf)

                  And add "max_allowed_packet=16M" under "[mysqld]" so your file should be:

                  [mysqld]
                  datadir=/var/lib/mysql
                  socket=/var/lib/mysql/mysql.sock
                  query_cache_type=1
                  query_cache_size=10m
                  max_allowed_packet=16M

                  [mysql.server]
                  user=mysql
                  basedir=/var/lib

                  [safe_mysqld]
                  err-log=/var/log/mysqld.log
                  pid-file=/var/run/mysqld/mysqld.pid

                  Well at least this is how my file looks like.
                  Then restart mysql and run:

                  mysqldump -u DBUSER -p database > backup.sql

                  This worked for me on a 300MB database file.
                  If it doesn't work again, try chaning 16MB to 32MB.

                  I think it will work now

                  Comment

                  • jumbo-mumbo
                    Senior Member
                    • Dec 2007
                    • 443
                    • 4.0.0

                    #10
                    had same error.. i changed max_allowed_packet=16M to 64m
                    and it seems to be working now

                    Thanks

                    Comment

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