MySQL Bugs: #39070: Connection to MySQL server gets lost

Bug #39070	Connection to MySQL server gets lost
Submitted:	27 Aug 2008 11:46	Modified:	19 Oct 2008 6:39
Reporter:	Alex Kourelis	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.67-community-nt	OS:	Windows (2003)
Assigned to:		CPU Architecture:	Any
Tags:	Connection, drops, Logs

Description:
I'm running 5.0.67-community-nt on a Windows 2003 environment, on a 8core 4G RAM machine.

On a random basis, MySQL gets lost for a few seconds. Sometimes it is 1 second, sometimes 10.
Log files do not report a thing.
Neither does Event Viewer.

I have tried multiple configurations for my.ini. These don't seem to have any effect and this issue seem to be indeed a bug a not a configuration error.

How to repeat:
I'm not sure how to reproduce this bug. But I suspect that this happens only to busy MySQL servers for Windows.

Do you use InnoDB strage engine? If you do, please, send the results of SHOW INNODB STATUS immediately after such a problematic period.

Unfortunately i am unable to run SHOW INNODB STATUS
It does not return anything
Neither the .err log files contains any output.

Please advise.

Please, copy and paste here the results of:

show innodb status\G

from the mysql comm and line client. According to the files you had uploaded, InnoDB is enables and this statement should work (or return error at least).

Here it is:

mysql> show innodb status\G
*************************** 1. row ***************************
Status:
=====================================
080827 15:59:31 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 37 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 563, signal count 503
Mutex spin waits 0, rounds 100052, OS waits 339
RW-shared spins 256, OS waits 119; RW-excl spins 62, OS waits 50
------------
TRANSACTIONS
------------
Trx id counter 0 454718477
Purge done for trx's n:o < 50612266 352649270 undo n:o < 0 0
History list length 48954421
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 454718425, not started, OS thread id 2928
MySQL thread id 23594, query id 452667 localhost 127.0.0.1 root
---TRANSACTION 0 0, not started, OS thread id 9468
MySQL thread id 14956, query id 453948 localhost 127.0.0.1 root
show innodb status
---TRANSACTION 0 454718475, COMMITTED IN MEMORY, OS thread id 11512 committing,
thread declared inside InnoDB 499
mysql tables in use 1, locked 1
, undo log entries 1
MySQL thread id 23679, query id 453915 localhost 127.0.0.1 root end
update sessions set member_id='2855',last_update='2008-08-27 15-59-31' where id=
'0ce2d9a7c3cc0541dc5655043e17e0a4'
---TRANSACTION 0 454718473, COMMITTED IN MEMORY, OS thread id 10644 committing,
thread declared inside InnoDB 499
mysql tables in use 1, locked 1
, undo log entries 1
MySQL thread id 23675, query id 453894 localhost 127.0.0.1 root end
update sessions set member_id='3184',last_update='2008-08-27 15-59-31' where id=
'055c156c6a4e0b3442470596b2e3e681'
--------
FILE I/O
--------
I/O thread 0 state: wait Windows aio (insert buffer thread)
I/O thread 1 state: wait Windows aio (log thread)
I/O thread 2 state: wait Windows aio (read thread)
I/O thread 3 state: wait Windows aio (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 1; buffer pool: 0
4685 OS file reads, 2856 OS file writes, 1996 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 4.24 writes/s, 3.03 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 5, seg size 7,
0 inserts, 0 merged recs, 0 merges
Hash table size 1155127, used cells 64031, node heap has 66 buffer(s)
331.05 hash searches/s, 5.73 non-hash searches/s
---
LOG
---
Log sequence number 14 3862068217
Log flushed up to   14 3862067671
Last checkpoint at  14 3862061007
1 pending log writes, 0 pending chkp writes
1800 log i/o's done, 2.81 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 290891060; in additional pool allocated 2065792
Buffer pool size   16000
Free buffers       11223
Database pages     4711
Modified db pages  13
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 4675, created 36, written 962
0.00 reads/s, 0.05 creates/s, 1.32 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
2 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 3156, state: sleeping
Number of rows inserted 60, updated 1669, deleted 51, read 8111381
0.05 inserts/s, 2.68 updates/s, 0.08 deletes/s, 11749.09 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.00 sec)

Hello,

Any updates on this?

Please, send your current my.ini file content and SHOW GLOBAL STATUS results.

I have uploaded my.ini file and will now report status

I have also uploaded global status results

Hello,

Any updates on this?

I also want to add that this issue started since i upgraded to 5.0.67

Thank you for the feedback.

> On a random basis, MySQL gets lost for a few seconds. Sometimes it is 1 second, sometimes 10.

What do you mean by "MySQL gets lost"? Can you connect to it? Does client get some errors or connection just hangs?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".