附錄A:問題和常見錯誤

目錄

A.1. 如何確定導致問題的原因
A.2. 使用MySQL程序時的常見錯誤
A.2.1. 拒絕訪問
A.2.2. 無法連接到[local] MySQL服務器
A.2.3. 客戶端不支持鑒定協議
A.2.4. 輸入密碼時出現密碼錯誤
A.2.5. 主機的host_name被屏蔽
A.2.6. 連接數過多
A.2.7. 內存溢出
A.2.8. MySQL服務器不可用
A.2.9. 信息包過大
A.2.10. 通信錯誤和失效連接
A.2.11. 表已滿
A.2.12. 無法創建文件/寫入文件
A.2.13. 命令不同步
A.2.14. 忽略用戶
A.2.15. 表tbl_name不存在
A.2.16. 無法初始化字符集
A.2.17. 文件未找到
A.3. 與安裝有關的事宜
A.3.1. 與MySQL客戶端庫的鏈接問題
A.3.2. 如何以普通用戶身份運行MySQL
A.3.3. 與文件許可有關的問題
A.4. 與管理有關的事宜
A.4.1. 如何復位根用戶密碼
A.4.2. 如果MySQL依然崩潰,應作些什么
A.4.3. MySQL處理磁盤滿的方式
A.4.4. MySQL將臨時文件儲存在哪里
A.4.5. 如何保護或更改MySQL套接字文件/tmp/mysql.sock
A.4.6. 時區問題
A.5. 與查詢有關的事宜
A.5.1. 搜索中的大小寫敏感性
A.5.2. 使用DATE列方面的問題
A.5.3. 與NULL值有關的問題
A.5.4. 與列別名有關的問題
A.5.5. 非事務表回滾失敗
A.5.6. 從相關表刪除行
A.5.7. 解決與不匹配行有關的問題
A.5.8. 與浮點比較有關的問題
A.6. 與優化器有關的事宜
A.7. 與表定義有關的事宜
A.7.1. 與ALTER TABLE有關的問題
A.7.2. 如何更改表中的列順序
A.7.3. TEMPORARY TABLE問題
A.8. MySQL中的已知事宜
A.8.1. MySQL中的打開事宜
在本附錄中,列出了一些你可能會遇到的常見問題和錯誤消息。并介紹了確定故障原因的方法,以及為解決問題所應采取的措施。

A.1. 如何確定導致問題的原因

當你遇到問題時,首先要做的是找出導致問題的程序和設備部件:

·         如果遇到下述征兆之一,或許是因為硬件問題(如內存、主板、CPU或硬盤)或內核問題:

1.    鍵盤不工作。正常情況下可通過按Caps Lock建進行檢查。如果Caps Lock的點亮狀態未改變,就需要更換鍵盤(在此之前,應嘗試重啟計算機,并檢查與鍵盤相連的所有電纜)。

2.    鼠標指針不移動。

3.    機器未對遠程機器的Ping命令做出應答。

4.    MySQL無關的其他程序工作不正常。

5.    系統意外重啟(有問題的用戶級程序應不能使系統出現嚴重問題)。

在該情況下,應檢查所有的電纜并運行某些診斷工具,對你的硬件進行檢查!此外,還應檢查是否有能夠解決問題的、適用于你的操作系統的補丁、更新或服務包。請檢查所有的庫(如glibc)是否是最新的。

使用配備ECC內存的機器以便盡早發現問題總是個好主意。

·         如果鍵盤已鎖定,可從另一臺機器登錄到你的機器,并執行kbd_mode –a,或許能恢復。

·         請檢查系統的日志文件(/var/log/messages或類似物)以找出問題的原因。如果你認為問題出在MySQL中,應檢查MySQL的日志文件。請參見5.11節,“MySQL日志文件”

·         如果你不認為存在硬件問題,應嘗試找出導致問題的原因。請使用topps、任務管理器或類似程序,以檢查哪個程序占用了所有CPU時間或鎖定了機器。

·         使用topdf或類似程序檢查是否內存不夠、磁盤空間不足、文件描述符缺乏、或其他關鍵資源缺少。

·         如果問題是失去控制的進程,應嘗試殺死它。如果殺不死進程,或許是因為操作系統中存在缺陷。

如果在檢查了所有其他可能性之后,并得出結論問題是由MySQL服務器或MySQL客戶端導致的,應創建提供給我方的郵件列表或支持團隊的缺陷報告。在缺陷報告中,請詳細描述系統的行為,以及你認為發生了什么情況。+3.

還應闡明為什么你認為是MySQL導致了問題。請考慮本章介紹的所有情況。準確闡明當你檢查系統時問題是如何出現的。對于程序和日志文件的任何輸出和錯誤消息,請使用“復制和粘貼”方法。

盡量詳細描述不工作的程序,以及你所見到的所有征兆。我們過去收到過很多僅說明“系統不工作”的缺陷報告。這不會為我們提供有助于解決問題的信息。

如果程序失敗,了解下述信息總是有用的:

·         有嫌疑的程序是否出現了分段故障(是否轉儲內核)?

·         程序是否占用了所有可用的CPU時間?用top.進行檢查。讓程序運行一段時間,或許能簡單地評估某些事是否是計算密集性的。

·         如果問題是因mysqld服務器導致的,使用mysqladmin -u root pingmysqladmin -u root processlist是否能獲得服務器的響應?

·         當你嘗試連接到MySQL服務器(例如,mysql)時,客戶端程序給出的信息是什么?客戶端是否堵塞?是否獲得了來自程序的任何輸出?

發送缺陷報告時,請遵循1.7.1.2節,“請教問題或通報缺陷”中給出的說明。

A.2. 使用MySQL程序時的常見錯誤

本節列出了用戶運行MySQL服務器時常會遇到的一些錯誤。盡管問題是在你嘗試運行客戶端時出現的,但對很多問題的解決方案來說,需要更改MySQL服務器的配置。

A.2.1.?拒絕訪問

導致拒絕訪問錯誤的原因很多。該錯誤常與連接時服務器允許客戶端使用的MySQL賬戶有關。請參見5.7.8節,“拒絕訪問錯誤的原因。請參見5.7.2節,“權限系統工作原理”

A.2.2.?無法連接到[local] MySQL服務器

Unix平臺上的MySQL客戶端能夠以兩種不同的方式連接到mysqld服務器:通過文件系統中的文件(默認為/tmp/mysql.sock)使用Unix套接字進行連接,或通過端口號使用TCP/IP進行連接。Unix套接字文件的連接速度比TCP/IP快,但僅能在與相同計算機上的服務器相連時使用。如果未指定指定主機名或指定了特殊的主機名localhost,將使用Unix套接字。

如果MySQL服務器運行在Windows 9xMe上,僅能通過TCP/IP進行連接。如服務器運行在Windows NT2000XP2003上,而且使用--enable-named-pipe選項啟動,如果在運行服務器的機器上運行客戶端,也能使用命名管道進行連接。默認情況下,命名管道的名稱為MySQL。如果在連接到mysqld時未給定主機名,MySQL客戶端首先會嘗試連接到命名管道。如果不能工作,將連接到TCP/IP端口。使用“.”作為主機名,可在Windows平臺上強制使用命名管道。

錯誤(2002)“無法連接到…”通常意味著在系統沒有運行的MySQL服務器,或在連接到服務器時使用了不正確的Unix套接字文件名或TCP/IP端口號。

首先檢查服務器主機上是否有名為mysqld的進程(在Unix平臺上使用ps xa | grep mysqld,或在Windows平臺上使用任務管理器)。如果沒有這類進程,應啟動服務器。請參見2.9.2.3節,“啟動MySQL服務器以及其故障診斷和排除”

如果mysqld進程正在運行,可使用下述命令檢查。在你的具體設置中,端口號或Unix套接字文件名可能會有所不同。host_ip代表運行服務器的機器的IP編號。

shell> mysqladmin version
shell> mysqladmin variables
shell> mysqladmin -h `hostname` version variables
shell> mysqladmin -h `hostname` --port=3306 version
shell> mysqladmin -h host_ip version
shell> mysqladmin --protocol=socket --socket=/tmp/mysql.sock version

注意,應與主機名命令一起使用“`”而不是“’”,這會使主機名輸出(當前主機名)被代入mysqladmin命令。如果沒有主機名命令或正運行在Windows平臺上,應以手動方式輸入機器的主機名(無“`”符號),后跟-h選項。也可以使用TCP/IP協議用-h 127.0.0.1連接到本地主機。

下面給出了一些“無法連接到本地MySQL服務器”錯誤的可能原因:

1.    Mysqld未運行。請檢查操作系統的進程列表以確保mysqld進程正在運行。

2.    你正在具有很多TCP/IP連接的Windows平臺上運行MySQL服務器。如果你的客戶端經常出現錯誤,請參見A.2.2.1節,“在Windows上與MySQL服務器的連接失敗”,以找出規避方法。

3.    你正在使用MIT-pthreads的系統上運行。如果你正在運行不具有固有線程的操作系統,mysqld將使用MIT-pthreads軟件包。請參見2.1.1節,“MySQL支持的操作系統”。但是,并非所有的MIT-pthreads版本均支持Unix套接字文件。在不支持套接字文件的系統上,連接到服務器時,必須明確指定主機名。請使用下述命令來檢查是否連接到了服務器:

4.           shell> mysqladmin -h `hostname` version

5.    某人移動了mysqld使用的Unix套接字文件(默認為/tmp/mysql.sock)。例如,你可能執行了將舊文件從/tmp目錄刪除的cron任務。你總能執行mysqladmin version來檢查mysqladmin試圖使用的Unix套接字文件是否的確存在。在該情況下,更正方式是更改cron任務,不刪除mysql.sock文件,或將套接字文件置于其他地方。請參見A.4.5節,“如何保護或更改MySQL套接字文件/tmp/mysql.sock

6.    你使用--socket=/path/to/socket選項啟動了mysqld服務器,當忘記將套接字文件的新名稱通知客戶端程序。如果更改了關于服務器的套接字路徑,也必須通知MySQL客戶端。可在運行客戶端程序時使用相同的—socket選項來完成該任務。此外,你還應確保客戶端具有訪問文件mysql.sock的權限。要想找出套接字文件的位置,可使用:

7.           shell> netstat -ln | grep mysql

請參見A.4.5節,“如何保護或更改MySQL套接字文件/tmp/mysql.sock

8.    你正在使用Linux而且1個服務器線程已死亡(內核已清除)。在此情況下,在重啟MySQL服務器之前,必須殺死其他mysqld線程(例如,使用killmysql_zap腳本)。請參見A.4.2節,“如果MySQL依然崩潰,應作些什么”

9.    服務器或客戶端程序不具有訪問包含Unix套接字文件的目錄或套接字文件本身的恰當權限。在該情況下,必須更改目錄或套接字文件的訪問權限,以便服務器或客戶端程序能夠訪問它們,或用–socket選項重啟mysqld,在該選項中指定服務器能創建、而且客戶端可訪問的目錄下的套接字文件名。

如果遇到錯誤消息“無法連接到some_host上的MySQL服務器”,可嘗試采取下述步驟以找出問題所在:

·         執行“telnet some_host 3306并按兩次回車鍵,檢查服務器是否運行在該主機上(3306是默認的MySQL端口號。如果你的服務器正在監聽不同的端口,請更改該值)。如果有1MySQL服務器正在運行并監聽該端口,你應收到包含服務器版本號的回應。如果遇到錯誤,如“telnet:無法連接到遠程主機:拒絕連接,表示在該定端口上沒有運行的服務器。

·         如果服務器正運行在本地主機上,請使用Unix套接字文件,并使用mysqladmin -h localhost variables進行連接。驗證服務器監聽的TCP/IP端口號(它是port變量的值)。

·         確保你的mysqld服務器未用--skip-networking選項啟動。如果使用了該選項,將無法使用TCP/IP連接到它。

·         檢查并確認不存在屏蔽了對MySQL訪問的防火墻。需要配置諸如ZoneAlarmWindows XP個人防火墻等應用程序,以允許對MySQL服務器的外部訪問。

A.2.2.1.?在Windows上與MySQL服務器的連接失敗

當你在具有很多TCP/IP連接的Windows上運行MySQL服務器,并經常在客戶端上遇到“無法連接到MySQL服務器”錯誤時,可能是因為Windows不允許足夠的臨時(短命)端口用于這類連接。

默認情況下,Windows允許用于使用5000個臨時(短命)TCP端口。任何端口關閉后,它將在TIME_WAIT狀態保持120秒。與重新初始化全新的連接相比,該狀態允許以更低的開銷重新使用連接。但是,在該時間逝去前,無法再次使用該端口。

對于小的可用TCP端口堆棧(5000),以及具有TIME_WAIT狀態的大量在短時間內打開和關閉的TCP端口,你很可能遇到端口耗盡問題。處理該問題的方法有兩種:

·         通過調查連接池以及可能的持久連接,減少快速消耗的TCP端口數。

·         調整Windows注冊表中的某些設置(請參見下面)。

要點:下述步驟涉及更改Windows 注冊表。更改注冊表之前,請備份注冊表,并確認你已掌握在出現問題時恢復注冊表的方法。關于備份年、恢復和編輯注冊表的更多信息,請請參見Microsoft知識庫中的下述文獻:http://support.microsoft.com/kb/256986/EN-US/

·         啟動注冊表編輯器(Regedt32.exe)。

·         在注冊表中確定下述鍵值的位置:

·                HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters

·         在“編輯”菜單上點擊“添加值”,然后增加下述注冊值:

·                Value Name: MaxUserPort
·                Data Type: REG_DWORD
·                Value: 65534

它用于設置為任何用戶提供的臨時端口數。有效范圍介于500065534之間(十進制)。默認值為0x13885000,十進制)。

·         在“編輯”菜單上點擊“添加值”,然后增加下述注冊值:

·                Value Name: TcpTimedWaitDelay
·                Data Type: REG_DWORD
·                Value: 30

它用于設置關閉之前將TCP端口連接保持在TIME_WAIT狀態的秒數。有效范圍介于0秒和300秒之間。默認值為0x78120秒)。

·         退出注冊表編輯器。

·         重新引導機器。

注釋:撤銷上述設置十分簡單,就像刪除你創建的注冊表一樣。

A.2.3.?客戶端不支持鑒定協議

MySQL 5.1采用了基于密碼混編算法的鑒定協議,它與早期客戶端(4.1之前)使用的協議不兼容。如果你將服務器升級到4.1之上,用早期的客戶端進行連接可能失敗,并給出下述消息:

shell> mysql
客戶端不支持服務器請求的鑒定協議:請考慮升級MySQL客戶端。

要想解決該問題,應使用下述方法之一:

·         升級所有的客戶端程序,以使用4.1.1或更新的客戶端庫。

·         4.1版之前的客戶端連接到服務器時,請使用仍具有4.1版之前風格密碼的賬戶。

·         對于需要使用4.1版之前的客戶端的每位用戶,將密碼恢復為4.1版之前的風格。可以使用SET PASSWORD語句和OLD_PASSWORD()函數完成該任務:

·                mysql> SET PASSWORD FOR
·                    -> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

也可以使用UPDATEFLUSH PRIVILEGES

mysql> UPDATE mysql.user SET Password = OLD_PASSWORD('newpwd')
    -> WHERE Host = 'some_host' AND User = 'some_user';
mysql> FLUSH PRIVILEGES;

用你打算使用的密碼替換前例中的“newpwd”。MySQL不能告訴你原來的密碼是什么,因此,你需要選擇新的密碼。

·         通知服務器使用舊的密碼混編算法:

1.    使用“--old-passwords”選項啟動mysqld

2.    對于已將密碼更新為較長4.1格式的每個賬戶,為其指定具有舊格式的密碼。可以使用下述查詢確定這些賬戶:

3.                 mysql> SELECT Host, User, Password FROM mysql.user
4.                     -> WHERE LENGTH(Password) > 16;

對于查詢顯示的每個賬戶記錄,請使用HostUser值,并使用OLD_PASSWORD()函數以及SET PASSWORDUPDATE之一指定密碼,如前面所介紹的那樣。

注釋:在早期的PHP版本中,mysql擴展不支持MySQL 4.1.1和更高版中的鑒定協議。無論使用的PHP版本是什么,它均是正確的。如果你打算與MySQL 4.1或更高版本一起使用mysql擴展,需要使用前面介紹的選項之一,配置MySQL,以便與較早的客戶端一起使用。mysqli擴展(支持改進的MySQL”,在PHP 5中增加)與MySQL 4.1和更高版本中使用的改進的密碼混編算法兼容,不需要對MySQL進行特殊配置就能使用該MySQL客戶端庫。關于mysqli擴展的更多信息,請參見http://php.net/mysqli

關于密碼混編和鑒定功能的額外背景知識,請參見5.7.9節,“MySQL 4.1中的密碼哈希處理”

A.2.4. 輸入密碼時出現密碼錯誤

使用無下述密碼值的“—password”-p”選項調用時,MySQL客戶端程序將提示輸入密碼:
shell> mysql -u user_name -p
Enter password:

在某些系統上,當你在選項文件或命令行上指定時,你可能會發現密碼能夠工作,但是當你在“Enter password:”提示下以交互方式輸入密碼時,你可能會發現輸入的密碼不工作。當系統所提供的用于讀取密碼的庫將密碼值限定在少數字符時(典型情況下為8個),就會出現該問題。這是與系統庫有關的問題,與MySQL無關。要想處理該問題,可將MySQL密碼更改為由8個字符或更少字符構成的值,或將密碼置于選項文件中。

A.2.5.?主機的host_name被屏蔽

如果遇到下述錯誤,表示mysqld已收到來來自主機“host_name”的很多連接請求,但該主機卻在中途中斷。

由于出現很多連接錯誤,主機'host_name'被屏蔽。
可使用'mysqladmin flush-hosts'解除屏蔽。

允許的中斷連接請求的數目由max_connect_errors系統變量的值決定。當超出max_connect_errors規定的連接請求時,mysqld將認為某處出錯(例如,某人正試圖插入),并屏蔽主機的進一步連接請求,直至執行了mysqladmin flush-hosts命令,或發出了FLUSH HOSTS語句為止。請參見5.3.3節,“服務器系統變量”

在默認情況下,mysqld會在10次連接錯誤后屏蔽主機。你可以通過下述方式啟動服務器來調整該值:

shell> mysqld_safe --max_connect_errors=10000 &

如果在給定主機上遇到該錯誤,首先應核實該主機的TCP/IP連接是否正確。如果存在網絡問題,增加max_connect_errors變量的值不會有任何好處。

A.2.6.?連接數過多

當你試圖連接到mysqld服務器時遇到“過多連接”錯誤,這表示所有可用的連接均已被其他客戶端使用。

允許的連接數由max_connections系統變量控制。默認值為100。如果需要支持更多的連接,應使用該變量的較大值重啟mysqld

mysqld實際上允許max_connections+1個客戶端進行連接。額外的連接保留給具有SUPER權限的賬戶。通過為系統管理員而不是普通用戶授予SUPER權限(普通用戶不應具有該權限),系統管理員能夠連接到服務器,并使用SHOW PROCESSLIST來診斷問題,即使已連接的無特權客戶端數已達到最大值也同樣。請參見13.5.4.16節,“SHOW PROCESSLIST語法”

MySQL能支持的最大連接數取決于給定平臺上線程庫的質量。LinuxSolaris應能支持500-1000個并發連接,具體情況取決于RAM容量,以及客戶端正在作什么。MySQL AB提供的靜態Linux庫能支持高達4000個連接。

A.2.7.?內存溢出

如果使用mysql客戶端程序發出了查詢,并收到下述錯誤之一,則表示mysql沒有足夠內存來保存全部查詢結果:

mysql: Out of memory at line 42, 'malloc.c'
mysql: needed 8136 byte (8k), memory in use: 12481367 bytes (12189k)
錯誤2008: MySQL client ran out of memory

要想更正該問題,首先應檢查查詢是否正確。返回這么多行是否合理?如果不合理,更正查詢并再次嘗試。否則,應使用“--quick”選項調用mysql。這樣,將使用mysql_use_result() C API函數來檢索結果集,這類函數能夠降低客戶端上的負載(但會加重服務器上的負載)。

A.2.8.?MySQL服務器不可用

在本節中,還介紹了出現查詢錯誤期間,與丟失了服務器連接有關的事宜。

MySQL服務器不可用錯誤的最常見原因是服務器超時以及連接已關閉。在該情況下,通常能見到下述錯誤代碼之一(具體的錯誤代碼與操作系統有關):

錯誤代碼

描述

CR_SERVER_GONE_ERROR

客戶端無法將問題發送至服務器。

CR_SERVER_LOST

寫入服務器時客戶端未收到錯誤,但也未獲得問題的完整答案(或任何答案)。

在默認情況下,如果未發生任何事,8小時后服務器將關閉連接。也可以在啟動mysqld時,通過設置wait_timeout變量更改時間限制。請參見5.3.3節,“服務器系統變量”.

如果有1個腳本,你僅需要再次發出查詢,讓客戶端再次進行自動連接即可。其中,假定在客戶端中啟用了自動再連接功能(對于mysql命令行客戶端,這是默認設置)。

MySQL服務器不可用錯誤的一些其他常見原因如下:

·         你(或db系統管理員)使用KILL語句或mysqladmin kill命令殺死了正在運行的線程。

·         你試圖在關閉了與服務器的連接后運行查詢。這表明應更正應用程序中的邏輯錯誤。

·         你在客戶端一側遇到TCP/IP連接超時錯誤。如果你使用了命令:mysql_options(..., MYSQL_OPT_READ_TIMEOUT,...)mysql_options(..., MYSQL_OPT_WRITE_TIMEOUT,...),就可能出現該問題。在該情況下,增加超時值可能有助于問題的解決。

·         你在服務器端遇到超時錯誤,而且禁止了客戶端中的自動再連接功能(MYSQL結構中的再連接標志等于0)。

·         你正在使用Windows客戶端,而且在發出命令之前服務器撤銷了連接(或許是因為已超過wait_timeout

Windows平臺上出現問題的原因,在某些情況下,將TCP/IP連接寫入服務器時,MySQL未收到來自操作系統的錯誤,但當試圖從連接讀取答案時出現錯誤。

在該情況下,即使MYSQL結構中的再連接標志等于1MySQL也不會執行自動再連接并再次發出查詢,這是因為它不知道服務器是否收到原始查詢。

對此的解決方式是:如果自上一次查詢以來經過了較長時間,在連接上執行mysql_ping(正是MyODBC所作的);或在mysqld服務器上將wait_timeout設置得很高,使之實際上不存在超時。

·         如果你向服務器發出了不正確或過大的查詢,也會遇到這類問題。如果mysqld收到過大或無序的信息包,它會認為客戶端出錯,并關閉連接。如果需要執行較大的查詢(例如,正在處理大的BLOB列),可通過設置服務器的max_allowed_packet變量,增加查詢限制值,該變量的默認值為1MB。或許,你還需增加客戶端上的最大信息包大小。關于設置信息包大小的更多信息,請參見A.2.9節,“信息包過大”

·         如果你的客戶端低于4.0.8而且你的服務器高于4.0.8,當你接收16MB或更大的信息包時,可能會丟失連接。

·         如果MySQL是用“--skip-networking”選項啟動的,也會見到MySQL服務器不可用錯誤。

·         你遇到了執行查詢時服務器宕機的缺陷。

通過執行mysqladmin version并檢查服務器的正常工作時間,可檢查服務器是否宕機并重啟。如果客戶端連接是因mysqld崩潰和重啟而斷開的,應將重點放在查找崩潰你方面。首先應再次檢查發出的查詢是否再次殺死了服務器。請參見A.4.2節,“如果MySQL依然崩潰,應作些什么”

用“--log-warnings=2選項啟動mysqld,可獲得關于連接的更多信息。這樣,就能將某些斷開連接錯誤記錄到hostname.err文件中。請參見5.11.1節,“錯誤日志”

如果你打算創建與該問題有關的缺陷報告,務必包含下述信息:

1.    指明MySQL服務器是否宕機。通過服務器錯誤日志可發現這方面的信息。請參見A.4.2節,“如果MySQL依然崩潰,應作些什么”

2.    如果特定查詢殺死了mysqld,而且在運行查詢前用CHECK TABLE檢查了涉及的表,你是否能提供可重復的測試范例?請參見E.1.6節,“如果出現表崩潰,請生成測試案例”

3.    MySQL服務器中,系統變量wait_timeout的值是什么?mysqladmin variables給出了該變量的值)。

4.    你是否嘗試使用“--log”選項來運行mysqld,以確定是否在日志中出現問題?

另請參見A.2.10節,“通信錯誤和失效連接”

請參見1.7.1.2節,“請教問題或通報缺陷”

A.2.9.?信息包過大

通信信息包是發送至MySQL服務器的單個SQL語句,或發送至客戶端的單一行。

MySQL 5.1服務器和客戶端之間最大能發送的可能信息包為1GB

MySQL客戶端或mysqld服務器收到大于max_allowed_packet字節的信息包時,將發出信息包過大錯誤,并關閉連接。對于某些客戶端,如果通信信息包過大,在執行查詢期間,了能回遇到丟失與MySQL服務器的連接錯誤。

客戶端和服務器均有自己的max_allowed_packet變量,因此,如你打算處理大的信息包,必須增加客戶端和服務器上的該變量。

如果你正在使用mysql客戶端程序,其max_allowed_packet變量的默認值為16MB。要想設置較大的值,可用下述方式啟動mysql

mysql> mysql --max_allowed_packet=32M

它將信息包的大小設置為32MB

服務器的默認max_allowed_packet值為1MB。如果服務器需要處理大的查詢,可增加該值(例如,如果準備處理大的BLOB列)。例如,要想將該設置為16MB,可采用下述方式啟動服務器:

mysql> mysqld --max_allowed_packet=16M

也能使用選項文件來設置max_allowed_packet要想將服務器的該變量設置為16MB,可在選項文件中增加下行內容:

[mysqld]
max_allowed_packet=16M

增加該變量的值十分安全,這是因為僅當需要時才會分配額外內存。例如,僅當你發出長查詢或mysqld必須返回大的結果行時mysqld才會分配更多內存。該變量之所以取較小默認值是一種預防措施,以捕獲客戶端和服務器之間的錯誤信息包,并確保不會因偶然使用大的信息包而導致內存溢出。

如果你正是用大的BLOB值,而且未為mysqld授予為處理查詢而訪問足夠內存的權限,也會遇到與大信息包有關的奇怪問題。如果懷疑出現了該情況,請嘗試在mysqld_safe腳本開始增加ulimit -d 256000,并重啟mysqld

A.2.10. 通信錯誤和失效連接

對于連接問題,服務器錯誤日志是有用的信息源。請參見5.11.1節,“錯誤日志”。如果服務器是用“--log-warnings”選項啟動的,在錯誤日志中可能會發現下述消息:

010301 14:38:23  Aborted connection 854 to db: 'users' user: 'josh'

如果“Aborted connections”(放棄連接)消息出現在錯誤日志中,可能的原因是:

1.    客戶端程序在退出之前未調用mysql_close()

2.    客戶端的空閑時間超過wait_timeoutinteractive_timeout秒,未向服務器發出任何請求。請參見5.3.3節,“服務器系統變量”

3.    客戶端在數據傳輸中途突然結束。

出現這類情況時,服務器將增加“Aborted_clients”(放棄客戶端)狀態變量。

出現下述情況時,服務器將增加“Aborted_clients”(放棄客戶端)狀態變量。

·         客戶端不具有連接至數據庫的權限。

·         客戶端采用了不正確的密碼。

·         連接信息包不含正確信息。

·         獲取連接信息包的時間超過connect_timeout秒。請參見5.3.3節,“服務器系統變量”

如果出現這類情況,可能表明某人正試圖侵入你的服務器!

對于放棄客戶端或放棄連接問題,其他可能的源應包括:

·         Linux一起使用以太網協議,半雙工或全雙工。很多Linux以太網驅動均存在該缺陷。應通過FTP在客戶端和服務器機器之間傳輸大文件來測試該缺陷。如果傳輸處于burst-pause-burst-pause(爆發-暫停-爆發-暫停)模式,表明你遇到了Linux雙工故障。唯一的解決方法是,將網卡和Hub/交換器的雙工模式切換為全雙工或半雙工,并對結果進行測試以確定最佳設置。

·         與線程庫有關的某些問題導致讀取中斷。

·         配置不良的TCP/IP

·         有問題的以太網、Hub、交換器、電纜等。僅能通過更換硬件才能恰當診斷。

·         變量max_allowed_packet過小或查詢要求的內存超過為mysqld分配的內存。請參見A.2.9節,“信息包過大”

另請參見A.2.8節,“MySQL服務器不可用”

A.2.11.?表已滿

表已滿錯誤出現的方式有數種:

·         你正在使用低于3.23版的MySQL服務器,而且“內存中”臨時表超過了tmp_table_size字節。要想避免該問題,可使用“-O tmp_table_size=val”選項以便mysqld增加臨時表的大小,或在發出有問題的查詢之前,使用SQL選項SQL_BIG_TABLES請參見13.5.3節,“SET語法”

也可以使用“--big-tables”選項啟動mysqld。它與使用針對所有查詢的SQL_BIG_TABLES完全相同。

MySQL 3.23起,該問題應不再出現。如果“內存中”臨時表超過tmp_table_size,服務器會自動將其轉換為基于磁盤的MyISAM表。

·         你正在使用InnoDB表,并超出了InnoDB表空間。在該情況下,解決方法是增加InnoDB表空間。請參見15.2.7節,“添加和刪除InnoDB數據和日志文件

·         你正在僅支持2GB文件的操作系統上使用ISAMMyISAM表,數據文件或索引文件達到了該限制值。

·         你正在使用MyISAM表,而且表所需的空間超過內部指針允許的大小。如果在創建表時未指定MAX_ROWS表,MySQL將使用myisam_data_pointer_size系統變量。默認值為6字節,它足以容納65536TB數據。請參見5.3.3節,“服務器系統變量”

使用該語句,可檢查最大數據/索引大小:

SHOW TABLE STATUS FROM database LIKE 'tbl_name';

也可以使用myisamchk -dv /path/to/table-index-file

如果指針大小過小,可使用ALTER TABLE更正該問題:

ALTER TABLE tbl_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

僅應為具有BLOBTEXT列的表指定AVG_ROW_LENGTH在該情況下,MySQL不能僅根據行數優化所需的空間。

A.2.12.?無法創建文件/寫入文件

如果對某些查詢遇到下述類型的錯誤,它意味著MySQL不能為臨時目錄下的結果集創建臨時文件:

無法創建/寫入文件'\\sqla3fe_0.ism'

前述錯誤是Windows平臺上的典型消息,Unix平臺上的消息與之類似。

一種更正方式是使用“--tmpdir”選項啟動mysqld,或在選項文件的[mysqld]部分增加該選項。例如,要想指定目錄C:\temp,可使用:

[mysqld]
tmpdir=C:/temp

目錄C:\temp必須存在,并有足夠的空間允許MySQL寫入它。請參見4.3.2節,“使用選項文件”

該錯誤的另一個原因可能是許可事宜。請確認MySQL服務器能夠寫入tmpdir目錄。

此外,還用使用perror檢查錯誤代碼。服務器無法寫入表的一個原因是文件系統已滿。

shell> perror 28
錯誤代碼28:磁盤上無剩余空間。

A.2.13.?命令不同步

如果遇到“命令不同步”錯誤,將無法在你的客戶端代碼中運行該命令,你正在以錯誤順序調用客戶端函數。

例如,如果你正使用mysql_use_result(),并打算在調用mysql_free_result()之前執行新查詢,就會出現該問題。如果你試圖執行兩次查詢,但并未在兩次查詢之間調用mysql_use_result()mysql_store_result(),也會出現該問題。

A.2.14.?忽略用戶

如果遇到下述錯誤,表示當啟動mysqld時或重新加載授權表時,在用戶表中發現具有非法密碼的賬戶。

發現用戶'some_user'@'some_host'密碼錯誤:忽略用戶。

作為其結果,許可系統將簡單忽略賬戶。

在下面的介紹中,指明了可能的原因和問題的更正措施:

1.    或許,你正打算用舊的用戶表運行新版本的mysqld。執行mysqlshow mysql user檢查Password(密碼)列是否短于16個字符,通過該方式可檢查該問題。如果結果是肯定的,可運行腳本/add_long_password腳本更正該問題。

2.    賬戶具有舊的密碼(8字符長),而且未使用“--old-protocol”選項啟動mysqld。更新用戶表中的賬戶,使之具有新的密碼,或使用“--old-protocol”選項重啟mysqld

3.    在用戶表中未使用PASSWORD()函數指定了密碼。使用mysql用新密碼更新用戶表中的賬戶,務必使用PASSWORD()函數:

4.           mysql> UPDATE user SET Password=PASSWORD('newpwd')
5.               -> WHERE User='some_user' AND Host='some_host';

A.2.15.?表tbl_name不存在

如果遇到下述錯誤之一,通常意味著當前數據庫中不存在具有給定名稱的表:

'tbl_name'不存在
無法找到文件:'tbl_name' (errno: 2)

在某些情況下,表或許存在,但未正確引用它:

·         由于MySQL使用目錄和文件來保存數據庫和表,如果它們位于區分文件名大小寫的文件系統上,數據庫和表名也區分文件大小寫。

·         即使對于不區分大小寫的文件系統,如Windows,在查詢內對給定表的所有引用必須使用相同的大小寫。

可以使用SHOW TABLES檢查位于當前數據庫中的表。請參見13.5.4節,“SHOW語法”

A.2.16.?無法初始化字符集

如果存在字符集問題,可能會遇到下述錯誤:

MySQL連接失敗:無法初始化字符集charset_name

導致該錯誤的原因:

·         字符集為多字節字符集,但客戶端不支持該字符集。在該情況下,需要使用“--with-charset=charset_name”或“--with-extra-charsets=charset_name”選項運行configure以重新編譯客戶端。請參見2.8.2節,“典型配置選項

所有的標準MySQL二進制文件均是采用“--with-extra-character-sets=complex”編譯的,能夠支持所有的多字節字符集。請參見5.10.1節,“數據和排序用字符集”

·         字符集是未編譯到mysqld中的簡單字符集,而且字符集定義文件不在客戶端預期的位置。

在該情況下,需要采取下述方法之一解決問題:

1.    重新編譯客戶端,使之支持字符集。請參見2.8.2節,“典型配置選項

2.    為客戶端指定字符集定義文件所在的目錄。對于很多客戶端,可使用“--character-sets-dir”選項完成該任務。

3.    將字符集定義文件復制到客戶端預期的位置。

A.2.17. 文件未找到

如果遇到“ERROR '...'未發現(errno: 23)”無法打開文件:... (errno: 24)”,或來自MySQL的具有errno 23errno 24的其它錯誤,它表示未為MySQL服務器分配足夠的文件描述符。你可以使用perror實用工具來了解錯誤編號的含義:

shell> perror 23
錯誤代碼23:文件表溢出
shell> perror 24
錯誤代碼24:打開文件過多
shell> perror 11
錯誤代碼11:資源暫時不可用

這里的問題是,mysqld正試圖同時打開過多的文件。你可以通知mysqld不要一次打開過多文件,或增加mysqld可用文件描述符的數目。

要想通知mysqld將一次打開的文件控制在較小的數目上,可降低table_cache系統變量的值(),從而減少表高速緩沖(默認值為64)。降低max_connections的值也能降低打開文件的數目(默認值為100)。

要想更改mysqld可用的文件描述符的數目,可在mysqld_safe上使用“--open-files-limit”選項或設置(自MySQL 3.23.30開始)open_files_limit系統變量。請參見5.3.3節,“服務器系統變量”。設置這些值的最簡單方式是在選項文件中增加1個選項。請參見4.3.2節,“使用選項文件”。如果mysqld的版本較低,不支持設置打開文件的數目,可編輯mysqld_safe腳本。在腳本中有1個注釋掉的行ulimit -n 256。你可以刪除#’字符取消對該行的注釋,更改數值256,以設置mysqld可用的文件描述符數目。

“--open-files-limit”ulimit能夠增加文件描述符的數目,但最高不能超過操作系統限制的數目。此外還有1個“硬”限制,僅當以根用戶身份啟動mysqld_safemysqld時才能覆蓋它(請記住,在該情況下,還需使用“--user”選項啟動服務器,以便在啟動后不再以根用戶身份繼續運行)。如果需要增加操作系統限制的對各進程可用文件描述符的數目,請參閱系統文檔。

注釋:如果運行tcsh shellulimit不工作!請求當前限制值時,tcsh還能通報不正確的值。在該情況下,應使用sh啟動mysqld_safe

A.3.?與安裝有關的事宜

A.3.1. 與MySQL客戶端庫的鏈接問題

當你鏈接到應用程序以使用MySQL客戶端庫時,可能會遇到以mysql_開始的未定義引用錯誤,如下所示:

/tmp/ccFKsdPa.o: 在函數`main':
/tmp/ccFKsdPa.o(.text+0xb): `mysql_init'的未定義引用。
/tmp/ccFKsdPa.o(.text+0x31): `mysql_real_connect'的未定義引用。
/tmp/ccFKsdPa.o(.text+0x57): `mysql_real_connect'的未定義引用。
/tmp/ccFKsdPa.o(.text+0x69): `mysql_error'的未定義引用。
/tmp/ccFKsdPa.o(.text+0x9a): `mysql_close'的未定義引用。

通過在鏈接命令后增加“-Ldir_path -lmysqlclient”選項,應能解決該問題,其中,dir_path代表客戶端庫所在目錄的路徑名。要想確定正確的目錄,可嘗試下述命令:

shell> mysql_config --libs

來自mysql_config的輸出可能會指明應在鏈接命令上指定的其他庫。

對于非壓縮或壓縮函數,如果遇到未定義引用錯誤,可在鏈接命令后添加-lz,并再次嘗試。

對于應在系統上存在的函數(如connect),如果遇到未定義引用錯誤,請檢查相關函數的手冊頁,以便確定應在鏈接命令上增加哪些庫。

對于系統上不存在的函數,可能會遇到未定義引用錯誤,如下所示:

mf_format.o(.text+0x201): `__lxstat'的未定義引用。

它通常意味著你的MySQL客戶端庫是在與你的系統不100%兼容的系統上編譯的。在該情況下,應下載最新的MySQL源碼分發版,并自己編譯MySQL。請參見2.8節,“使用源碼分發版安裝MySQL”

當你試圖執行MySQL程序時,可能會遇到運行時未定義引用錯誤。如果這類錯誤指明了以mysql_開始的符號,或指明未發現mysqlclient,這意味著你的系統無法找到共享的libmysqlclient.so庫。對其的更正方式是,通知系統在庫所在位置搜索共享庫。請使用與系統相適應的下述方法:

·         libmysqlclient.so所在目錄的路徑添加到LD_LIBRARY_PATH環境變量中。

·         libmysqlclient.so所在目錄的路徑添加到LD_LIBRARY環境變量中。

·         libmysqlclient.so拷貝到可被系統搜索的目錄下,如/lib,然后通過執行ldconfig更新共享庫信息。

解決該問題的另一種方法是,以靜態方式將你的程序與“-static”選項鏈接在一起,或在鏈接代碼之前刪除動態MySQL庫。使用第2種方法之前,應確保沒有使用動態庫的其它程序。

A.3.2. 如何以普通用戶身份運行MySQL

Windows平臺上,能夠使用普通用戶賬戶以Windows服務方式運行服務器。

Unix平臺上,不是任何用戶都能啟動并運行MySQL服務器mysqld。但是,處于安全方面的原因,應避免以Unix根用戶身份運行服務器。要想更改mysqld,使之能以正常的無特權Unix用戶user_name身份運行,必須采取下述步驟:

如果服務器正在運行,停止它(使用mysqladmin shutdown)。

更改數據庫目錄和文件,允許user_name讀寫其中的文件(可能需要以Unix根用戶身份完成這類設置):

shell> chown -R user_name /path/to/mysql/datadir

如果未這樣做,當以user_name身份運行時,服務器無法訪問數據庫或表。

如果MySQL數據目錄下的目錄或文件采用的是符號鏈接,還需跟蹤這些鏈接,并更改它們指向的目錄和文件。chown –R可幫助你跟蹤符號鏈接。

user_name身份啟動服務器。如果你正在使用MySQL 3.22或更高版本,另一種可選方式是,以Unix根用戶身份啟動mysqld,并使用--user=user_name選項。mysqld啟動,然后在接受任何連接前,切換至Unix用戶user_name并以該用戶身份運行。

要想在系統啟動時自動以給定的用戶身份啟動服務器,可在服務器數據目錄下的/etc/my.cnf選項文件或my.cnf選項文件的[mysqld]組中,通過增加用戶選項來指定用戶名。。例如:

[mysqld]
user=user_name

如果你的Unix機器本身并不安全,應在授權表中為MySQL根賬戶指定密碼。如不然,任何在該機器上具有登錄賬戶的用戶都能使用“--user=root”選項運行mysql客戶端,并執行任何操作。在任何情況下均應為MySQL賬戶指定密碼,這是個好主意,尤其是在服務器主機上存在其他登錄賬戶時,更是如此。請參見2.9節,“安裝后的設置和測試”

A.3.3. 與文件許可有關的問題

如果遇到與文件許可有關的問題,可能數啟動mysqldUMASK環境變量設置得不正確。例如,當你創建表時,MySQL可能會發出下述錯誤消息:

ERROR: 無法找到文件:'path/with/filename.frm' (Errcode: 13)

UMASK的默認值是0660。通過下述方式啟動mysqld_safe,可改變該情況:

shell> UMASK=384  # = 600 in octal
shell> export UMASK
shell> mysqld_safe &

在默認情況下,MySQL0700的許可創建數據庫和RAID目錄。你可以通過設置UMASK_DIR變量更改該行為。如果你設置了它的值,將使用組合的UMASKUMASK_DIR值創建新目錄。例如,如果你打算為所有新的目錄授予組訪問權限,可:

shell> UMASK_DIR=504  # = 770 in octal
shell> export UMASK_DIR
shell> mysqld_safe &

MySQL 3.23.25和更高版本中,如果是以0開始的,MySQL將認為UMASKUMASK_DIR的值均采用八進制形式。

請參見附錄F:環境變量

A.4. 與管理有關的事宜

A.4.1. 如何復位根用戶密碼

如果你從未為MySQL設置根用戶密碼,服務器在以根用戶身份進行連接時不需要密碼。但是,建議你為每個賬戶設置密碼。請參見5.6.1節,“通用安全指南”

如果你以前設置了根用戶密碼,但卻忘記了該密碼,可設置新的密碼。下述步驟是針對Windows平臺的。在本節后面的內容中,介紹了針對Unix平臺的步驟。

Windows平臺下,該步驟是:

以系統管理員身份登錄到系統。

如果MySQL服務器正在運行,停止它。對于作為Windows服務運行的服務器,進入服務管理器:

開始菜單->控制面板->管理工具->服務

然后在列表中找出MySQL服務器,并停止它。

如果服務器不是作為服務而運行的,可能需要使用任務管理器來強制停止它。

創建1個文本文件,并將下述命令置于單一行中:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

用任意名稱保存該文件。在本例中,該文件為C:\mysql-init.txt

打開控制臺窗口,進入DOS命令提示:

開始菜單->運行-> cmd

假定你已將MySQL安裝到C:\mysql。如果你將MySQL安裝到了另一位置,請對下述命令進行相應的調整。

DOS命令提示符下,執行命令:

C:\> C:\mysql\bin\mysqld-nt --init-file=C:\mysql-init.txt

在服務器啟動時,執行由“--init-file”選項命名的文件的內容,更改根用戶密碼。當服務器成功啟動后,應刪除C:\mysql-init.txt

如果你使用MySQL安裝向導安裝了MySQL,或許需要指定“--defaults-file”選項:

C:\> C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld-nt.exe
         --defaults-file="C:\Program Files\MySQL\MySQL Server 5.1\my.ini"
         --init-file=C:\mysql-init.txt

使用服務管理器,可找到恰當的“--defaults-file”設置:

開始菜單->控制面板->管理工具->服務

在列表中找出MySQL服務,右擊,并選擇“屬性”選項。在可執行字段的Path(路徑)中包含“--defaults-file”設置。

停止MySQL服務器,然后在正常模式下重啟它。如果以服務方式運行服務器,應從Windows服務窗口啟動它。如果以手動方式啟動了服務器,能夠像正常情形下一樣使用命令。

應能使用新密碼進行連接。

Unix環境下,重置根用戶密碼的步驟如下:

Unix根用戶身份、或以運行mysqld服務器的相同身份登錄到系統。

找到包含服務器進程ID.pid文件。該文件的準確位置和名稱取決于你的分發版、主機名和配置。常見位置是/var/lib/mysql//var/run/mysqld//usr/local/mysql/data/一般情況下,文件名的擴展名為.pid,并以mysqld或系統的主機名開始。

在下述命令中使用.pid文件的路徑名,向mysqld進程發出正常的kill(而不是kill -9),可停止MySQL服務器:

shell> kill `cat /mysql-data-directory/host_name.pid`

注意,cat命令使用符號“`”而不是“’”:這會使cat的輸出代入到kill命令中。

創建文本文件,并將下述命令放在文件內的1行上:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

用任意名稱保存文件。對于本例,文件為~/mysql-init

用特殊的“--init-file=~/mysql-init”選項重啟MySQL服務器:

shell> mysqld_safe --init-file=~/mysql-init &

文件init-file的內容在服務器啟動時執行,更改根用戶密碼。服務器成功啟動后,應刪除~/mysql-init

應能使用新密碼進行連接。

作為可選方式,在任何平臺上,可使用mysql客戶端設置新密碼(但該方法不夠安全):

停止mysqld,并用“--skip-grant-tables --user=root”選項重啟它Windows用戶可省略--user=root部分)。

使用下述命令連接到mysqld服務器:

shell> mysql -u root

mysql客戶端發出下述語句:

mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd')
    ->                   WHERE User='root';
mysql> FLUSH PRIVILEGES;

用打算使用的實際根用戶密碼替換“newpwd”。

應能使用新密碼進行連接。

A.4.2. 如果MySQL依然崩潰,應作些什么

正式發布之前,每個MySQL版本均在很多平臺上進行了測試。這不表示MySQL中不存在缺陷,但是,如果存在缺陷,它們應很少,而且很難發現。如果你遇到問題,如果你嘗試找出導致系統崩潰的準確原因,這始終很有幫助,這是因為,如果這樣的話,快速解決問題的機會很大。

首先,應嘗試找出問題是否與mysqld服務器有關,或是否與客戶端有關。通過執行mysqladmin version,可檢查mysqld服務器運行了多長時間。如果mysqld宕機并重啟,應查看服務器的錯誤日志以找出原因。請參見5.11.1節,“錯誤日志”

在某些系統上,在錯誤日志中,可發現mysqld宕機的堆棧跟蹤,可使用resolve_stack_dump程序解決它。請參見E.1.4節,“使用堆棧跟蹤”。注意,錯誤日志中的變量值并非始終是100%正確的。

很多服務器崩潰是因損壞的數據文件或索引文件而導致的,每次執行完SQL語句之后并在向客戶端通告結果之前,MySQL將使用write()系統調用更新磁盤上的文件(如果你使用了“--delay-key-write”選項,情況并非如此,此時將寫入數據文件而不是索引文件)。這意味著,即使mysqld崩潰,數據文件的內容也是安全的,這是因為操作系統能保證將未刷新的數據寫入磁盤。使用“--flush”選項啟動mysqld,這樣,每次執行完SQL語句后,可強制MySQL將所有內容寫入磁盤。

前述介紹表明,在正常情況下不會出現損壞的表,除非出現了下述情況之一:

在更新過程中,MySQL服務器或服務器主機被停止。

你發現了mysqld中存在的1個缺陷,該缺陷導致mysqld在更新中途中止。

mysqld操作的同時,某些外部程序正在操控數據文件或索引文件,未恰當鎖定表。

你正使用系統上的相同數據目錄運行很多mysqld服務器,該系統不支持良好的文件系統鎖定(通常是由lockd鎖定管理器負責的),或使用“--skip-external-locking”選項運行了多個服務器。

崩潰的數據文件或索引文件,其中包含導致mysqld混亂的損壞很嚴重的數據。

在數據存儲節點發現缺陷。這種可能性不大,但至少是可能的。在該情況下,可在修復的表副本上,通過使用ALTER TABLE嘗試將表類型更改為另一種存儲引擎。

由于很難得知為什么某事會出現崩潰,首先請檢查用于其他方面的事項是否崩潰。請嘗試采取下述措施:

mysqladmin shutdown停止mysqld服務器,從數據目錄運行myisamchk --silent --force */*.MYI,檢查所有的MyISAM表,并重啟mysqld。這樣,就能確保從干凈的狀態運行服務器。請參見第5章:數據庫管理

使用“--log”選項啟動mysqld,并根據寫入日志的信息確定是否某些特殊的查詢殺死了服務器。約95%的缺陷與特定的查詢有關。正常情況下,這是服務器重啟前日志文件中最夠數個查詢中的1個。請參見5.11.2節,“通用查詢日志”。如果能夠用特殊查詢重復殺死MySQL,即使在發出查詢前檢查了所有表的情況下也同樣,那么你就應能確定缺陷,并應提交關于該缺陷的缺陷報告。請參見1.7.1.3節,“如何通報缺陷和問題”

嘗試提供一個測試范例,我們應能利用該范例重復問題。請參見E.1.6節,“如果出現表崩潰,請生成測試案例”

請在mysql-test目錄下并根據MySQL基準進行測試。請參見27.1.2節,“MySQL測試套件”。它們能相當良好地測試MySQL。你也可以為基準測試增加代碼,以模擬你的應用程序。基準測試可在源碼分發版的sql-bench目錄下找到,對于二進制分發版,可在MySQL安裝目錄下的sql-bench目錄下找到。

嘗試使用fork_big.pl腳本(它位于源碼分發版的測試目錄下)。

如果你將MySQL配置為調試模式,如果某事出錯,可更為容易地搜集關于可能錯誤的信息。如果將MySQL配置為調試模式,可生成1個安全的內存分配程序,可使用它發現某些錯誤。此外,它還提供了很多輸出,這類輸出與出現的問題相關。在configure上使用“--with-debug”或“--with-debug=full”選項重新配置MySQL,然后再編譯它。請參見E.1節,“調試MySQL服務器”

確保為你的操作系統應用了最新的補丁。

mysqld使用“--skip-external-locking”選項。在某些系統上,lockd鎖定管理器不能正確工作,--skip-external-locking”選項通知mysqld不使用外部鎖定。(這意味著,你不能在相同的數據目錄上運行2mysqld服務器,如果使用myisamchk,必須謹慎。然而,嘗試將該選項用作測試也是有益的)。

mysqld看上去正在運行但并未響應時,是否運行了mysqladmin -u root processlist?某些時候,即使你認為mysqld處于閑置狀態時,實際情況并非如此。問題可能是因為所有連接均已使用,或存在某些內部鎖定問題。即使在該情況下,mysqladmin -u root processlist通常能夠進行連接,并能提供關于當前連接數以及其狀態的有用信息。

在運行其他查詢的同時,在單獨的窗口中運行命令mysqladmin -i 5 statusmysqladmin -i 5 -r status,以生成統計信息。

嘗試采用下述方法:

gdb(或另一個調試器)啟動mysqld。請參見E.1.3節,“在gdb環境下調試mysqld

運行測試腳本。

3個較低層面上輸出backtrace(向后跟蹤)和局部變量。在gdb中,當mysqldgdb內崩潰時,可使用下述命令完成該任務:

backtrace
info local
up
info local
up
info local

使用gdb,你還能檢查與info線程共存的線程,并切換至特定的線程N,其中,N是線程ID

嘗試用Perl腳本模擬你的應用程序,強制MySQL崩潰或行為異常。

發送正常的缺陷報告。請參見1.7.1.3節,“如何通報缺陷和問題”。應比通常的報告更詳細。由于MySQL是為很多人提供服務的,它可能因僅存在于你的計算機上的某事崩潰(例如,與你的特定系統庫有關的錯誤)。

如果你遇到與包含動態長度行的表有關的問題,而且你僅使用VARCHAR(而不是BLOBTEXT列),可嘗試用ALTER TABLE將所有VARCHAR列更改為CHAR列。這樣,就會強制MySQL使用固定大小的行。固定大小的行占用的空間略多,但對損壞的容忍度更高。

目前的動態行代碼在MySQL AB已使用多年,很少遇到問題,但從本質上看,動態長度行更傾向于出現錯誤,因此,不妨嘗試采用該策略以查看它是否有幫助,這不失為一個好主意。

診斷問題時不要將你的服務器硬件排除在外。有缺陷的硬件能夠導致數據損壞。對硬件進行故障診斷與排除操作時,尤其應注意RAM和硬盤驅動器。

A.4.3. MySQL處理磁盤滿的方式

在本節中,介紹了MySQL響應磁盤滿錯誤的方式(如“設備上無剩余空間”),以及響應超配額錯誤的方式(如“寫入失敗”或“達到了用戶屏蔽限制”)。

本節介紹的內容與寫入MyISAM表有關。它也適用于寫入二進制日志文件和二進制索引文件,但對row”和“record”的應用應被視為“event”。

出現磁盤滿狀況時,MySQL將:

每分鐘檢查一次,查看是否有足夠空間寫入當前行。如果有足夠空間,將繼續,就像什么也未發生一樣。

10分鐘將1個條目寫入日志文件,提醒磁盤滿狀況。

為了減輕問題,可采取下述措施:

要想繼續,僅需有足夠的磁盤空間以插入所有記錄。

要想放棄線程,必須使用mysqladmin kill。下次檢查磁盤時將放棄線程(1分鐘)。

其他線程可能會正在等待導致磁盤滿狀況的表。如果有數個“已鎖定”的線程,殺死正在磁盤滿狀況下等待的某一線程,以便允許其他線程繼續。

對前述行為的例外是,當你使用REPAIR TABLEOPTIMIZE TABLE時,或當索引是在LOAD DATA INFILEALTER TABLE語句后、在批操作中創建的。所有這些語句能創建大的臨時文件,如果保留這些文件,會導致系統其他部分出現大問題。如果在MySQL執行這類操作的同時磁盤已滿,它將刪除大的臨時文件,并將表標注為崩潰。但對于ALTER TABLE例外,舊表保持不變。

A.4.4. MySQL將臨時文件儲存在哪里

MySQL使用環境變量TMPDIR的值作為保存臨時文件的目錄的路徑名。如果未設置TMPDIRMySQL將使用系統的默認值,通常為/tmp/var/tmp/usr/tmp。如果包含臨時文件目錄的文件系統過小,可對mysqld使用“—tmpdir”選項,在具有足夠空間的文件系統內指定1個目錄。

MySQL 5.1中,“—tmpdir”選項可被設置為數個路徑的列表,以循環方式使用。在Unix平臺上,路徑用冒號字符“:”隔開,在WindowsNetWareOS/2平臺上,路徑用分號字符“;”隔開。注意,為了有效分布負載,這些路徑應位于不同的物理磁盤上,而不是位于相同磁盤的不同分區中。

如果MySQL服務器正作為復制從服務器使用,不應將“--tmpdir”設置為指向基于內存的文件系統的目錄,或當服務器主機重啟時將清空的目錄。對于復制從服務器,需要在機器重啟時仍保留一些臨時文件,以便能夠復制臨時表或執行LOAD DATA INFILE操作。如果在服務器重啟時丟失了臨時文件目錄下的文件,復制將失敗。

MySQL會以隱含方式創建所有的臨時文件。這樣,就能確保中止mysqld時會刪除所有臨時文件。使用隱含文件的缺點在于,在臨時文件目錄所在的位置中,看不到占用了文件系統的大臨時文件。

進行排序時(ORDER BYGROUP BY),MySQL通常會使用1個或多個臨時文件。所需的最大磁盤空間由下述表達式決定:

(length of what is sorted + sizeof(row pointer))
* number of matched rows
* 2

row pointer”(行指針)的大小通常是4字節,但在以后,對于大的表,該值可能會增加。

對于某些SELECT查詢,MySQL還會創建臨時SQL表。它們不是隱含表,并具有SQL_*形式的名稱。

ALTER TABLE會在與原始表目錄相同的目錄下創建臨時表。

A.4.5. 如何保護或更改MySQL套接字文件/tmp/mysql.sock

對于服務器用來與本地客戶端進行通信的Unix套接字文件,其默認位置是/tmp/mysql.sock。這有可能導致問題,原因在于,在某些版本的Unix上,任何人都能刪除/tmp目錄下的文件。

在大多數Unix版本中,可對/tmp目錄進行保護,使得文件只能被其所有這或超級用戶(根用戶)刪除。為此,以根用戶身份登錄,并使用下述命令在/tmp目錄上設置粘著位:

shell> chmod +t /tmp

通過執行ls -ld /tmp,可檢查是否設置了粘著位。如果最后一個許可字符是“t”,表明設置了粘著位。

另一種方法是改變服務器創建Unix套接字文件的位置。如果進行了這類操作,還應讓客戶端程序知道文件的位置。能夠以多種不同方式指定文件位置:

在全局或局部選項文件中指定路徑。例如,將下述行置于文件/etc/my.cnf中:

[mysqld]
socket=/path/to/socket
 
[client]
socket=/path/to/socket

請參見4.3.2節,“使用選項文件”

在運行客戶端程序時,在命令行上為mysqld_safe指定--socket”選項。

MYSQL_UNIX_PORT環境變量設置為Unix套接字文件的路徑。

重新從源碼編譯MySQL,以使用不同的默認Unix套接字文件位置。運行configure時,用“--with-unix-socket-path”選項定義文件路徑。請參見2.8.2節,“典型配置選項

用下述命令連接服務器,能夠測試新的套接字位置是否工作:

shell> mysqladmin --socket=/path/to/socket version

A.4.6. 時區問題

如果遇到與SELECT NOW()有關的問題,它返回GMT值而不是當地時間,就應通知服務器你的當前失去。如果UNIX_TIMESTAMP()返回錯誤值,上述方式同樣適用。應為服務器所運行的環境進行這類設置,例如,在mysqld_safemysql.server中。請參見附錄F:環境變量

也可以對mysqld_safe使用“--timezone=timezone_name”選項,為服務器設置失去。也可以在啟動mysqld之前,通過設置TZ環境變量完成該設置。

--timezone”或TZ的允許值與系統有關。關于可接受的值,請參見操作系統文檔。

A.5. 與查詢有關的事宜

A.5.1. 搜索中的大小寫敏感性

在默認情況下,MySQL搜索不區分大小寫(但某些字符集始終區分大小寫,如czech)。這意味著,如果你使用col_name LIKE 'a%'進行搜索,你將獲得以Aa開始的所有列。如果打算使搜索區分大小寫,請確保操作數之一具有區分大小寫的或二進制校對。例如,如果你正在比較均適用latin1字符集的列和字符串,可使用COLLATE操作符,使1個操作數具有latin1_general_cslatin1_bin校對特性。例如:

col_name COLLATE latin1_general_cs LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_general_cs
col_name COLLATE latin1_bin LIKE 'a%'
col_name LIKE 'a%' COLLATE latin1_bin

如果希望總是以區分大小寫的方式處理列,可使用區分大小寫的或二進制校對聲明它。請參見13.1.5節,“CREATE TABLE語法”

簡單的比較操作(>=, >, =, <, <=, 排序和分組)基于每個字符的“排序值”。具有相同排序值的字符(如‘E, e,和‘??’)將被當作相同的寫字符。

A.5.2. 使用DATE列方面的問題

DATE值的格式是'YYYY-MM-DD'按照標準的SQL,不允許其他格式。在UPDATE表達式以及SELECT語句的WHERE子句中應使用該格式。例如:

mysql> SELECT * FROM tbl_name WHERE date >= '2003-05-05';

為了方便,如果日期是在數值環境下使用的,MySQL會自動將日期轉換為數值(反之亦然)。它還具有相當的智能,在更新時或在與TIMESTAMPDATEDATETIME列比較日期的WHERE子句中,允許寬松的字符串形式(寬松形式表示,任何標點字符均能用作各部分之間的分隔符。例如,'2004-08-15''2004#08#15'是等同的)。MySQL還能轉換不含任何分隔符的字符串(如'20040815'),前體是它必須是有意義的日期。

使用<<==>=>、或BETWEEN操作符將DATETIMEDATETIMETIMESTAMP與常量字符串進行比較時,MySQL通常會將字符串轉換為內部長整數,以便進行快速比較(以及略為“寬松”的字符串檢查)。但是,該轉換具有下述例外:

比較兩列時

DATETIMEDATETIMETIMESTAMP列與表達式進行比較時

使用其他比較方法時,如INSTRCMP()

對于這些例外情形,會將對象轉換為字符串并執行字符串比較,采用該方式進行比較。

為了保持安全,假定按字符串比較字符串,如果你打算比較臨時值和字符串,將使用恰當的字符串函數。

對于特殊日期'0000-00-00',能夠以'0000-00-00'形式保存和檢索。在MyODBC中使用'0000-00-00'日期時,對于MyODBC 2.50.12或更高版本,該日期將被自動轉換為NULL,這是因為ODBC不能處理這類日期。

由于MySQL能夠執行前面所介紹的轉換,下述語句均能正常工作:

mysql> INSERT INTO tbl_name (idate) VALUES (19970505);
mysql> INSERT INTO tbl_name (idate) VALUES ('19970505');
mysql> INSERT INTO tbl_name (idate) VALUES ('97-05-05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997.05.05');
mysql> INSERT INTO tbl_name (idate) VALUES ('1997 05 05');
mysql> INSERT INTO tbl_name (idate) VALUES ('0000-00-00');
 
mysql> SELECT idate FROM tbl_name WHERE idate >= '1997-05-05';
mysql> SELECT idate FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT MOD(idate,100) FROM tbl_name WHERE idate >= 19970505;
mysql> SELECT idate FROM tbl_name WHERE idate >= '19970505';

但是,下述語句不能正常工作:

mysql> SELECT idate FROM tbl_name WHERE STRCMP(idate,'20030505')=0;

STRCMP()是一種字符串函數,它能將idate轉換為'YYYY-MM-DD'格式的字符串,并執行字符串比較。它不能將'20030505'轉換為日期'2003-05-05'并進行日期比較。

如果你正在使用ALLOW_INVALID_DATES SQL模式,MySQL允許以僅執行給定的有限檢查方式保存日期:MySQL僅保證天位于131的范圍內,月位于112的范圍內。

這樣就使得MySQL很適合于Web應用程序,其中,你能獲得三個不同字段中的年、月、日值,也能準確保存用戶插入的值(無日期驗證)。

如果未使用NO_ZERO_IN_DATE SQL模式,“天”和“月”部分可能為0。如果你打算將生日保存在DATE列而且僅知道部分日期,它十分方便。

如果未使用NO_ZERO_DATE SQL模式,MySQL也允許你將'0000-00-00'保存為“偽日期”。在某些情況下,它比使用NULL值更方便。

如果無法將日期轉換為任何合理值,“0”將保存在DATE列中,并被檢索為'0000-00-00'。這是兼顧速度和便利性的事宜。我們認為,數據庫服務器的職責是檢索與你保存的日期相同的日期(即使在任何情況下,數據在邏輯上不正確也同樣)。我們認為,對日期的檢查應由應用程序而不是服務器負責。

如果你希望MySQL檢查所有日期并僅接受合法日期(除非由IGNORE覆蓋),應將sql_mode設置為"NO_ZERO_IN_DATE,NO_ZERO_DATE"

A.5.3. 與NULL值有關的問題

對于SQL的新手,NULL值的概念常常會造成混淆,他們常認為NULL是與空字符串''相同的事。情況并非如此。例如,下述語句是完全不同的:

mysql> INSERT INTO my_table (phone) VALUES (NULL);
mysql> INSERT INTO my_table (phone) VALUES ('');

這兩條語句均會將值插入phone(電話)列,但第1條語句插入的是NULL值,第2條語句插入的是空字符串。第1種情況的含義可被解釋為“電話號碼未知”,而第2種情況的含義可被解釋為“該人員沒有電話,因此沒有電話號碼”。

為了進行NULL處理,可使用IS NULLIS NOT NULL操作符以及IFNULL()函數。

SQL中,NULL與任何其它值的比較(即使是NULL)永遠不會為“真”。包含NULL的表達式總是會導出NULL值,除非在關于操作符的文檔中以及表達式的函數中作了其他規定。下述示例中的所有列均返回NULL

mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);

如果打算搜索列值為NULL的列,不能使用expr = NULL測試。下述語句不返回任何行,這是因為,對于任何表達式,expr = NULL永遠不為

mysql> SELECT * FROM my_table WHERE phone = NULL;

要想查找NULL值,必須使用IS NULL測試。在下面的語句中,介紹了查找NULL電話號碼和空電話號碼的方式:

mysql> SELECT * FROM my_table WHERE phone IS NULL;
mysql> SELECT * FROM my_table WHERE phone = '';

更多信息和示例,請參見3.3.4.6節,“使用NULL值”

如果你正在使用MyISAMInnoDBBDB、或MEMORY存儲引擎,能夠在可能具有NULL值的列上增加1條索引。如不然,必須聲明索引列為NOT NULL,而且不能將NULL插入到列中。

LOAD DATA INFILE讀取數據時,對于空的或丟失的列,將用''更新它們。如果希望在列中具有NULL值,應在數據文件中使用\N。在某些情況下,也可以使用文字性單詞“NULL”。請參見13.2.5 LOAD DATA INFILE語法”

使用DISTINCTGROUP BYORDER BY時,所有NULL值將被視為等同的。

使用ORDER BY時,首先將顯示NULL值,如果指定了DESC按降序排列,NULL值將最后顯示。

對于聚合(累計)函數,如COUNT()MIN()SUM(),將忽略NULL值。對此的例外是COUNT(*),它將計數行而不是單獨的列值。例如,下述語句產生兩個計數。首先計數表中的行數,其次計數age列中的非NULL值數目:

mysql> SELECT COUNT(*), COUNT(age) FROM person;

對于某些列類型,MySQL將對NULL值進行特殊處理。如果將NULL插入TIMESTAMP列,將插入當前日期和時間。如果將NULL插入具有AUTO_INCREMENT屬性的整數列,將插入序列中的下一個編號。

A.5.4. 與列別名有關的問題

可以使用別名來引用GROUP BYORDER BYHAVING子句中的列。別名也能用于為列提供更好的名稱:
SELECT SQRT(a*b) AS root FROM tbl_name GROUP BY root HAVING root > 0;
SELECT id, COUNT(*) AS cnt FROM tbl_name GROUP BY id HAVING cnt > 0;
SELECT id AS 'Customer identity' FROM tbl_name;

標準SQL不允許在WHERE子句中已用列別名。這是因為,執行WHERE代碼時,可能尚未確定列值。例如,下述查詢是非法的:

SELECT id, COUNT(*) AS cnt FROM tbl_name WHERE cnt > 0 GROUP BY id;

執行WHERE語句以確定哪些行應被包含在GROUP BY部分中,而HAVING用于確定應使用結果集中的哪些行。

A.5.5. 非事務表回滾失敗

執行ROLLBACK(回滾)時,如果收到下述消息,表示事務中使用的1個或多個表不支持事務:

警告:某些更改的非事務性表不能被回滾。

這些非事務性表不受ROLLBACK語句的影響。

如果在事務中意外地混合了事務性表和非事務性表,導致該消息的最可能原因是,你認為本應是事務性的表實際上不是。如你試圖使用mysqld服務器不支持的事務性存儲引擎(或用啟動選項禁止了它)創建表,就可能出現該情況。如果mysqld不支持存儲引擎,它將以MyISAM表創建表,這是非事務性表。

可使用下述語句之一檢查表的標類型:

SHOW TABLE STATUS LIKE 'tbl_name';
SHOW CREATE TABLE tbl_name;

請參見13.5.4.18節,“SHOW TABLE STATUS語法以及13.5.4.5節,“SHOW CREATE TABLE語法”

使用下述語句,可檢查mysqld服務器支持的存儲引擎:

SHOW ENGINES;

也可以使用下述語句,檢查與你感興趣的存儲引擎有關的變量值:

SHOW VARIABLES LIKE 'have_%';

例如,要想確定InnoDB存儲引擎是否可用,可檢查have_innodb變量的值。

請參見13.5.4.8節,“SHOW ENGINES語法”13.5.4.21節,“SHOW VARIABLES語法”

A.5.6. 從相關表刪除行

如果針對related_tableDELETE語句的總長度超過1MB(系統變量max_allowed_packet的默認值),應將其分為較小的部分,并執行多個DELETE語句。如果related_column是索引列,為每條語句指定1001000related_column值,或許能獲得更快的DELETE速度。如果related_column不是索引列,速度與IN子句中的參量數目無關。

A.5.7. 解決與不匹配行有關的問題

如果有使用了很多表的復雜查詢,但未返回任何行,應采用下述步驟找出什么出錯:

EXPLAIN測試查詢,以檢查是否發現某事顯然出錯。請參見7.2.1節,“EXPLAIN語法(獲取關于SELECT的信息)

僅選擇在WHERE子句中使用的列。

從查詢中1次刪除1個表,直至返回了某些行為止。如果表很大,較好的主意是在查詢中使用LIMIT 10

對于具有與上次從查詢中刪除的表匹配的行的列,發出SELECT查詢。

如果將FLOATDOUBLE列與具有數值類型的數值進行比較,不能使用等式(=)比較。在大多數計算機語言中,該問題很常見,這是因為,并非所有的浮點值均能以準確的精度保存。在某些情況下,將FLOAT更改為DOUBLE可更正該問題。請參見A.5.8節,“與浮點比較有關的問題”

如果仍不能找出問題之所在,請創建能與顯示問題的“mysql test < query.sql”一起運行的最小測試。通過使用mysqldump --quick db_name tbl_name_1 ... tbl_name_n > query.sql轉儲表,可創建測試文件。在編輯器中打開文件,刪除某些插入的行(如果有超出演示問題所需的行),并在文件末尾添加SELECT語句。

通過執行下述命令,驗證測試文件能演示問題:

shell> mysqladmin create test2
shell> mysql test2 < query.sql

使用mysqlbug將測試文件張貼到喲娜通用MySQL郵件列表。請參見1.7.1.1節,“The MySQL郵件列表”

A.5.8. 與浮點比較有關的問題

注意,下述部分主要與DOUBLEFLOAT列相關,原因在于浮點數的不準確本質。MySQL使用64位十進制數值的精度執行DECIMAL操作,當處理DECIMAL列時,應能解決大多數常見的不準確問題。

浮點數有時會導致混淆,這是因為它們無法以準確值保存在計算機體系結構中。你在屏幕上所看到的值通常不是數值的準確值。對于FLOATDOUBLE列類型,情況就是如此。DECIMAL列能保存具有準確精度的值,這是因為它們是由字符串表示的。

在下面的示例中,介紹了使用DOUBLE時的問題:

mysql> CREATE TABLE t1 (i INT, d1 DOUBLE, d2 DOUBLE);
mysql> INSERT INTO t1 VALUES (1, 101.40, 21.40), (1, -80.00, 0.00),
    -> (2, 0.00, 0.00), (2, -13.20, 0.00), (2, 59.60, 46.40),
    -> (2, 30.40, 30.40), (3, 37.00, 7.40), (3, -29.60, 0.00),
    -> (4, 60.00, 15.40), (4, -10.60, 0.00), (4, -34.00, 0.00),
    -> (5, 33.00, 0.00), (5, -25.80, 0.00), (5, 0.00, 7.20),
    -> (6, 0.00, 0.00), (6, -51.40, 0.00);
 
mysql> SELECT i, SUM(d1) AS a, SUM(d2) AS b
    -> FROM t1 GROUP BY i HAVING a <> b;
 
+------+-------+------+
| i    | a     | b    |
+------+-------+------+
|    1 |  21.4 | 21.4 |
|    2 |  76.8 | 76.8 |
|    3 |   7.4 |  7.4 |
|    4 |  15.4 | 15.4 |
|    5 |   7.2 |  7.2 |
|    6 | -51.4 |    0 |
+------+-------+------+

結果是正確的。盡管前5個記錄看上去不應能進行比較測試(ab的值看上去沒有什么不同),但它們能進行比較,這是因為顯示的數值間的差異在十分位左右,具體情況取決于計算機的體系結構。

如果列d1d2定義為DECIMAL而不是DOUBLESELECT查詢的結果僅包含1行,即上面顯示的最后1行。

A.6. 與優化器有關的事宜

MySQL采用了基于開銷的優化器,以確定處理查詢的最解方式。在很多情況下,MySQL能夠計算最佳的可能查詢計劃,但在某些情況下,MySQL沒有關于數據的足夠信息,不得不就數據進行“有教養”的估測。

MySQL未能做“正確的”事時,可使用下述工具來幫助MySQL

使用EXPLAIN語句獲取關于MySQL如何處理查詢的信息。要想使用它,可在SELECT語句前添加關鍵字EXPLAIN

mysql> EXPLAIN SELECT * FROM t1, t2 WHERE t1.i = t2.i;

關于EXPLAIN的詳細討論,請參見7.2.1節,“EXPLAIN語法(獲取關于SELECT的信息)

使用ANALYZE TABLE tbl_name,為已掃描的表更新鍵分配。請參見13.5.2.1節,“ANALYZE TABLE語法”

為已掃描的表使用FORCE INDEX,通知MySQL:與使用給定的索引相比,表掃描開銷昂貴。請參見13.2.7節,“SELECT語法”

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
WHERE t1.col_name=t2.col_name;

USE INDEXIGNORE INDEX也有一定的幫助。

關于全局和表級別的STRAIGHT_JOIN。請參見13.2.7節,“SELECT語法”

你可以調節全局或線程類系統變量。例如,用“--max-seeks-for-key=1000選項啟動mysqld,或使用“SET max_seeks_for_key=1000”來通知優化器:假定任何表掃描均不會導致1000個以上的鍵搜索。請參見5.3.3節,“服務器系統變量”

A.7. 與表定義有關的事宜

A.7.1. 與ALTER TABLE有關的問題

ALTER TABLE將表更改為當前字符集。如果在執行ALTER TABLE操作期間遇到重復鍵錯誤,原因在于新的字符集將2個鍵映射到了相同值,或是表已損壞。在后一種情況下,應在表上運行REPAIR TABLE

如果ALTER TABLE失敗并給出下述錯誤,問題可能是因為在ALTER TABLE操作的早期階段出現MySQL崩潰,沒有名為A-xxxB-xxx的舊表:

Error on rename of './database/name.frm'
to './database/B-xxx.frm' (Errcode: 17)

在該情況下,進入MySQL數據目錄,并刪除其名稱為以A-B-開始的所有文件(或許你希望將它們移動到其他地方而不是刪除它們)。

ALTER TABLE的工作方式如下:

  • 用請求的結構變化創建名為A-xxx的新表。
  • 將所有行從原始表拷貝到A-xxx
  • 將原始表重命名為B-xxx
  • A-xxx重命名為原始表的名稱。
  • 刪除B-xxx

如果在重命名操作中出錯,MySQL將嘗試撤銷更改。如果錯誤很嚴重(盡管這不應出現),MySQL會將舊表保留為B-xxx。簡單地在系統級別上重命名表文件,應能使數據復原。

如果在事務性表上使用ALTER TABLE,或正在使用WindowsOS/2操作系統,如果已在表上執行了LOCK TABLE操作,ALTER TABLE將對表執行解鎖操作。這是因為InnoDB和這類操作系統不能撤銷正在使用的表。

A.7.2. 如何更改表中的列順序

首先,請考慮是否的確需要更改表中的列順序。SQL的核心要點是從數據存儲格式獲取應用。總應指定檢索數據的順序。在下面的第1條語句中,以col_name1col_name2col_name3順序返回列;在第2條語句中,以col_name1col_name3col_name2順序返回列:

mysql> SELECT col_name1, col_name2, col_name3 FROM tbl_name;
mysql> SELECT col_name1, col_name3, col_name2 FROM tbl_name;

如果決定更改表列的順序,可執行下述操作:

  1. 用具有新順序的列創建新表。
  2. 執行該語句:
mysql> INSERT INTO new_table
    -> SELECT columns-in-new-order FROM old_table;
  1. 撤銷或重命名old_table
  2. 將新表重命名為原始名稱:
mysql> ALTER TABLE new_table RENAME old_table;

SELECT *十分適合于測試查詢。但是,在應用程序中,永遠不要依賴SELECT *的使用,不要依賴根據其位置檢索列。如果添加、移動或刪除了列,所返回的列的順序和位置不會保持相同。對表結構的簡單更改也會導致應用程序失敗。

A.7.3.?TEMPORARY TABLE問題

下面介紹了對使用TEMPORARY表的限制:

  • TEMPORARY表只能是HEAPISAMMyISAMMERGE、或InnoDB類型。
  • 在相同的查詢中,不能引用TEMPORARY1次以上。例如,下例不能正常工作:
mysql> SELECT * FROM temp_table, temp_table AS t2;
錯誤1137:不能再次打開表:'temp_table'
  • SHOW TABLES語句不會列出TEMPORARY表。
  • 不能使用RENAME重命名TEMPORARY表。但能使用ALTER TABLE取而代之:
mysql> ALTER TABLE orig_name RENAME new_name;

A.8. MySQL中的已知事宜

在本節中,列出了當前MySQL版本中的已知事宜。

關于平臺相關事宜的更多信息,請參見2.12節,“具體操作系統相關的注意事項”附錄E:移植到其他系統中的安裝和移植說明。

A.8.1. MySQL中的打開事宜

下面列出了已知問題,更正它們具有較高的優先級:

  • 如果將NULL值與使用ALL/ANY/SOME的子查詢進行比較,而且子查詢返回空的結果,比較操作會評估NULL的非標準結果而不是TRUEFALSE。在MySQL 5.1中將更正該問題。
  • 對于IN的線子查詢優化不像“=”那樣有效。
  • 即使使用了lower_case_table_names=2(允許MySQL記住數據庫名和表名使用的大小寫),對于函數DATABASE()或在各種日志內(在不區分大小寫的系統上),MySQL也不會記住數據庫名使用的大小寫情況。
  • 在復制操作中,撤銷FOREIGN KEY約束不工作,這是因為約束可能在從服務器上有另一個名稱。
  • REPLACE(以及具有REPLACE選項的LOAD DATA)不會觸發ON DELETE CASCADE
  • 如果未使用所有列而且僅使用DISTINCT列表中的列,在GROUP_CONCAT()中,DISTINCT不能與ORDER BY一起工作。
  • 如果1位用戶擁有長時間運行的事務,而且另1位用戶撤銷了在事務中更新的某1表,那么在表用于事務本身之前,存在較小的機會,會在二進制日志中包含DROP TABLE命令。我們計劃更正該問題,方法是讓DROP TABLE命令等待,直至表未在任何事務中使用為止。
  • 將大的整數值(介于2632641之間)插入數值或字符串列時,它將作為負值插入,這是因為該數值是在有符號整數環境下評估的。
  • 如果服務器運行在不具備二進制日志功能的條件下,FLUSH TABLES WITH READ LOCK不能屏蔽COMMIT,執行完整備份時這可能會導致問題(表間的一致性問題)。
  • 在某些情況下,作用在BDB表上的ANALYZE TABLE會導致表不可用,直至重啟mysqld為止。如果出現該情況,請在MySQL錯誤文件中查找下述形式的錯誤:
001207 22:07:56  bdb:  log_flush: LSN past current end-of-log
  • 在所有事務完成之前,不要在BDB表(正在其上運行多語句事務)上執行ALTER TABLE(可能會忽略事務)。
  • 對于正在使用INSERT DELAYED的表,在其上執行ANALYZE TABLEOPTIMIZE TABLEREPAIR TABLE時,可能會導致問題。
  • 在表上執行LOCK TABLE ...FLUSH TABLES ...時,不保證沒有完成一半的事務。
  • BDB打開的速度相對較慢。如果你在數據庫上有很多BDB表,如果未使用“-A”選項或正使用再混編功能,要想在數據庫上使用mysql客戶端,需要花費較長的時間。當你有大的表高速緩沖時,這點尤其明顯。
  • 復制功能采用了查詢級日志功能:主服務器將已執行的查詢寫入二進制日志。這是一種速度很快、簡潔和有效的記錄方法,在大多數情況下工作良好。

如果以特定的方式設計查詢,使得數據更改是非決定性(通常不推薦,即使在復制之外也同樣),主服務器和從服務器上的數據將變得不同。

例如:

  • 將0或NULL值插入AUTO_INCREMENT列中的CREATE ... SELECT或INSERT ... SELECT語句。
  • DELETE,如果從具有ON DELETE CASCADE屬性的外鍵的表中刪除行。
  • REPLACE ... SELECT、INSERT IGNORE ... SELECT,如果在插入的數據中具有重復鍵。

當且僅當前述查詢沒有保證決定行順序的ORDER BY子句時。

例如,對于不具有ORDER BYINSERT ... SELECTSELECT可能會以不同的順序返回行(它會導致具有不同等級的行,從而導致AUTO_INCREMENT列中的不同數值),具體情況取決于優化器在主服務器和從服務器上所作的選擇。

在主服務器和從服務器上,查詢將進行不同的優化,僅當:

  • 使用不同的存儲引擎在主服務器上而不是從服務器上保存表。(能夠在主服務器和從服務器上使用不同的存儲引擎。例如,如果從服務器具有較少的可用磁盤空間,可以在主服務器上使用InnoDB,但在 從服務器桑使用MyISAM)。
  • 在主服務器和從服務器上,MySQL緩沖區大小是不同的(key_buffer_size等)。
  • 在主服務器和從服務器上運行不同的MySQL版本,版本間的優化器代碼也不同。

該問題也會影響使用mysqlbinlog|mysql的數據庫恢復。

避免該問題的最簡單方法是,為前述的非決定性查詢增加ORDER BY子句,以確保總是以相同的順序保存或更改行。

在將來的MySQL版本中,需要時,我們將自動增加ORDER BY子句。

下面列出了已知的事宜,這些事宜將在恰當的時候更正:

  • 日志文件名基于服務器主機名(如果未使用啟動選項指定文件名的話)。如果更改了主機名,你將不得不使用諸如“--log-bin=old_host_name-bin”等選下美國。另一種選擇是重命名舊文件,以反映主機名變更情況(如果是二進制日志,需要編輯二進制日志索引文件,并更正binlog名稱)。請參見5.3.1節,“mysqld命令行選項”
  • Mysqlbinlog不刪除執行LOAD DATA INFILE命令后遺留的臨時文件。請參見8.6節,“mysqlbinlog:用于處理二進制日志文件的實用工具”
  • RENAME不能與TEMPORARY表一起工作,也不能與MERGE表中使用的表一起工作。
  • 由于表定義文件的保存方式,不能在表名、列名或枚舉中使用字符255CHAR(255))。按照安排,當我們實施了新的表定義格式文件時,將在5.1版中更正該問題。
  • 使用SET CHARACTER SET時,不能在數據庫、表和列名中使用轉換的字符。
  • 不能在LIKE ... ESCAPE中與ESCAPE一起使用_’或‘%’。
  • 如果你有1DECIMAL列,其中,相同的數值以不同的格式保存(例如,+01.001.0001.00),GROUP BY可能會將每個值當作不同的值。
  • 使用MIT-pthreads時,不能在另一個目錄下創建服務器。這是因為它需要更改MIT-pthreads,我們不太會更正該問題。請參見2.8.5 MIT-pthreads注意事項”
  • GROUP BYORDER BYDISTINCT中,不能可靠地使用BLOBTEXT值。在這類情況下,與BLOB值進行比較時,僅使用最前的max_sort_length字節。max_sort_length的默認值是1024,可在服務器啟動時或運行時更改它。
  • 數值計算是使用BIGINTDOUBLE(正常情況下均為64位長)進行的。你所能獲得的精度取決于函數。通用規則是位函數是按BIGINT精度執行的,IFELT()是按BIGINTDOUBLE精度執行的,其余的函數是按DOUBLE精度執行的。對于除位字段外的其他數,如果大于63位(9223372036854775807),應避免使用無符號長long值。
  • 1個表中,最多能有255ENUMSET列。
  • MIN()MAX()以及其他聚合函數中,MySQL目前會根據其字符串值比較ENUMSET列,而不是根據字符串在集合中的相對位置。
  • mysqld_safe會將來自mysqld的所有消息再定向到mysqld日志。與之相關的1個問題是,如果你執行mysqladmin refresh關閉并再次打開日志,stdoutstderr仍會被重定向到舊的日志。如果你以廣義方式使用“--log”應編輯mysqld_safe以記錄到host_name.err而不是host_name.log,以便通過刪除它并執行mysqladmin refresh,方便地收回為舊日志分配的空間。
  • UPDATE語句中,列從左向右更新。如果引用了已更新的列,你將得到更新值而不是原始值。例如,下述語句會將KEY增加2,而不是1
mysql> UPDATE tbl_name SET KEY=KEY+1,KEY=KEY+1;
  • 你可以在相同查詢中引用多個臨時表,但不能引用任何給定的臨時表1次以上。例如,下述語句不能正常工作:
mysql> SELECT * FROM temp_table, temp_table AS t2;
錯誤1137:不能再次打開表:'temp_table'
  • 當你在聯合操作中使用“隱含”列時,與未使用隱含列相比,優化器將以不同的方式處理DISTINCT。在聯合操作中,隱含列將作為結果的組成部份計數(即使未顯示),但在正常查詢中,隱含列不參與DISTINCT比較。在以后,我們可能會更改該情況,在執行DISTINCT時不比較隱含列。

例如:

SELECT DISTINCT mp3id FROM band_downloads
       WHERE userid = 9 ORDER BY id DESC;

以及

SELECT DISTINCT band_downloads.mp3id
       FROM band_downloads,band_mp3
       WHERE band_downloads.userid = 9
       AND band_mp3.id = band_downloads.mp3id
       ORDER BY band_downloads.id DESC;

在第2種情況下,使用MySQL服務器3.23.x,可在結果集中獲得2個等同行(這是因為,隱藏ID列中的值可能不同)。

注意,在結果集中,僅對不含ORDER BY列的查詢才會出現該情況。

  • 如果在返回空集的查詢上執行PROCEDURE,在某些情況下,PROCEDURE不轉換列。
  • 創建具有MERGE類型的表時,不檢查基本表是否具有兼容的類型。
  • 如果使用ALTER TABLEMERGE表中使用的表增加了UNIQUE索引,然后在MERGE表上增加了正常索引,如果在表中存在舊的、非UNIQUE鍵,對于這些表,鍵順序是不同的。這是因為,ALTER TABLE會將UNIQUE索引放在正常索引之前,以便能盡早檢測到重復的鍵。
 

這是MySQL參考手冊的翻譯版本,關于MySQL參考手冊,請訪問dev.mysql.com。原始參考手冊為英文版,與英文版參考手冊相比,本翻譯版可能不是最新的。

广西11选五走势图彩经网