Jump to content

Archived

This topic is now archived and is closed to further replies.

JamieWAstin

MySQL Error 2013 - Lost connection to MySQL Server

Recommended Posts

When I try to query or access my local DB server I get the 2013 connection error 4 out of 5 times. I was hoping there was a way to debug where the problem lies.

So far my investigations show:

[blockquote]- This is a problem when i try using PHP, NaviCat and the mysql bin executables

- Upgrading from MySQL 4.0 (where the problem started) to 5.0 didnt help

- My connection timeout is very high (the default, its 8 days or something?)

- After I get the error if i check the server up-time it appears that the server has not been restarted [/blockquote]

Has anyone got any ideas on what could be happening, or what I can do to investigate further?

Thanks,

Jamie

Share this post


Link to post
Share on other sites
So the server starts up ok? When you do this from the local server, do you still get the problem? Have you tried changing your thread-size?

Share this post


Link to post
Share on other sites
The server starts fine, im running it on my Windows development machine. Performing NET START MYSQL has no problems.

As this is my development machine I am only ever accessing it from localhost

I have not tried changing the thread size. Which specific variable in my.ini should I alter?

Thanks for you help,

Jamie

Share this post


Link to post
Share on other sites
Try adding "set-variable = thread_stack=256K" in your my.cnf file.

Share this post


Link to post
Share on other sites
I added that line to the bottom of my.ini, stopped the service, started the service and still no luck!

Trying to connect to the DB i get the error again!

Any other ideas?

Jamie

Share this post


Link to post
Share on other sites
Not really -- there are no errors anywhere other than this one? You should read [a href=\"http://dev.mysql.com/doc/refman/5.0/en/gone-away.html\" target=\"_blank\"]this refman page[/a], and probably [a href=\"http://dev.mysql.com/doc/refman/5.0/en/Can_not_connect_to_server.html\" target=\"_blank\"]this one too[/a]. Maybe you'll find something there. It's very strange that you can lose a connection to a local server! Good luck.

Share this post


Link to post
Share on other sites
Just thought I'd pop my [b]MysqlAdmin variables[/b] up, in case they were any use:

[code]
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| back_log                        | 50                              |
| basedir                         | S:\MySQL Server\                |
| binlog_cache_size               | 32768                           |
| bulk_insert_buffer_size         | 8388608                         |
| character_set_client            | latin1                          |
| character_set_connection        | latin1                          |
| character_set_database          | latin1                          |
| character_set_results           | latin1                          |
| character_set_server            | latin1                          |
| character_set_system            | utf8                            |
| character_sets_dir              | S:\MySQL Server\share\charsets/ |
| collation_connection            | latin1_swedish_ci               |
| collation_database              | latin1_swedish_ci               |
| collation_server                | latin1_swedish_ci               |
| concurrent_insert               | ON                              |
| connect_timeout                 | 5                               |
| datadir                         | S:\MySQL Server\Data\           |
| date_format                     | %Y-%m-%d                        |
| datetime_format                 | %Y-%m-%d %H:%i:%s               |
| default_week_format             | 0                               |
| delay_key_write                 | ON                              |
| delayed_insert_limit            | 100                             |
| delayed_insert_timeout          | 300                             |
| delayed_queue_size              | 1000                            |
| expire_logs_days                | 0                               |
| flush                           | OFF                             |
| flush_time                      | 1800                            |
| ft_boolean_syntax               | + -><()~*:""&|                  |
| ft_max_word_len                 | 84                              |
| ft_min_word_len                 | 4                               |
| ft_query_expansion_limit        | 20                              |
| ft_stopword_file                | (built-in)                      |
| group_concat_max_len            | 1024                            |
| have_archive                    | YES                             |
| have_bdb                        | NO                              |
| have_blackhole_engine           | NO                              |
| have_compress                   | YES                             |
| have_crypt                      | NO                              |
| have_csv                        | NO                              |
| have_example_engine             | NO                              |
| have_geometry                   | YES                             |
| have_innodb                     | YES                             |
| have_isam                       | NO                              |
| have_ndbcluster                 | NO                              |
| have_openssl                    | NO                              |
| have_query_cache                | YES                             |
| have_raid                       | NO                              |
| have_rtree_keys                 | YES                             |
| have_symlink                    | NO                              |
| init_connect                    |                                 |
| init_file                       |                                 |
| init_slave                      |                                 |
| innodb_additional_mem_pool_size | 2097152                         |
| innodb_autoextend_increment     | 8                               |
| innodb_buffer_pool_awe_mem_mb   | 0                               |
| innodb_buffer_pool_size         | 8388608                         |
| innodb_data_file_path           | ibdata1:10M:autoextend          |
| innodb_data_home_dir            |                                 |
| innodb_fast_shutdown            | ON                              |
| innodb_file_io_threads          | 4                               |
| innodb_file_per_table           | OFF                             |
| innodb_flush_log_at_trx_commit  | 1                               |
| innodb_flush_method             |                                 |
| innodb_force_recovery           | 0                               |
| innodb_lock_wait_timeout        | 50                              |
| innodb_locks_unsafe_for_binlog  | OFF                             |
| innodb_log_arch_dir             |                                 |
| innodb_log_archive              | OFF                             |
| innodb_log_buffer_size          | 1048576                         |
| innodb_log_file_size            | 10485760                        |
| innodb_log_files_in_group       | 2                               |
| innodb_log_group_home_dir       | .\                              |
| innodb_max_dirty_pages_pct      | 90                              |
| innodb_max_purge_lag            | 0                               |
| innodb_mirrored_log_groups      | 1                               |
| innodb_open_files               | 300                             |
| innodb_table_locks              | ON                              |
| innodb_thread_concurrency       | 8                               |
| interactive_timeout             | 28800                           |
| join_buffer_size                | 131072                          |
| key_buffer_size                 | 8388608                         |
| key_cache_age_threshold         | 300                             |
| key_cache_block_size            | 1024                            |
| key_cache_division_limit        | 100                             |
| language                        | S:\MySQL Server\share\english\  |
| large_files_support             | ON                              |
| license                         | GPL                             |
| local_infile                    | ON                              |
| log                             | OFF                             |
| log_bin                         | OFF                             |
| log_error                       | .\jimbob.err                    |
| log_slave_updates               | OFF                             |
| log_slow_queries                | OFF                             |
| log_update                      | OFF                             |
| log_warnings                    | 1                               |
| long_query_time                 | 10                              |
| low_priority_updates            | OFF                             |
| lower_case_file_system          | OFF                             |
| lower_case_table_names          | 1                               |
| max_allowed_packet              | 1048576                         |
| max_binlog_cache_size           | 4294967295                      |
| max_binlog_size                 | 1073741824                      |
| max_connect_errors              | 10                              |
| max_connections                 | 100                             |
| max_delayed_threads             | 20                              |
| max_error_count                 | 64                              |
| max_heap_table_size             | 16777216                        |
| max_insert_delayed_threads      | 20                              |
| max_join_size                   | 4294967295                      |
| max_length_for_sort_data        | 1024                            |
| max_relay_log_size              | 0                               |
| max_seeks_for_key               | 4294967295                      |
| max_sort_length                 | 1024                            |
| max_tmp_tables                  | 32                              |
| max_user_connections            | 0                               |
| max_write_lock_count            | 4294967295                      |
| myisam_data_pointer_size        | 4                               |
| myisam_max_extra_sort_file_size | 107374182400                    |
| myisam_max_sort_file_size       | 107374182400                    |
| myisam_recover_options          | OFF                             |
| myisam_repair_threads           | 1                               |
| myisam_sort_buffer_size         | 8388608                         |
| myisam_stats_method             | nulls_unequal                   |
| named_pipe                      | OFF                             |
| net_buffer_length               | 16384                           |
| net_read_timeout                | 30                              |
| net_retry_count                 | 10                              |
| net_write_timeout               | 60                              |
| new                             | OFF                             |
| old_passwords                   | OFF                             |
| open_files_limit                | 622                             |
| pid_file                        | S:\MySQL Server\Data\jimbob.pid |
| port                            | 3306                            |
| preload_buffer_size             | 32768                           |
| protocol_version                | 10                              |
| query_alloc_block_size          | 8192                            |
| query_cache_limit               | 1048576                         |
| query_cache_min_res_unit        | 4096                            |
| query_cache_size                | 0                               |
| query_cache_type                | ON                              |
| query_cache_wlock_invalidate    | OFF                             |
| query_prealloc_size             | 8192                            |
| range_alloc_block_size          | 2048                            |
| read_buffer_size                | 61440                           |
| read_only                       | OFF                             |
| read_rnd_buffer_size            | 258048                          |
| relay_log_purge                 | ON                              |
| relay_log_space_limit           | 0                               |
| rpl_recovery_rank               | 0                               |
| secure_auth                     | OFF                             |
| shared_memory                   | OFF                             |
| shared_memory_base_name         | MYSQL                           |
| server_id                       | 0                               |
| skip_external_locking           | ON                              |
| skip_networking                 | OFF                             |
| skip_show_database              | OFF                             |
| slave_net_timeout               | 3600                            |
| slave_transaction_retries       | 0                               |
| slow_launch_time                | 2                               |
| sort_buffer_size                | 217080                          |
| sql_mode                        |                                 |
| sql_notes                       | ON                              |
| sql_warnings                    | ON                              |
| storage_engine                  | InnoDB                          |
| sync_binlog                     | 0                               |
| sync_frm                        | ON                              |
| sync_replication                | 0                               |
| sync_replication_slave_id       | 0                               |
| sync_replication_timeout        | 0                               |
| system_time_zone                | GMT Standard Time               |
| table_cache                     | 256                             |
| table_type                      | InnoDB                          |
| thread_cache_size               | 25                              |
| thread_stack                    | 262144                          |
| time_format                     | %H:%i:%s                        |
| time_zone                       | SYSTEM                          |
| tmp_table_size                  | 5242880                         |
| tmpdir                          |                                 |
| transaction_alloc_block_size    | 8192                            |
| transaction_prealloc_size       | 4096                            |
| tx_isolation                    | REPEATABLE-READ                 |
| version                         | 4.1.16-nt                       |
| version_comment                 | Official MySQL binary           |
| version_compile_machine         | ia32                            |
| version_compile_os              | Win32                           |
| wait_timeout                    | 28800                           |
+---------------------------------+---------------------------------+
[/code]

Regards,

Jamie

Share this post


Link to post
Share on other sites
Perhaps I have found the problem, in phpMyAdmin I asked for a list of the variable and found:

connect timeout : 5
long query time : 10
wait timeout : 28800

Now the top two dont look very long compared to the third one! How would I go about changing these? As I cannot find them in my.ini

Thoughts?

Jamie

Share this post


Link to post
Share on other sites
I wouldn't worry about the long query wait time -- 10 seconds is a very long query, and even so, this is just for logging, if enabled. If you want to change the connection_timeout, use the same type of syntax as I specified earlier (i.e. set-variable = ... ).

Good luck.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.