以前用的是win2003和apache,换了linux和nginx后报数据库错误。
数据库是可用的,这点可以确定,因为有些页面是可以打开的。
但涉及到类似这样的查询语句时select * from( select * from (select tid,pushtime as time from srqq_pushthread where clazz='生活特权' and part='优惠讨论区' order by time desc limit 10) as a union select * from (select srqq_threads.tid,postdate as time from srqq_threads where ifcheck='1' and locked='0' and state='0' and tid not in (select tid from srqq_pushthread where part = '不出现在未推送频道' and clazz='其它') AND fid= '75' and replies>'8' order by time desc limit 10) as b ) as c group by tid order by time desc limit 10
报错,Lost connection to MySQL server during query ( 2013 ) 查看错误相关信息。以前在win2003下和apache下时一切都是正常的,求解。
数据库是可用的,这点可以确定,因为有些页面是可以打开的。
但涉及到类似这样的查询语句时select * from( select * from (select tid,pushtime as time from srqq_pushthread where clazz='生活特权' and part='优惠讨论区' order by time desc limit 10) as a union select * from (select srqq_threads.tid,postdate as time from srqq_threads where ifcheck='1' and locked='0' and state='0' and tid not in (select tid from srqq_pushthread where part = '不出现在未推送频道' and clazz='其它') AND fid= '75' and replies>'8' order by time desc limit 10) as b ) as c group by tid order by time desc limit 10
报错,Lost connection to MySQL server during query ( 2013 ) 查看错误相关信息。以前在win2003下和apache下时一切都是正常的,求解。
但换回以前的环境,用navicat是可执行的。
两个max_allowed_packet,分别是
[mysqld]
max_allowed_packet = 2M[mysqldump]
max_allowed_packet = 32M有什么问题吗
SELECT *
FROM (SELECT *
FROM (SELECT tid, pushtime AS time
FROM srqq_pushthread
WHERE clazz = '生活特权'
AND part = '优惠讨论区'
ORDER BY time DESC
LIMIT 10
) AS a
UNION SELECT *
FROM (SELECT srqq_threads.tid, postdate AS time
FROM srqq_threads
WHERE ifcheck = '1'
AND locked = '0'
AND state = '0'
AND tid NOT
IN (SELECT tid
FROM srqq_pushthread
WHERE part = '不出现在未推送频道'
AND clazz = '其它'
)
AND fid = '75'
AND replies > '8'
ORDER BY time DESC
LIMIT 10
) AS b
) AS c
GROUP BY tid
ORDER BY time DESC
LIMIT 10
右链接:
select * from(
select * from (
select tid,pushtime as time from srqq_pushthread where clazz='生活特权' and part='优惠讨论区' order by time desc limit 10) as a
union
select * from (
select srqq_threads.tid,postdate as time from srqq_threads right join srqq_pushthread on srqq_threads.tid<>srqq_pushthread.tid and srqq_pushthread.part = '不出现在未推送频道' and srqq_pushthread.clazz='其它' where ifcheck='1' and locked='0' and state='0' AND fid= '75' and replies>'8' order by time desc limit 10) as b ) as c
group by tid order by time desc limit 10
#2013 - Lost connection to MySQL server during query
No connection. Trying to reconnect...会不会跟这有点关系
[mysqld]
max_allowed_packet = 64M
set global max_allowed_packet = 64*1024*1024;
但刚发现个奇怪的现象。
下面这句在phpMyAdmin 2.11.6,下执行报错 #2006 - MySQL server has gone away SELECT tid,postdate as time FROM srqq_threads WHERE ifcheck = '1' AND locked = '0' AND state = '0' AND tid NOT IN (SELECT tid FROM srqq_pushthread WHERE part = '不出现在未推送频道' AND clazz = '其它')AND fid = '74' AND replies > '8' ORDER BY time DESC LIMIT 10;
我把子查询中where中查询条件中的'不出现在未推送频道'和'其它'替换成空字符后就不报错了。SELECT tid,postdate as time FROM srqq_threads WHERE ifcheck = '1' AND locked = '0' AND state = '0' AND tid NOT IN (SELECT tid FROM srqq_pushthread WHERE part = '' AND clazz = '')AND fid = '74' AND replies > '8' ORDER BY time DESC LIMIT 10;
==================================================
错误SQL 查询: 编辑SHOW TABLE STATUS LIKE 'srqq_threads';MySQL 返回:文档
#2006 - MySQL server has gone away
==================================================查询语句SELECT tid,postdate as time FROM srqq_threads WHERE ifcheck = '1' AND locked = '0' AND state = '0' AND tid NOT IN (SELECT tid FROM srqq_pushthread WHERE part = '不出现在未推送频道' AND clazz = '其它')AND fid = '74' AND replies > '8' ORDER BY time DESC LIMIT 10;
换成英文不报错,但是我换成其它的中文也不报错,比如SELECT tid, postdate AS time
FROM srqq_threads
WHERE ifcheck = '1'
AND locked = '0'
AND state = '0'
AND tid NOT
IN (SELECT tid
FROM srqq_pushthread
WHERE part = '不出现'
AND clazz = '其它'
)
AND fid = '74'
AND replies > '8'
ORDER BY time DESC
LIMIT 10 ;
而去掉其中一个筛选条件也不报错,如SELECT tid, postdate AS time
FROM srqq_threads
WHERE ifcheck = '1'
AND locked = '0'
AND state = '0'
AND tid NOT
IN (SELECT tid
FROM srqq_pushthread
WHERE part = '不出现在未推送频道'
)
AND fid = '74'
AND replies > '8'
ORDER BY time DESC
LIMIT 10 ;
PS:我在字段"clazz","part"上建了个normal索引
SELECT tid,postdate as time FROM srqq_threads WHERE ifcheck = '1' AND locked = '0' AND state = '0' AND tid NOT IN (SELECT tid FROM srqq_pushthread WHERE clazz = '其它')AND fid = '74' AND replies > '8' ORDER BY time DESC LIMIT 10;
FROM srqq_threads
WHERE ifcheck = '1'
AND locked = '0'
AND state = '0'
AND tid NOT
IN (SELECT tid
FROM srqq_pushthread
WHERE part = '不出现在未推送频道'
)
AND fid = '74'
AND replies > '8'
ORDER BY time DESC
LIMIT 10 ;
然后我又加了个索引pc(字段"part"与"clazz"的索引)。
这句SQL就报错了
#2006 - MySQL server has gone away 看来和索引有关系