Another MySQL Error. ERROR 2006 (HY000) at line []: MySQL server has gone away

When I tried to import fairly large SQL file, I got this error. File was too large to be imported.

This was because ‘max_allowed_packet’ variable had fairly small value. Mine was ‘4194304’ which is 4MB.

I set it to 64 and same import went smoothly.

Let’s look at variables in MySQL: SHOW VARIABLES LIKE ‘max%’. This will bring a large list of variables.

SHOW VARIABLES LIKE 'max%';
+----------------------------+----------------------+
| Variable_name              | Value                |
+----------------------------+----------------------+
| max_allowed_packet         | 4194304              |
| max_binlog_cache_size      | 18446744073709547520 |
| max_binlog_size            | 1073741824           |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| max_connect_errors         | 100                  |
| max_connections            | 151                  |
| max_delayed_threads        | 20                   |
| max_digest_length          | 1024                 |
| max_error_count            | 64                   |
| max_execution_time         | 0                    |
| max_heap_table_size        | 16777216             |
| max_insert_delayed_threads | 20                   |
| max_join_size              | 18446744073709551615 |
| max_length_for_sort_data   | 1024                 |
| max_points_in_geometry     | 65536                |
| 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 |
+----------------------------+----------------------+
23 rows in set (0,00 sec)

Let’s set ‘max_allowed_packet’ to 64MB (it’s 67108864 bytes): ‘SET GLOBAL max_allowed_packet = 67108864′.

Since we know what we are looking for, let’s query: ‘SHOW VARIABLES LIKE ‘max_allowed_packet’ or ‘SHOW VARIABLES WHERE variable_name = ‘max_allowed_packet”.

SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| max_allowed_packet | 67108864 |
+--------------------+----------+
1 row in set (0,01 sec)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>