hi all
I am using moodle 2.3.11.Then i want to restore a database of 2Gb file size to my moodle.I did the steps.But after some mints they are showing error messeges
"Line 5090: mysql server has gone away"
So i edited in my.cnf "max_allowed_packet=2500mb"
Then restarted the mysql and apache.But still the problem exists.Please help me.
Designator is "M" (by itself), I believe - max_allowed_packet=2500M (no 'b').
The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.
mysql> SHOW VARIABLES;Anytime one changes configuration of either Apache or MySQL it's a good practice to check the logs of either to see if there is an error in restarting.
'spirit of sharing', Ken
Thanks for your support.Sir my databse is almost 2250 mb.Got errors always during restore.Sir error is"at 5090:server has gone away"
How can i fix that sir.Please help me sir.
Ok, so line '5090' of what file?
'server has gone away' errors are most often max_packet related, but could also be network. So is the DB on local host or is the DB server another box? Running what version of Moodle? Running what OS (Wonders?)?
Also suggest pasting all of debug here so we can see all of what is being reported. Could be an issue with a mod/block or something in the course.
'spirit of sharing', Ken
Hi sir
Thanks for your time and help.
I am using moodle 2.3.11 and database and moodle code on local server.Database having size of almost 2250 mb.OS is ubuntu 14.04 LTS.Then i take the DB from another moodle 2.3+ .Got that very easily.Then my next task is to restore that db on my local server.Then during the restore they are showing error after 10 mints.And also i checked moodle front end.I got the home page.
I have exp on database backup and restore.Did so many times.But at that time the DB size is just 15 mb.So no idea why this.,Then i searched on internet and got some links
Set values on mysql or in my.cnf
max_allowed_packetSET GLOBAL max_allowed_packet
Tried all and restarted apache and mysql all are working fine.But still have error.
Error is " ERROR 2006 (HY000) at line 5090: MySQL server has gone away "
sir
I will show you the result of show variables;
|
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffering | all |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_io_capacity | 200 |
| innodb_large_prefix | OFF |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 0 |
| innodb_open_files | 300 |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 0 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 5.5.38 |
| innodb_write_io_threads | 4 |
| insert_id | 0 |
| interactive_timeout | 28800 |
| join_buffer_size | 131072 |
| keep_files_on_create | OFF |
| key_buffer_size | 16777216 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| large_files_support | ON |
| large_page_size | 0 |
| large_pages | OFF |
| last_insert_id | 0 |
| lc_messages | en_US |
| lc_messages_dir | /usr/share/mysql/ |
| lc_time_names | en_US |
| license | GPL |
| local_infile | ON |
| lock_wait_timeout | 31536000 |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | /var/log/mysql/error.log |
| log_output | FILE |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10.000000 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 16777216 |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 104857600 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_connect_errors | 10 |
| max_connections | 151 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 18446744073709551615 |
| max_length_for_sort_data | 1024 |
| max_long_data_size | 16777216 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 18446744073709551615 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 18446744073709551615 |
| metadata_locks_cache_size | 1024 |
| min_examined_row_limit | 0 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 9223372036853727232 |
| myisam_mmap_size | 18446744073709551615 |
| myisam_recover_options | BACKUP |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| myisam_use_mmap | OFF |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old | OFF |
| old_alter_table | OFF |
| old_passwords | OFF |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on |
| performance_schema | OFF |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 1000 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 10000 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | 1000000 |
| performance_schema_max_rwlock_classes | 30 |
| performance_schema_max_rwlock_instances | 1000000 |
| performance_schema_max_table_handles | 100000 |
| performance_schema_max_table_instances | 50000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
| pid_file | /var/run/mysqld/mysqld.pid |
| plugin_dir | /usr/lib/mysql/plugin/ |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| proxy_user | |
| pseudo_slave_mode | OFF |
| pseudo_thread_id | 38 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| rand_seed1 | 0 |
| rand_seed2 | 0 |
| range_alloc_block_size | 4096 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log | |
| relay_log_index | |
| relay_log_info_file | relay-log.info |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| report_host | |
| report_password | |
| report_port | 3306 |
| report_user | |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 0 |
| skip_external_locking | ON |
| skip_name_resolve | OFF |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_exec_mode | STRICT |
| slave_load_tmpdir | /tmp |
| slave_max_allowed_packet | 1073741824 |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slave_type_conversions | |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/vineeth-System-Product-Name-slow.log |
| socket | /var/run/mysqld/mysqld.sock |
| sort_buffer_size | 2097152 |
| sql_auto_is_null | OFF |
| sql_big_selects | ON |
| sql_big_tables | OFF |
| sql_buffer_result | OFF |
| sql_log_bin | ON |
| sql_log_off | OFF |
| sql_low_priority_updates | OFF |
| sql_max_join_size | 18446744073709551615 |
| sql_mode | |
| sql_notes | ON |
| sql_quote_show_create | ON |
| sql_safe_updates | OFF |
| sql_select_limit | 18446744073709551615 |
| sql_slave_skip_counter | 0 |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | InnoDB |
| stored_program_cache | 256 |
| sync_binlog | 0 |
| sync_frm | ON |
| sync_master_info | 0 |
| sync_relay_log | 0 |
| sync_relay_log_info | 0 |
| system_time_zone | IST |
| table_definition_cache | 400 |
| table_open_cache | 400 |
| thread_cache_size | 8 |
| thread_concurrency | 10 |
| thread_handling | one-thread-per-connection |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| timestamp | 1417700842 |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| unique_checks | ON |
| updatable_views_with_limit | YES |
| version | 5.5.38-0ubuntu0.14.04.1 |
| version_comment | (Ubuntu) |
| version_compile_machine | x86_64 |
| version_compile_os | debian-linux-gnu |
| wait_timeout | 28800 |
| warning_count | 0 |
The use of 'set global' is temporary. If one restarts MySQL any variable set via set global is reverted to defaults.
Your settings show:
| max_allowed_packet | 16777216 - Is 16M
Make the setting in my.cnf and restart mysql.
Then check via command line/mysql client:
SHOW VARIABLES LIKE 'max_allowed_packet'
'spirit of sharing', Ken
Thanks sir.......I will change the value and inform you
Here's the official page for the error reported:
http://dev.mysql.com/doc/refman/5.5/en/gone-away.html
Google for 'ERROR 2006 (HY000)' yourself and you'll see quite a few hits.
So, let me get this straight ...
you've done an mysqldump of a 2.3 version db to an sql file.
That command using mysqldump looked like what?
you transferred the sql file to a local machine. How did you transfer that file to local machine? Might need to tar ball/zip the .sql file then transfer it to protect the text inside the .sql file.
Now you are trying to import the sql file into a new DB on the local machine ... show command you are using.
Have you turned on logging in MySQL? Is there an error log for MySQL you can reference?
'spirit of sharing', Ken
Sir
Thanks for your support.
Export command
sudo mysqldump - u username -p dbname > path to .sql file
import
sudo mysql -u username -p dbname < path to .sql file.
my error log is
141209 14:56:26 [Note] Event Scheduler: Purging the queue. 0 events
141209 14:56:26 InnoDB: Starting shutdown...
141209 14:56:27 InnoDB: Shutdown completed; log sequence number 2336225673
141209 14:56:27 [Note] /usr/sbin/mysqld: Shutdown complete
141209 15:58:20 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
141209 15:58:20 [Note] Plugin 'FEDERATED' is disabled.
141209 15:58:20 InnoDB: The InnoDB memory heap is disabled
141209 15:58:20 InnoDB: Mutexes and rw_locks use GCC atomic builtins
141209 15:58:20 InnoDB: Compressed tables use zlib 1.2.8
141209 15:58:20 InnoDB: Using Linux native AIO
141209 15:58:20 InnoDB: Initializing buffer pool, size = 128.0M
141209 15:58:20 InnoDB: Completed initialization of buffer pool
141209 15:58:20 InnoDB: highest supported file format is Barracuda.
141209 15:58:31 InnoDB: Waiting for the background threads to start
141209 15:58:32 InnoDB: 5.5.38 started; log sequence number 2336225673
141209 15:58:32 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
141209 15:58:32 [Note] - '127.0.0.1' resolves to '127.0.0.1';
141209 15:58:32 [Note] Server socket created on IP: '127.0.0.1'.
141209 15:58:33 [Note] Event Scheduler: Loaded 0 events
141209 15:58:33 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.38-0ubuntu0.14.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
I did not understand anything from this.Am totally confused sir.