Lost connection to MySQL server during query错误日志

[seaspace]

程式碼:

22-10-2005 10:53:41 ERROR - The following query = 
SELECT * FROM Plog_blogs WHERE id = '1'
generated the following error message = 
Lost connection to MySQL server during query

22-10-2005 10:53:42 ERROR - The following query = 
SELECT * FROM Plog_blogs WHERE id = '1'
generated the following error message = 
Lost connection to MySQL server during query

22-10-2005 10:53:57 ERROR - The following query = 
SELECT u.id AS id, u.user AS user, u.password AS password, u.email AS email,
                             u.about AS about, u.full_name AS full_name, u.properties AS properties,
                             u.resource_picture_id AS resource_picture_id,
                             IF(p.permission_id = 1, 1, 0 ) AS site_admin,
                             u.status AS status
                      FROM Plog_users u LEFT JOIN Plog_users_permissions p ON u.id = p.user_id
                      WHERE u.user = 'gibraltar'
generated the following error message = 
Lost connection to MySQL server during query

22-10-2005 11:00:31 ERROR - The following query = 
SELECT u.id AS id, u.user AS user, u.password AS password, u.email AS email,
                             u.about AS about, u.full_name AS full_name, u.properties AS properties,
                             u.resource_picture_id AS resource_picture_id,
                             IF(p.permission_id = 1, 1, 0 ) AS site_admin,
                             u.status AS status
                      FROM Plog_users u LEFT JOIN Plog_users_permissions p ON u.id = p.user_id
                      WHERE u.user = 'momoko'
generated the following error message = 
Lost connection to MySQL server during query

22-10-2005 11:03:06 ERROR - The following query = 
SELECT * FROM Plog_blogs WHERE id = '84'
generated the following error message = 
Lost connection to MySQL server during query

22-10-2005 11:03:31 ERROR - The following query = 
SELECT * FROM Plog_blogs WHERE id = '84'
generated the following error message = 
Lost connection to MySQL server during query

22-10-2005 11:06:04 ERROR - The following query = 
SELECT u.id AS id, u.user AS user, u.password AS password, u.email AS email,
                             u.about AS about, u.full_name AS full_name, u.properties AS properties,
                             u.resource_picture_id AS resource_picture_id,
                             IF(p.permission_id = 1, 1, 0 ) AS site_admin,
                             u.status AS status

请问sql_error.log里面有出现以上错误日志是何缘故？
恳请知道的朋友给与解答！
 

[seaspace]

我们的程序自plog0.32版开始升级至1.0再到现在的1.01
平台访问及操作越来越慢甚至不能登陆,很棘手的！

[seaspace]

服务器平台：Mysql3.xx + PHP4.xx + IIS6.x

[minstrel]

服務器平台：Mysql3.xx + PHP4.xx + IIS6.x

這個. 怎麼看起來都像是MySQL的問題....

MySQL太舊的關係??

[seaspace]

非常抱歉！
我前面写错得很厉害实际上mysql 是4.3.6 版本

minstrel请见谅!
 

[minstrel]

非常抱歉！
我前面寫錯得很厲害實際上mysql 是4.3.6 版本

minstrel請見諒!
 

MySQL版本不算舊. 不過看errorlog, 怎麼看也還是MySQL的問題.

用google查了一下, 發現一堆解答. 貼一篇上來. 順便翻一下.

===============================================================================

http://www.mysql.com/doc/G/o/Gone_away.html
http://www.mysql.com/doc/C/o/Common_errors.html
http://www.mysql.com/doc/S/e/Server_parameters.html
http://www.mysql.com/doc/O/p/Option_files.html
http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html

The most common reason for the MySQL server has gone away error
(MySQL 消失的常見原因)

1. Is that the server timed out and closed the connection. By default, the server closes the connection after 8 hours or 28800 seconds if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld via your server's /etc/my.cnf as well.
(Server 連線逾時並切斷連線. Server本身會在8小時或28800秒後, 切斷沒做任何事的連線. 你可以修改 wait_timeout 這個時以更改這個時限. 通常是放在 /etc/my.cnf 檔之中)

2. Another common reason to receive the MySQL server has gone away error is because you have issued a ``close'' on your MySQL connection and then tried to run a query on the closed connection. You can check that the MySQL hasn't died by executing mysqladmin version and examining the uptime.
(另一個常見原因, 是你已切斷連線並嘗試使用已切斷的連線. 你可以執行 mysqladmin 以檢查MySQL是否存在)

i.e. to check mysql uptime, in shell as root user type:
(要檢查mysql的uptime, 請使用下列方式)

mysqladmin -u root -p version

3. You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld gets a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by starting mysqld with the -O max_allowed_packet=# option (default 1M) or via max_allowed_packet variable in your /etc/my.cnf file and restarting mysql after you edited your /etc/my.cnf file. The extra memory is allocated on demand, so mysqld will use more memory only when you issue a big query or when mysqld must return a big result row
(如果你送出的查詢不正確或太大, 亦會產生這個錯誤. 如果mysqld收到一個太大的封包, 它會假設發生了某些錯誤, 並且切斷連線. 如果你需要做大的查詢, 你可以用兩種方式達到. 一是在啟動 mysqld時, 加上參數 -O Max_allowed_packet=###, 另一是在你的 /etc/my.cnf 中修改 max_allowed_packet 的值, 這個值預設是 1M. )

4. or simply your host restarted mysql..
(或者你的主機商剛好重啟mysql)

[markwu]

如果這是你自己的機器，加大你的 connection pool，看樣子是 connection 被佔滿所以無法連結。

如果這是放在虛擬主機，那麼請你的主機商調大 mysql 的 connection 的數目。

這是我目前想的到的方式。不確定有沒有用。

Mark

[seaspace]

谢谢minstrel和markwu的解答,我看能不能解决,如果没有问题再过来答复！
谢谢！

[FIEND]

1. 你要清查你所有的 query命令，將 index key做好加快的你 sql 效能 , 然後再做 otpimize  , 因為你可能一直 update 而 update 沒替你做好 kye 又或者是 你太久沒有 otpimize 了

2. 你的流量太高的話 , 請快點 用 ob_start 的技巧 將 首頁快取 成靜態網頁 , 資料生命週期你自己定 可以大量減少 db  conettion 數 .

[markwu]

2. 你的流量太高的話 , 請快點 用 ob_start 的技巧 將 首頁快取 成靜態網頁 , 資料生命週期你自己定 可以大量減少 db  conettion 數 .

Hi  seaspace：

FIEND 建議的是一個好方法，你透過這個方法來作一個自己的 cache system （靜態頁面），我在 0.32 就是這麼做的，只是你必須要會一些 coding 的技巧。對於 Performance 與 Mysql 的 Connection 真的會改善很多。

Mark