edcharleslynn
asked on
PHP suddenly unable to connect to MySQL on Windows 2003 server - Error 10055
We have website running PHP on Windows Server 2003 served by IIS. We're also using a MySQL db, which is hosted on a separate server.
Most of the time the connection to the MySQL server works fine. However we've had an issue recently where the website will suddenly become unable to get a connection to the MySQL DB. This is usually temporary, for around 5 minutes, after which normal service is resumed. But more recently the MySQL connection has not begun connecting again. The error we're getting when the connection fails is:
MySQL Error Number 2003 Can't connect to MySQL server on {IP} (10055)
Even connecting to MySQL through the MySQL Administrator (GUI) from the web server to the db server fails. The MySQL server itself is definately running and can be connected to locally, or from any other machine, i.e. my desktop. It seems that it's the connection between the webserver and the db server which is failing.
A reboot of the webserver seems to sort the issue out for a week or two but it tends to reoccur after that.
I've seen a very simliar issue reported on the MySQL bugs site below:
http://bugs.mysql.com/bug.php?id=24744
I suspect our issue is the same as that described above.
It appears that it is not a MySQL error but is something to do with Windows and buffer limits, sockets? I'm afraid I'm no expert on Windows OS admin.
Any advice on how to resolve this would be greatly appreciated.
Most of the time the connection to the MySQL server works fine. However we've had an issue recently where the website will suddenly become unable to get a connection to the MySQL DB. This is usually temporary, for around 5 minutes, after which normal service is resumed. But more recently the MySQL connection has not begun connecting again. The error we're getting when the connection fails is:
MySQL Error Number 2003 Can't connect to MySQL server on {IP} (10055)
Even connecting to MySQL through the MySQL Administrator (GUI) from the web server to the db server fails. The MySQL server itself is definately running and can be connected to locally, or from any other machine, i.e. my desktop. It seems that it's the connection between the webserver and the db server which is failing.
A reboot of the webserver seems to sort the issue out for a week or two but it tends to reoccur after that.
I've seen a very simliar issue reported on the MySQL bugs site below:
http://bugs.mysql.com/bug.php?id=24744
I suspect our issue is the same as that described above.
It appears that it is not a MySQL error but is something to do with Windows and buffer limits, sockets? I'm afraid I'm no expert on Windows OS admin.
Any advice on how to resolve this would be greatly appreciated.
ASKER
There's nothing in the MySQL error log - the MySQL doesn't appear to be the issue as this remains running and can be accessed even when the webserver is unable to connect.
global mysql settings:
global mysql settings:
mysql> show global status;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 108 |
| Aborted_connects | 12 |
| Binlog_cache_disk_use | 46895 |
| Binlog_cache_use | 8856782 |
| Bytes_received | 1915413993 |
| Bytes_sent | 791613322 |
| Com_admin_commands | 2476 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 8849214 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 24 |
| Com_dealloc_sql | 0 |
| Com_delete | 12486 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 24 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 1 |
| Com_insert | 782 |
| 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_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 12311243 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 7729052 |
| Com_set_option | 62 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 1 |
| Com_show_collations | 36 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 4 |
| Com_show_databases | 8 |
| Com_show_errors | 0 |
| Com_show_fields | 15 |
| Com_show_grants | 2 |
| Com_show_innodb_status | 1912 |
| Com_show_keys | 12 |
| Com_show_logs | 0 |
| Com_show_master_status | 14 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 208 |
| Com_show_slave_hosts | 15 |
| Com_show_slave_status | 9 |
| Com_show_status | 1914 |
| Com_show_storage_engines | 1 |
| Com_show_tables | 23 |
| Com_show_triggers | 0 |
| Com_show_variables | 45 |
| Com_show_warnings | 0 |
| Com_slave_start | 1 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 41474 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
| Connections | 8849285 |
| Created_tmp_disk_tables | 23 |
| Created_tmp_files | 46900 |
| Created_tmp_tables | 2070 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 21179397 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 17713564 |
| Handler_read_first | 26 |
| Handler_read_key | 46146367 |
| Handler_read_next | 7283675 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 42465 |
| Handler_read_rnd_next | 1196861 |
| Handler_rollback | 1114 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 2115 |
| Handler_write | 12783253 |
| Innodb_buffer_pool_pages_data | 15983 |
| Innodb_buffer_pool_pages_dirty | 70 |
| Innodb_buffer_pool_pages_flushed | 14855737 |
| Innodb_buffer_pool_pages_free | 1 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 16 |
| Innodb_buffer_pool_pages_total | 16000 |
| Innodb_buffer_pool_read_ahead_rnd | 1188 |
| Innodb_buffer_pool_read_ahead_seq | 6 |
| Innodb_buffer_pool_read_requests | 517049337 |
| Innodb_buffer_pool_reads | 470741 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 212722799 |
| Innodb_data_fsyncs | 22526409 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 3939061760 |
| Innodb_data_reads | 502442 |
| Innodb_data_writes | 36962146 |
| Innodb_data_written | 652268032 |
| Innodb_dblwr_pages_written | 14855737 |
| Innodb_dblwr_writes | 442567 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 219806338 |
| Innodb_log_writes | 21356139 |
| Innodb_os_log_fsyncs | 21581408 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 3370494464 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 211325 |
| Innodb_pages_read | 502432 |
| Innodb_pages_written | 14855737 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 509091428 |
| Innodb_row_lock_time_avg | 66521 |
| Innodb_row_lock_time_max | 428596 |
| Innodb_row_lock_waits | 7653 |
| Innodb_rows_deleted | 782211 |
| Innodb_rows_inserted | 827129 |
| Innodb_rows_read | 19423028 |
| Innodb_rows_updated | 11483003 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 115681 |
| Key_blocks_used | 2417 |
| Key_read_requests | 863703 |
| Key_reads | 29460 |
| Key_write_requests | 50461 |
| Key_writes | 30076 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 119 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 8 |
| Open_streams | 0 |
| Open_tables | 7 |
| Opened_tables | 6190 |
| Qcache_free_blocks | 4 |
| Qcache_free_memory | 88009552 |
| Qcache_hits | 1162274 |
| Qcache_inserts | 7611255 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 119863 |
| Qcache_queries_in_cache | 22 |
| Qcache_total_blocks | 50 |
| Questions | 38960213 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 2070 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 2 |
| Slave_running | OFF |
| Slow_launch_threads | 2 |
| Slow_queries | 1486 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 20095099 |
| Table_locks_waited | 0 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 32 |
| Threads_connected | 8 |
| Threads_created | 1802 |
| Threads_running | 2 |
| Uptime | 2412213 |
+-----------------------------------+------------+
245 rows in set (0.16 sec)
|
| 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 | 3145728
|
| innodb_log_file_size | 52428800
|
| 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_support_xa | ON
|
| innodb_sync_spin_loops | 20
|
| innodb_table_locks | ON
|
| innodb_thread_concurrency | 8
|
| innodb_thread_sleep_delay | 10000
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 135266304
|
| key_cache_age_threshold | 300
|
| key_cache_block_size | 1024
|
| key_cache_division_limit | 100
|
| language | C:\Program Files\MySQL\MySQL Server 5.0\shar
e\english\ |
| large_files_support | ON
|
| large_page_size | 0
|
| large_pages | OFF
|
| license | GPL
|
| local_infile | ON
|
| log | OFF
|
| log_bin | ON
|
| log_bin_trust_function_creators | OFF
|
| log_error | .\pp-sql-01.err
|
| log_queries_not_using_indexes | 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 | 1
|
| max_allowed_packet | 1048576
|
| max_binlog_cache_size | 4294967295
|
| max_binlog_size | 1073741824
|
| max_connect_errors | 10
|
| max_connections | 800
|
| 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_prepared_stmt_count | 16382
|
| max_relay_log_size | 0
|
| max_seeks_for_key | 4294967295
|
| max_sort_length | 1024
|
| max_sp_recursion_depth | 0
|
| max_tmp_tables | 32
|
| max_user_connections | 0
|
| max_write_lock_count | 4294967295
|
| multi_range_count | 256
|
| myisam_data_pointer_size | 6
|
| myisam_max_sort_file_size | 107374182400
|
| myisam_recover_options | OFF
|
| myisam_repair_threads | 1
|
| myisam_sort_buffer_size | 31457280
|
| 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 | ON
|
| open_files_limit | 2048
|
| optimizer_prune_level | 1
|
| optimizer_search_depth | 62
|
| pid_file | L:\SQLDATA\MySQL\pp-sql-01.pid
|
| port | 3306
|
| preload_buffer_size | 32768
|
| prepared_stmt_count | 0
|
| protocol_version | 10
|
| query_alloc_block_size | 8192
|
| query_cache_limit | 1048576
|
| query_cache_min_res_unit | 4096
|
| query_cache_size | 88080384
|
| 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 | 1
|
| skip_external_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slave_compressed_protocol | OFF
|
| slave_load_tmpdir | C:\WINDOWS\TEMP\
|
| slave_net_timeout | 3600
|
| slave_skip_errors | OFF
|
| slave_transaction_retries | 10
|
| slow_launch_time | 2
|
| sort_buffer_size | 262136
|
| sql_big_selects | ON
|
| sql_mode | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_E
NGINE_SUBSTITUTION |
| sql_notes | ON
|
| sql_warnings | OFF
|
| ssl_ca |
|
| ssl_capath |
|
| ssl_cert |
|
| ssl_cipher |
|
| ssl_key |
|
| storage_engine | InnoDB
|
| sync_binlog | 0
|
| sync_frm | ON
|
| system_time_zone | GMT Daylight Time
|
| table_cache | 619
|
| table_lock_wait_timeout | 50
|
| table_type | InnoDB
|
| thread_cache_size | 38
|
| thread_stack | 196608
|
| time_format | %H:%i:%s
|
| time_zone | SYSTEM
|
| timed_mutexes | OFF
|
| tmp_table_size | 31457280
|
| tmpdir | C:\WINDOWS\TEMP\
|
| transaction_alloc_block_size | 8192
|
| transaction_prealloc_size | 4096
|
| tx_isolation | REPEATABLE-READ
|
| updatable_views_with_limit | YES
|
| version | 5.0.24-community-nt-log
|
| version_comment | MySQL Community Edition (GPL)
|
| version_compile_machine | ia32
|
| version_compile_os | Win32
|
| wait_timeout | 28800
|
+---------------------------------+---------------------------------------------
-------------------+
220 rows in set (0.03 sec)
mysql>
I didn't notice anything suspicious in the status variables.. You are right no problems at the MySQL end.. may be the TCP/IP has issues... Did you check anything on Event log or something in the server??
ASKER
We are using PHP 5.2.5 running as FastCGI on IIS. We're using the standard mysql_connect() in our scripts.
That's ok..nothing problem with that.. What I wanted to know is did you notice anything on server's
related to MySQL or TCP/IP stating something went wrong etc..
System Tools -> event viewer / Application /System
related to MySQL or TCP/IP stating something went wrong etc..
System Tools -> event viewer / Application /System
ASKER
We appear to be getting a bunch of warnings but i'm unsure whether they're related to this issue.
A process serving application pool '1921686520' exceeded time limits during shut down. The process id was '7384'.
They are regular, and reference different process ID's. Looking at the process ID's I think they relate to w3wp.exe, NT AUTHORITY\NETWORK SERVICE, for the web applicaiton pool the site is running in in IIS.
A process serving application pool '1921686520' exceeded time limits during shut down. The process id was '7384'.
They are regular, and reference different process ID's. Looking at the process ID's I think they relate to w3wp.exe, NT AUTHORITY\NETWORK SERVICE, for the web applicaiton pool the site is running in in IIS.
What IIS error log says? Normally if something goes wrong with the Apache/php it reports in some error log stating what was the cause..
ASKER
I can only see requests in the log like:
2008-11-17 00:04:29 W3SVC1 192.168.65.20 GET /scc/transparent.gif - 80 - 80.86.39.11 Hobbit+bbtest-net/4.2.0 200 0 0
I guess i'm looking in the wrong place? I can't see anything other that GET entries.
2008-11-17 00:04:29 W3SVC1 192.168.65.20 GET /scc/transparent.gif - 80 - 80.86.39.11 Hobbit+bbtest-net/4.2.0 200 0 0
I guess i'm looking in the wrong place? I can't see anything other that GET entries.
ASKER
I'm getting a bunch of errors in HTTPERR:
2008-11-17 15:00:47 77.97.48.193 1093 192.168.65.20 80 - - - - - Timer_ConnectionIdle -
2008-11-17 15:00:47 77.97.48.193 1089 192.168.65.20 80 - - - - - Timer_ConnectionIdle -
2008-11-17 15:00:47 77.97.48.193 1092 192.168.65.20 80 - - - - - Timer_ConnectionIdle -
2008-11-17 15:00:47 77.97.48.193 1094 192.168.65.20 80 - - - - - Timer_ConnectionIdle -
2008-11-17 15:00:47 77.97.48.193 1090 192.168.65.20 80 - - - - - Timer_ConnectionIdle -
2008-11-17 15:01:07 77.97.48.193 1091 192.168.65.20 80 - - - - - Timer_ConnectionIdle -
Is this relevant do you think?
2008-11-17 15:00:47 77.97.48.193 1093 192.168.65.20 80 - - - - - Timer_ConnectionIdle -
2008-11-17 15:00:47 77.97.48.193 1089 192.168.65.20 80 - - - - - Timer_ConnectionIdle -
2008-11-17 15:00:47 77.97.48.193 1092 192.168.65.20 80 - - - - - Timer_ConnectionIdle -
2008-11-17 15:00:47 77.97.48.193 1094 192.168.65.20 80 - - - - - Timer_ConnectionIdle -
2008-11-17 15:00:47 77.97.48.193 1090 192.168.65.20 80 - - - - - Timer_ConnectionIdle -
2008-11-17 15:01:07 77.97.48.193 1091 192.168.65.20 80 - - - - - Timer_ConnectionIdle -
Is this relevant do you think?
I guess the connection was reset as its idle time exceeded the ConnectionTimeout property.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also post output of below statement
show global status;
show global variables;