[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PHP suddenly unable to connect to MySQL on Windows 2003 server - Error 10055

Posted on 2008-11-17
11
Medium Priority
?
1,057 Views
Last Modified: 2013-12-13
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.
0
Comment
Question by:edcharleslynn
  • 6
  • 5
11 Comments
 
LVL 26

Expert Comment

by:Umesh
ID: 22974627
Pls post error log contents. error log file should be in mysql/data/*.err

Also post output of below statement

show global status;
show global variables;

0
 
LVL 1

Author Comment

by:edcharleslynn
ID: 22974690
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:
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>

Open in new window

0
 
LVL 26

Expert Comment

by:Umesh
ID: 22974780
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??
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:edcharleslynn
ID: 22975131
We are using PHP 5.2.5 running as FastCGI on IIS. We're using the standard mysql_connect() in our scripts.
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22975553
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
0
 
LVL 1

Author Comment

by:edcharleslynn
ID: 22975675
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.

0
 
LVL 26

Expert Comment

by:Umesh
ID: 22975923
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..
0
 
LVL 1

Author Comment

by:edcharleslynn
ID: 22976004
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.
0
 
LVL 1

Author Comment

by:edcharleslynn
ID: 22976034
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?
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22976237
I guess the connection was reset as its idle time exceeded the ConnectionTimeout property.
0
 
LVL 1

Accepted Solution

by:
edcharleslynn earned 0 total points
ID: 23033066
I think i've found the solution to this. Its a windows issue. We changed the maxuserport setting in the registry on the client and we haven't had the problem since. One to look of for!

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month18 days, 12 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question