MySQL 相关项目
在mysql workbench中执行一句比较复杂、返回数据量较大的语句时，mysql workbench显示执行已经结束，开始fetch过程。然后突然服务器连接丢失，查看日志数据库服务器自动重启。
但err中没有记录任何错误，只有重启的信息。原来该语句正常执行过，而现在每次执行必定会重现崩溃的情况，使用navicat也有同样问题。语句中使用了mysqludf_preg函数。
附innodb_status如下：
=====================================
2014-08-26 22:07:49 7fa7c74ba700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 139 srv_idle
srv_master_thread log flush and writes: 139
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4
OS WAIT ARRAY INFO: signal count 4
Mutex spin waits 1, rounds 0, OS waits 0
RW-shared spins 51, rounds 650, OS waits 4
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 12.75 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 11231557
Purge done for trx's n:o < 11230539 undo n:o < 0 state: running but idle
History list length 499
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7fa7bd779700, query id 10 116.233.71.19 root cleaning up
---TRANSACTION 11231556, ACTIVE 37 sec
mysql tables in use 6, locked 0
MySQL thread id 5, OS thread handle 0x7fa7bd6f7700, query id 533643 116.233.71.19 root Opening tables
SELECT l.bulletin_info_pk 公告编号,
l.bulletin_pro_pk 项目编号,
l.bulletin_url 网址,
x.dict_name 地区,
x1.dict_name 公告类型,
l.bulletin_source 来源,
x2.dict_name 采购方式,
l.bulletin_title 标题,
l.bulletin_projectid 项目编号,
l.bulletin_release_date 发布日期,
substr(l.bulletin_release_date,1,7) 年月,
week(l.bulletin_release_date,3) 周数,
WEEKDAY(l.bulletin_release_date)+1 工作日,
l.bulletin_deadline_date 截止时间,
l.datatype 数据类型,
func_getclsdtl(l.bulletin_clsmatch,'1',0) 分剔rx read view will not see trx with id >= 11231557, sees < 11231557
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
44122 OS file reads, 5 OS file writes, 5 OS fsyncs
1067.60 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 302, seg size 304, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 2365241, node heap has 43 buffer(s)
976.05 hash searches/s, 516.12 non-hash searches/s
---
LOG
---
Log sequence number 386774480070
Log flushed up to 386774480070
Pages flushed up to 386774480070
Last checkpoint at 386774480070
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1098907648; in additional pool allocated 0
Dictionary memory allocated 370732
Buffer pool size 65528
Free buffers 21485
Database pages 44000
Old database pages 16397
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 44000, created 0, written 1
1067.60 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 805 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 44000, unzip_LRU len: 0
I/O sum[0]:cur[6736], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 8191
Free buffers 2670
Database pages 5515
Old database pages 2055
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5515, created 0, written 1
128.54 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 940 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5515, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 8191
Free buffers 2743
Database pages 5442
Old database pages 2028
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5442, created 0, written 0
125.19 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 447 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5442, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 8191
Free buffers 2731
Database pages 5454
Old database pages 2033
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5454, created 0, written 0
128.04 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 880 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5454, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 8191
Free buffers 2574
Database pages 5612
Old database pages 2091
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5612, created 0, written 0
139.04 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 822 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5612, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 8191
Free buffers 2460
Database pages 5726
Old database pages 2133
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5726, created 0, written 0
153.64 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 500 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5726, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 8191
Free buffers 2536
Database pages 5650
Old database pages 2105
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5650, created 0, written 0
133.39 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 454 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5650, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 8191
Free buffers 3313
Database pages 4873
Old database pages 1818
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4873, created 0, written 0
125.79 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 472 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4873, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 8191
Free buffers 2458
Database pages 5728
Old database pages 2134
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5728, created 0, written 0
133.94 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 718 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5728, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 26050, id 140358552311552, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 314905
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 5058.85 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
实现看不出什么原因造成的，请各位大神指点迷津，到底是什么原因造成的？非常感谢！
但err中没有记录任何错误，只有重启的信息。原来该语句正常执行过，而现在每次执行必定会重现崩溃的情况，使用navicat也有同样问题。语句中使用了mysqludf_preg函数。
附innodb_status如下：
=====================================
2014-08-26 22:07:49 7fa7c74ba700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 139 srv_idle
srv_master_thread log flush and writes: 139
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4
OS WAIT ARRAY INFO: signal count 4
Mutex spin waits 1, rounds 0, OS waits 0
RW-shared spins 51, rounds 650, OS waits 4
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 mutex, 12.75 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 11231557
Purge done for trx's n:o < 11230539 undo n:o < 0 state: running but idle
History list length 499
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x7fa7bd779700, query id 10 116.233.71.19 root cleaning up
---TRANSACTION 11231556, ACTIVE 37 sec
mysql tables in use 6, locked 0
MySQL thread id 5, OS thread handle 0x7fa7bd6f7700, query id 533643 116.233.71.19 root Opening tables
SELECT l.bulletin_info_pk 公告编号,
l.bulletin_pro_pk 项目编号,
l.bulletin_url 网址,
x.dict_name 地区,
x1.dict_name 公告类型,
l.bulletin_source 来源,
x2.dict_name 采购方式,
l.bulletin_title 标题,
l.bulletin_projectid 项目编号,
l.bulletin_release_date 发布日期,
substr(l.bulletin_release_date,1,7) 年月,
week(l.bulletin_release_date,3) 周数,
WEEKDAY(l.bulletin_release_date)+1 工作日,
l.bulletin_deadline_date 截止时间,
l.datatype 数据类型,
func_getclsdtl(l.bulletin_clsmatch,'1',0) 分剔rx read view will not see trx with id >= 11231557, sees < 11231557
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
44122 OS file reads, 5 OS file writes, 5 OS fsyncs
1067.60 reads/s, 16384 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 302, seg size 304, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 2365241, node heap has 43 buffer(s)
976.05 hash searches/s, 516.12 non-hash searches/s
---
LOG
---
Log sequence number 386774480070
Log flushed up to 386774480070
Pages flushed up to 386774480070
Last checkpoint at 386774480070
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 1098907648; in additional pool allocated 0
Dictionary memory allocated 370732
Buffer pool size 65528
Free buffers 21485
Database pages 44000
Old database pages 16397
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 44000, created 0, written 1
1067.60 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 805 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 44000, unzip_LRU len: 0
I/O sum[0]:cur[6736], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 8191
Free buffers 2670
Database pages 5515
Old database pages 2055
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5515, created 0, written 1
128.54 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 940 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5515, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 8191
Free buffers 2743
Database pages 5442
Old database pages 2028
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5442, created 0, written 0
125.19 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 447 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5442, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 8191
Free buffers 2731
Database pages 5454
Old database pages 2033
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5454, created 0, written 0
128.04 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 880 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5454, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 8191
Free buffers 2574
Database pages 5612
Old database pages 2091
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5612, created 0, written 0
139.04 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 822 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5612, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 8191
Free buffers 2460
Database pages 5726
Old database pages 2133
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5726, created 0, written 0
153.64 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 500 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5726, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 8191
Free buffers 2536
Database pages 5650
Old database pages 2105
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5650, created 0, written 0
133.39 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 454 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5650, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 8191
Free buffers 3313
Database pages 4873
Old database pages 1818
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 4873, created 0, written 0
125.79 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 472 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 4873, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 8191
Free buffers 2458
Database pages 5728
Old database pages 2134
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5728, created 0, written 0
133.94 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 718 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 5728, unzip_LRU len: 0
I/O sum[0]:cur[842], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 26050, id 140358552311552, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 314905
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 5058.85 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
实现看不出什么原因造成的，请各位大神指点迷津，到底是什么原因造成的？非常感谢！
1
Select Language