Random Error Connecting to MySQL

rwalling

Platform: Windows XP SP 2 - Apache 2.0.47 MySQL 4.0.15 - PHP 4.3.3 as a module with Zend opt 2.1.0

The problem: Lost connection during query (connecting to db). The problem occurs whether I connect using mysql_connect or odbc_connect. It doesn't seem to happen after a particular load on the server or number of connections or anything else for that matter. I have checked the database tables and everything is fine. mysqld does not crash when the problem happens.

When it happens: Randomly when following statement is called but never on an actual select or insert ...etc.

$db = mysql_connect($DBHOST, $DBUSER, $DBPASS);

always using localhost,root,password

When did it start happening? It might have started happening when I upgraded to SP2 for Win XP

After investigating as much as I can I am at a loss. Every time this failed connection occurs the aborted_connects var increments. If anyone can help me, I would appreciate it. Please tell me its not my hardware.

The following is from the documentaion:

The server variable Aborted_connects is incremented when:

When a connection packet doesn't contain the right information. (i assume its right)
When the user didn't have privileges to connect to a database.(same user pass all the time)

When a user uses a wrong password. (same user pass all the time)
When it takes more than connect_timeout seconds to get a connect package. See section 4.5.7.4 SHOW VARIABLES. (connect_timeout is set at 10 but even tried as high as 30)
Note that the above could indicate that someone is trying to break into your database!(no visible ip activity)

Other reasons for problems with Aborted clients / Aborted connections.

Usage of Ethernet protocol with Linux, both half and full duplex. Many Linux Ethernet drivers have this bug. You should test for this bug by transferring a huge file via ftp between these two machines. If a transfer goes in burst-pause-burst-pause ... mode then you are experiencing a Linux duplex syndrome. The only solution is switching duplex mode for both your network card and Hub/Switch to either full duplex or to half duplex and testing the results to decide on the best setting. (Im on Windows XP)
Some problem with the thread library that causes interrupts on reads. COLOR=blue[/COLOR]
Badly configured TCP/IP. (everything else works)
Faulty Ethernets or hubs or switches, cables ... This can be diagnosed properly only by replacing hardware. COLOR=blue[/COLOR]
max_allowed_packet is too small or queries require more memory than you have allocated for mysqld. See section A.2.9 Packet too large Error. (it's just a connection)

Below is a list of my.ini contents, a process list during the time when the problem occurs, a list of the status at the time problem happens, and the MySQL contents of my PHP.INI

here are the contents of my.ini:

[client]
port=3306
user = dba
password = sql

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
set-variable = key_buffer=8M
set-variable = sort_buffer=8M

[myisamchk]
set-variable = key_buffer=8M
set-variable = sort_buffer=8M

[mysqlhotcopy] (whats this?)
interactive-timeout

The MySQL server

[mysqld]
port=3306
skip-locking
default-character-set=latin1
set-variable = key_buffer=16M
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K
set-variable = flush_time=1800

changes rdw

set-variable = connect_timeout=10
set-variable = max_connections=400
set-variable = max_connect_errors=30
set-variable = table_cache=2048

slave-load-tmpdir = c:/apache2/temp
tmpdir = c:/apache2/temp
basedir = C:\apache2\mysql
datadir = C:\apache2\mysql\data
character-sets-dir = C:\apache2\mysql\share\charsets
log = C:\apache2\mysql\logs\myaccess.log
log-error = C:\apache2\mysql\logs\myerror.log
pid-file = C:\apache2\mysql\logs\mysql.pid
log-warnings
bind-address = 127.0.0.1

here is a list of the variables during the problem

+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Aborted_clients | 0 |
| Aborted_connects | 6 |
| Bytes_received | 23586 |
| Bytes_sent | 73611 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 20 |
| Com_change_master | 0 |
| Com_check | 15 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 0 |
| 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_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 134 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 0 |
| Com_show_fields | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 4 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 5 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 1 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 18 |
| Connections | 37 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_read_first | 91 |
| Handler_read_key | 297 |
| Handler_read_next | 10 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 270 |
| Handler_read_rnd_next | 2559 |
| Handler_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 0 |
| Key_blocks_used | 34 |
| Key_read_requests | 350 |
| Key_reads | 34 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Max_used_connections | 1 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 15 |
| Open_files | 31 |
| Open_streams | 0 |
| Opened_tables | 21 |
| Questions | 224 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 61 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 464 |
| Sort_scan | 3 |
| Table_locks_immediate | 205 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 36 |
| Threads_connected | 2 |
| Threads_running | 1 |
| Uptime | 847 |
+--------------------------+-------+

php.ini settings related to MySQL
[MySQL]
; Allow or prevent persistent links.
mysql.allow_persistent = On

; Maximum number of persistent links. -1 means no limit.
mysql.max_persistent = -1

; Maximum number of links (persistent + non-persistent). -1 means no limit.
mysql.max_links = -1

; Default port number for mysql_connect(). If unset, mysql_connect() will use
; the $MYSQL_TCP_PORT or the mysql-tcp entry in /etc/services or the
; compile-time value defined MYSQL_PORT (in that order). Win32 will only look
; at MYSQL_PORT.
mysql.default_port =

; Default socket name for local MySQL connects. If empty, uses the built-in
; MySQL defaults.
mysql.default_socket =

; Default host for mysql_connect() (doesn't apply in safe mode).
mysql.default_host = localhost

; Default user for mysql_connect() (doesn't apply in safe mode).
mysql.default_user = root

; Default password for mysql_connect() (doesn't apply in safe mode).
; Note that this is generally a bad idea to store passwords in this file.
; Any user with PHP access can run 'echo get_cfg_var("mysql.default_password")
; and reveal this password! And of course, any users with read access to this
; file will be able to reveal the password as well.
mysql.default_password =

; Maximum time (in secondes) for connect timeout. -1 means no limimt
mysql.connect_timeout = -1

; Trace mode. When trace_mode is active (=On), warnings for table/index scans and
; SQL-Erros will be displayed.
mysql.trace_mode = On